A sluggish app on a VPS can have several causes, even when CPU, RAM and disk I/O look unremarkable. PostgreSQL table bloat is one possibility worth investigating, not a diagnosis to assume. Check whether autovacuum is keeping up with the workload before changing its settings.
This guide separates dead-row cleanup from physical bloat, explains autovacuum eligibility and resource controls, and sets out a scoped pg_repack workflow. PostgreSQL settings are checked against version 17; pg_repack examples use 1.5.3. Verify the installed versions before applying them. Online rebuilding still consumes resources and needs locks.
Why Bloat Happens and Why a VPS Can Be Sensitive to It
PostgreSQL MVCC keeps old row versions while another transaction might still need them. VACUUM removes eligible obsolete versions and makes their space reusable; HOT page pruning can also remove some versions during normal operations. A count of dead tuples is not the same as physical bloat, and a large file can contain free space that the workload will reuse.
On a resource-constrained VPS, excess table or index pages can add storage, scanning and cache costs. The impact depends on the workload and hosting resources. Measure physical layout and query behavior before attributing slow requests to bloat.
Autovacuum schedules VACUUM and ANALYZE as needed. VACUUM supports cleanup, visibility information and freezing; ANALYZE collects planner statistics. Default suitability depends on workload and table size. Standard VACUUM usually retains reusable space inside the file; it can return completely empty tail pages when the required exclusive lock is available. Shrinking a whole table generally requires a rewrite.
Separate Maintenance Signals from Bloat Measurements
Check whether maintenance runs, finishes and advances cleanup. Investigate old transactions or snapshots and relevant replication retention before assuming that more workers or a lower threshold will help. A cleanup horizon can prevent removal of versions still needed elsewhere.
Use this read-only query to prioritize investigation. n_live_tup and n_dead_tup are estimates; statistics collection and resets affect their interpretation. They do not measure reclaimable bytes.
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
Repeatedly high dead-tuple estimates and old maintenance timestamps warrant investigation, not an automatic rewrite. Use pg_stat_progress_vacuum for running VACUUM phases and compare them with locks and workload; VACUUM FULL appears in cluster progress instead. The progress reporting documentation describes the available views.
Use the active postgresql.conf for diagnostic settings. The following duration threshold is reloadable; an authorized administrator should validate and reload the configuration, then verify the effective value. Plan log access, retention and storage before enabling it.
# Active postgresql.conf; review after the investigation
log_autovacuum_min_duration = '5s'
This logs autovacuum actions lasting at least five seconds; enabled logging can also report actions skipped for conflicting locks or dropped relations. Zero logs all actions and can create substantial volume. The centralized logging guide can help organize evidence; check support status for the collector you deploy.
Measure table and index size alongside physical occupancy where appropriate. pgstattuple reports physical length, dead tuples and free space; pgstatindex reports B-tree layout. Provision the extension and required permissions before use. Large scans consume I/O, and concurrent changes mean their results are not one instantaneous snapshot. Slow index scans or a large index alone do not establish reclaimable bloat.
Autovacuum Eligibility, Pacing and Resource Limits
Change the control that matches the observed problem. A table waiting to become eligible, a lack of available workers, slow cleanup and an old snapshot are different constraints. Record baseline maintenance progress, application latency, CPU, I/O and memory before testing a candidate.
For ordinary PostgreSQL 17 update/delete vacuum eligibility, the threshold is the base threshold plus scale factor multiplied by estimated table rows. Insert-driven vacuum has separate thresholds. ANALYZE compares its threshold with inserted, updated and deleted rows since the last analysis. The following global values are candidates, not a universal small-VPS profile.
# postgresql.conf: PostgreSQL 17 eligibility candidates
autovacuum = on
autovacuum_vacuum_scale_factor = 0.1
autovacuum_vacuum_threshold = 50
autovacuum_analyze_scale_factor = 0.05
autovacuum_analyze_threshold = 50
A scale factor of 0.1 does not mean every 10% change immediately starts VACUUM: the base threshold, relevant counters, scheduler and worker availability also matter. Lower scale factors do not alter the launcher’s wake interval. Large tables may need individual thresholds; use the formula for the installed version rather than assuming later-version caps or formulas apply to PostgreSQL 17.
Use autovacuum-specific cost controls to test pacing. These account for estimated page costs and delays, not a fixed MB/s limit. Start from effective settings and workload evidence before changing the illustrative values below.
# postgresql.conf: autovacuum-specific pacing
autovacuum_vacuum_cost_limit = 200
autovacuum_vacuum_cost_delay = '2ms'
In PostgreSQL 17, autovacuum_vacuum_cost_delay defaults to 2ms and only inherits vacuum_cost_delay when set to -1. The autovacuum cost limit defaults to -1, inheriting vacuum_cost_limit. Global cost limits are normally balanced among workers; relevant per-table overrides can be excluded from that balancing. A larger limit or shorter delay can increase resource use. Faster completion does not automatically mean lower total workload or acceptable peak latency.
Budget worker concurrency and memory together with user queries and manual maintenance. The following values are explicit candidates for testing. PostgreSQL 17 requires a restart for autovacuum_max_workers; a reload does not apply that change.
# postgresql.conf: budget against actual available RAM
autovacuum_max_workers = 3
autovacuum_work_mem = '64MB'
maintenance_work_mem = '256MB'
autovacuum_naptime = '30s'
Three workers at 64 MiB imply about 192 MiB of autovacuum working memory, excluding process overhead. User queries, parallel workers and manual maintenance need additional headroom. At autovacuum_work_mem = -1, each worker inherits maintenance_work_mem instead. Naptime is the minimum interval between rounds on a database, not a promise that a particular table will be vacuumed every 30 seconds. More frequent checks and more workers can add overhead.
Freezing protects against transaction-ID and multixact wraparound; it is not merely space cleanup. PostgreSQL 17 defaults autovacuum_freeze_max_age to 200 million transactions and its multixact counterpart to 400 million. Do not raise these limits to defer inconvenient work without a separate capacity and age analysis. Start by reading current settings and database ages:
SHOW autovacuum_freeze_max_age;
SHOW autovacuum_multixact_freeze_max_age;
SHOW vacuum_freeze_table_age;
SHOW vacuum_freeze_min_age;
SELECT datname, age(datfrozenxid) AS xid_age,
mxid_age(datminmxid) AS multixact_age
FROM pg_database
ORDER BY xid_age DESC;
These ages count identifiers, not elapsed days. Low dead-tuple counts do not remove the need to freeze old identifiers. Investigate table and TOAST ages, long transactions, prepared transactions and relevant cleanup horizons with an administrator when age grows. Anti-wraparound work can run even when ordinary autovacuum is disabled. Do not substitute a routine pg_repack job for the documented wraparound recovery procedure.
Additional diagnostic settings can help correlate I/O with maintenance. Keep each setting in one effective configuration location and review its volume and platform cost after investigation.
# Optional diagnostic additions to postgresql.conf
log_checkpoints = on
log_temp_files = '4MB'
# Measure platform overhead before enabling timing:
# track_io_timing = on
Checkpoint activity coinciding with VACUUM is not proof that max_wal_size should increase. Distinguish timed and WAL-volume triggers, then check free space, archiving and recovery implications; max_wal_size is a soft threshold. Temporary files may come from sorts, hashes or other query work, so identify the producer before adjusting work_mem. Its per-operation, hash and parallel-worker multipliers still apply. Do not assume an ANALYZE observation is fixed by increasing query memory.
The autovacuum configuration reference documents the installed version’s parameter scopes. Validate changes, reload only reloadable parameters and schedule required restarts. Confirm effective settings and pending_restart rather than assuming a successful reload applied everything:
SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE name LIKE 'autovacuum_%'
OR name IN ('maintenance_work_mem', 'track_io_timing',
'log_autovacuum_min_duration')
ORDER BY name;
Tune Individual Tables and Review Indexes
For an illustrative frequently updated sessions table, compare row count, cleanup rate and query plans before changing its storage parameters. Keep the table-specific change separate from global tuning so the result can be attributed.
The example retains a scale factor of 0.02, so the threshold is not a fixed 50 rows: the scaled estimate is added. ALTER TABLE requires appropriate ownership/privileges and a lock; check the target and operational window first.
ALTER TABLE public.sessions
SET (autovacuum_vacuum_scale_factor = 0.02,
autovacuum_vacuum_threshold = 50,
autovacuum_analyze_scale_factor= 0.02,
autovacuum_analyze_threshold = 50);
A lower table fillfactor reserves room on newly packed pages for updates. HOT updates also require that changed columns are not referenced by ordinary indexes, with special handling for summarizing indexes, and that the page has enough space. Fillfactor alone does not guarantee HOT or lower total disk use; compare update patterns and HOT statistics.
ALTER TABLE public.sessions SET (fillfactor = 80);
-- Optional separate blocking rewrite, only after a tested maintenance plan:
-- VACUUM FULL public.sessions;
Changing fillfactor does not immediately rewrite existing contents. VACUUM FULL can repack the table but holds ACCESS EXCLUSIVE during its rewrite and needs extra disk space. The commented command is a separate alternative, not an automatic follow-up. A pg_repack workflow may reduce the periods of exclusive locking, but its prerequisites and resource costs still need evaluation.
Before removing an apparently unused index, check constraint dependencies and a representative workload cycle, including rare reports and administrative jobs. Test a supported index change in staging and plan production locking and recovery separately. Do not edit PostgreSQL system catalogs to mark an index invalid as an experiment. An unnecessary index can add write and maintenance work, but no fixed cost multiplier follows from its presence.
Plan a Scoped pg_repack Operation
pg_repack builds a replacement table, records concurrent changes through a trigger and log table, builds indexes, catches up and swaps the objects. Version 1.5.3 takes ACCESS EXCLUSIVE during initial setup and final swap/drop, and SHARE UPDATE EXCLUSIVE during the rest of a full-table repack. Normal DML can continue through much of the work; conflicting DDL and maintenance cannot. Lock acquisition and application impact are not guaranteed to be brief.
Verify a consistent PostgreSQL backup and restore procedure, and define acceptable lock waits, free-space margins and replication lag before beginning. The disaster recovery planning guide can help define recovery requirements. A configuration rollback is different from recovering data after a maintenance failure.
The official pg_repack manual covers installation. This workflow uses 1.5.3 with PostgreSQL 17; ensure the client binary, server library and SQL extension versions match. Install the required server package first, then have an authorized administrator prepare the extension in the target database:
CREATE EXTENSION IF NOT EXISTS pg_repack;
SELECT extversion FROM pg_extension WHERE extname = 'pg_repack';
Compare with pg_repack –version. IF NOT EXISTS does not upgrade an old extension. Use the documented upgrade process for the installed version; do not proceed through a mismatch.
Full-table repack requires a primary key or a valid, non-partial, non-expression UNIQUE index whose key columns are all NOT NULL. Without a suitable key it does not silently fall back to a fully locked rewrite. The manual calls for about twice the target table-plus-index size in additional free disk space: a 1 GiB target needs about 2 GiB extra, plus headroom for growth, change logs and WAL. Check each tablespace and WAL volume. Temporary tables are unsupported.
Version 1.5.3 permits a superuser or the owner of target tables and indexes using –no-superuser-check. The maintenance_owner placeholder below must meet that ownership requirement; the flag grants no privilege. Connect directly to PostgreSQL with approved authentication, using a protected credential mechanism. Start with one reviewed table and list scope first; dry-run does not establish lock availability, required runtime or adequate capacity.
# Scope preview only
pg_repack -h 127.0.0.1 -p 5432 -U maintenance_owner \
-d mydb -t public.sessions --no-superuser-check \
--no-order --wait-timeout=60 --no-kill-backend --dry-run
# After reviewing scope, space, permissions, locks and recovery
pg_repack -h 127.0.0.1 -p 5432 -U maintenance_owner \
-d mydb -t public.sessions --no-superuser-check \
--no-order --wait-timeout=60 --no-kill-backend
–no-order omits physical clustering; it does not remove the copy or resource cost. –no-kill-backend skips a table when the required lock cannot be obtained within the wait timeout. Without it, pg_repack can cancel conflicting queries and later terminate remaining backends. A skipped table was not successfully rewritten. To review eligible tables in one database, omit -t and retain -d; the next command is a preview only.
# Preview eligible tables in one database; does not rewrite them
pg_repack -h 127.0.0.1 -p 5432 -U maintenance_owner \
-d mydb --no-superuser-check --no-order \
--wait-timeout=60 --no-kill-backend --dry-run
-a/–all targets all cluster databases and cannot be combined with -d or -t. A broader run needs ownership, keys, space and lock review for every target. –jobs opens extra connections to parallelize index rebuilding during full-table repacks; it is not an I/O throttle. Monitor WAL, change-log growth, replication lag and user latency. Heavy writes can delay catching up to the source table.
After a run, inspect exit status, logs and skipped tables, then compare relation sizes, query plans and application correctness. pg_repack normally performs ANALYZE unless disabled. Investigate artifacts after an interrupted or failed run using the matching version’s documentation; do not blindly drop the extension with CASCADE or remove objects whose ownership and purpose are uncertain. Space savings and query speed are outcomes to measure.
A utility container or controlled host tool can supply the matching pg_repack client, but the extension library and SQL objects must still be installed for the database server. Verify connectivity, authentication and ownership from that environment. The container security guide provides wider context; separating the client does not remove the server-side dependency.
Maintain a Workload-Based Review Schedule
Choose review frequency from change rate and operational requirements. A calendar reminder is useful, but ongoing alerts are needed for stalled cleanup, age growth or storage pressure between reviews.
Compare dead-tuple estimates, completed maintenance, physical table/index growth and query behavior over a representative cycle. A monthly review is one possible schedule, not a sufficient safeguard for every workload. Investigate a change before deciding whether tuning, a query/index correction or a rewrite addresses it.
Reduce unnecessary updates and review indexes and fillfactor where evidence supports a change. Keep statistics appropriate to the data distribution. default_statistics_target is a general setting; a targeted statistics increase is available per column through ALTER TABLE … ALTER COLUMN … SET STATISTICS. More detailed statistics add analysis cost and do not fix physical bloat.
Alert on relevant maintenance failures, prolonged waits, age growth and low free space; choose thresholds from the workload. Confirm notifications arrive and distinguish successful repack from a skipped target. The VPS logging guide can support centralized investigation, alongside database and OS metrics.
Document the target, reason, tested version, operator, capacity assumptions, lock policy and stop criteria before a rewrite. Include how to investigate partial failure and when recovery is necessary. The recovery runbook guide offers a structure for these decisions.
Compare online tools by their actual locks, dependencies and recovery behavior. The MySQL online migration guide covers a different database and tooling; its availability assumptions cannot be transferred directly to pg_repack.
Choose the Action That Fits the Evidence
If cleanup eligibility is too late, evaluate thresholds. If workers cannot keep up, investigate I/O, memory, concurrency and cleanup horizons. If reusable free space is not a problem, a rewrite may be unnecessary. Consider repack only when physical space recovery justifies its full operational cost and prerequisites.
Compare one tested change over the relevant workload cycle. Record maintenance completion, application latency and storage effects; keep the previous configuration for reversible tuning. Recheck transaction and multixact ages independently of file size. A smaller table does not prove that future maintenance is healthy.
Keep the installed version’s autovacuum configuration reference and pg_repack documentation with the runbook. Repeat the compatibility review when upgrading either component.
Verify Maintenance Before Expanding Its Scope
A slow application is not automatically a bloat problem. Confirm the constraint, choose a bounded change and measure both maintenance progress and user impact. Autovacuum tuning, freezing and physical rewrites solve different problems; none is a substitute for the others.
Before extending a repack to additional tables, confirm suitable keys, matching versions, ownership, extra space, a reviewed lock policy and a working recovery path. Keep dry-run scope review separate from execution, and treat skipped or incomplete targets as unresolved work.
The desired result is sustained cleanup and acceptable application performance within the resource budget. Record the evidence so the next maintenance decision follows the workload rather than a fixed recipe.
