https://www.cybertec-postgresql.com/en/products/pg_squeeze/ * EN + DE + ES + PL + ET + SV [github][svg] Blog Cybertec LogoCybertec Logo * Services + Requirement Analysis + PostgreSQL consulting + PostgreSQL migration o Migration from Oracle o Migrating from MySQL / MariaDB o CYBERTEC Migrator + PostgreSQL Infrastructure o Setup & Installation o Kubernetes o Database architecture + PostgreSQL development o Database modeling o Functions & Features + Update & Upgrade + Optimization & Security o PostgreSQL Health Check o Performance Tuning o Enterprise Security o Security Audit + Troubleshooting & Data Recovery + PostgreSQL clustering and HA o Clustering and failover o High availability with Patroni o Synchronous and asynchronous replication o Scaling with PL/Proxy + Spatial Services o GIS Tooling o PostGIS OSM Downloader + CYBERTEC Partner Network * Support + Standard PostgreSQL Support o Product Support o 9/5 Basic Support o 24/7 Basic Support o 24/7 Enterprise Support + Advanced PostgreSQL Support o 9/5 Remote DBA o 24/7 Remote DBA o 9/5 Dedicated DBA o 24/7 Cloud-based Support + Support for Reseller o 3rd Level Support o CYBERTEC Partner Network * Products + Our Products o CYPEX - Build Apps & Forms o CYBERTEC PostgreSQL Enterprise Edition o CYBERTEC Migrator o PostgreSQL Transparent Data Encryption o Scalefield - Private Cloud o Data Masking for PostgreSQL o PL/pgSQL_sec - Fully encrypted stored procedures + PostgreSQL Tools & Extensions o pg_timetable - Advanced Job Scheduling o pg_show_plans - Monitoring Execution Plans o pgwatch - PostgreSQL Monitoring Tool o pg_squeeze - Shrinks Tables o Walbouncer - Enterprise Grade Partial Replication o PGConfigurator - Visual PostgreSQL Configuration o ora_migrator o Patroni Environment Setup + Assessment Packages o Data Science Assessment Package o Start-Up Assessment Package o Spatial Data Assessment Package + CYBERTEC Partner Network * Training * PostgreSQL + Advantages of PostgreSQL + PostgreSQL Books + Solutions - Who uses PostgreSQL o PostgreSQL for Startups o PostgreSQL for governments and public services o Longlife solutions + Business Cases o Fraud Detection o PostgreSQL for biotech and scientific applications * Data Science + Data Science Overview + Machine Learning + Big Data Analytics * Contact pg_squeeze - Shrinks tables better than VACUUM pg_squeeze is an open source PostgreSQL extension that automatically fixes table bloat - without extensive table locking. The process works for you completely 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) * Built-in advanced scheduling * Fully Open Source pg_squeeze is not a replacement for autovacuum - it is an add-on to perform even better cleanups . PostgreSQL uses a mechanism called "MVCC" (Multi Version Concurrency Control) to store data. As the name already suggests, MVCC holds 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 the 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. 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 the table automatically. pg_squeeze fixes table bloatpg_squeeze fixes table bloat Rebuilding happens concurrently in the background with minimal storage and computational overhead, due to the use of PostgreSQL's logical decoding. pg_squeeze uses PostgreSQL's built-in replication slots to extract any table changes which happen during the rebuild. The bloat threshold is configurable and the bloat ratio calculation is based on the Free Space Map (it also takes FILLFACTOR 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 to ignore unsuitable tables. Moreover, reordering using an index or moving the table or indexes to a new tablespace are both possible. pg_squeeze 1.3 Download The newest version of pg_squeeze can be downloaded from github.com. Download pg_squeeze >> License * PostgreSQL License FAQ Q: Is it safe? What happens when power fails during a table rebuild? A: Yes, it's safe because rebuild happens within a single transaction. Furthermore, maximum lock time can also be set so that the extension can limit the time taken to switch the table. Q: How does it differ from "pg_repack"? A: It is different in the sense that it is more resource-friendly, because it doesn't use triggers. It automatically determines which tables are bloated - on its own - and does not require the use of a separate command-line tool. Q: What are the requirements for tables to be rebuilt? A: Besides hitting the "bloat threshold", the only hard-coded requirement is that a table needs to have an identity key, thus defining the primary key or a unique constraint. Q: Is PostgreSQL version 14 supported? A: Yes, with the newest update, it is. Do you want to know more about pg_squeeze? Contact us >> Other products that might interest you * CYBERTEC MIGRATOR Read more * YAIM - PostgreSQL IP Manager Read more * Patroni Environment Setup Read more logologo CYBERTEC PostgreSQL International GmbH Romerstrasse 19 2752 Wollersdorf AUSTRIA +43 (0) 2622 93022-0 office@cybertec.at twitter.com/PostgresSupport [github][svg]github.com/cybertec-postgresql [facebook][svg][linkedin][svg][xing][svg][yt_icon_mo][svg] Our Services * Administration * Replication * Consulting * Database Design * Support * Migration * Development SUPPORT CUSTOMERS Go to the support platform >> Newsletter Check out previous newsletters! Stay well informed about PostgreSQL by subscribing to our newsletter. [ ] [ ] Ja, ich mochte regelmassig Informationen uber neue Produkte, aktuelle Angebote und Neuigkeiten rund ums Thema PostgreSQL per E-Mail erhalten. Ich kann diese Zustimmung jederzeit widerrufen. Weitere Informationen finden Sie in der Datenschutzerklarung. Yes, I would like to receive information about new products, current offers and news about PostgreSQL via e-mail on a regular basis. Granting consent to receive the CYBERTEC Newsletter by electronic means is voluntary and can be withdrawn free of charge at any time. Further information can be found in the privacy policy. Yes, I would like to receive information about new products, current offers and news about PostgreSQL via e-mail on a regular basis. Granting consent to receive the Cybertec Newsletter by electronic means is voluntary and can be withdrawn free of charge at any time. Further information can be found in the privacy policy. Tak, chce regularnie otrzymywac wiadomosci e-mail o nowych produktach, aktualnych ofertach i nowosciach dotyczacych PostgreSQL. Wyrazenie zgody na otrzymywanie Newslettera Cybertec droga elektroniczna jest dobrowolne i moze zostac w kazdej chwili bezplatnie odwolane.Wiecej informacji mozna znalezc w polityce prywatnosci. Yes, I would like to receive information about new products, current offers and news about PostgreSQL via e-mail on a regular basis. Granting consent to receive the CYBERTEC Newsletter by electronic means is voluntary and can be withdrawn free of charge at any time. Further information can be found in the privacy policy. Jah, ma soovin saada regulaarselt e-posti teel teavet uute toodete, praeguste pakkumiste ja uudiste kohta PostgreSQLi kohta. Cyberteci uudiskirja elektroonilisel teel vastuvotmiseks nousoleku andmine on vabatahtlik ja seda saab igal ajal tasuta tagasi votta. Lisateavet leiate privaatsuseeskirjadest. Leave this field empty if you're human: [ ] © 2000-2022 CYBERTEC PostgreSQL International GmbH * IMPORTANT INFORMATION ABOUT COVID-19 * Contact * Data protection policy * Imprint * Terms and Conditions [ ]