Maintenance
The Maintenance tab helps you monitor PostgreSQL maintenance operations and identify tables requiring attention. Use it to track vacuum status, dead tuple accumulation, bloat levels, and freeze age.
Sections​
The Maintenance tab is organized into three sections:
- Critical Stats — Key maintenance health indicators
- Vacuum Analysis — Manual and auto vacuum tracking
- Bloat Monitoring — Table and index bloat trends
Critical Stats Section​
The Critical Stats section shows the most important maintenance metrics requiring attention.
Table Analyze Stats​
What it shows: When tables were last analyzed and rows modified since.
Why it matters:
- ANALYZE updates table statistics
- Query planner uses statistics for optimization
- Stale statistics cause poor query plans
Columns:
| Column | Description |
|---|---|
| Table | Table name |
| Last Analyze | When last ANALYZE ran |
| Last Autoanalyze | When autovacuum last analyzed |
| Rows Modified | Rows changed since last analyze |
When to investigate:
- Very old analyze timestamps
- High rows modified counts
- Poor query performance
Table Freeze Age Status​
What it shows: Transaction ID age of tables approaching wraparound.
Why it matters:
- PostgreSQL uses 32-bit transaction IDs
- IDs must be "frozen" before wraparound
- Failure to freeze causes database shutdown
Warning thresholds:
| Age | Status |
|---|---|
| < 100M | Healthy |
| 100M - 150M | Monitor |
| 150M - 200M | Warning |
| > 200M | Critical — immediate action needed |
When to investigate:
- Any table > 100M age
- Increasing age trends
- Tables not being frozen
Emergency action:
-- Check freeze age
SELECT c.relname, age(c.relfrozenxid) as freeze_age
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
ORDER BY age(c.relfrozenxid) DESC;
-- Force vacuum freeze
VACUUM FREEZE tablename;
Dead Tuple Percentage​
What it shows: Percentage of dead tuples per table over time.
Why it matters:
- Dead tuples consume space
- Cause table bloat
- Slow down sequential scans
Healthy range: < 10% for most tables.
When to investigate:
- Tables > 10% dead tuples
- Growing dead tuple percentage
- Correlation with performance issues
Vacuum Analysis Section​
The Vacuum Analysis section shows vacuum operation frequency. This section is collapsed by default — click to expand.
Manual Vacuum Events​
What it shows: Manual VACUUM operations over time.
How to interpret:
- Scheduled maintenance windows
- Ad-hoc cleanup operations
- DBA interventions
When you need manual vacuum:
- After bulk deletions
- Before planned heavy read operations
- When autovacuum is falling behind
Auto Vacuum Events​
What it shows: Autovacuum operations over time.
How to interpret:
- Higher frequency = more dead tuples being generated
- Low frequency = low write activity or generous thresholds
- Spikes = after bulk operations
When to investigate:
- Very low autovacuum frequency on busy tables
- Autovacuum not running when expected
- High frequency indicating excessive churn
Bloat Monitoring Section​
The Bloat Monitoring section tracks wasted space in tables and indexes. This section is collapsed by default — click to expand.
Table Bloat Percentage​
What it shows: Estimated table bloat over time.
What causes bloat:
- UPDATE operations (old versions retained)
- DELETE operations (space not immediately reclaimed)
- Vacuum not running frequently enough
Healthy range: < 20% for most tables.
When to investigate:
- Tables > 20% bloat
- Growing bloat trend
- Performance degradation
Remediation:
| Bloat Level | Action |
|---|---|
| 10-20% | Standard VACUUM |
| 20-40% | VACUUM FULL (during maintenance window) |
| > 40% | pg_repack or VACUUM FULL |
-- Standard vacuum (non-blocking)
VACUUM tablename;
-- Vacuum full (blocking, reclaims all space)
VACUUM FULL tablename;
-- Using pg_repack (non-blocking, requires extension)
-- pg_repack --table tablename
Index Bloat Percentage​
What it shows: Estimated index bloat over time.
What causes index bloat:
- Same factors as table bloat
- Page splits in B-tree indexes
- Non-HOT updates
Healthy range: < 30% for most indexes.
Remediation:
-- Rebuild a single index (blocking)
REINDEX INDEX indexname;
-- Rebuild all indexes on a table (blocking)
REINDEX TABLE tablename;
-- Concurrent reindex (PostgreSQL 12+, non-blocking)
REINDEX INDEX CONCURRENTLY indexname;
Use Cases​
Daily Maintenance Check​
Daily health check routine:
- Check Table Freeze Age Status for critical ages
- Review Dead Tuple Percentage for accumulation
- Verify Auto Vacuum Events are running
- Check Table Bloat Percentage trends
Planning Maintenance Windows​
Before scheduling maintenance:
- Identify tables with high Dead Tuple Percentage
- Check Table Bloat Percentage for candidates
- Review Index Bloat Percentage for reindex needs
- Schedule VACUUM FULL or pg_repack for bloated tables
- Plan REINDEX for bloated indexes
Investigating Slow Queries​
When queries slow down:
- Check Table Analyze Stats — stale statistics?
- Review Dead Tuple Percentage — high dead tuples?
- Check Table Bloat Percentage — excessive bloat?
- Run ANALYZE and VACUUM as needed
Configuring Autovacuum​
Use maintenance data to tune autovacuum:
-- Check autovacuum settings
SELECT name, setting
FROM pg_settings
WHERE name LIKE 'autovacuum%';
-- Per-table autovacuum settings
ALTER TABLE tablename SET (
autovacuum_vacuum_threshold = 50,
autovacuum_vacuum_scale_factor = 0.1,
autovacuum_analyze_threshold = 50,
autovacuum_analyze_scale_factor = 0.05
);
Tuning guidelines:
- High-churn tables: Lower thresholds, more frequent vacuum
- Large tables: Lower scale factors
- Read-heavy tables: Less aggressive vacuum acceptable
Emergency Freeze Prevention​
If a table approaches wraparound:
- Check Table Freeze Age Status for critical tables
- Identify the specific table(s)
- Run emergency vacuum freeze:
-- Check current oldest unfrozen XID
SELECT datname, age(datfrozenxid) FROM pg_database;
-- Vacuum freeze specific table
VACUUM (FREEZE, VERBOSE) tablename;
-- Monitor progress
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'tablename';
Best Practices​
Autovacuum Configuration​
Recommended settings for most workloads:
-- Increase autovacuum workers
ALTER SYSTEM SET autovacuum_max_workers = 4;
-- More aggressive vacuum
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02;
-- Cost limits (adjust based on I/O capacity)
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 400;
-- Apply changes
SELECT pg_reload_conf();
Monitoring Thresholds​
Set up alerts for:
| Metric | Warning | Critical |
|---|---|---|
| Dead Tuple % | > 10% | > 20% |
| Table Bloat % | > 20% | > 40% |
| Index Bloat % | > 30% | > 50% |
| Freeze Age | > 100M | > 150M |
| Time Since Analyze | > 7 days | > 14 days |
Maintenance Schedule​
Recommended maintenance cadence:
| Task | Frequency |
|---|---|
| Review maintenance dashboard | Daily |
| ANALYZE heavily modified tables | After bulk loads |
| VACUUM FULL bloated tables | Monthly (maintenance window) |
| REINDEX bloated indexes | Monthly (maintenance window) |
| Check freeze ages | Weekly |
Related Metrics​
The Maintenance section uses these metrics from the Metrics Reference:
| Panel | Primary Metrics |
|---|---|
| Analyze Stats | pg_table_stats.last_analyze, pg_table_stats.last_autoanalyze, pg_table_stats.n_mod_since_analyze |
| Freeze Age | pg_table_stats.age_relfrozenxid |
| Dead Tuple % | pg_table_stats.n_dead_tup, pg_table_stats.n_live_tup |
| Vacuum Events | pg_table_stats.vacuum_count, pg_table_stats.autovacuum_count |
| Table Bloat | pg_table_stats.bloat_bytes, pg_table_stats.size_bytes |
| Index Bloat | pg_index_stats.bloat_bytes, pg_index_stats.size_bytes |
Related Guides​
- Tables & Indexes — Detailed table analysis
- Performance — Query performance
- Configuration Reference — pgX settings