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.
IME as a DBRE, it usually is rejecting simplicity. Most devs know precious little about RDBMS, and so reach for what they do know. When an experienced DBA / DBRE shows them the idiomatic way to do what they’re attempting, they often reject it and claim “flexibility,” or “keeping logic out of the database.” It’s just not wanting to do things they’re unfamiliar with, but for some reason it’s accepted for RDBMS, because it lives in the liminal space of infra and code.
> Because a workflow typically performs many steps over a long period of time, those step records are widely separated on disk
Clustered indices would be so, so nice for Postgres to have. OrioleDB can’t come quickly enough.
> However, this stamp is applied only to the row itself, not to any indexes referencing the row.
Indices also hold the hint bit LP_DEAD, which is set by any query that follows the index to the heap and finds that it’s invisible to everyone.
I suspect that a combined query that uses pagination rather than LIMIT … OFFSET would be more performant here, and could skip the dead tuple problem.
DELETE FROM step_outputs
WHERE ctid = ANY(ARRAY(
SELECT s.ctid
FROM step_outputs s
WHERE (s.created_at, s.id) > (?, ?)
AND s.created_at < ?
AND NOT EXISTS (
SELECT 1 FROM workflow_status w
WHERE w.workflow_uuid = s.workflow_uuid
)
ORDER BY s.created_at, s.id
LIMIT ?
))
RETURNING created_at, id;
Haven’t tested that, but something of that shape. You’d need a composite index on (created_at, id) as well.
The insertion hit isn’t as bad as it’s made out to be; also, the majority of applications are heavily read-centric, something like a 98/2 split, so it’s usually a net gain.
You should, of course, test it on your own data before deciding.
First of all, what are you optimizing for? Expiry latency shouldn't matter, only keeping pace with insert throughput. And while I appreciate that you sidestepped the immediate issue through the solutions presented in the article, dropping the foreign key and adding an anti-join doesn't feel like a long-term solution, since you trade referential integrity for orphan risk.
There's a point in PostgreSQL scalability where the need to vacuum (following updates/deletes) becomes a problem in itself, and that point is a lot closer than most anticipate. For instance, vacuum can’t be unable to reclaim versions unexpectedly (e.g stuck transaction holding the TID horizon), leaving you with millions of dead tuples & massive stale index linked-lists, which can lead to major service degradations.
An alternative could be to tombstone rows with a HOT UPDATE on a `deleted_at` column. If that update is HOT (heap-only), the new row version lives on the same page and no new index entries are created. The old version does become a dead tuple, but it's pruned in-page and ad-hoc by ordinary reads and writes, so VACUUM is never needed to reclaim them, and dead tuples never accumulate, least of all in the indexes. On PG16+ this even works with a BRIN index on `deleted_at`, given some fillfactor headroom.
That also addresses the 20-minute scan, which seems to have been caused by walking millions of dead index entries at the oldest end of created_at, each needing a heap visit to confirm it was dead. With HOT tombstoning, those dead entries never get created in the first place. A persisted keyset cursor (the last processed `created_at`) would avoid re-walking that region either way.
The manual VACUUM also couples your business logic to the storage layer, which is a bit of a slippery slope that’ll cause issues when you scale to the next order of magnitude.
I understand why long-running workflows and per-class retention complicate partitions. But if most workflows are short-lived, partitioning plus a tombstone sweep for the long-lived stragglers could avoid most DELETEs, and would also help index sanity, caching.. and let you profit from the many other benefits of partitions.
The index problem is interesting, maybe indexes should be updated synchronously at DELETE time? Guess there's architectural reason why postgres can't do that. I'd try to do it manually, by adding "deleted" column to both table and index whether it's performant(edit: that copies whole row so not likely).
Extreme scale and indexes are not at all a happy couple.
Doing away with database-managed indexes at scale makes for simpler queries and predictable latencies now and in the future. These indexes have costs associated with them which become detrimental as scale rises.
Application-managed indexes along with the required codebase discipline results in reduced bottlenecks, minimal latencies, and better parallelism. One can even parallelize the index-fetch phase itself, not to mention all the other goodies that come with it. This results in a different trade-off as cost moves to the application where it is controlled and therefore leveraged.
Note: I am talking generic database architecture and not just about Postgres.
> Are you suggesting that one should recreate B+trees in code?
Why would you think that?
No, not at all. Application maintains indexes as separate normal data/row via CRUD operations into a dedicated table/collection with references to the original instead of letting the database do it on your behalf.
Additional indirection was always there for indexes, be it database managed or application managed.
If the database is running embedded in your application, then there is very little extra cost involved.
If one is using the database as a separate process, you incur additional latency for the network round-trip + the payload of the matched index items.
That is the cost you pay.
But in return you get natural parallel fanout, reduced locks/contention bottleneck, etc.
You also get to have your indexes in dedicated shards on dedicated nvme hardware. With batching, you can reduce the contention on indexes even further.
You also get a simpler mental model, simpler queries, more deterministic and predictable latency. The list goes on.
Application-managed indexes are ideal for extreme scaling.
You also now have to manage your own ACID guarantees, and what amounts to cache invalidation. Not sure how this is “a simpler mental model.”
Same with locks / contention: non-locking reads don’t cause contention, or at least, none that wouldn’t also occur with a separate table acting as the index.
Do you have any examples of this being used at scale, preferably with an accompanying blog post or other form of documentation?
What industries is It common to have data small enough that running an embedded databases makes sense but also the data is so large that the indices that database provides aren't sufficient and you need to roll your own and they will perform better than the ones built with coordination with the storage engine and everything else? This seems a very unusual set of circumstances so I'm very curious on the use case.
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.
Clustered indices would be so, so nice for Postgres to have. OrioleDB can’t come quickly enough.
> However, this stamp is applied only to the row itself, not to any indexes referencing the row.
Indices also hold the hint bit LP_DEAD, which is set by any query that follows the index to the heap and finds that it’s invisible to everyone.
I suspect that a combined query that uses pagination rather than LIMIT … OFFSET would be more performant here, and could skip the dead tuple problem.
Haven’t tested that, but something of that shape. You’d need a composite index on (created_at, id) as well.Doesn't that just trade slower inserts for faster deletes?
You should, of course, test it on your own data before deciding.
There's a point in PostgreSQL scalability where the need to vacuum (following updates/deletes) becomes a problem in itself, and that point is a lot closer than most anticipate. For instance, vacuum can’t be unable to reclaim versions unexpectedly (e.g stuck transaction holding the TID horizon), leaving you with millions of dead tuples & massive stale index linked-lists, which can lead to major service degradations.
An alternative could be to tombstone rows with a HOT UPDATE on a `deleted_at` column. If that update is HOT (heap-only), the new row version lives on the same page and no new index entries are created. The old version does become a dead tuple, but it's pruned in-page and ad-hoc by ordinary reads and writes, so VACUUM is never needed to reclaim them, and dead tuples never accumulate, least of all in the indexes. On PG16+ this even works with a BRIN index on `deleted_at`, given some fillfactor headroom.
That also addresses the 20-minute scan, which seems to have been caused by walking millions of dead index entries at the oldest end of created_at, each needing a heap visit to confirm it was dead. With HOT tombstoning, those dead entries never get created in the first place. A persisted keyset cursor (the last processed `created_at`) would avoid re-walking that region either way.
The manual VACUUM also couples your business logic to the storage layer, which is a bit of a slippery slope that’ll cause issues when you scale to the next order of magnitude.
I understand why long-running workflows and per-class retention complicate partitions. But if most workflows are short-lived, partitioning plus a tombstone sweep for the long-lived stragglers could avoid most DELETEs, and would also help index sanity, caching.. and let you profit from the many other benefits of partitions.
Doing away with database-managed indexes at scale makes for simpler queries and predictable latencies now and in the future. These indexes have costs associated with them which become detrimental as scale rises.
Application-managed indexes along with the required codebase discipline results in reduced bottlenecks, minimal latencies, and better parallelism. One can even parallelize the index-fetch phase itself, not to mention all the other goodies that come with it. This results in a different trade-off as cost moves to the application where it is controlled and therefore leveraged.
Note: I am talking generic database architecture and not just about Postgres.
Are you suggesting that one should recreate B+trees in code?
Why would you think that?
No, not at all. Application maintains indexes as separate normal data/row via CRUD operations into a dedicated table/collection with references to the original instead of letting the database do it on your behalf.
If the database is running embedded in your application, then there is very little extra cost involved.
If one is using the database as a separate process, you incur additional latency for the network round-trip + the payload of the matched index items.
That is the cost you pay.
But in return you get natural parallel fanout, reduced locks/contention bottleneck, etc.
You also get to have your indexes in dedicated shards on dedicated nvme hardware. With batching, you can reduce the contention on indexes even further.
You also get a simpler mental model, simpler queries, more deterministic and predictable latency. The list goes on.
Application-managed indexes are ideal for extreme scaling.
Same with locks / contention: non-locking reads don’t cause contention, or at least, none that wouldn’t also occur with a separate table acting as the index.
Do you have any examples of this being used at scale, preferably with an accompanying blog post or other form of documentation?