Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 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.

share|improve this answer
    
Thanks for your reply! Am seeing how it fits the tables i have. Also, Can you please let me know of the other alternative approaches and any Reference docs for them? –  Vishnu Kumar Jan 28 '13 at 12:30
    
The primary case for partitioning is when you have a lot of bulk load operations against the sub-tables. In other cases, it isn't really recommended in PostgreSQL. The best approach instead is a periodic CLUSTER (if you can afford the lock time and temporary disk space usage) along with partial indexes, i.e. an index with a where clause. –  Chris Travers Jan 28 '13 at 12:35
    
See the PostgreSQL docs on the CLUSTER command, the reference on CREATE INDEX, and the documentation section on indexes more generally. The two real life-savers here are partial and functional indexes (and note, these can be combined, you can CREATE INDEX ON foo (myfunc(bar)) where date_entered between '2011-01-01' and '2012-12-31'; –  Chris Travers Jan 29 '13 at 1:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.