From Alert to Online Repack: Playbook for PostgreSQL Table Bloat on Azure Database for PostgreSQL

I ran a experiment on my own Azure Database for PostgreSQL Flexible Server: deliberately bloat a table, build a monitoring job that would catch it, and then reclaim the space while keeping the application available.

This post is the scenario-specific playbook that came out of that experiment: first the detector, then the remediation built around pg_cron and pg_repack. It is intended for update- or delete-heavy tables with measurable physical bloat, enough temporary storage for an online rebuild, and a maintenance window in which brief lock waits and elevated I/O are acceptable.

Everything below was run in a lab. I validated the process end to end on Azure Database for PostgreSQL Flexible Server running PostgreSQL 17, with 32 GiB of storage and autogrow enabled.

I use bloatlab throughout as the name of my existing application database. Replace it with your own database name. Every table, timestamp, and command in this post came from that lab.

Setup

I loaded a table (customer_orders) with 3,000,000 rows (~1 KB payload each), then held back autovacuum first with a long-running transaction, then by explicitly disabling it on the table and ran repeated full-table UPDATE passes against random rows.

That’s the only artificial part of this: in production, the same accumulation happens naturally on any update- or delete-heavy table, just slower.

SELECT pg_size_pretty(pg_total_relation_size(‘customer_orders’)) AS total_size, pg_size_pretty(pg_relation_size(‘customer_orders’)) AS table_size, pg_size_pretty(pg_indexes_size(‘customer_orders’)) AS indexes_size;

 

After the bloat-inducing passes:

SELECT pg_size_pretty(pg_total_relation_size(s.relid)) AS total_size, pg_size_pretty(pg_relation_size(s.relid)) AS table_size, pg_size_pretty(pg_indexes_size(s.relid)) AS indexes_size, s.n_live_tup, s.n_dead_tup, round(100.0 * s.n_dead_tup / NULLIF(s.n_live_tup + s.n_dead_tup, 0), 2) AS dead_pct FROM pg_stat_all_tables s WHERE s.schemaname = ‘public’ AND s.relname = ‘customer_orders’;

 

Row count never moved. Physical size roughly quadrupled. That gap between row count and bytes on disk is the whole story of this post.

Part 1: build the alarm before you need it

I built a watchdog first instead of jumping straight to the fix because bloat is silent by default. PostgreSQL does not proactively alert you when a table grows unexpectedly. In many cases, the issue becomes visible only after the table has expanded significantly and starts affecting performance or storage.

Azure Monitor’s storage_percent metric can show storage trending toward an autogrow threshold, but remediation is more useful before additional storage has already been provisioned.

Step 1: a catalog check you can run anywhere

This query reads catalog statistics and does not scan table data. Validate permissions and workload policies before running it in production:

SELECT schemaname, relname, n_dead_tup, n_live_tup, round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tup_pct, autovacuum_count, last_vacuum, last_autovacuum, last_autoanalyze, last_analyze FROM pg_stat_all_tables WHERE n_live_tup > 0 ORDER BY dead_tup_pct DESC NULLS LAST;

Step 2: don’t trust dead_tup_pct on its own

Here’s that made me build a real watchdog instead of a one-off check. I polled the lab every 5 minutes and caught this the moment autovacuum finally ran:

last_autovacuum confirmed autovacuum ran at 13:12:06. Dead-tuple percentage dropped from 75% to 0% and the table stayed byte-for-byte the same size, 14,452,064,256 bytes, before and after. dead_tup_pct is derived from n_dead_tup, PostgreSQL’s estimated number of dead rows. I use it as an investigation signal, not as a direct measurement of reclaimable space.

It says nothing about whether the file itself shrank. A watchdog built only on this number would have reported healthy at 13:15 while the table was still sitting at 13 GB.

Step 3: get the physical truth with pgstattuple

This query performs a full table scan and is more resource-intensive than lightweight monitoring checks. Run it during off-peak hours to minimize impact; it provides an accurate view of table contents and reusable space.

CREATE EXTENSION IF NOT EXISTS pgstattuple; SELECT *, round(free_percent + dead_tuple_percent, 2) AS approx_bloat_pct FROM pgstattuple(‘public.customer_orders’);

 

Only 23.5% of that 13 GB file held live data. Another 23.5% was dead but not yet vacuumed, and 52.22% was already-vacuumed free space just sitting there reusable by future rows in this table, but not returned to the OS. Total physical waste: roughly 76% of the file (75.72%).

Index bloat can be checked the same way:

SELECT *, round(100.0 * (1 – avg_leaf_density/100), 2) AS index_bloat_pct FROM pgstatindex(‘public.idx_customer_orders_status’);

Step 4: find what’s stopping autovacuum from keeping up

Three checks worth running whenever bloat outpaces autovacuum. Long-running or idle-in-transaction sessions are the most common real-world cause:

SELECT pid, age(backend_xid) AS age_in_xids, now() – xact_start AS xact_age, now() – query_start AS query_age, state, query FROM pg_stat_activity WHERE state != ‘idle’ ORDER BY 2 DESC LIMIT 10;

Unused replication slots can also hold back vacuum:

SELECT slot_name, slot_type, database, xmin FROM pg_replication_slots ORDER BY age(xmin) DESC;

So can uncommitted prepared transactions:

SELECT gid, prepared, owner, database, transaction FROM pg_prepared_xacts ORDER BY age(transaction) DESC;

Also worth a quick check: is autovacuum_enabled explicitly turned off on the table, or is autovacuum_vacuum_scale_factor tuned too high for how fast the table changes?

Step 5: wire all of this into pg_cron

pg_cron is available on Flexible Server through azure.extensions, with no restart required. Its control tables (cron.job and cron.job_run_details) live in the database where the extension is created commonly postgres.

Jobs targeting another database use cron.schedule_in_database. In this article, bloatlab is the existing application database that contains customer_orders; replace it with your own database name.

This is the exact job I ran:

\c postgres
CREATE EXTENSION IF NOT EXISTS pg_cron;

\c bloatlab
— History table (created once, in the SAME database the job below runs in)
CREATE TABLE IF NOT EXISTS bloat_alert_history (
checked_at timestamptz NOT NULL DEFAULT now(),
schemaname text,
relname text,
table_size_bytes bigint,
dead_tup_pct numeric,
live_tuples bigint,
dead_tuples bigint,
alert_triggered boolean
);

\c postgres
— Idempotent: unschedule any previous copy of this job name first
SELECT cron.unschedule(jobid) FROM cron.job WHERE jobname = ‘bloat-ratio-check’;

— Every 5 min in this lab (fast feedback); 15-30 min is plenty in production
SELECT cron.schedule_in_database(
‘bloat-ratio-check’,
‘*/5 * * * *’,
$$
INSERT INTO bloat_alert_history
(schemaname, relname, table_size_bytes, dead_tup_pct, live_tuples, dead_tuples, alert_triggered)
SELECT
schemaname,
relname,
pg_total_relation_size(relid) AS table_size_bytes,
round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tup_pct,
n_live_tup,
n_dead_tup,
(round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) > 20) AS alert_triggered
FROM pg_stat_user_tables
WHERE relname = ‘customer_orders’;
$$,
‘bloatlab’,
null,
true
);

Here’s what actually happened when I ran it (re-verified live on a PostgreSQL 17 instance with pg_cron installed):

You are now connected to database “postgres” as user “postgres”.
CREATE EXTENSION
You are now connected to database “bloatlab” as user “postgres”.
CREATE TABLE
You are now connected to database “postgres” as user “postgres”.
unschedule
————
t
(1 row)

schedule_in_database
———————–
6
(1 row)

 

And triggering the job body manually inside bloatlab the same way pg_cron fires it every 5 minutes to confirm the insert actually works end-to-end. This is the exact body of the scheduled job:

INSERT INTO bloat_alert_history
(schemaname, relname, table_size_bytes, dead_tup_pct, live_tuples, dead_tuples, alert_triggered)
SELECT
schemaname,
relname,
pg_total_relation_size(relid) AS table_size_bytes,
round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tup_pct,
n_live_tup,
n_dead_tup,
(round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) > 20) AS alert_triggered
FROM pg_stat_user_tables
WHERE relname = ‘customer_orders’;
INSERT 0 1
SELECT * FROM bloat_alert_history;
checked_at | schemaname | relname | table_size_bytes | dead_tup_pct | live_tuples | dead_tuples | alert_triggered
——————————–+————+——————+——————-+————–+————-+————-+—————–
2026-09-02 09:52:26.002581+02 | public | customer_orders | 122880 | 0.00 | 1000 | 0 | f
(1 row)
SELECT jobid, jobname, schedule, database, active FROM cron.job ORDER BY jobid;
jobid | jobname | schedule | database | active
——-+——————–+————-+———-+——–
6 | bloat-ratio-check | */5 * * * * | bloatlab | t
(1 row)

Worth showing why the \c bloatlab matters: with the table created in postgres instead, registration still succeeds no error at setup time but running that same INSERT INTO bloat_alert_history query against bloatlab fails immediately:

ERROR: relation “bloat_alert_history” does not exist
LINE 1: INSERT INTO bloat_alert_history ^

One thing to get right: \c targets. cron.schedule_in_database’s job body executes inside whichever database you pass as its 4th argument (bloatlab here) not wherever you were connected when you called it. bloat_alert_history has to live in that same database, or every run fails with relation “bloat_alert_history” does not exist. The extension, and cron.job/cron.job_run_details, stay in postgres by convention; only the history table needs to move.

Important: as written, this doesn’t send a real notification. alert_triggered is just a boolean column the job only inserts a row into bloat_alert_history every 5 minutes.

Nobody gets paged, emailed, or messaged unless you separately wire that table (or the query) into a real channel: an Azure Monitor action group, a Logic App, or a Teams webhook. Until you do that, this is silent detection you have to go query yourself.

A 20% dead-tuple threshold worked well as a starting point tune it per table based on write pattern.

What the Watchdog Caught Over Eight Hours

SELECT to_char(checked_at,’YYYY-MM-DD HH24:MI’) as checked_at, live_tuples, dead_tuples, dead_tup_pct, pg_size_pretty(table_size_bytes) as table_size, alert_triggered
FROM bloat_alert_history
ORDER BY checked_at;

 

Five hours of continuous alerting with the table pinned at 13 GB that’s the exact silent, ongoing cost this kind of monitoring exists to catch. And the last row isn’t me manually re-checking: it’s the same cron job automatically confirming the pg_repack fix below worked, five minutes after it finished.

Keeping the history table itself in check

The watchdog asks, “Is this table bloated?” This maintenance job applies the same discipline to the watchdog’s history table. Cleanup is not as time-sensitive as detection, so a 12- to 24-hour schedule is generally sufficient:

 

SELECT cron.unschedule(jobid) FROM cron.job WHERE jobname = ‘bloat-alert-history-cleanup’; — Once every 24h at 03:00 UTC (use ‘0 */12 * * *’ for every 12h instead)
SELECT cron.schedule_in_database(
‘bloat-alert-history-cleanup’,
‘0 3 * * *’,
$$ DELETE FROM bloat_alert_history WHERE checked_at < now() – interval ’30 days’;
DELETE FROM cron.job_run_details WHERE end_time < now() – interval ‘7 days’;
$$,
‘bloatlab’,
null,
true
);

cron.job_run_details is pg_cron’s own run-history table, and it isn’t pruned automatically either so it gets cleaned up in the same job instead of a separate schedule. 30 days of history here is just a starting point; pick a window long enough to see the patterns you actually care about for a given table.

If you’d rather not maintain custom SQL for this, Azure has a native path too:

Monitor Using Metrics and Logs in Azure Database for PostgreSQL Flexible Server – Azure Database for PostgreSQL | Microsoft Learn

Portal’s Diagnose and solve problems blade also ships ready-made Autovacuum monitoring and Autovacuum blockers/wraparound workbooks, and Azure Advisor raises a High bloat ratio recommendation automatically once a server crosses its threshold.

Part 2: fix it online with pg_repack

Once the watchdog told me the table was bloated, the obvious next move is VACUUM FULL. I didn’t use it, and here’s why: it reclaims space correctly, but it holds an ACCESS EXCLUSIVE lock on the table for the entire rewrite. For a multi-GB table under live traffic, that’s real, customer-visible downtime.

pg_repack can reclaim the space while normal reads and writes continue for most of the run. It is an online alternative to VACUUM FULL, not a guarantee of zero user impact: it still needs ACCESS EXCLUSIVE locks (typically brief) during setup and the final swap, and lock contention, workload pressure, or an interrupted run can delay or affect the application. Here’s the mechanism, followed by every command I ran in this lab.

How it works under the hood

For a table with a primary key or unique index (required), pg_repack:

1. Creates a log table and a trigger that captures every INSERT/UPDATE/DELETE on the original table during the repack.


2. Bulk-copies the current live rows into a shadow table using a plain INSERT … SELECT, no exclusive lock, reading only live data and skipping all the bloated free space.


3. Rebuilds all indexes on the shadow table.


4. Replays everything the trigger captured onto the shadow table, iteratively, until caught up.


5. Takes a brief ACCESS EXCLUSIVE lock just long enough to swap the shadow table in for the original — a metadata-only rename, not a data rewrite.


6. Drops the old table and temporary objects, then runs ANALYZE.

Only steps 1 and 5 require ACCESS EXCLUSIVE locks. In this lab, I did not separately measure the exact lock-hold duration for those phases; they are typically short because they cover setup and metadata swap work rather than the bulk copy. That is not guaranteed: acquiring either lock can take longer when other transactions, DDL, or queued sessions are present, and final impact depends on workload, dependencies, server resources, and table activity. The bulk copy and index build run while normal traffic continues, but they still consume I/O, CPU, storage, and connection capacity.

Decision point: I treat pg_repack as an option only after the applicability checks pass. If physical bloat is confirmed, the table has a qualifying key, there is enough storage, I/O, and WAL headroom, and brief blocking is acceptable, I plan a controlled pg_repack run. If any of those checks fail, I choose the safer path for that problem: ordinary VACUUM for space reuse, REINDEX for index-only bloat, or VACUUM FULL during an approved maintenance window.

Everything I ran, in order

After allowing the extension through the server’s azure.extensions setting, create it in the target database; no server restart is required:

CREATE EXTENSION IF NOT EXISTS pg_repack;

Two things I had to know going in:

• azure_pg_admin is not a true PostgreSQL superuser on Flexible Server, so pg_repack needs -k / –no-superuser-check to run without complaint.


• TCP keepalives on the client connection turned out to be necessary in my lab more on that below.

The command that did the actual work:

pg_repack -d “host=<server>.postgres.database.azure.com port=5432 dbname=<db> \ user=<admin> sslmode=require keepalives=1 keepalives_idle=20 keepalives_interval=10 keepalives_count=6” \ -t public.customer_orders -k –echo

It completed cleanly in 13 minutes 28 seconds against a 13 GB table. Here’s the real –echo trace it produced, with my own annotations added:

— 1. Trigger + log table setup (brief exclusive lock on the original table for setup)
LOCK TABLE public.customer_orders IN ACCESS EXCLUSIVE MODE
SELECT repack.create_log_table(24851)
CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON public.customer_orders
FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger(‘order_id’)

— 2. Bulk-copy live rows only (no exclusive lock)
INSERT INTO repack.table_24851
SELECT order_id,customer_id,order_status,order_total,created_at,updated_at,payload
FROM ONLY public.customer_orders

— 3. Rebuild indexes on the new copy
CREATE UNIQUE INDEX index_24859 ON repack.table_24851 USING btree (order_id)
CREATE INDEX index_24861 ON repack.table_24851 USING btree (customer_id)
CREATE INDEX index_24862 ON repack.table_24851 USING btree (order_status)

— 4. Catch up on everything written during steps 2-3 (ran twice: bulk pass + mop-up pass)
SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)

— 5. Atomic swap — final exclusive-lock phase; duration depends on contention (rename operation, not a data copy)
LOCK TABLE public.customer_orders IN ACCESS EXCLUSIVE MODE
LOCK TABLE repack.table_24851 IN ACCESS EXCLUSIVE MODE
SELECT repack.repack_swap($1)
COMMIT

— 6. Cleanup and finalize
SELECT repack.repack_drop($1, $2)
ANALYZE public.customer_orders

 

When it doesn’t go cleanly the first time

I encountered this warning after a client-side connection dropped during the copy phase in my lab. Before retrying, inspect the table and repack schema to determine which temporary objects remain:

WARNING: The table’s row triggers are not migrated to the new table.

In this lab, the following commands removed the identified leftovers. Replace <oid> only after verifying the object identifiers, dependencies, and active sessions. Do not treat this as a universal cleanup sequence:

DROP TRIGGER IF EXISTS repack_trigger ON public.customer_orders;
DROP TABLE IF EXISTS repack.log_<oid> CASCADE;
DROP TYPE IF EXISTS repack.pk_<oid> CASCADE; — pg_repack’s own error handling can miss this one

What actually changed

In this lab, I observed no full application outage during the 13-minute 28-second run, but I did observe latency spikes and one statement-timeout event. Treat this as a test result, not a guarantee: pg_repack still acquires ACCESS EXCLUSIVE locks in critical phases, and production impact depends on lock contention, workload intensity, client timeouts, server headroom, and whether the run completes cleanly.

One thing pg_repack doesn’t do

pg_repack returns space to the filesystem inside the data directory, but it doesn’t shrink the Azure-provisioned storage tier itself. If Storage Auto-Growth already bumped into a server from, say, 32 GiB to 64 GiB before you remediate, the server stays provisioned and billed at 64 GiB afterward, since Flexible Server storage doesn’t scale back down.

That’s exactly why Part 1 and Part 2 of this post are meant to be used together: run pg_repack proactively, off the back of the pg_cron alert, before autogrow ever fires.

One more practical note: Azure Monitor’s storage_used / storage_percent metrics can lag real time by up to an hour. Verify results directly with pg_stat_user_tables / pgstattuple immediately after a repack, rather than trusting the portal graph right away.

Conclusion

Table bloat is manageable once you distinguish statistical signals from physical storage evidence.

The key is to treat dead_tup_pct as a hint rather than a verdict. Pair it with pgstattuple or a physical size check so that “autovacuum ran” is never mistaken for “the file became smaller.”

When physical bloat is confirmed and the prerequisites are satisfied, pg_repack can reclaim space while preserving availability for most of the operation—but it still requires careful planning for locks, resource consumption, and failure recovery.

There is no universal bloat percentage that requires remediation. Reusable free space may be acceptable when it is stable and causes no storage, performance, or maintenance pressure. Investigate when waste keeps growing, affects large tables, slows queries, threatens storage headroom, or indicates that autovacuum is falling behind or being blocked.

A bloated table does not always mean autovacuum is misconfigured, but it is a reason to check autovacuum health: last run time, dead tuple trend, long-running transactions, replication slots, table-level settings, and whether cleanup is keeping pace with the write workload.

Key takeaways

• Don’t rely on dead_tup_pct alone — pair it with pgstattuple or physical size checks for ground truth.


• Schedule a pg_cron job against pg_stat_user_tables so bloat gets caught in minutes, not after an Advisor recommendation.


• Consider pg_repack instead of VACUUM FULL when a table must remain available and the scenario fits: physical bloat is confirmed, a suitable primary key or unique index exists, temporary storage and I/O headroom are available, and brief lock attempts are acceptable.


• On Azure Database for PostgreSQL Flexible Server, use -k / –no-superuser-check when the administrative role is not a true PostgreSQL superuser. Configure client keepalives when network stability or long-running connections warrant them.


• After an interrupted pg_repack run, inspect and remove only verified leftover objects before retrying; do not run generic DROP commands without validating object identifiers and dependencies.


• Remember that pg_repack frees space inside the data directory; it does not reduce the Azure-provisioned storage tier. Use proactive monitoring to avoid unnecessary autogrow events.