Re: Postgresql partitioning - single hot table or distributed

Vick Khera wrote:
> The drawback to partitioning by an ID number using modulo is that for
> constraint exclusion to work you have to actually add something like
> "AND (my_id % 42) = 0" to match the constraint. The exclusion is not
> done by executing the constraint, but by proving the constraint will
> hold true for the WHERE clause.

Vick's "Case study of partitioning a large table in Postgres 8"
presentation at http://www.mailermailer.com/labs/presentations/index.rwp
covers this topic in more detail. The important thing to realize is
that your partitioning scheme must respect the limitations of the
constraint exclusion code in the query planner to be most useful. You
really need to consider not just the structure of the data, but what the
queries against it look like, to make that call. If you can hack the
application to include the modulo bit in every query that might be a
sufficient breakdown for you. But you have to consider what that looks
like from the query side, given what the optimizer knows how to do, not
just the perspective of the table structure. Building a quick prototype
and using EXPLAIN ANALYZE of common queries is invaluable here to do an
early investigation of any potential redesign.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us