How to use the pg_squeeze Extension in PostgreSQL

Managing table bloat is an important part of maintaining PostgreSQL performance, especially in databases that experience frequent INSERT, UPDATE, and DELETE operations. While VACUUM reclaims dead tuples for future reuse, it does not reduce the physical size of a table.

The pg_squeeze extension addresses this by rebuilding bloated tables online, reclaiming unused disk space, and minimizing locking during the process. In this guide, you'll learn how to install pg_squeeze, configure its required settings, perform manual squeeze operations, monitor background workers, schedule automatic squeeze jobs, and verify the results using practical examples.

Clone the repository

git clone https://github.com/cybertec-postgresql/pg_squeeze.git

Go to the folder named pg_squeeze

cd pg_squeeze/

Check the contents inside this folder like this

cybrosys@cybrosys:~/pg_squeeze$ ls

Result:

concurrent.c  Makefile                  pg_squeeze--1.2.sql       pg_squeeze--1.5--1.6.sql  pg_squeeze--1.8--1.9.sql  pg_squeeze.h    README.md  worker.c
expected      NEWS                      pg_squeeze--1.3--1.4.sql  pg_squeeze--1.6--1.7.sql  pg_squeeze.c              pg_squeeze.md   sql
LICENSE       pg_squeeze--1.2--1.3.sql  pg_squeeze--1.4--1.5.sql  pg_squeeze--1.7--1.8.sql  pg_squeeze.control        pgstatapprox.c  test

Now, compile this extension for the system-installed Postgres like this. We are using postgres version 18; that’s why we used the pg_config of postgres version 18.

cybrosys@cybrosys:~/pg_squeeze$ make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config

Now, install this extension against the system-installed postgres version 18.

cybrosys@cybrosys:~/pg_squeeze$ sudo make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config install

Check the extension is available in postgres like this.

select * from pg_available_extensions where name = 'pg_squeeze';

Result :

    name    | default_version | installed_version |                    comment                     
------------+-----------------+-------------------+------------------------------------------------
 pg_squeeze | 1.9             |                   | A tool to remove unused space from a relation.
(1 row)

Create the extension like this.

create extension pg_squeeze ;

Result:

ERROR:  pg_squeeze must be loaded via shared_preload_libraries
CONTEXT:  SQL statement "CREATE FUNCTION get_heap_fillfactor(a_relid oid)
RETURNS int
AS '$libdir/pg_squeeze', 'get_heap_fillfactor'
VOLATILE
LANGUAGE C"
extension script file "pg_squeeze--1.2.sql", near line 200

This is because we need to set this extension name to the parameter named shared_preload_libraries in postgresql.

Before that, check the current value of this parameter named shared_preload_libraries like this.

show shared_preload_libraries ;

Result :

 shared_preload_libraries 
--------------------------
(1 row)

Check the path of the postgres configuration file like this.

show config_file ;

Result:

               config_file               
-----------------------------------------
 /etc/postgresql/18/main/postgresql.conf
(1 row)

Open this file.

Sudo nano /etc/postgresql/18/main/postgresql.conf

Set the value for this parameter like this.

shared_preload_libraries = 'pg_squeeze'         # (change requires restart)

Restart postgresql to reflect the changed value in postgres.

sudo systemctl restart postgresql

Now, check the value of this parameter and ensure that it has changed.

show shared_preload_libraries ;

Result:

 shared_preload_libraries 
--------------------------
 pg_squeeze
(1 row)

Now, create the extension.

 create extension pg_squeeze ;

Check the functionality of this extension like this.

\dx+ pg_squeeze 

Result:

             Objects in extension "pg_squeeze"
                     Object description                     
------------------------------------------------------------
 function squeeze.cancel_task(integer)
 function squeeze.check_schedule()
 function squeeze.cleanup_tables()
 function squeeze.dispatch_new_tasks()
 function squeeze.finalize_task(integer)
 function squeeze.get_active_workers()
 function squeeze.get_heap_fillfactor(oid)
 function squeeze.get_heap_freespace(oid)
 function squeeze.pgstattuple_approx(regclass)
 function squeeze.squeeze_table(name,name,name,name,name[])
 function squeeze.start_worker()
 function squeeze.stop_worker()
 function squeeze.tables_internal_trig_func()
 function squeeze.update_free_space_info()
 table squeeze.errors
 table squeeze.log
 table squeeze.tables
 table squeeze.tables_internal
 table squeeze.tasks
 view squeeze.scheduled_for_now

Parameters related to this extension.

1. squeeze.max_xlock_time

Check this parameter’s metadata from pg_settings like this.

select * from pg_settings where name = 'squeeze.max_xlock_time';

Result:

-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
name            | squeeze.max_xlock_time
setting         | 0
unit            | ms
category        | Customized Options
short_desc      | The maximum time the processed table may be locked exclusively.
extra_desc      | The source table is locked exclusively during the final stage of processing. If the lock time should exceed this value, the lock is released and the final stage is retried a few more times.
context         | user
vartype         | integer
source          | default
min_val         | 0
max_val         | 2147483647
enumvals        | 
boot_val        | 0
reset_val       | 0
sourcefile      | 
sourceline      | 
pending_restart | f

Purpose:

The time to skip the final stage of squeezing when the locks held on the tables exceed this limit.

2. Squeeze.worker_autostart

Check this parameter’s metadata from pg_settings like this.

 select * from pg_settings where name = 'squeeze.worker_autostart';

Result:

-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------
name            | squeeze.worker_autostart
setting         | 
unit            | 
category        | Customized Options
short_desc      | Names of databases for which background workers start automatically.
extra_desc      | Comma-separated list for of databases which squeeze worker starts as soon as the cluster startup has completed.
context         | postmaster
vartype         | string
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | 
reset_val       | 
sourcefile      | 
sourceline      | 
pending_restart | f

Purpose:

The database where the background worker of extensions starts automatically.

3. squeeze.worker_role

Check this parameter’s metadata from pg_settings like this.

 select * from pg_settings where name = 'squeeze.worker_role';

Result:

-[ RECORD 1 ]---+--------------------------------------------------------------------------------------------------------------------------
name            | squeeze.worker_role
setting         | 
unit            | 
category        | Customized Options
short_desc      | Role that background workers use to connect to database.
extra_desc      | If background worker was launched automatically on cluster startup, it uses this role to initiate database connection(s).
context         | postmaster
vartype         | string
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | 
reset_val       | 
sourcefile      | 
sourceline      | 
pending_restart | f

Purpose:

This parameter specifies the name of the user used by the background worker of this pg_squeeze extension used to connect to the database.

4. squeeze.workers_per_database

Check this parameter’s metadata from pg_settings like this.

select * from pg_settings where name = 'squeeze.workers_per_database';

Result:

-[ RECORD 1 ]---+-----------------------------------------------------------------------
name            | squeeze.workers_per_database
setting         | 1
unit            | 
category        | Customized Options
short_desc      | Maximum number of squeeze worker processes launched for each database.
extra_desc      | 
context         | postmaster
vartype         | integer
source          | default
min_val         | 1
max_val         | 8
enumvals        | 
boot_val        | 1
reset_val       | 1
sourcefile      | 
sourceline      | 
pending_restart | f

Purpose:

This parameter specifies the number of workers for squeezing on each database.

View related to the pg_squeeze extension.

We can see the view definition of any view by using the \d+ command before the view name like this.

 \d+ squeeze.scheduled_for_now

Result:

                       View "squeeze.scheduled_for_now"
  Column   |  Type   | Collation | Nullable | Default | Storage | Description 
-----------+---------+-----------+----------+---------+---------+-------------
 table_id  | integer |           |          |         | plain   | 
 tabschema | name    |           |          |         | plain   | 
 tabname   | name    |           |          |         | plain   | 
View definition:
 SELECT i.table_id,
    t.tabschema,
    t.tabname
   FROM squeeze.tables_internal i,
    pg_stat_user_tables s,
    squeeze.tables t,
    pg_class c,
    pg_namespace n
  WHERE t.tabschema = s.schemaname AND t.tabname = s.relname AND i.table_id = t.id AND n.nspname = t.tabschema AND c.relnamespace = n.oid AND c.relname = t.tabname AND ((t.schedule).minutes IS NULL OR (EXTRACT(minute FROM now())::integer = ANY ((t.schedule).minutes::integer[]))) AND ((t.schedule).hours IS NULL OR (EXTRACT(hour FROM now())::integer = ANY ((t.schedule).hours::integer[]))) AND ((t.schedule).months IS NULL OR (EXTRACT(month FROM now())::integer = ANY ((t.schedule).months::integer[]))) AND ((t.schedule).days_of_month IS NULL AND (t.schedule).days_of_week IS NULL OR (EXTRACT(day FROM now())::integer = ANY ((t.schedule).days_of_month::integer[])) OR (EXTRACT(dow FROM now())::integer = ANY ((t.schedule).days_of_week::integer[])) OR (EXTRACT(isodow FROM now())::integer = ANY ((t.schedule).days_of_week::integer[])));

Now, let’s start to use this pg_squeeze extension.

Start the pg_squeeze worker like this.

select squeeze.start_worker();

Result:

 start_worker 
--------------
 
(1 row)

Now, check the background activities from pg_stat_activity like this.

select * from pg_stat_activity where wait_event_type = 'Extension';

Result:

-[ RECORD 1 ]----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
datid            | 5
datname          | postgres
pid              | 35236
leader_pid       | 
usesysid         | 10
usename          | postgres
application_name | 
client_addr      | 
client_hostname  | 
client_port      | 
backend_start    | 2026-10-24 21:31:20.058338+05:30
xact_start       | 
query_start      | 2026-10-24 21:31:40.10096+05:30
state_change     | 2026-10-24 21:31:40.101477+05:30
wait_event_type  | Extension
wait_event       | Extension
state            | idle
backend_xid      | 
backend_xmin     | 
query_id         | 
query            | SELECT t.id, tb.tabschema, tb.tabname, tb.clustering_index, tb.rel_tablespace, tb.ind_tablespaces, t.tried >= tb.max_retry, tb.skip_analyze FROM squeeze.tasks t, squeeze.tables tb LEFT JOIN squeeze.get_active_workers() AS w ON (tb.tabschema, tb.tabname) = (w.tabschema, w.tabname) WHERE w.tabname ISNULL AND t.state = 'ready' AND t.table_id = tb.id ORDER BY t.id LIMIT 1
backend_type     | squeeze worker

Now, we can see that a new background worker is activated.

Now, stop the worker like this.

 SELECT squeeze.stop_worker();

Result:

-[ RECORD 1 ]-
stop_worker | 

Now, check is there any activities related to this extension are running or not.

 select * from pg_stat_activity where wait_event_type = 'Extension';

Result:

(0 rows)

Now, we can see that there is no background worker working in postgres based on this extension.

Set the values for the parameters related to the pg_squeeze extension like this.

 alter system set squeeze.worker_autostart = 'odoo_17';

Reload the postgres configuration like this.

 select pg_reload_conf();

Result:

-[ RECORD 1 ]--+--
pg_reload_conf | t

Now, check that the changed value is reflected here.

 show squeeze.worker_autostart ;

Result:

-[ RECORD 1 ]------------+-
squeeze.worker_autostart | 

Now, we can see that the value has not changed here.

This is the same thing when we set the value for this parameter named squeeze.worker_role.

 alter system set squeeze.worker_role = 'postgres';
 select pg_reload_conf();

Result:

-[ RECORD 1 ]--+--
pg_reload_conf | t

Now, check whether the value has changed for this parameter or not.

 show squeeze.worker_role ;

Result:

-[ RECORD 1 ]-------+-
squeeze.worker_role | 

Now, restart postgresql like this.

sudo systemctl restart postgresql

Now, check the values again.

show squeeze.worker_role ;

Result:

 squeeze.worker_role 
---------------------
 postgres
(1 row)

Check the value of the parameter named squeeze.worker_autostart.

show squeeze.worker_autostart;

Result:

 squeeze.worker_autostart 
--------------------------
 odoo_17
(1 row)

Now, create a sample table for testing this extension’s functionality.

 CREATE TABLE public.orders (
    id     serial PRIMARY KEY,       -- pg_squeeze needs a PK (or a replica identity index)
    amount numeric,
    notes  text
);
CREATE INDEX orders_amount_idx ON public.orders (amount);

Now, insert some sample values.

INSERT INTO public.orders (amount, notes)
SELECT random() * 1000, repeat('x', 200)
FROM generate_series(1, 200000);

Perform a delete operation to make some dead tuples.

DELETE FROM public.orders WHERE id % 2 = 0;   

Now manually squeeze using the function named squeeze_table() like this.

The 5 function arguments are :

• Schema name
• Table name
• Clustering index
• Tablespace
• Index tablespace

 SELECT squeeze.squeeze_table('public', 'orders', NULL, NULL, NULL);

Result:

ERROR:  library "pg_squeeze" may not be used as an output plugin

This is because PostgreSQL is trying to load pg_squeeze as a logical replication output plugin, which it is not. So, we need to set a value for the parameter named output_plugin_libraries in postgres.

alter system set output_plugin_libraries = 'pg_squeeze';

Reload the postgres configuration like this.

select pg_reload_conf();

Result:

 pg_reload_conf 
----------------
 t
(1 row)

Ensure that the value has changed or not.

show output_plugin_libraries ;

Result:

 output_plugin_libraries 
-------------------------
 pg_squeeze
(1 row)

Check the size of this table.

SELECT pg_size_pretty(pg_relation_size('public.orders')) AS table_size;

Result:

 table_size 
------------
 49 MB
(1 row)

Check the tuple statistics of this table like this.

SELECT * FROM squeeze.pgstattuple_approx('public.orders');

Result:

 table_len | scanned_percent | approx_tuple_count | approx_tuple_len | approx_tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | approx_free_space | approx_free_percent 
-----------+-----------------+--------------------+------------------+----------------------+------------------+----------------+--------------------+-------------------+---------------------
  51200000 |             100 |             100000 |         24436196 |        47.7269453125 |           100000 |       24436600 |       47.727734375 |            625000 |         1.220703125
(1 row)

Now, we can see the metadata is related to tuples of the orders table, and it includes the dead tuple-related info also.

Execute vacuum on this table.

vacuum orders ;

Now, check the statistics again.

SELECT * FROM squeeze.pgstattuple_approx('public.orders');

Result:

 table_len | scanned_percent | approx_tuple_count | approx_tuple_len | approx_tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | approx_free_space | approx_free_percent 
-----------+-----------------+--------------------+------------------+----------------------+------------------+----------------+--------------------+-------------------+---------------------
  51200000 |               0 |             100000 |         25800000 |            50.390625 |                0 |              0 |                  0 |          25400000 |           49.609375
(1 row)

Check the fill factor of this table.

SELECT squeeze.get_heap_fillfactor('public.orders'::regclass);  

Result:

 get_heap_fillfactor 
---------------------
                 100
(1 row)

Check the free space of this table.

SELECT squeeze.get_heap_freespace('public.orders'::regclass); 

Result:

get_heap_freespace 
--------------------
         0.49609375
(1 row)

Now, execute a manual squeeze operation on the orders table like this.

SELECT squeeze.squeeze_table('public', 'orders', NULL, NULL, NULL);

Result:

 squeeze_table 
---------------
 
(1 row)

Check the size again.

SELECT pg_size_pretty(pg_relation_size('public.orders')) AS table_size;

Result:

 table_size 
------------
 25 MB
(1 row)

Now, we can see that the table size is reduced from 49 MB to 25 MB by manually executing the squeeze operation.

SELECT squeeze.squeeze_table('public', 'orders', 'orders_amount_idx', NULL, NULL);

Result:

 squeeze_table 
---------------
 
(1 row)

The squeeze.squeeze_table() function rebuilds the orders table using the orders_amount_idx index.

During this process, all live rows are copied into a new, compact version of the table, removing any unused space or table bloat. After the rebuild, querying the CTID values shows the new physical locations of the rows.

Since ctid represents where a row is stored on disk, changes in these values confirm that the table has been successfully reorganized while keeping the data intact.

SELECT ctid, amount FROM public.orders LIMIT 10;

Result:

  ctid  |       amount       
--------+--------------------
 (0,1)  | 0.0138119880572773
 (0,2)  | 0.0212415184188686
 (0,3)  | 0.0218297417320379
 (0,4)  | 0.0454783323386998
 (0,5)  | 0.0549669542886377
 (0,6)  |   0.05649125211038
 (0,7)  | 0.0661987638113093
 (0,8)  | 0.0771984166287876
 (0,9)  | 0.0798987113208582
 (0,10) |  0.122694842656479
(10 rows)

Now, check the log from the table named squeeze.log.

SELECT tabname, started, finished - started AS duration,
       ins_initial, ins, upd, del
FROM squeeze.log
ORDER BY started DESC;

Result:

 tabname |             started              |    duration     | ins_initial | ins | upd | del 
---------+----------------------------------+-----------------+-------------+-----+-----+-----
 orders  | 2026-10-25 11:56:51.097255+05:30 | 00:00:00.435502 |      100000 |   0 |   0 |   0
(1 rows)

Now, we can see that the information related to the squeeze operation happened on the table named orders, and it includes the updated records during this time, deleted records, the start time, etc.

Now, in session A, just execute this manual squeeze command multiple times using the \watch command.

 SELECT squeeze.squeeze_table('public', 'orders', NULL, NULL, NULL);
 squeeze_table 
---------------
 
(1 row)
\watch

At the same time, open another session and check this function result

SELECT * FROM squeeze.get_active_workers();  

Result:

  pid  | tabschema | tabname | ins_initial | ins | upd | del 
-------+-----------+---------+-------------+-----+-----+-----
 81910 | public    | orders  |       69167 |   0 |   0 |   0
(1 row)

Now, we can see the metadata of active background workers related to the squeeze operation is like this.

Now, we can schedule the squeeze operation at regular intervals like this.

 INSERT INTO squeeze.tables
(
    tabschema,
    tabname,
    clustering_index,
    schedule
)
VALUES
(
    'public',
    'orders',
    'orders_amount_idx',
    ROW(
        ARRAY[
            0,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,32,33,34,35,36,37,38,39,
            40,41,42,43,44,45,46,47,48,49,
            50,51,52,53,54,55,56,57,58,59
        ],
        NULL,
        NULL,
        NULL,
        NULL
    )::squeeze.schedule
);

Now, check the records from the squeeze.tables like this

select * from squeeze.tables;

Result:

-[ RECORD 1 ]----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id               | 1
tabschema        | public
tabname          | orders
clustering_index | orders_amount_idx
rel_tablespace   | 
ind_tablespaces  | 
free_space_extra | 50
min_size         | 8
vacuum_max_age   | 01:00:00
max_retry        | 0
skip_analyze     | f
schedule         | ("{0,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,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59}",,,,)

The squeeze.tables catalog now shows that the orders table has been configured to use the orders_amount_idx index for the squeeze operation. The schedule field contains all minute values from 0 to 59, which means the pg_squeeze background worker checks this table every minute and performs the squeeze operation whenever the required conditions are met.

The pg_squeeze extension provides a practical way to reclaim unused table space while allowing normal database activity to continue with minimal interruption. Throughout this guide, you learned how to install and configure the extension, resolve common setup issues, execute manual squeeze operations, inspect worker activity, schedule automatic maintenance, and verify the effects using PostgreSQL system catalogs and helper functions. With these capabilities, pg_squeeze becomes a useful tool for maintaining storage efficiency and reducing table bloat in PostgreSQL databases.