“Expiring” rows shouldn’t need to be fast. And while I appreciate that you managed to sidestep your immediate issue, is deletion even necessary? Dropping the foreign key & then having to add an anti-join doesn’t feel like a long-term solution.
There’s a point in PostgreSQL scalability where the mere need to vacuum becomes a huge problem in itself.
Instead, the rows could be tombstone’d through a heap-only tuple UPDATE on a “deleted_at” column (BRIN indexable), which does not churn records/pages & does not create any dead tuple at all. This likely solves 99% of the problem cleanly without sacrificing database coherence (i.e without having to drop the foreign key).
The manual VACUUM is also a bit of an anti-pattern that locks your business logic layer with the storage layer - when the use of a reverse index would probably have solved the long chain of dead-tuple in your index (and probably improved performance in other areas too).
And finally, as another idea remarked, handwaving partitions feels hasty. Not only is it a very powerful tool for many obvious reasons (incl. index sanity), I highly doubt that your workflow platform does not carry a “hard timeout” (explicit or implicit) after which all workflows/steps are most definitely dead. A HOT UPDATE tombstone, and nicely arranged partitions, would avoid all DELETEs, all dead tuples, and offer many more scalability benefits over time (caching included. Which you talk about a bunch).
MVCC doesn't have to work the way Postgres does it. In some other databases a write like a DELETE or UPDTE directly overwrites disk blocks in place. There are no row copies or vacuums. To provide MVCC the database generates "undo logs" which are what they sound like - if you need to read the database at an earlier snapshot it reads the data off disk then use the undo data to revert the writes that happened after your MVCC snapshot. Undo data is periodically dropped when no live transaction needs it anymore.
There are patches to change Postgres to this kind of model, as it has many advantages - all the outages and problems caused by autovacuuming falling behind go away - but the patches were never merged.
Oracle has a couple of related features:
- undo data can be retained and used for time travel queries (SELECT .. AS OF).
- DML can be parallelized and write masters scale horizontally, so a large DELETE can run on multiple machines simultaneously. You do need to opt in manually though:
ALTER SESSION ENABLE PARALLEL DML;
DELETE /*+ PARALLEL(t, 8) */
FROM t
WHERE created_at < :cutoff;
COMMIT;
That said the issues discussed in the blog post aren't directly to do with how fast the DELETE itself is processed but what it does to the database's performance afterwards.
Disclosure: I have a part time role in the Oracle DB group.
> A partition can’t be deleted until every single piece of data it stores is no longer needed. This is appropriate for some systems, like message queues, where data is needed for a predictable amount of time. However, it’s not appropriate for a workflow system that supports long-running workflows.
If most workflows complete within a few days, we could explicitly copy the long running items forward and then reclaim the entire partition. Sacrificing an extremely good technical solution because we can't be bothered to normalize <1% of use cases seems quite hasty, especially when we consider where this rejection of technological simplicity often takes us.
Yes, but "DELETE" is the basic, built-in tool for deleting some rows from a database. Maintaining partitions, and migrating long-lived data from old partitions is a workaround.
There is nothing wrong with using the partition solution -- it might be a good solution to your problem. But it certainly isn't a case of "rejecting technological simplicity" if someone chooses to use DELETE instead.
“Expiring” rows shouldn’t need to be fast. And while I appreciate that you managed to sidestep your immediate issue, is deletion even necessary? Dropping the foreign key & then having to add an anti-join doesn’t feel like a long-term solution.
There’s a point in PostgreSQL scalability where the mere need to vacuum becomes a huge problem in itself.
Instead, the rows could be tombstone’d through a heap-only tuple UPDATE on a “deleted_at” column (BRIN indexable), which does not churn records/pages & does not create any dead tuple at all. This likely solves 99% of the problem cleanly without sacrificing database coherence (i.e without having to drop the foreign key).
The manual VACUUM is also a bit of an anti-pattern that locks your business logic layer with the storage layer - when the use of a reverse index would probably have solved the long chain of dead-tuple in your index (and probably improved performance in other areas too).
And finally, as another idea remarked, handwaving partitions feels hasty. Not only is it a very powerful tool for many obvious reasons (incl. index sanity), I highly doubt that your workflow platform does not carry a “hard timeout” (explicit or implicit) after which all workflows/steps are most definitely dead. A HOT UPDATE tombstone, and nicely arranged partitions, would avoid all DELETEs, all dead tuples, and offer many more scalability benefits over time (caching included. Which you talk about a bunch).
There are patches to change Postgres to this kind of model, as it has many advantages - all the outages and problems caused by autovacuuming falling behind go away - but the patches were never merged.
Oracle has a couple of related features:
- undo data can be retained and used for time travel queries (SELECT .. AS OF).
- DML can be parallelized and write masters scale horizontally, so a large DELETE can run on multiple machines simultaneously. You do need to opt in manually though:
https://docs.oracle.com/en/database/oracle/oracle-database/2...That said the issues discussed in the blog post aren't directly to do with how fast the DELETE itself is processed but what it does to the database's performance afterwards.
Disclosure: I have a part time role in the Oracle DB group.
If most workflows complete within a few days, we could explicitly copy the long running items forward and then reclaim the entire partition. Sacrificing an extremely good technical solution because we can't be bothered to normalize <1% of use cases seems quite hasty, especially when we consider where this rejection of technological simplicity often takes us.
There is nothing wrong with using the partition solution -- it might be a good solution to your problem. But it certainly isn't a case of "rejecting technological simplicity" if someone chooses to use DELETE instead.