[HN Gopher] Pg_squeeze: An extension to fix table bloat
___________________________________________________________________
Pg_squeeze: An extension to fix table bloat
Author : soheilpro
Score : 50 points
Date : 2022-10-04 15:25 UTC (7 hours ago)
(HTM) web link (www.cybertec-postgresql.com)
(TXT) w3m dump (www.cybertec-postgresql.com)
| craigkerstiens wrote:
| 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.
| levkk wrote:
| 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.
| samokhvalov wrote:
| 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?)
| darksaints wrote:
| 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.
| brightball wrote:
| Didn't the Citus columnar get rolled into the main Citus
| extension?
| efxhoy wrote:
| 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.
| samokhvalov wrote:
| 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...
| oa335 wrote:
| Can someone with knowledge please explain what the use cases are
| for this vs. just letting autovacuum run?
| lfittl wrote:
| 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.
| samokhvalov wrote:
| 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.
| dminor wrote:
| There's a use case to replace CLUSTER, which requires downtime
| and needs to be rerun for new/updated data.
| CubsFan1060 wrote:
| Looks like this is somewhat similar to doing a pg_repack
| regularly, but with a slightly different mechanism?
| mzs wrote:
| repo: https://github.com/cybertec-postgresql/pg_squeeze
| notorandit wrote:
| No comparison test? No party!
| tpetry wrote:
| How much storage overhead does the process take? pg_repack needs
| size for a full copy minus bloat. Is pg_squeeze the same?
___________________________________________________________________
(page generated 2022-10-04 23:01 UTC)