Vacuum Is a Lie: About Your Indexes
boringsql.com
Vacuum Is a Lie: About Your Indexes
1–10 of 52 posts
Re: Vacuum Is a Lie: About Your Indexes
#2Good article, and easy to follow. I learned more than I’d expected from this one.
Re: Vacuum Is a Lie: About Your Indexes
#3I think this article goes a bit overboard with the negative language ('lies', 'fools'), especially since (auto)VACUUM and indexes really don’t have that much to do with each other: the former is indeed critical on PostgreSQL to ensure availability, but something of a niche feature for most other databases, while index maintenance is important regardless of platform.
For a certain class of applications ('SQLite level'), there’s not even much of that, though, other than ensuring there are no missing or obsolete indexes, which you can take care of with 15 minutes of quality time with the EXPLAIN statement every now and then.
When using a database with persistent index statistics (like SQL Server and Oracle and, yeah, PostgreSQL), it’s important to at least ensure those get updated on a regular basis (but that’s almost always automatic and sufficient unless you're prone to not-usually-done bulk operations) and to optimize or rebuild the underlying tree on a semi-regular basis. This does require some additional non-default setup and monitoring, and can be surprising when you first encounter it.
But it’s not exactly an obscure-slash-secret bit of DBA lore either, unlike what's suggested here...
Re: Vacuum Is a Lie: About Your Indexes
#4I think this article goes a bit overboard with the negative language ('lies', 'fools'), especially since (auto)VACUUM and indexes really don’t have that much to do with each other: the former is indeed critical on PostgreSQL to ensure availability, but something of a niche feature for most other databases, while index maintenance is important regardless of platform. For a certain class of applications ('SQLite level'…
There is a bunch of AI slop in there ... It does seem like the author probably knows what he's talking about, since there is seemingly good info in the article [1], but there's still a lot of slop
Also, I think the end should be at the beginning:
Know when your indexes are actually sick versus just breathing normally - and when to reach for REINDEX.
VACUUM handles heap bloat. Index bloat is your problem.
The intro doesn't say that, and just goes on and on about "lies" and stupid stuff like that.
This part also feels like AI:
Yes. But here's what it doesn't do - it doesn't restructure the B-tree.
What VACUUM actually does
What VACUUM cannot do
I don't necessarily think this is bad, since I know writing is hard for many programmers. But I think we should also encourage people to improve their writing skills.
[1] I'm not an SQL expert, but it seems like some of the concrete examples point to some human experience
Re: Vacuum Is a Lie: About Your Indexes
#5I think this article goes a bit overboard with the negative language ('lies', 'fools'), especially since (auto)VACUUM and indexes really don’t have that much to do with each other: the former is indeed critical on PostgreSQL to ensure availability, but something of a niche feature for most other databases, while index maintenance is important regardless of platform. For a certain class of applications ('SQLite level'…
There is a bunch of AI slop in there ... It does seem like the author probably knows what he's talking about, since there is seemingly good info in the article [1], but there's still a lot of slop Also, I think the end should be at the beginning: Know when your indexes are actually sick versus just breathing normally - and when to reach for REINDEX. VACUUM handles heap bloat. Index bloat is your problem. The intro do…
A better title might have been VACUUM addresses heap bloat; REINDEX addresses index bloat
Similar to a recent story Go is portable, until it isn't -- the better title is Go is portable until you pull in C dependencies
Re: Vacuum Is a Lie: About Your Indexes
#6I think this article goes a bit overboard with the negative language ('lies', 'fools'), especially since (auto)VACUUM and indexes really don’t have that much to do with each other: the former is indeed critical on PostgreSQL to ensure availability, but something of a niche feature for most other databases, while index maintenance is important regardless of platform. For a certain class of applications ('SQLite level'…
There is a bunch of AI slop in there ... It does seem like the author probably knows what he's talking about, since there is seemingly good info in the article [1], but there's still a lot of slop Also, I think the end should be at the beginning: Know when your indexes are actually sick versus just breathing normally - and when to reach for REINDEX. VACUUM handles heap bloat. Index bloat is your problem. The intro do…
Yeah my eyes glaze over when I see the familiar tone.
If it's not worth writing it sure ain't worth reading.
Re: Vacuum Is a Lie: About Your Indexes
#7Shorter:
* VACUUM does not compact your indexes (much).
* VACUUM FULL does. It's slow though.
Re: Vacuum Is a Lie: About Your Indexes
#8[dead]
Re: Vacuum Is a Lie: About Your Indexes
#9> The exclusive lock is only needed during the final swap phase, and its duration can be configured.
FYI: even a very short operation that requires an exclusive lock can induce significant downtime if there’s anything else that holds a shared lock for extended periods. In [1], there was:
- a wraparound autovacuum (which holds a shared lock for potentially a long time — like hours)
- lots of data path operations wanting a shared lock
- one operation that should have been very brief that merely tried to take an exclusive lock
The result is that the presence of an operation wanting an exclusive lock blocked the data path for the duration of the autovacuum. Major outage.
[1] https://web.archive.org/web/20190320162510/https://www.joyen...
Edit: this was a while ago with v9.2, but I don’t know if any of this behavior has changed.
Re: Vacuum Is a Lie: About Your Indexes
#10The article has a section where it estimates index bloat based on comparing the number of index reltuples * 40 bytes (?), compared to the size of the file on disk.
This is problematic, first of all because I don't think the math is right (see [0] for a more comprehensive query that takes into account column sizes), and second because it ignores the effects of B-Tree index deduplication in Postgres 13+: [1]
In my experience, fast bloat estimation queries can work okay for table bloat, but for index bloat I'd recommend instead looking at the change in page density over time (i.e. track relpages divided by reltuples), or just go direct to running pgstatindex outside business hours.
[0]: https://github.com/pgexperts/pgx_scripts/blob/master/bloat/i... [1]: https://www.postgresql.org/docs/current/btree.html#BTREE-DED...