Setting up a Postgresql Partition with foreign key

Question

I have a Ubuntu 12.04 Server with PostgreSQL 9.1 installed.
I am trying to setup Partitioning on a existing Postgres DB(say 'xyz') which has very huge tables and some have foreign keys set on them.

This is what i did until now

• I followed http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html to create Partition triggers.
  
• Tested the Triggers, Each data i inserted got placed in the right partition.
  
• Took take a pg_dump of all the tables individually from DB 'xyz'.
  The option --column-inserts was enabled for each dump.
  
• Created a new DB 'xyz_new'.
  
• Imported Partition Triggers and Funtions i created into it.
  
• Started importing tables with ones which were free of foreign keys.
  
• Then i imported the ones with foreign keys, in a way that the tables referred to by the foreign keys are already imported.
  

But while doing the last step i get a error like

ERROR: insert or update on table "aaaaa" violates foreign key constraint "bbbbb_id_refs_id_5b63fd27"
DETAIL: Key (bbbbb_id)=(6817) is not present in table "bbbbb".
As a Result, no import happened on the foreign key based tables.

If i do a Select query on bbbbb for id 6817, the record does shows up.

Then after some googling i found this, http://www.postgresql.org/docs/9.1/static/ddl-inherit.html
As per the Caveats mentioned in the Wiki, the Inheritance doesnt work with the foreign key.
(That was a very bad news for me :( i had spent lots of time to create the triggers.)

Now, I would like to know if there is a workaround to manage Partitions on Postgres with foreign keys?
OR
Is there any other way where i can handle huge tables with foreign keys?

Answer 1

First, I would suggest reading this: http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-3-table.html because although it doesn't cover partitioning, you are doing partitioning using table inheritance and this will cover a lot of details.

In general there are three ways you can handle this. The first is to use specialized key tables (managed with triggers) and reference these tables in your fkey constraints. This is often the simplest approach but it has significant costs in some cases.

The second possibility is you can partition your joining table by the foreign key. This can lead to major complexity problems though and so aside from rare cases, it is my least-favored approach.

A final possibility is you can write your own constraint triggers to manage foreign key enforcement.

Which approach you choose will be dependent on the complexity of joining information, particularly transitively joining information. The second approach allows you to do things entirely declaratively at a significant (possibly major) complexity cost, while the first and third move away from declarative approaches, meaning you may also want to use pgTAP to run test cases on your triggers.....

None of these solutions are without significant complexity. You may want to rethink whether partitioning is the best way to go here and whether partial indexes and other approaches will get you where you need to go.