PostgreSQL – Handling Foreign Keys with Soft Delete Partition

postgresql

I have a table A that has a couple of other tables (B, C) attached to it (many rows in B and C for one row in A).

I'm having performance problems with A, so I thought I would partition it using a date_deleted column (NULL/NOT NULL) into 2 tables A_active and A_deleted.

Data flow is as follows:

  1. Records are created at a rate 10k per day, inserted into A_active
  2. After 2 months records are moved to A_deleted
  3. Records are never un-deleted
  4. Nothing is ever hard deleted, currently there are 1.3M rows

I have written a full script to split my A into A_active and A_deleted, create triggers for inserts and updates, but I'm at a loss about the foreign keys.

Should I create B_active and B_deleted? The triggers will get super complex.

Should I create 2 FKs from B? One to A_active, one to A_deleted? Triggers will also be very complex.

Should I scrape this idea altogether? I kind of like the idea of having a dedicated table for historic records. It will simplify my selects.

Best Answer

A table of 1.3M rows is pretty small. It would be unusual for a table of that size to benefit from partitioning.

Have you looked at the queries which are slow with EXPLAIN (ANALYZE, BUFFERS) to see what the problems are?

When partitioning works well, it is because it works hand-in-glove with your use cases. So there can't really be generic partitioning advice. You would have to tell us much more about the types of queries you run and what your performance expectations of them are, and what the current execution plans look like.

Related Question