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

If you will be selecting sets of data within a time range, it should also improve performance if you can build a clustered index on the sample_time. It may also be worth looking at whether partitioning by timestamp & channel offers any advantages.

Brent Wood

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Justin Graf 02/10/10 3:07 PM >>>
On 2/9/2010 4:41 PM, Asher Hoskins wrote:
>
> Thanks for that, it looks like partitioning is the way to go. I'm
> assuming that I should try and keep my total_relation_sizes less than
> the memory size of the machine?
This depends on what the quires look like. As other have stated when
partitioning you have to consider how the data is quired.

>
>
> If I partition so that each partition holds data for a single channel
> (and set a CHECK constraint for this) then I can presumably remove the
> channel from the index since constraint exclusion will mean that only
> partitions holding the channel I'm interested in will be searched in a
> query. Given that within a partition all of my sample_time's will be
> different do you know if there's a more efficient way to index these?
Given the timestamp will most likely be the where clause, NO on the
plus side its only 8 bytes

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.

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.