Thread: Best way to check for new data.
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?
Thank you for your time,
Rodrigo
Rodrigo,
You could use LISTEN + NOTIFY with triggers.
In after_insert_statement trigger you could notify a listener, the client could query it immediately.
Best Regards,
Otto
----- Original Message -----From: Rodrigo MaderaSent: Friday, October 28, 2005 11:39 PMSubject: [PERFORM] Best way to check for new data.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?Thank you for your time,Rodrigo
Rodrigo Madera wrote:
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
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...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?
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
There are a few ways to do this...thinking about it a bit, I would add a timestamp column to your log table (indexed) andkeep a control table which keeps track of the last log print sweep operation. The print operation would just do select * from log where logtime > (select lastlogtime()); The idea here is not to have to keep track of anything on the log table like a flag indicating print status, which will causesome bloat issues. All you have to do is reindex once in a while. lastlogtime() is a function which returns the last log time sweep from the control table. we use a function declared immutableto force planner to treat as a constant (others might tell you to do different here). Merlin ________________________________________ From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Rodrigo Madera Sent: Friday, October 28, 2005 5:39 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Best way to check for new data. 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? Thank you for your time, Rodrigo