Database Maintenance

How to keep Cyclone's Postgres tables healthy under sustained load.

Autovacuum

cyclone_steps and cyclone_runs are high-churn tables. Each step gets a claim UPDATE, a heartbeat UPDATE, and a completion UPDATE in its lifecycle. These updates create dead tuples that autovacuum must reclaim.

Cyclone's migrations apply custom storage parameters that trigger aggressive autovacuum:

-- Applied by cyclone.Migrate to cyclone_steps and cyclone_runs:
autovacuum_vacuum_scale_factor = 0.02   -- vacuum when 2% of rows are dead
autovacuum_vacuum_threshold = 1000      -- but only if at least 1000 rows are dead
fillfactor = 90                         -- leave 10% page slack for in-place updates (HOT)

The fillfactor = 90 setting leaves room for heap-only tuple (HOT) updates. Changes to indexed columns still write index entries. Changes to partial-index predicates still write index entries. Many state and lease_expiry transitions do this.

When to adjust autovacuum settings:

• Under high throughput (more than 1000 short jobs/sec), increase autovacuum_vacuum_cost_delay or add a dedicated autovacuum worker:

  ALTER TABLE cyclone_steps SET (autovacuum_vacuum_cost_delay = 2);

• If table bloat grows despite autovacuum running, check pg_stat_bgwriter.maxwritten_clean for autovacuum throttling.

Bloat

Dead tuples accumulate from:

• Completed and discarded rows that the retention sweeper has not purged yet.
• The frequent UPDATE pattern on state and lease_expiry.

Monitor bloat:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) as dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('cyclone_steps', 'cyclone_runs')
ORDER BY n_dead_tup DESC;

Alert threshold: dead_pct > 20% on cyclone_steps.

Manual vacuum:

-- Force immediate vacuum without waiting for autovacuum schedule:
VACUUM ANALYZE cyclone_steps;
VACUUM ANALYZE cyclone_runs;

Index Maintenance

The partial claim-loop index (queue, priority DESC, scheduled_at) WHERE state = 'available' on cyclone_steps is the primary index to watch.

Monitor index bloat:

SELECT indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'cyclone_steps'
ORDER BY pg_relation_size(indexrelid) DESC;

If the index is larger than expected (for example, 10× the table size), rebuild it:

-- Non-blocking index rebuild (requires Postgres 12+):
REINDEX INDEX CONCURRENTLY idx_steps_claim;

Partitioning Recipe

For high-volume deployments (millions of runs per day), you can range-partition cyclone_runs and cyclone_steps by run_id (a sequential bigserial). You can then drop old partitions instead of a DELETE:

-- Example: quarterly partitions
CREATE TABLE cyclone_runs_2024_q1 PARTITION OF cyclone_runs
    FOR VALUES FROM (0) TO (1000000);

CREATE TABLE cyclone_runs_2024_q2 PARTITION OF cyclone_runs
    FOR VALUES FROM (1000000) TO (2000000);

Partitioning requires planning before cyclone.Migrate creates the original tables. Cyclone does not apply partitioning for you. Apply partitioning DDL manually and reconcile it with the migration schema.

For most deployments, aggressive retention (purging terminal runs daily) is sufficient and simpler than partitioning.

Retention Interaction

client.PurgeTerminalRuns performs bulk DELETEs. Each batch deletes rows from cyclone_steps, cyclone_deps, and cyclone_runs in its own transaction. The call as a whole is not atomic. It repeats bounded deletes until no eligible rows remain. After purge:

• Dead tuple count drops.
• The next autovacuum pass is faster (fewer live rows to scan).
• Table size decreases after autovacuum completes.

Purge frequently (for example, daily) rather than letting rows accumulate for weeks.

Query Plans

If the claim loop slows down, check the query plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT run_id, node, kind, queue, priority, max_attempts
FROM cyclone_steps
WHERE state = 'available'
  AND queue = 'default'
  AND scheduled_at <= now()
ORDER BY priority DESC, scheduled_at, id
FOR UPDATE SKIP LOCKED
LIMIT 1;

The plan must use the claim-loop index. If it does a sequential scan, the index can be bloated. Stale statistics can also cause a bad plan:

ANALYZE cyclone_steps;

Postgres Version Notes

• Postgres 14+: the supported floor. It is not a technical minimum. Every feature Cyclone uses predates it. Those features are FOR UPDATE SKIP LOCKED and ON CONFLICT from 9.5, JSONB from 9.4, and GENERATED ALWAYS AS IDENTITY from 10. It is the oldest major that still gets upstream security fixes.
• Postgres 15+: improved VACUUM throughput. Use it for high-churn deployments.
• Postgres 16+: improved parallel VACUUM. It helps on large tables.

Use the latest stable Postgres release for best performance. CI executes the acceptance suite and the regression suite on the floor version and on the current default. Here, "supported" means "proven on every push", not "believed to work".

The schema and the queries also work on Postgres 12. That release is out of upstream support. That result is a data point, not a supported configuration. Nothing older than 12 has been tried. No CI job covers it. A CYCLONETEST_PG_IMAGE of a sub-floor major starts the suite. TestServerVersionMeetsSupportedFloor skips rather than passes.

Related

• Retention
• Postgres Architecture
• Production Checklist