PG_SQUEEZE – SHRINKS TABLES BETTER THAN VACUUM

 pg_squeeze is an open source PostgreSQL extension that enables automatic and transparent fixing of bloated tables – without extensive table locking. The process fully works in the background.

KEY BENEFITS

• More aggressive space reduction
• Close-to lock-free table reorganization
• Ability to move tables between tablespaces (without downtime)
• Ability to cluster tables (without downtime)
• Builtin advanced scheduling
• Fully Open Source

pg_squeeze is not a replacement for autovacuum – it is an add-on to perform cleanups even better. 

PostgreSQL uses a mechanism called “MVCC” (Multi Version Concurrency Control) to store data. As the name already suggests, MVCC will hold various versions of a row to support as much concurrency as possible. At some point, those additional rows must be removed from the storage system and this is where VACUUM comes along.

Unlike with built-in commands “VACUUM FULL” or “CLUSTER”, with “pg_squeeze” there are no extended periods of full-table locking, and consequently reads and writes are not blocked during the rebuild. Also the rebuilding process is very efficient due to a novel approach of using transaction log files and logical decoding (instead of triggers) to capture possible data changes to the table being rebuilt. First of all, this helps save disk space and IO throughput, but even more importantly, it enables very short locking times, making it a perfect fit for mission-critical OLTP systems.

How does pg_squeeze work?

1. The extension is implemented as a background-worker process (a framework introduced in version 9.4) that periodically monitors user-defined tables.
2. When it detects that a table has exceeded the “bloat threshold”,
3. it kicks in and rebuilds that table automatically.

 

 

Rebuilding happens concurrently in the background with minimal storage and computational overhead due to the use of Postgres’ built-in replication slots together with logical decoding to extract possible table changes happening during the rebuild from XLOG. Bloat threshold is of course configurable and the bloat ratio calculation is based on the Free Space Map (taking FILLFACTOR also into account) or under certain conditions on the “pgstattuple” extension when it’s available. Additionally, many customization parameters like “minimum table size” can be set with non-suitable tables being ignored. Moreover, reordering using an index or moving the table or indexes to a new tablespace are possible.

PG_SQUEEZE 1.3 DOWNLOAD

The newest version of pg_squeeze can be downloaded from github.com.

DOWNLOAD PG_SQUEEZE >>