Last week I learned a few things (the hard way) about PostgreSQL (pgsql) partitioning:

• You really have to read the “caveats” part of the manual (scroll down, very bottom)
• Server config matters (SET constraint_exclusion = on;) if you want to avoid unnecessarily checking out-of-bounds partitions
• Child tables don’t inherit permissions, so owning the parent table won’t automagically grant you access to child tables — which in this case prevented me from setting up my explicit indexes.
• Foreign key constraints on a parent table are not inherited. This is actually a bug in pgsql. They plan on changing “CREATE TABLE LIKE” in pgsql 8.3 but fixing this for INHERIT statements is a bit different since it means inheriting for the lifetime of the parent->child relationship (instead of one-time-only during creation). For more, read chizu’s thread on the issue — he actually submitted a patch that fixed things for table creation. 🙂

Long story short, partitioning is tricky business. For more, see bsmedberg’s blog, where he talks about query performance in queries spanning multiple partitions and his battle with the postgresql query planner.

From the very beginning, even before you start messing with the query planner, I think it’s important to understand the pitfalls of inheritance in pgsql and how it will affect your use of partitions.

So just be extra careful when you read the manual and make sure you’re aware of these issues when you plan your schema. If you’re not, it’ll come back to bite you.