Re: Postgresql performace question

> device. As mentioned above I may want to be able to sequentially read records
> from the database to be able to recreate the data stream that I received and
> recored. I would need to be able to read the records back fast enough to
> replay the at the same rate at which I received them.

It's only 20 /second, and it doesn't appear as if you'll be getting
bursts -- so it should be easy to handle. The trick is getting around
the lack of maintenance time.

7.4 will be much better suited for this application due to the index
corrections, however, in the mean time you'll need to do a little
trickery.

That is, create a new table for each ~month or so, then copy the last 24
hours of data into the new table, and change a marker so that queries go
against it. A month puts 54M records into the table -- not such a big
deal if you have good indexes.

Create two 'control' tables. One points to the table to do queries
from, the second points to the table(s) to inject new data into. Tech
your interface and data loader to use these to determine where to run
queries out of. An alternative approach is to use views and rules to
make it transparent -- alter the view to point at the new data source.

During the transition stage, you'll be querying out of the old table,
while doing the data load into both old and new with the data loader,
and copying the last 24 hours worth of data into the new table.

Once new table is ready (has data copied) update the 'query' control
table to have the interface query the new table. You can then drop the
old one.

It's a kludgy way of doing it, but gets around PostgreSQLs growing index
issue. Anything else and you'll see hick-ups during maintenance. MySQL
will require a similar technique, but for different reasons.

As stated above, PostgreSQL 7.4 is a few steps closer to being able to
deal with large table churn in a 24/7 environment. If you do the table
swap on a monthly basis, you could do a bulk archive of that tables data
very easily -- in which case you'll almost never need to vacuum ;)

A different (and perhaps easier) approach is to create a partial index
for each day doing everything else as you normally would on a single
table. Since you will have a segmented index, you can easily remove the
old indexes once the data has been archived. Once again, the goal is to
allow the index to shrink.

This will require you to vacuum frequently and have a large FSM. It's
also preferred you do a gradual archival of data and not in bulk.
Archive 10 minutes worth every 10 minutes after 3 days have past rather
than an entire days at once.

> On Sunday 02 March 2003 06:35 pm, Rod Taylor wrote:
> > On Sun, 2003-03-02 at 18:52, Mark Jones wrote:
> > > Hello
> > >
> > > I am working on a project that acquires real-time data from an external
> > > device that I need to store and be able to search through and retrieve
> > > quickly. My application receives packets of data ranging in size from 300
> > > to 5000 bytes every 50 milliseconds for the minimum duration of 24 hours
> > > before the data is purged or archived off disk. There are several fields
> > > in the data that I like to be able to search on to retrieve the data at
> > > later time. By using a SQL database such as Postgresql or Mysql it seams
> > > that it would make this task much easier. My questions are, is a SQL
> > > database such as Postgresql able to handle this kind of activity saving a
> > > record of 5000 bytes at rate of 20 times a second, also how well will it
> > > perform at searching through a database which contains nearly two million
> > > records at a size of about 8 - 9 gigabytes of data, assuming that I have
> > > adequate computing hardware. I am trying to determine if a SQL database
> > > would work well for this or if I need to write my own custom database for
> > > this project. If anyone has any experience in doing anything similar with
> > > Postgresql I would love to know about your findings.
> >
> > Not such a big load depending on the complexity of the queries being
> > performed. From the sounds of it, they're pretty simple.
> >
> > The real question is, the data collection is in real-time, but can you
> > have a maintenance window (6 hours a week?) to do things like REINDEX?
> >
> > Can you use a parallel loader or does it need to be loaded sequentially?
> >
> > Will the data purge be spread over time or come in large lumps?
> >
> >
> > Simple queries on 2 million records happen in low double digit
> > milliseconds on relatively low end hardware with a decent index -- but
> > complex queries can take minutes on 1/10000th the dataset.
> >
> > 20 inserts / second shouldn't be difficult to achieve even on the lowest
> > end hardware -- my laptop can do that -- giving each packet it's own
> > transaction.
> >
> > But if it's 20 packets with parts being inserted into 50 tables (now
> > 1000 inserts / second) things could become a little trickier -- but
> > still easily doable.
> >
> > The most important question is the maintenance window.
--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc