3 ms·
Efficient resource recovery while preserving consistency and minimally impacting workload throughput is one of THE central problems in database engine design. Y
by jandrewrogers 9y ago
Efficient resource recovery while preserving consistency and minimally impacting workload throughput is one of THE central problems in database engine design. You can move the pain around but it never goes away, and a poor solution will very negatively impact the throughput of normal workloads. People have been thinking about this problem for as long as we've been building database engines.
This is not to say vacuum-like mechanisms are necessarily the best method but it was a common architectural idiom for database engines from the 1990s because it works well with sequential storage devices. Newer techniques tend to put resource recovery inline with the workload rather than outside of it but that has other disadvantages.
Indexing all rows in a database by transaction id would cause an extreme loss of throughput. That just creates a continuously mutated (and therefore locking) structure every thread constantly uses that is also paged to disk. And deletion of individual records is a problem for indexes generally.
- lobster_johnson 9y agoOracle, with its undo log and some clever in-place tuple modification and space reclamation, seems to have sidestepped the issue to a much greater degree than Postgres. Oracle is MVCC-like, but sacrifices technical elegance for performance. For example, as I recall, Oracle actually writes new row data to the page the row lives in, overwriting the old data in place on commit. It also writes the modifying transaction ID to the row header. If a different transaction finds the row, it will see that the transaction ID doesn't match (it's newer than itself), which forces the database to go to the undo log to look for the older data. I'm not privy to the technical details of how the undo log is implemented or how it avoids the vacuum problem. I suspect it's related to the fact that the undo log is separate from the tuple data, so undo bloat doesn't affect table performance to the same extent as with Postgres.
- Tostino 9y agoThat is correct, it also means that things like rollbacks, which are pretty much instant in Postgres can take significant time in Oracle.