Обсуждение: Best way to check for new data.

----- Original Message -----

From: Rodrigo Madera

To: pgsql-performance@postgresql.org

Sent: Friday, October 28, 2005 11:39 PM

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

 

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

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