I have a postgres db where everyday a significant number of rows across multiple tables are considered outdated and soft deleted. My understanding is that there's no point in keeping these rows around and it would be better to remove them on a regular basis (example once a week) to get the best performance out of my db. Which leads me to my first question:

  1. Am I right to think that deleting unused rows will result in the best performance over time or is that bit naive?

If the answer is yes, the problem is that the delete process can be very very slow due to the size of the delete and the constraints between tables. I'm unable to get superuser privileges to do stuff like disabling all triggers or changing session_replication_role.

The other option I have is to drop my constraints and recreate them but this seems risky since my constraints are automatically generated using prisma and it would seem like a matter of time until a change in my schema would result in these going out of sync causing some issues I'd much rather avoid.

  1. Is there some way I have missed to easily disable the constraints and re-enable them without being a superuser?
  • 1
    If the tables where you want to get rid of the rows aren't referenced by other tables, you can consider partitioning. Then getting rid of the rows is quite fast as you only need to drop the (old) partitions. Sadly, Postgres doesn't support foreign keys referencing partitioned tables, so if you need that, this isn't an option.
    – user1822
    Commented May 21, 2022 at 12:46
  • Yeh I do have foreign key constraints between tables so I guess that means it’s not an option to do that. If I didn’t have the foreign keys I think the delete would be pretty fast anyway so not sure if there’s a benefit to doing it using partitions in that case.
    – Luke Vella
    Commented May 21, 2022 at 13:11
  • 1
    Dropping (or simply detaching) a partition takes a few milliseconds (regardless of the size of the partition). That will always be substantially faster than any DELETE statement.
    – user1822
    Commented May 21, 2022 at 13:14
  • soft deletes are usually a poisoned chalice. Can't you just have a tested PITR strategy and then actually delete rows when you want to delete them? How often do you have to resurrect soft-deleted rows?
    – jjanes
    Commented May 21, 2022 at 15:28
  • Why does it need to be fast?
    – jjanes
    Commented May 21, 2022 at 15:37


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.