pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBest way to check for new data.
4+ messages / 4 participants
[nested] [flat]
* Best way to check for new data.
@ 2005-10-28 21:39 Rodrigo Madera <rodrigo.madera@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Rodrigo Madera @ 2005-10-28 21:39 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Best way to check for new data.
@ 2005-10-28 22:13 Havasvölgyi Ottó <h.otto@freemail.hu>
parent: Rodrigo Madera <rodrigo.madera@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Havasvölgyi Ottó @ 2005-10-28 22:13 UTC (permalink / raw)
To: Rodrigo Madera <rodrigo.madera@gmail.com>; pgsql-performance
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 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Best way to check for new data.
@ 2005-10-31 13:43 David Roussel <pgsql-performance@diroussel.xsmail.com>
parent: Rodrigo Madera <rodrigo.madera@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David Roussel @ 2005-10-31 13:43 UTC (permalink / raw)
To: Rodrigo Madera <rodrigo.madera@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Best way to check for new data.
@ 2005-10-31 13:47 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 4+ messages in thread
From: Merlin Moncure @ 2005-10-31 13:47 UTC (permalink / raw)
To: Rodrigo Madera <rodrigo.madera@gmail.com>; +Cc: pgsql-performance
There are a few ways to do this...thinking about it a bit, I would add a timestamp column to your log table (indexed) and keep 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 cause some 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 immutable to 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-10-31 13:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-28 21:39 Best way to check for new data. Rodrigo Madera <rodrigo.madera@gmail.com>
2005-10-28 22:13 ` Havasvölgyi Ottó <h.otto@freemail.hu>
2005-10-31 13:43 ` David Roussel <pgsql-performance@diroussel.xsmail.com>
2005-10-31 13:47 Re: Best way to check for new data. Merlin Moncure <merlin.moncure@rcsonline.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox