Date: Thu, 12 Apr 2007 13:54:01 -0400
From: Robert Treat <xzilla@users.sourceforge.net>
Subject: Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY"
	"MAX_ROWS=1000"
In-reply-to: <46139132.8000807@andromeiberica.com>
To: pgsql-performance@postgresql.org, arnaulist@andromeiberica.com
Cc: Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
Message-id: <200704121354.01896.xzilla@users.sourceforge.net>
MIME-version: 1.0
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7bit
Content-disposition: inline
References: <4612A421.4060808@andromeiberica.com>
	<20070404110832.GA1244@mail.planetcobalt.net>
	<46139132.8000807@andromeiberica.com>
User-Agent: KMail/1.9.1

On Wednesday 04 April 2007 07:51, Arnau wrote:
> Hi Ansgar ,
>
> > On 2007-04-04 Arnau wrote:
> >> Josh Berkus wrote:
> >>>> Is there anything similar in PostgreSQL? The idea behind this is how
> >>>> I can do in PostgreSQL to have tables where I can query on them very
> >>>> often something like every few seconds and get results very fast
> >>>> without overloading the postmaster.
> >>>
> >>> If you're only querying the tables every few seconds, then you don't
> >>> really need to worry about performance.
> >>
> >> Well, the idea behind this is to have events tables, and a monitoring
> >> system polls that table every few seconds.  I'd like to have a kind of
> >> FIFO stack. From "the events producer" point of view he'll be pushing
> >> rows into that table, when it's filled the oldest one will be removed
> >> to leave room to the newest one. From "the consumer" point of view
> >> he'll read all the contents of that table.
> >>
> >> So I'll not only querying the tables, I'll need to also modify that
> >> tables.
> >
> > Ummm... this may be a dumb question, but why are you trying to implement
> > something like a FIFO with an RDBMS in the first place? Wouldn't it be
> > much easier to implement something like that as a separate program or
> > script?
>
> Well, the idea is have a table with a maximum number of rows. As the
> number of queries over this table will be very high, I'd like to keep it
> as small as possible and without indexes and so on that could make the
> update slower.
>
> Maybe it's the moment to change my question, is there any trick to get a
> table that can be modified/queried very fast and with the minimum of
> overhead? This table will have several queries every second and I'd like
> to do this as fast as possible
>

If you're wedded to the FIFO idea, I'd suggest reading this:
http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL