pg_squeeze (tablespace reorganization)

Step 1: Configure cluster parameters

1. Log on to the PolarDB console. On the Clusters page, find the target cluster and click its ID.

2. In the left-side navigation pane, choose Settings and Management > Parameters.

3. On the Parameters page, modify the following parameters:

   • wal_level: Set to logical. Required for logical decoding; slightly increases WAL generation.

   • max_replication_slots: pg_squeeze needs at least 1 + squeeze.workers_per_database replication slots per enabled database. Increase by at least 2 (e.g., from 10 to 12).

   • shared_preload_libraries: Append ,pg_squeeze to the existing value. For example, if the original value is pg_stat_statements, change it to pg_stat_statements,pg_squeeze.

4. Restart the cluster for the changes to take effect.

Step 2: Install and start the extension

Enable pg_squeeze in the target database.

1. Connect to the target database.

2. Run the following SQL statements to create the extension and start the scheduler worker. This process periodically checks for and initiates compaction tasks based on the configuration in squeeze.tables.

   -- Create the extension in the target database
   CREATE EXTENSION pg_squeeze;
   
   -- Start the background scheduler process
   -- This operation only needs to be performed once for each database where pg_squeeze will be used
   SELECT squeeze.start_worker();

3. (Optional) Verify the background process started. A row with application_name = squeeze scheduler confirms success.

   SELECT * FROM pg_stat_activity WHERE application_name LIKE 'squeeze%';

Step 3: Configure tables to process

Register tables for pg_squeeze and define their compaction schedule.

1. Ensure the table has a replica identity: pg_squeeze uses this to match rows between old and new tables. A primary key serves as the default replica identity.

   • Check: Run \d+ your_table_name and verify replica identity is DEFAULT or FULL.

   • Set: If the table has no primary key but has a unique index (e.g., your_table_uidx), set it as the replica identity:

     ALTER TABLE your_table_name REPLICA IDENTITY USING INDEX your_table_uidx;

2. Register the table in squeeze.tables: Insert table information and scheduling policy into squeeze.tables. The schedule field uses a cron-like format.

   -- Example 1: Check the public.foo table every day at 2:30 AM
   INSERT INTO squeeze.tables (tabschema, tabname, schedule)
   VALUES ('public', 'foo', ROW(ARRAY[30], ARRAY[2], NULL, NULL, NULL)::squeeze.schedule);
   
   -- Example 2: Check the public.bar table every Sunday at 4:00 AM and sort by its primary key after compaction
   INSERT INTO squeeze.tables (tabschema, tabname, schedule, clustering_index)
   VALUES ('public', 'bar', ROW(ARRAY[0], ARRAY[4], NULL, NULL, ARRAY[0])::squeeze.schedule, 'bar_pkey');

   The structure of the schedule field is (minutes, hours, days of month, months, days of week). NULL represents any value. All available fields are listed in Appendix: squeeze.tables configuration table.

Step 4: Monitor and verify

Monitor compaction task status through pg_squeeze views.

-- View active compaction tasks in the current database
SELECT * FROM squeeze.get_active_workers();

-- View the 10 most recent successful compaction history records
SELECT * FROM squeeze.log ORDER BY finished DESC LIMIT 10;

-- View failed compaction records and their causes
SELECT * FROM squeeze.errors;

Step 5: (Optional) Disable scheduled compaction

To stop scheduled compaction for a table, delete its row from squeeze.tables.

DELETE FROM squeeze.tables WHERE tabschema = 'public' AND tabname = 'foo';

Tune concurrency and lock behavior

Set squeeze.max_xlock_time to limit how long the exclusive lock is held during the final compaction phase, preventing long blocks on business queries.

-- Set the maximum exclusive lock holding time to 100 milliseconds
SET squeeze.max_xlock_time TO 100;

If the lock exceeds this limit, pg_squeeze releases it, processes pending changes, and retries. Repeated timeouts cause the task to fail. Schedule compaction during off-peak hours, or increase this value if timeouts persist.

Configure concurrent workers

Set squeeze.workers_per_database greater than 1 to compact multiple tables in parallel within a single database.

-- Allow a maximum of 2 concurrent compaction worker processes per database
SET squeeze.workers_per_database = 2;