Re: Table Clustering & Time Range Queries

On 10/22/09 12:25 PM, "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

> Kevin Buckham <kbuckham(at)applocation(dot)net> wrote:
>
>> Our primary location table is clustered by "reporttime" (bigint).
>> Many of the queries we need to perform are of the nature : "get me
>> all positions from a given device for yesterday". Similar queries
>> are "get me the most recent 10 positions from a given device".
>
> Have you looked at table partitioning? You would then only need to
> cluster the most recent partition or two. I *seems* like a good fit
> for your application.
>
> http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html
>
> -Kevin

Partitioning by time should help a lot here as Kevin says.

Also, you might want to experiment with things like pg_reorg:
http://reorg.projects.postgresql.org/
http://pgfoundry.org/projects/reorg/
http://reorg.projects.postgresql.org/pg_reorg.html

Which is basically an online, optimized cluster or vacuum full. However it
has several caveats. I have not used it in production myself, just
experiments with it.

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