Database Maintenance Runbook

HeliosDB Database Maintenance Runbook

Use this runbook for routine maintenance, storage hygiene, statistics refresh, and query health checks.

VACUUM Procedure

Run cleanup when dead tuple count, table bloat, or transaction age indicates pressure.

psql -h heliosdb-primary -U admin -d production -c "
SELECT schemaname, relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;"

Procedure:

1. Confirm the table and impact.
2. Run nonblocking cleanup first.
3. Monitor I/O and replication lag.
4. Use stronger maintenance only during approved windows.

VACUUM VERBOSE target_table;
VACUUM ANALYZE target_table;

ANALYZE Statistics Update

Refresh statistics after large loads, deletes, or skewed query plans.

ANALYZE target_table;
ANALYZE;

Validate query plans before and after the refresh.

Index Rebuilding (REINDEX)

Rebuild indexes when bloat, corruption, or degraded scan performance is confirmed.

1. Identify affected indexes.
2. Prefer concurrent rebuilds when supported.
3. Rebuild during low traffic for large indexes.
4. Validate query plans and index size.

REINDEX INDEX target_index;

Table Reorganization

Use table reorganization for severe bloat or layout problems after validating that routine cleanup is insufficient.

1. Estimate lock impact and disk requirements.
2. Schedule a maintenance window if locks are required.
3. Take and verify a fresh backup.
4. Reorganize one table at a time.

Query Performance Analysis

Start with active queries and historical slow statements:

SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC
LIMIT 20;

Use EXPLAIN to validate indexes, join strategy, and row estimates.

Storage Management

Monitor:

• Data directory free space.
• WAL growth.
• Temporary file usage.
• Backup staging directories.
• Index bloat and unused indexes.

Keep enough free space for maintenance operations and recovery.