Postgres DELETE can scale after all, if you tune it right
How Postgres DELETE can actually scale
Postgres DELETE has a bad reputation, and for good reason: deleting every row is orders of magnitude slower than dropping a table, because MVCC stamps each row version instead of removing it outright. But the flexibility of DELETE matters when data is heterogeneous and retention varies. DBOS engineers explain how they tuned cache and index performance to run retention policies on a system handling tens of thousands of workflows and steps per second.
The accusations aren't wrong: Postgres DELETE uses a lot of expensive machinery to ensure deletes are transactionally safe, so deleting every row in a table is indeed orders of magnitudes slower than just dropping the table.
- mike_hearn
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;
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.
- bob1029
> 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.