Rendered at 18:03:10 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
mike_hearn 8 hours ago [-]
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.
bob1029 11 hours ago [-]
> 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.
mcherm 8 hours ago [-]
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.
sgarland 5 hours ago [-]
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.
sgarland 4 hours ago [-]
> 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.
prirun 3 hours ago [-]
> Clustered indices would be so, so nice for Postgres to have.
Doesn't that just trade slower inserts for faster deletes?
sgarland 2 hours ago [-]
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.
menthe 8 hours ago [-]
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.
rini17 7 hours ago [-]
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).
elendilm 7 hours ago [-]
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.
sgarland 5 hours ago [-]
> Application-managed indexes
Are you suggesting that one should recreate B+trees in code?
elendilm 5 hours ago [-]
> 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.
sgarland 4 hours ago [-]
I do not see how this could possibly be faster. Now you have an additional indirection.
elendilm 4 hours ago [-]
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.
dpark 1 hours ago [-]
I too would like to know concretely what sort of industry or scenario you are involved in, because in general “it’s simpler to roll your own than use mature, industry standard tooling” is bad advice. “Just build your own index with another table” is either hyper-specific to a niche scenario or flat out a terrible idea.
As another commenter pointed out, this means if nothing else you pay an extra round trip to the DB. Allowing the DB to own the index moves that computation to the data and eliminate a round trip network cost. In general fetching some data from the DB to tell you what other data to fetch from the same DB is a poor design. SQL exists to solve this exact problem.
There are absolutely scenarios where scale is so large that you need a partition lookup service. But this is different from not using indices.
sgarland 3 hours ago [-]
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?
elendilm 1 hours ago [-]
> You also now have to manage your own ACID guarantees and what amounts to cache invalidation ...
Hint: Extreme scale. You anyway have no full ACID guarantees at scale and one has to implement their own relevant guarantees and cache invalidation at extreme scale. You either have to implement your guarantees or pay the cost of bottleneck and latency.
They are achieved via idempotency, durable execution, etc.
There is sufficient reason for me to think that either you may not have read my comments attentively enough or you may lack the experience of building reliable systems at scale.
A developer need not be hand-held with this reasoning on the topic of scaling. I have given ample reasoning to reconstruct.
I advise do your own research.
ndriscoll 1 hours ago [-]
What is extreme scale here? Like you've been able to fit over 1 PB of NVMe in 1U for years now (and before the AI inflation, prices were low enough that you could do it for like $100k IIRC). Are we talking 10s+ of PB? Who actually needs this scale?
At mere 100s of TB of data, I think you'll find that a normal database is far more likely to scale better. Most developers are... not as good as database developers at maintaining a database, and will not do a good job of it when they write their own inside of their application. They're often not even that good at using a database.
e.g.
> 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 can just do this with postgres. It works great. Most people don't even get that right. And they're going to suddenly get it right and implement efficient indexes?
sgarland 49 minutes ago [-]
I have operated a BDR Postgres cluster spread across five global regions with hundreds of TB of data, with multiple reader nodes attached to each primary, each on instances with node-local NVMe drives; the entire cluster saw over 1 million QPS. I will grant you that there are much larger examples of scale, but they typically exist on DBs designed for that, like Spanner, Vitess, etc.
I am not asking for hand-holding, I’m asking for one single publicly-available example of this being done, because I cannot fathom it being necessary or beneficial, and you have not provided concrete proof.
Tanjreeve 3 hours ago [-]
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.
As another commenter pointed out, this means if nothing else you pay an extra round trip to the DB. Allowing the DB to own the index moves that computation to the data and eliminate a round trip network cost. In general fetching some data from the DB to tell you what other data to fetch from the same DB is a poor design. SQL exists to solve this exact problem.
There are absolutely scenarios where scale is so large that you need a partition lookup service. But this is different from not using indices.
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?
Hint: Extreme scale. You anyway have no full ACID guarantees at scale and one has to implement their own relevant guarantees and cache invalidation at extreme scale. You either have to implement your guarantees or pay the cost of bottleneck and latency.
They are achieved via idempotency, durable execution, etc.
There is sufficient reason for me to think that either you may not have read my comments attentively enough or you may lack the experience of building reliable systems at scale.
A developer need not be hand-held with this reasoning on the topic of scaling. I have given ample reasoning to reconstruct.
I advise do your own research.
At mere 100s of TB of data, I think you'll find that a normal database is far more likely to scale better. Most developers are... not as good as database developers at maintaining a database, and will not do a good job of it when they write their own inside of their application. They're often not even that good at using a database.
e.g.
> 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 can just do this with postgres. It works great. Most people don't even get that right. And they're going to suddenly get it right and implement efficient indexes?
I am not asking for hand-holding, I’m asking for one single publicly-available example of this being done, because I cannot fathom it being necessary or beneficial, and you have not provided concrete proof.