[HN Gopher] Vacuum Is a Lie: About Your Indexes
___________________________________________________________________
Vacuum Is a Lie: About Your Indexes
Author : birdculture
Score : 52 points
Date : 2025-12-14 13:13 UTC (6 hours ago)
(HTM) web link (boringsql.com)
(TXT) w3m dump (boringsql.com)
| apothegm wrote:
| Good article, and easy to follow. I learned more than I'd
| expected from this one.
| ZeroConcerns wrote:
| I 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...
| chubot wrote:
| 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
| chubot wrote:
| 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_
|
| https://lobste.rs/s/ijztws/go_is_portable_until_it_isn_t
| anotherhue wrote:
| Yeah my eyes glaze over when I see the familiar tone.
|
| If it's not worth writing it sure ain't worth reading.
| radimm wrote:
| Author here - it's actually funny, as you pointed out parts
| that are my own (TM) attempts to make it a bit lighthearted.
|
| LLM is indeed used for correction and improving some
| sentences, but the rest is my honest attempt at making
| writing approachable. If you're willing to invest the time,
| you can see my fight with technical writing over time if you
| go through my blog.
|
| (Writing this in the middle of a car wash on my iPhone
| keyboard ;-)
| ZeroConcerns wrote:
| Yeah, I get accused of being an LLM all the time as well,
| best to ignore that kind of slop... (which, ironically,
| goes both ways!)
| radimm wrote:
| Author here - thank you for the comments. This article is
| indeed playing a lot on verge of clickbait and I did asked
| about that shortly after publishing.
| ZeroConcerns wrote:
| No worries -- not publishing at all is worse than publishing
| disliked content (well, to a certain extent), so keep reading
| that feedback, but don't be too discouraged by it!
| chuckadams wrote:
| Shorter:
|
| * VACUUM does not compact your indexes (much).
|
| * VACUUM FULL does. It's slow though.
| karolinepauls wrote:
| That's too reductive. Vacuum full isn't just slow, it
| exclusively locks the table for the duration of the vacuum and
| is basically a no-go when the database is in use.
| kccqzy wrote:
| You also missed the main recommendation of REINDEX INDEX
| CONCURRENTLY along with optionally pg_squeeze.
| dap wrote:
| > 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.
| lfittl wrote:
| The 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...
| dap wrote:
| Every time Postgres advice says to "schedule [important
| maintenance] during low traffic period" (OP) or "outside
| business hours", it reinforces my sense that it's not suitable
| for performance-sensitive data path on a 24/7/365 service and
| I'm not sure it really aims to be. (To be fair, running it like
| that for several years and desperately trying to make it work
| also gave me that feeling. But I'm kind of aghast that
| necessary operational maintenance still carries these caveats.)
| jeffbee wrote:
| Postgres-compatible cloud databases like AlloyDB address this
| issue.
| lfittl wrote:
| Regarding pgstattuple specifically: If this was a 24/7/365
| service and you would be concerned by the I/O impact of
| loading the full table or index at any time, you could run
| this on a replica too. For tables there is pgstattuple_approx
| which is much better at managing its impact, but there is no
| equivalent for indexes today.
|
| The REINDEX CONCURRENTLY mentioned in OP could also be run at
| other times of the day - the main issue is again I/O impact
| (with potentially some locking concerns at the very end of
| the reindex concurrently to swap out the index).
|
| There are no magic solutions here - other databases have to
| deal with the same practical limitations, though Postgres
| sometimes is a bit slow to adopt operational best practices
| in core (e.g. the mentioned pg_squeeze from OP may finally
| get an in-core "REPACK CONCURRENTLY" equivalent in Postgres
| 19, but its been a long time to get there)
| anarazel wrote:
| > Every time Postgres advice says to "schedule [important
| maintenance] during low traffic period" (OP) or "outside
| business hours", it reinforces my sense that it's not
| suitable for performance-sensitive data path on a 24/7/365
| service and I'm not sure it really aims to be.
|
| It's a question of resource margins. If you have regular and
| predictable windows of low resource utilization, you can
| afford to run closer to the sun during busy periods,
| deferring (and amortizing, to some degree) maintenance costs
| till later. If you have a 24/7/365 service, you need
| considerably higher safety margins.
|
| Also, there's a lot of terrible advice on the internet, if
| you haven't noticed.
|
| > (To be fair, running it like that for several years and
| desperately trying to make it work also gave me that feeling.
| But I'm kind of aghast that necessary operational maintenance
| still carries these caveats.)
|
| To be fair, I find oxides' continual low-info griping against
| postgres a bit tedious. There's _plenty_ weaknesses in
| postgres, but criticizing postgres based on 10+ year old
| experiences of running an, at the time, outdated postgres, on
| an outdated OS is just ... not useful? Like, would it useful
| to criticize oxides lack of production hardware availability
| in 2021 or so?
|
| Edit: duplicated word removed
| dap wrote:
| > It's a question of resource margins.
|
| What you describe is true and very important (more margin
| lets you weather more disruption), but it's not the whole
| story. The problem we had was queueing delays mainly due to
| I/O contention. The disks had the extra IOPS for the
| maintenance operation, but the resulting latency for _all_
| operations was higher. This meant overall throughput
| decreased when the maintenance was going on. The customer,
| finally accepting the problem, thought: "we'll just build
| enough extra shards to account for the degradation". But it
| just doesn't work like that. If the degradation is 30%, and
| you reduce the steady-state load on the database by 30%,
| that doesn't change the fact that when the maintenance is
| ongoing, even if the disks have the IOPS for the extra
| load, latency goes up. Throughput will still degrade. What
| they wanted was predictability but we just couldn't give
| that to them.
|
| > To be fair, I find oxides' continual low-info griping
| against postgres a bit tedious. There's plenty weaknesses
| in postgres, but criticizing postgres based on 10+ year old
| experiences of running an, at the time, outdated postgres,
| on an outdated OS is just ... not useful?
|
| First, although I work at Oxide, please don't think I speak
| for Oxide. None of this happened at Oxide. It informed some
| of the choices we made at Oxide and we've talked about that
| publicly. I try to remember to include the caveat that this
| information is very dated (and I made that edit immediately
| after my initial comment above).
|
| I admit that some of this has been hard for me personally
| to let go. These issues dominated my professional life for
| three _very_ stressful years. For most of that time (and
| several years earlier), the community members we reached
| out to were very dismissive, saying either these weren 't
| problems, or they were known problems and we were wrong for
| not avoiding them, etc. And we certainly did make mistakes!
| But many of those problems were later acknowledged by the
| community. And many have been improved -- which is great!
| What remains is me feeling triggered when it feels like
| users' pain is being casually dismissed.
|
| I'm sorry I let my crankiness slip into the comment above.
| I try to leave out the emotional baggage. Nonetheless, I do
| feel like it's a problem that, intentionally or otherwise,
| a lot of the user base has absorbed the idea that it's okay
| for necessary database maintenance to significantly degrade
| performance because folks will have some downtime in which
| to run it.*
| horizion2025 wrote:
| "When VACUUM runs, it removes those dead tuples and compacts the
| remaining rows within each page. If an entire page becomes empty,
| PostgreSQL can reclaim it entirely."
|
| Is this true? I was of the belief that standard vacuum doesnt
| move any data even within a page... It merely enables dead tuples
| to be reused in the future. But I could be mistaken
| anarazel wrote:
| It's true - otherwise the space couldn't freely be reused,
| because the gaps for the vacuumed tuples wouldn't allow for any
| larger tuples to be inserted.
|
| See
| https://github.com/postgres/postgres/blob/b853e644d78d99ef17...
| lfittl wrote:
| I think there are two aspects to that:
|
| 1) When do pages get removed? (file on disk gets smaller)
|
| Regular vacuum can truncate the tail of a table if those pages
| at the end are fully empty. That may or may not happen in a
| typical workload, and Postgres isn't particular about placing
| new entries in earlier pages. Otherwise you do need a VACUUM
| FULL/pg_squeeze.
|
| 2) Does a regular VACUUM rearrange a single page when it works
| on it? (i.e. remove empty pockets of data within an 8kb page,
| which I think the author calls compacting)
|
| I think the answer to that is yes, e.g. when looking at the
| Postgres docs on page layout [0] the following sentence stands
| out: "Because an item identifier is never moved until it is
| freed, its index can be used on a long-term basis to reference
| an item, even when the item itself is moved around on the page
| to compact free space". That means things like HOT pruning can
| occur without breaking index references (which modify the
| versions of the tuple on the same page, but keep the item
| identifier in the same place), but (I think) during VACUUM,
| even breaking index references is allowed when cleaning up dead
| item identifiers.
|
| [0]: https://www.postgresql.org/docs/current/storage-page-
| layout....
|
| Edit: And of course you should trust the parallel comment by
| anarazel to be the correct answer to this :)
| dgreensp wrote:
| Upvoted because educational, despite the AI-ness and clickbait.
|
| I've worked at orgs that used Postgres in production, but I've
| never been the one responsible for tuning/maintenance. I never
| knew that Postgres doesn't merge pages or have a minimum page
| occupancy. I would have thought it's not technically a B-tree if
| it doesn't.
| supermatt wrote:
| > When VACUUM runs, it removes those dead tuples and compacts the
| remaining rows within each page.
|
| No it doesn't. It just removes unused line pointers and marks the
| space as free in the FSM.
| anarazel wrote:
| > > When VACUUM runs, it removes those dead tuples and compacts
| the remaining rows within each page.
|
| > No it doesn't. It just removes unused line pointers and marks
| the space as free in the FSM.
|
| It does:
|
| https://github.com/postgres/postgres/blob/b853e644d78d99ef17...
|
| Which is executed as part of vacuum.
| supermatt wrote:
| I guess I stand corrected - I thought that was the purpose
| behind VACUUM FULL
| anarazel wrote:
| VACUUM FULL is about cleanup up things _above_ the level of
| a single page. Moving stuff around within a page doesn 't
| allow you to reclaim space on the OS level, nor does it
| "compact" tuples onto fewer pages.
|
| But it's important for normal vacuum to compact the tuples
| on the page, otherwise the space of deleted tuples couldn't
| effectively be reused. Imagine a page that's entirely
| filled with 100 byte tuples, then every other tuple is
| deleted. Then, after a vacuum, a single 108 byte tuple
| should be inserted onto the page. Without compacting the
| space in the page during the vacuum, there would not be any
| space for that larger tuple.
| throwaway613745 wrote:
| Dont' forget to ANALYZE your tables sometimes too.
|
| Just recently was trying to optimize a 12s index scan, turns out
| I didn't need to change anything about the query I just had to
| update the table statistics. 12s down to 100ms just form running
| ANALYZE (no vacuum needed).
| tehlike wrote:
| there are ways to see out which indexes get used and which are
| not. It's surprising to find out table scans or incorrect
| indexes getting used.
| karolinepauls wrote:
| And make sure your `random_page_cost` is about 1.1 if running
| on an SSD or if >~98% of your hot pages fit in memory. Rather
| than 4 by default which makes the planner afraid of using
| indexes.
| JohnCClarke wrote:
| I'm (genuinely) curious about the overwhelming preference for
| PostgreSQL on HN. I've always used MySQL for OLTP, and been very
| happy with it.
|
| If you've seriously considered both and then selected PostgreSQL
| please comment and tell me what drove that decision.
|
| Note: I'm only talking about OLTP. I do see that PostgreSQL adds
| a lot for OLAP.
| tharkun__ wrote:
| Personally it's the history for me. MySQL started with MyISAM,
| not innodb.
|
| So if you wanted an actual transactional database back in the
| day, MySQL was definitely not it. You needed Postgres.
|
| InnoDB was not MySQL. It was an add on. So if I had to use
| MySQL it was with innodb of course but why not just use
| Postgres. And after the Oracle acquisition... Yes I know
| MariaDB. But I'm already on Postgres so...
| didgetmaster wrote:
| This article points out some of the pain associated with index
| maintenance. It should also point out that ALL indexes on a table
| suffer from the same issue. If your 20 column table has 7
| indexes, then the suggestions should be applied 7x.
|
| It is conventional wisdom that indexes are absolutely essential
| for any relational table of at least reasonable size (e.g.
| thousands of rows) and is accessed more often than daily. Indexes
| can be a pain to create and maintain; but can greatly speed up
| queries and primary key validations. The pain mostly comes from
| having to figure out what indexes to create and how often to
| maintain them, rather than doing the actual thing.
|
| Indexes also have a performance penalty for any table updates.
| Creating new rows, updating existing rows, or deleting rows all
| require updates to each index.
|
| But are indexes really required? I am creating a new kind of
| general purpose data management system (a kind of object store)
| called Didgets. The tagging mechanism that I invented to allow
| tags to be attached to each data object, are key-value stores
| that essentially form a set of columnar stores.
|
| I found that these columnar stores could also be used to create
| regular relational database tables. The data is structured such
| that indexes are not needed. All the tests that I have run (up to
| a thousand columns with over 100 million rows), show that query
| speeds are equal to, or better than other database systems that
| are well indexed.
|
| The system is still under development, so it is still missing
| some key features that would make it a drop-in replacement for
| other databases; but it proves that it is possible to structure
| relational data such that query speeds can be optimal without
| needing separate indexing structures that have to be maintained.
| arcbyte wrote:
| Interesting ideas. Im very interested in database ideas that
| bring new capabilities or better ways to acconplish old ones.
|
| W.r.t. query speeds on your columnar storage engine, you will
| obviously have much better writes that row oriented storage
| engines. This limits your write capabilities though. Any effort
| you put into restoring write speeds necessitates an extra step
| to the maintain the columnar stores - which puts you back into
| the group of databases naintaining indices that you criticize
| above.
|
| I think modern databases are bringing new ideas on how to
| accelerate both write and query speeds simultaneously with
| tradeoffs around CAP.
___________________________________________________________________
(page generated 2025-12-14 20:00 UTC)