When running PostgreSQL in production, performance can degrade and transactions can bloat without you noticing. This article covers the maintenance fundamentals you need to keep PostgreSQL running smoothly.

1. VACUUM: Cleaning Up Dead Tuples

PostgreSQL uses MVCC (Multi-Version Concurrency Control), which means old row versions (dead tuples) linger in tables after updates and deletes. Left unchecked, tables bloat and performance suffers.

-- Run a standard VACUUM (does not block other operations)
VACUUM;

-- Reclaim space to the OS (acquires table lock)
VACUUM FULL;

Production environments have autovacuum enabled by default, but busy tables may need tuned settings:

autovacuum_vacuum_scale_factor = 0.01
autovacuum_vacuum_threshold = 50
autovacuum_naptime = 30s

2. ANALYZE: Keeping Statistics Fresh

The query planner relies on table statistics to choose execution plans. Heavy data changes without a fresh ANALYZE can lead to poor index choices and slow queries.

ANALYZE;

-- Or combine with VACUUM
VACUUM ANALYZE;

Autovacuum handles this automatically, but it’s wise to run a manual ANALYZE after large data imports.

3. Reviewing Index Strategy

Remove Unused Indexes

SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

An idx_scan of 0 likely means the index has never been used. 然而, check carefully before deleting — some indexes are only used by monthly batch jobs.

Rebuild Bloated Indexes

Over time, indexes fragment and performance drops:

REINDEX INDEX index_name;
REINDEX TABLE table_name;
REINDEX DATABASE database_name;

4. Monitoring Query 性能 with pg_stat_statements

The pg_stat_statements extension reveals which queries consume the most time:

-- Enable in postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

-- Find top 10 slowest queries
SELECT query, calls, total_exec_time / calls AS avg_time_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Use this data to decide where to add indexes or rewrite slow SQL.

5. Backup Strategy

pg_dump -U postgres mydb > /backup/mydb_$(date +%Y%m%d).sql

Physical backup combined with WAL archiving is the safest approach when possible.

6. Connection Pooling

PostgreSQL forks a process per client connection, so many concurrent connections can strain memory. A connection pooler like PgBouncer shares a small number of database connections across many clients efficiently.

Sample Maintenance 日程

摘要

PostgreSQL is highly reliable with proper care. Making VACUUM, ANALYZE, index management, and backups part of your routine dramatically reduces production surprises. Start by enabling pg_stat_statements to understand your current query performance baseline.