pg_squeeze
A PostgreSQL extension that automatically removes table bloat and optionally sorts tuples by an index, using background workers and logical decoding.
What is pg_squeeze?
pg_squeeze is a PostgreSQL extension that automatically reclaims unused space (bloat) from tables and can physically reorder tuples according to a specified index. It performs these operations concurrently with regular reads and writes, similar to the CLUSTER command but without blocking access. The project aims to replace pg_repack by implementing functionality purely on the server side using modern PostgreSQL features.
Database administrators and DevOps engineers managing PostgreSQL databases who need to maintain performance by reducing table bloat without downtime. It's particularly valuable for teams running large, write-heavy databases where table fragmentation impacts query performance.
Developers choose pg_squeeze over alternatives like pg_repack because it's a pure server-side implementation that leverages PostgreSQL's background workers and logical decoding for simpler configuration and unattended operation. It provides finer control over scheduling, tablespace management, and concurrency impact with minimal overhead compared to trigger-based solutions.
Overview
A PostgreSQL extension for automatic bloat cleanup
Use Cases
Best For
- Automated bloat cleanup in PostgreSQL databases without manual intervention
- Reorganizing tables according to specific indexes while maintaining read/write access
- Moving tables and indexes between tablespaces without downtime
- Managing large, frequently updated tables where VACUUM FULL would cause unacceptable blocking
- Scheduled maintenance operations in multi-tenant database environments
- Replacing pg_repack with a simpler, server-only architecture
Not Ideal For
- Environments where PostgreSQL configuration cannot be altered (e.g., restrictive managed cloud services), as it requires wal_level=logical and shared_preload_libraries
- Tables lacking primary keys or replica identity indexes, since an identity index is mandatory for processing
- Systems with critically limited disk space, given the need for approximately double the table size during operation
- Databases with frequent schema changes, because ALTER TABLE commands during processing can cause failures and retries
Pros & Cons
Pros
Runs entirely within PostgreSQL, eliminating client-side dependencies and simplifying deployment compared to tools like pg_repack.
Uses logical decoding to capture changes without triggers, allowing reads and writes during processing with minimal blocking.
Supports cron-like schedules via the squeeze.tables table, enabling background workers to automatically check and process bloated tables.
Can move tables and indexes to different tablespaces during reorganization, aiding in storage optimization without downtime.
Cons
Requires PostgreSQL server configuration changes (wal_level, replication slots, shared_preload_libraries) and a restart, which can be disruptive in production.
Needs free disk space about twice the size of the target table and indexes during operation, posing challenges for large or space-constrained databases.
Tables must have an identity index; if absent, manual setup with ALTER TABLE is required, adding complexity for schemas without proper indexes.
Processing can abort if table definitions are altered concurrently, necessitating retries and careful scheduling to avoid disruptions.
Open Source Alternative To
pg_squeeze is an open-source alternative to the following products:
Frequently Asked Questions
Related Projects
CitusDistributed PostgreSQL as an extension
AGEGraph database optimized for fast analysis and real-time data processing. It is provided as an extension to PostgreSQL.
ZomboDBMaking Postgres and Elasticsearch work together like it's 2023
pg_cronRun periodic jobs in PostgreSQL
Found a gem we're missing?
Open-Awesome is built by the community, for the community. Submit a project, suggest an awesome list, or help improve the catalog on GitHub.