Rodrigo Madera wrote:
I have a table that holds entries as in a ficticious table Log(id integer, msg text).
Lets say then that I have the program log_tail that has as it´s sole purpose to print newly added data elements.
What is the best solution in terms of performace?
I have a system that does this. We do it by PK, the PK is bigint, and always increases, the client remembers the last key seen as queries based on that key...
select ... where events.event_id > ?::bigint order by events.event_id limit 2000
it works, but when alot of data is added, it can become sensative to the index statistics getting out of sync with the data. Best to insert, then update the statistics, then read the data. For us these three activities are independent, but it still seems to work.
I'd investigate the notify mechanism suggested by Otto if you can afford to use a postgres specific mechanism like that.
David