pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: David Roussel <pgsql-performance@diroussel.xsmail.com>
To: Rodrigo Madera <rodrigo.madera@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Best way to check for new data.
Date: Mon, 31 Oct 2005 13:43:12 +0000
Message-ID: <43661F70.6060006@diroussel.xsmail.com> (raw)
In-Reply-To: <3cf983d0510281439s648fac45vbd7af544eeca4fc3@mail.gmail.com>
References: <3cf983d0510281439s648fac45vbd7af544eeca4fc3@mail.gmail.com>

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

view thread (4+ messages)  latest in thread

Message-ID: <43661F70.6060006@diroussel.xsmail.com>
Permalink:  ../43661F70.6060006@diroussel.xsmail.com/
Also on:    postgresql.org/message-id/43661F70.6060006@diroussel.xsmail.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pgsql-performance@diroussel.xsmail.com, rodrigo.madera@gmail.com
  Subject: Re: Best way to check for new data.
  In-Reply-To: <43661F70.6060006@diroussel.xsmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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