Re: Best way to handle multi-billion row read-only table?

On 2/9/2010 12:47 PM, Asher wrote:
> Hello.
>
> I'm putting together a database to store the readings from various
> measurement devices for later processing. Since these things (water
> pressure monitors attached to very large water pipes) take readings at
> 200Hz and are typically deployed over multiple sites for several
> months at a time I've got many billions of rows of data, each (at the
> moment) with the following simple format:
>
> value REAL NOT NULL,
> sample_time TIMESTAMP WITH TIME ZONE NOT NULL,
> channel INTEGER REFERENCES channel(id) NOT NULL
>
> (Where the "channel" table contains metadata to identify the
> particular sensor, data logger, etc. used to obtain the data and the
> combination of channel and sample_time is unique.)
>
>

Well first is that 200hz meaning 200 samples per channel per second.
That is very fast sampling for pressure sensor, I would be surprised if
the meters are actually giving real results at that rate. I would look
at reducing that down to what the meter is actual capable of sending
What kind of AD card is being used as this effects what makes sense to
record.

I would look into table partitioning
http://www.postgresql.org/docs/current/static/ddl-partitioning.html
http://wiki.postgresql.org/wiki/Table_partitioning

A one big index for such a small record will not be a big win because
the index are going to be the same size as table.
Look into limiting the number of records each index covers.
http://www.postgresql.org/docs/8.4/static/sql-createindex.html

All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.