INTRODUCING PG_SQUEEZE – A POSTGRESQL EXTENSION TO AUTO-REBUILD BLOATED TABLES

One of the few areas where out-of-the-box functionality by PostgreSQL is not 100% satisfying, is the “bloat problem”. Combating bloat, or just trying to ensure that your table data is physically ordered according to some column(s) (a.k.a. clustering) required accepting some inconvenient compromises until now. Extended periods of full table locking (no read or write activities) with built-in VACUUM FULL or CLUSTER commands or involving third party tooling, usually meaning “pg_repack”, were necessary. “pg_repack” offers good benefits like a lot smaller full-lock time, ordering by specific columns, but needs a bit of fiddling around – installing the extension, identifying bloated tables, running their command line client, and for larger tables it could also temporarily grow the disk size unnecessarily as it uses triggers to store the modifications made to tables during the pre-building phase.

To alleviate the situation, on behalf of the Cybertec development team, I’m really glad to announce a new bloat-painkiller called “pg_squeeze”! I myself, with my stereotypically calm Nordic temper, don’t usually get too excited by a piece of software, but this time as a day-to-day PostgreSQL user I must say that I’m really impressed – absolutely great piece of work! And also I wonder why nothing like that came about earlier.

What does pg_squeeze do exactly?

pg_squeeze is a PostgreSQL extension implementing a background worker process (one per DB) that periodically monitors tables defined by the user and when it detects a table crossing the “bloat threshold”, it kicks in and rebuilds that table automatically! Rebuilding happens concurrently in the background with minimal storage and computational overhead due to using Postgres’ built-in replication slots together with logical decoding to extract possible table changes happening during the rebuild from XLOG. Bloat threshold is configurable and bloat ratio calculation is based on the free space map or under certain conditions based on concepts of “pgstattuple” extension. Additionally minimum table size can be set, with smaller tables being ignored. Additional requirement for the table to be considered for rebuilding is that they need to have a primary key or unique constraint defined.

Sample setup

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

# Download and install the extension   git clone …   export PGCONFIG=/usr/bin/pg_config       # point it to your desired Postgres installation   make && sudo make install   cat <<-EOF>> testcluster/postgresql.conf   wal_level=logical   max_replication_slots = 1   shared_preload_libraries = 'pg_squeeze'   EOF   pg_ctl -D  testcluster restart   psql -c “CREATE EXTENSION pg_squeeze”   psql -c “INSERT INTO squeeze.tables \   (tabschema, tabname, first_check) \   VALUES ('public', 'foo', now());”   psql -c “SELECT squeeze.start_worker()”    # PS! not needed when we define the list of “squeezed”   # databases in postgresql.conf

Details

In addition to the above mentioned option to list databases and tables taking part in the auto-rebuild, also following “rebuild decision” aspects can be configured for every table by adjusting values in the “squeeze.tables” table. NB! Only this table and start/stop_worker() calls are meant to be the only “user interface” provided for the extension, with other tables/functions meant for internal use (although it is possible to launcher).

• Bloat threshold in percentage (“free_space_extra” param), defaulting to 50%
• Minimum disk space in megabytes the table must occupy to be eligible for processing
• An index name to physically order tuples according to keys of that index
• New tablespace for table/indexes can be specified
• Minimum time between two rebuilds (safety)

Additionallyy provided on global (database) level:

• squeeze.log – table storing rebuild events and their durations to see which tables get bloated the most.
• squeeze.errors – table contains errors that happened during squeezing. Normally errors should only be of type “DDL being performed” (adding of a new column for example) or “max_xlock_time” reached.
• squeeze.max_xlock_time – parameter specifying maximum exclusive lock holding time during the “table switch”.

Also due to using logical decoding, this means that only newer versions of PostgreSQL starting from version 9.4 can be taken into consideration.

Currently “pg_squeeze” only supports the latest PostgreSQL 9.6, but we’re thinking of backporting it also to 9.5.

Configuring automatic start of “squeeze” workers

If you want the background worker to start automatically during startup of the whole PostgreSQL cluster, add entries like this to “postgresql.conf”, selecting appropriate databases and a role for the worker. More explanations on that are available from the README.

1 2 3

squeeze.worker_autostart = 'mydb1 mydb2'   squeeze.worker_role = postgres

Grab the code here a try it out! Questions and feedback welcome.