Database Maintenance

HeliosDB-Lite provides several maintenance commands to keep the database healthy: VACUUM reclaims dead storage, CHECKPOINT flushes WAL data, and REINDEX rebuilds indexes. This tutorial covers each command and a recommended maintenance workflow.

Prerequisites

HeliosDB-Lite v3.5+
A database with some data (the examples use a table created below)

Step 1: Set Up Test Data

CREATE TABLE logs (
    id INT PRIMARY KEY,
    message TEXT,
    severity TEXT,
    created_at TIMESTAMP
);

-- Insert 1000 rows
INSERT INTO logs VALUES (1,    'App started',     'INFO',    '2026-03-25 08:00:00');
INSERT INTO logs VALUES (2,    'User login',      'INFO',    '2026-03-25 08:01:00');
INSERT INTO logs VALUES (3,    'Query timeout',   'WARNING', '2026-03-25 08:02:00');
INSERT INTO logs VALUES (4,    'Disk full',       'ERROR',   '2026-03-25 08:03:00');
INSERT INTO logs VALUES (5,    'Retry succeeded', 'INFO',    '2026-03-25 08:04:00');

-- Simulate churn: update and delete rows
UPDATE logs SET message = 'App restarted' WHERE id = 1;
UPDATE logs SET severity = 'CRITICAL' WHERE id = 4;
DELETE FROM logs WHERE id = 3;

After updates and deletes, HeliosDB retains old row versions for MVCC and time-travel. These dead versions consume space until vacuumed.

Step 2: Basic VACUUM

VACUUM scans for dead row versions older than the retention cutoff (default: 1 hour) and removes them:

VACUUM;

Expected output:

col0	col1	col2	col3	col4	col5	col6
VACUUM	2	1	384	5	false	false

Columns:

col1: Version records removed (dead MVCC versions)
col2: Tombstones removed (empty data records from DELETE)
col3: Bytes freed
col4: Duration in milliseconds
col5: Was FULL mode used
col6: Was ANALYZE run

Step 3: VACUUM a Specific Table

Target a single table instead of the entire database:

VACUUM logs;

Only dead versions and tombstones for the logs table are cleaned.

Step 4: VACUUM FULL

VACUUM FULL performs aggressive compaction. It is functionally the same scan but signals the storage backend to compact more aggressively:

VACUUM FULL;

Use VACUUM FULL during maintenance windows when temporary performance impact is acceptable.

Step 5: VACUUM ANALYZE

VACUUM ANALYZE combines garbage collection with statistics refresh. After removing dead rows, it scans each table and updates column statistics used by the query optimizer:

VACUUM ANALYZE;

You can also target a specific table:

VACUUM ANALYZE logs;

The optimizer uses these statistics for cost estimation, join ordering, and index selection. Run VACUUM ANALYZE after bulk data loads or large UPDATE operations.

Step 6: CHECKPOINT

CHECKPOINT flushes the Write-Ahead Log (WAL) to stable storage:

CHECKPOINT;

Expected output:

col0	col1	col2
CHECKPOINT	false	2

Columns: operation, was FORCE mode used, duration in milliseconds.

CHECKPOINT FORCE

Force an immediate backend flush in addition to the WAL flush:

CHECKPOINT FORCE;

Use CHECKPOINT FORCE before taking filesystem-level snapshots or before shutting down the server to ensure all data is on disk.

Step 7: REINDEX

REINDEX rebuilds index structures. Use it after bulk operations that may leave indexes fragmented or after recovering from an unexpected shutdown.

Reindex all indexes on a table

REINDEX TABLE logs;

Expected output:

col0	col1	col2	col3	col4	col5
REINDEX	TABLE	logs	1	4	3

Columns: operation, mode, target, indexes rebuilt, entries reindexed, duration in milliseconds.

Reindex a specific index

CREATE INDEX idx_logs_severity ON logs (severity);

REINDEX INDEX idx_logs_severity;

col0	col1	col2	col3	col4	col5
REINDEX	INDEX	idx_logs_severity	1	4	2

Step 8: Monitor Maintenance Progress

pg_stat_progress_vacuum

Query this view during a long-running VACUUM to see progress:

SELECT * FROM pg_stat_progress_vacuum;

pg_stat_database

Check overall database health:

SELECT datname, xact_commit, xact_rollback, tup_inserted, tup_updated, tup_deleted
FROM pg_stat_database;

Recommended Maintenance Workflow

Daily (off-peak)

-- 1. Flush WAL to ensure durability
CHECKPOINT;

-- 2. Clean up dead versions and update statistics
VACUUM ANALYZE;

-- 3. Take a backup
BACKUP DATABASE TO '/backups/daily/full_20260326.hldb';

Weekly (maintenance window)

-- 1. Full compaction
VACUUM FULL;

-- 2. Rebuild all indexes
REINDEX TABLE logs;
REINDEX TABLE accounts;
-- (repeat for each table)

-- 3. Force checkpoint
CHECKPOINT FORCE;

After bulk operations

-- After a large INSERT or data migration
VACUUM ANALYZE target_table;
REINDEX TABLE target_table;

Common Pitfalls

VACUUM removes time-travel history — Old versions cleaned by VACUUM are no longer available for AS OF queries or PITR. If you need long retention for historical queries, run VACUUM less frequently or adjust the retention cutoff.
VACUUM FULL blocks writes briefly — While the actual implementation runs concurrently, aggressive compaction can increase IO latency. Schedule VACUUM FULL during low-traffic periods.
Stale statistics — If you skip ANALYZE after major data changes, the query optimizer may choose suboptimal plans. Always pair bulk loads with VACUUM ANALYZE.
REINDEX requires table access — Rebuilding indexes reads all rows. On very large tables, this can take significant time. Plan accordingly.
CHECKPOINT before backups — Always run CHECKPOINT before a filesystem snapshot to ensure the WAL is flushed and the on-disk state is consistent.