Stores and Postgres tuning

The store holds everything that matters, so it is the part to back up and to size. Sextant creates and migrates its own tables; do not edit them by hand.

SQLite

[store]
driver = "sqlite"
dsn = "file:/var/lib/sextant/sextant.db"

Sextant opens SQLite in write-ahead-logging mode with synchronous = NORMAL, which is safe against application crashes and survives power loss with at most the last few transactions lost.

• Back up with sqlite3 sextant.db ".backup backup.db" or by snapshotting the volume while the server runs. Do not copy the .db file alone, since the -wal file next to it holds recent writes.
• One server process only.

Postgres

[store]
driver = "postgres"
dsn = "postgres://sextant:secret@db:5432/sextant?sslmode=require"
max_connections = 20

Requirements: version 14 or later, and a user that may create tables in its schema. Sextant uses SELECT ... FOR UPDATE SKIP LOCKED for leasing, plus advisory locks for the scheduler.

Connection budget

Every process holds its own pool. For a deployment with 2 servers (pool 20) and 10 workers (pool 5):

2 x 20 + 10 x 5 = 90 connections

Keep the total under max_connections of Postgres minus what other applications use. If you run a pooler such as PgBouncer, use session mode: transaction pooling breaks advisory locks.

Indexes and vacuum

The jobs table sees many updates. Autovacuum defaults are usually fine; if queue wait grows while CPU is idle, check for table bloat:

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname IN ('sextant_jobs', 'sextant_attempts');

If n_dead_tup is much larger than the live rows, lower the table’s vacuum scale factor:

ALTER TABLE sextant_jobs SET (autovacuum_vacuum_scale_factor = 0.02);

Backups

Back up the store like any other database. A restored store brings back jobs in the state they were in: running jobs return to queued once their leases expire, so a restore does not lose work, but a job may run again.

Keep it small

Most of the table is finished jobs. Lower retention first, and set a retention.output_bytes that matches how much handler output you really read.