Re: Table Clustering & Time Range Queries

* Kevin Buckham (kbuckham(at)applocation(dot)net) wrote:
> I came across links to pg_reorg previously but it seemed that the
> project was a bit "dead". There is active development but not much
> information, and not much in the way of discussions. I will definitely
> be testing both partitioning and pg_reorg. I am curious to see if
> pg_reorg will be stable enough for us to use or not.
>
> Thanks to everyone who provided answers for great and quick responses!
> Wow, it makes me really want to keep Postgres around. :)

I've been following this but havn't commented since it seemed well in
hand. A few specific things I would mention:

Be sure to read:
http://www.postgresql.org/docs/current/static/ddl-partitioning.html

I'd recommend partitioning using inheiritance.
Make sure to set constraint_exclusion = on unless you're using 8.4
(in 8.4, constraint_exclusion is tri-state: 'partition', where it will
be used when UNION ALL or inheiritance is used in queries, 'on' where it
will try to be used for all queries, and 'off' where it won't be used at
all; 8.4's default is 'partition').
You may want to consider upgrading to 8.4 if you're not on it already.
You probably want to use triggers on your 'input' table to handle
incoming traffic.
Decide on a sensible partitioning scheme and then test, test, test.
Make sure it does what you want. explain analyze and all that.

Enjoy,

Stephen