Postgres soft delete partition, what to do with Foreign keys?

Question

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.

Answer 1

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.

• mirosval: Thank you. Currently the slowest are counts, which is really just a HW limit it seems. If I split the table, I could do these counts as approximate, which are fast. In the end I decided to normalize and split them naturally into 2 tables.
• jjanes: You can also get approximate count using partial indexes or functional indexes and looking up pg_class.reltuples for the index. It is an unintuitive use of such indexes, but it can work very well.