X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 32169D8127
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 31 Oct 2005 09:43:18 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 83523-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 31 Oct 2005 13:43:14 +0000 (GMT)
Received: from out3.smtp.messagingengine.com (out3.smtp.messagingengine.com
	[66.111.4.27])
	by svr1.postgresql.org (Postfix) with ESMTP id 68716D8043
	for <pgsql-performance@postgresql.org>;
	Mon, 31 Oct 2005 09:43:16 -0400 (AST)
Received: from frontend1.internal (mysql-sessions.internal [10.202.2.149])
	by frontend1.messagingengine.com (Postfix) with ESMTP id 6205CCD72EA;
	Mon, 31 Oct 2005 08:43:19 -0500 (EST)
Received: from frontend2.messagingengine.com ([10.202.2.151])
	by frontend1.internal (MEProxy); Mon, 31 Oct 2005 08:43:19 -0500
X-Sasl-enc: dLh5is3gOxouDs/jj8w8C/vuaXzlGFcm/XQE5gGeAO3I 1130766198
Received: from [149.182.235.91] (unknown [129.230.248.1])
	by frontend2.messagingengine.com (Postfix) with ESMTP id 4DF9257070D;
	Mon, 31 Oct 2005 08:43:17 -0500 (EST)
Message-ID: <43661F70.6060006@diroussel.xsmail.com>
Date: Mon, 31 Oct 2005 13:43:12 +0000
From: David Roussel <pgsql-performance@diroussel.xsmail.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Rodrigo Madera <rodrigo.madera@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Best way to check for new data.
References: <3cf983d0510281439s648fac45vbd7af544eeca4fc3@mail.gmail.com>
In-Reply-To: <3cf983d0510281439s648fac45vbd7af544eeca4fc3@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------090204000504050705010008"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001]
X-Spam-Score: 0.001
X-Spam-Level: 
X-Archive-Number: 200510/468
X-Sequence-Number: 15236

This is a multi-part message in MIME format.
--------------090204000504050705010008
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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



--------------090204000504050705010008
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#333333">
Rodrigo Madera wrote:
<blockquote
 cite="mid3cf983d0510281439s648fac45vbd7af544eeca4fc3@mail.gmail.com"
 type="cite">
  <meta http-equiv="Context-Type"
 content="text/html; charset=ISO-8859-1">
  <div>I have a table that holds entries as in a ficticious table
Log(id integer, msg text).</div>
  <div>&nbsp;</div>
  <div>Lets say then that I have the program log_tail that has as it&acute;s
sole purpose to print newly added data elements.</div>
  <div>&nbsp;</div>
  <div>What is the best solution in terms of performace?</div>
</blockquote>
I have a system that does this.&nbsp; 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...<br>
<br>
select ... where events.event_id &gt; ?::bigint order by
events.event_id limit 2000<br>
<br>
it works, but when alot of data is added, it can become sensative to
the index statistics getting out of sync with the data.&nbsp; Best to
insert, then update the statistics, then read the data.&nbsp; For us these
three activities are independent, but it still seems to work.<br>
<br>
I'd investigate the notify mechanism suggested by Otto if you can
afford to use a postgres specific mechanism like that.<br>
<br>
David<br>
<br>
<br>
</body>
</html>

--------------090204000504050705010008--