Pg_squeeze: An extension to fix table bloat | Hacker News Reader

Pg_squeeze: An extension to fix table bloat

cybertec-postgresql.com

63 pointssoheilpro16 comments

We looked at adding support for this on Crunchy Bridge, and at the time opt'ed for pg_repack as it seemed bit like too much complexity and magic going on with automatically running and the background worker. The promise of automatically and proactively managing bloat is definitely an exciting one so optimistic to see it continue to improve and evolve. We'll personally probably take a fresh look next year, but for now pg_repack has definitely been a big win for users.

I used pg_repack at a very large scale, and one issue I ran into was write amplification. Since it used triggers to keep a log of rows changed, writes during repack were 2x normal.

pg_squeeze use of replication slots seems cool because it just tells Postgres to save WAL it already wrote, no 2x amplification.

Good point.

I wonder if pg_squeeze would work well in a large-scale heavy-writes case. There is a very sad and hard limit of single-threaded walsender that can saturate a single core (depends on CPU and schema / writes complexity; for example, I saw systems where it happened at ~2 WAL/s, or roughly 1-2 TiB of WAL data generated per day). So at some scale, the process might not converge.

Also, we apply changes in the same system, there are 2x writes anyway – though, it happens asynchronously with pg_squeeze, unlike with triggers (though, that "delta" table pg_repack temporarily writes to, it's very light in terms of indexes, and we do only INSERTs, so this amplification is not 2x in terms of IO and timing overhead, right?)

Anybody here familiar with the pluggable storage API? It seems like there were a couple of promising projects that have since been abandoned, like zheap and zedstore. Even Citus Columnar seems to have stalled in functionality. Are there still major limitations for the API? It seems like there is a lot of demand for different storage methodologies with different tradeoffs, and I'm kinda disappointed that this progress has stalled.

Didn’t the Citus columnar get rolled into the main Citus extension?

That appears to be the case:

https://github.com/citusdata/cstore_fdw

"Important notice: Columnar storage is now part of Citus

Columnar storage is now part of the Citus extension, which uses the table access method API to give a much more native experience."

There are lots of really good postgres extensions that provide features that seem generally useful for a wide audience. How often do good features from extensions make it in to base postgres? I’m generally very wary of extensions and plugins in general and tend to just make do with what’s in the base distribution of whatever software I’m using.

Not often. And depending of what is considered as base – only the core Postgres code, or contrib modules (shipped with it and supported by community). Extension/contrib -> core is a very rare event. For example, it happened with XML type and functions originated from contrib/xml2, and with contrib/tsearch2 which became FTS in core. But it's a very rare thing. And based on just these 2 examples, you should have -2 in the name:)

As for external extension becoming a contrib module, it's not a frequent event as well:

- Some "1000+ PostgreSQL EXTENSIONs" list https://gist.github.com/joelonsql/e5aa27f8cc9bd22b8999b7de8a...

- Current list of contrib modules – 58 modules https://gitlab.com/postgres/postgres/tree/master/contrib

- Same list 10 years ago, Postgres 9.2 – 53 modules https://gitlab.com/postgres/postgres/tree/REL9_2_STABLE/cont...

repo: https://github.com/cybertec-postgresql/pg_squeeze

Looks like this is somewhat similar to doing a pg_repack regularly, but with a slightly different mechanism?

How much storage overhead does the process take? pg_repack needs size for a full copy minus bloat. Is pg_squeeze the same?

Can someone with knowledge please explain what the use cases are for this vs. just letting autovacuum run?

Autovacuum essentially runs a regular VACUUM, which helps you prevent new table bloat. However, to avoid locking issues, it does not fully rewrite the table, so it does not remove existing table bloat.

If you want to remove existing table bloat you either need a VACUUM FULL (takes an exclusive lock, impacting your regular workload thus autovacuum doesn't do it), or use an extension that does an online version of that, i.e. pg_repack or pg_squeeze.

Edit: Also, to clarify, with table bloat in this context I mean pages in the table that are not actually needed, but were added to the table previously to store data. Existing pages couldn't be used because rows were still marked as dead, i.e. autovacuum hadn't yet gotten around to clean them up before new data came in.

There's a use case to replace CLUSTER, which requires downtime and needs to be rerun for new/updated data.

These tools remove bloat. Autovacuum removes dead tuples. They are often confused, but dead tuple and bloat are different though connected concepts.

A lot of accumulated dead tuples (row versions marked dead but still present occupying space) are converted to bloat (empty space, gaps) when VACUUM cleans them up.

No comparison test? No party!

Reply on news.ycombinator.com