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.

Mike Morgan is the Director of Web Development for the Mozilla Corporation. Mike joined the Mozilla community as a volunteer in 2003 and has worked on Mozilla's mirror management software, update system and addons.mozilla.org. These days he spends most of his time on strategy, recruiting and helping his team embrace their inner nerd.