Skip to content
HeliosDB HeliosDB Full

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.

Terminal window
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.

ANALYZE in the current release: ANALYZE (including VACUUM ANALYZE) is accepted for PostgreSQL compatibility and returns immediately; it does not collect planner statistics yet. Statistics collection is planned. EXPLAIN ANALYZE, which runs the query and reports actual rows and timing, works today.

VACUUM VERBOSE target_table;
VACUUM ANALYZE target_table;

ANALYZE Statistics Update (planned)

Once statistics collection is available, refresh statistics after large loads, deletes, or skewed query plans. Today the statement is accepted and has no effect.

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.