I used to have to keep an eye on tuple table bloat for awhile on big PG databases, but with recent versions of Postgres the auto-vacuuming has mostly kept it in check for all but the most gargantuan, busy tables. I pretty much have stopped caring (and I cared a lot less when SSDs became the default as the fragmentation penalty that a full repack would fix wasn't as bad as it was during the spinning metal days).
Index bloat has been another story, though. Pruning b-trees is expensive and I've mostly just been scheduling reindexes (which have been able to be done concurrently for awhile) instead.
USING INDEX is interesting, though...
Isn't losing MVCC consistency a pretty big drawback? Am I misinterpreting or does that mean occasionally a query might just return nothing for possibly difficult to diagnose reasons?
> You need 2x the space for the table and indexes you are repacking, since for a period, you will have two copies of it.
this feels like a foot gun. While other storage formats (eg LSMs) have lots of write amplification, it's done iteratively so you don't have massive storage and IO spikes when performing maintenance.
While this is _definitely_ better, I think the long-term solution is to replace the storage engine with something more modern.
They added plan hinting, but sadly not via comments. To use hinting an app needs to issue a `SET` command, which adds round trip latency for each query. Need to use quite unergonomic query batching/pipelining to negate that latency.
Trying to recoup space at a slow period, I remember using Repack on New Years Eve on my local computer for a multi TB production DB and waiting hours and hours hoping my connection doesn’t drop. When it dropped, I had to restart all over again.
I thought it would be a simple process but it ended up taking many hours and a few retries. Holy hell it was a miserable experience and it ruined my New Years Eve.
I’m so happy this is now built in.
Having worked on clusters where bloat was an ongoing serious problem, this looks really useful! Thanks for doing this work.
I would have loved REPACK CONCURRENTLY when we were generating millions of dead tuples an hour on our trading database …
Sadly it doesn’t support partitions yet, which is typical when reaching sufficient scale that bloat management really matters. People often mention disk space when talking about dead tuples, but the real killer is what’s hidden under - bloat in the ctid & btree leaf linked lists make every lookup feel like table scans…!
Thankfully HOT (Heap-only Tuples) has saved us since. Virtually no dead tulles generated, and they get otherwise collected during regular ops like SELECT, amortizing cost over time, and preventing dangerous build up.
[flagged]
[flagged]
I haven't used these extensions, but I like the direction of being able to extend Postgres by tailing its own internals (pg_squeeze approach) than triggers (pg_repack).
We (the SpiceDB developers) took a similar approach to solve a different problem a few years ago: implementing strict serializability in Postgres[0]. By hooking into the transactions layer themselves (pg_xact), we could layer on our own MVCC reusing the real Postgres transactions to compute visibility and total ordering. When you can just tail the Postgres internals, you don't even need to implement a Postgres extension, which can keep it portable across locked-down, managed Postgres offerings.
Do folks know of other clever implementations of behavior by "spying" on the Postgres internals?
[0]: https://m.youtube.com/watch?v=_f6fHy4DWWM