4 ms·

Pg_squeeze: An extension to fix table bloat

63 points by soheilpro 4y ago

mzs 4y ago
repo: https://github.com/cybertec-postgresql/pg_squeeze https://github.com/cybertec-postgresql/pg_squeeze
oa335 4y ago
Can someone with knowledge please explain what the use cases are for this vs. just letting autovacuum run?
- lfittl 4y ago
  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.
- dminor 4y ago
  There's a use case to replace CLUSTER, which requires downtime and needs to be rerun for new/updated data.
- samokhvalov 4y ago
  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.
CubsFan1060 4y ago
Looks like this is somewhat similar to doing a pg_repack regularly, but with a slightly different mechanism?
notorandit 4y ago
No comparison test? No party!
craigkerstiens 4y ago
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 4y ago
  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 4y ago
    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?)
tpetry 4y ago
How much storage overhead does the process take? pg_repack needs size for a full copy minus bloat. Is pg_squeeze the same?
darksaints 4y ago
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 4y ago
  Didn’t the Citus columnar get rolled into the main Citus extension?
  - sbuttgereit 4y ago
    That appears to be the case: https://github.com/citusdata/cstore_fdw 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."
efxhoy 4y ago
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 4y ago
  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/e5aa27f8cc9bd22b8999b7de8aee9d47 https://gist.github.com/joelonsql/e5aa27f8cc9bd22b8999b7de8a... - Current list of contrib modules – 58 modules https://gitlab.com/postgres/postgres/tree/master/contrib 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/contrib https://gitlab.com/postgres/postgres/tree/REL9_2_STABLE/cont...

63 points by soheilpro 4y ago

mzs 4y ago

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

oa335 4y ago

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

lfittl 4y ago
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.
dminor 4y ago
There's a use case to replace CLUSTER, which requires downtime and needs to be rerun for new/updated data.
samokhvalov 4y ago
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.

CubsFan1060 4y ago

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

notorandit 4y ago

No comparison test? No party!

craigkerstiens 4y ago

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 4y ago
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 4y ago
  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?)

tpetry 4y ago

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

darksaints 4y ago

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 4y ago
Didn’t the Citus columnar get rolled into the main Citus extension?
- sbuttgereit 4y ago
  That appears to be the case: https://github.com/citusdata/cstore_fdw 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."

efxhoy 4y ago

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 4y ago
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/e5aa27f8cc9bd22b8999b7de8aee9d47 https://gist.github.com/joelonsql/e5aa27f8cc9bd22b8999b7de8a... - Current list of contrib modules – 58 modules https://gitlab.com/postgres/postgres/tree/master/contrib 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/contrib https://gitlab.com/postgres/postgres/tree/REL9_2_STABLE/cont...