Postgres Architecture

This page describes the Cyclone tables, indexes, and views in Postgres. It also describes the locks and the operational effects.

Tables Overview

Table	Purpose	Churn
`cyclone_runs`	One row for each run. The run is a workflow or a standalone job.	Moderate
`cyclone_steps`	One row for each step or action in a run.	High
`cyclone_deps`	A dependency edge between steps.	Low
`cyclone_schedules`	A durable cron schedule definition.	Lowest
`cyclone_schedule_fires`	One deduplication row for each schedule and slot.	Low
`cyclone_step_inputs`	Ordered input bindings for a fan-in from more than one source.	Low
`cyclone_signals`	A signal payload that a wait node has not consumed.	Low
`cyclone_schema_version`	The record of each applied migration.	Lowest

`cyclone_runs`

sql

CREATE TABLE cyclone_runs (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    workflow    text NOT NULL,          -- workflow name or standalone kind
    def_version text NOT NULL,          -- DAG topology fingerprint
    input       jsonb,
    state       text NOT NULL DEFAULT 'running',
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now(),
    finished_at timestamptz,            -- NULL until terminal
    deadline_at timestamptz
);

def_version is a fingerprint of the serialized DAG. Routing and tracing use it to find a schema mismatch between the submitter and the worker.

`cyclone_steps`

cyclone_steps has more changes than the other tables. A claim, a heartbeat, and a completion each update it.

sql

CREATE TABLE cyclone_steps (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    run_id        bigint NOT NULL REFERENCES cyclone_runs(id),
    node          text NOT NULL,          -- stable node ID within the run
    kind          text NOT NULL,          -- handler kind for dispatch
    state         text NOT NULL DEFAULT 'pending',
    input         jsonb,                  -- handler input (immutable after insert)
    output        jsonb,                  -- handler output (written on completion)
    attempt       int NOT NULL DEFAULT 0, -- failed executions consumed
    max_attempts  int NOT NULL DEFAULT 5,
    error         text,                   -- last error message
    queue         text NOT NULL DEFAULT 'default',
    priority      int NOT NULL DEFAULT 0,
    scheduled_at  timestamptz NOT NULL DEFAULT now(), -- earliest claim time
    lease_expiry  timestamptz,            -- NULL when not running
    lease_owner   text,                   -- client UUID when running
    lease_generation bigint NOT NULL DEFAULT 0,
    retention     interval,               -- how long to keep after terminal
    UNIQUE (run_id, node)
);

The columns above are the hot-path subset. The Schema Reference lists the other columns. Those columns cover waits, errors, uniqueness, timing, and fan-in.

The migration sets these storage parameters:

sql

WITH (autovacuum_vacuum_scale_factor = 0.02,
      autovacuum_vacuum_threshold    = 1000,
      fillfactor                     = 90)

fillfactor = 90 keeps free space on the page for a HOT update. HOT means a heap-only tuple update. A change to an indexed column still needs index maintenance. A change to a partial-index predicate still needs index maintenance. Many state changes and many lease changes are in that group.

`cyclone_deps`

sql

CREATE TABLE cyclone_deps (
    run_id      bigint NOT NULL REFERENCES cyclone_runs(id),
    node        text NOT NULL,  -- dependent node
    dep_node    text NOT NULL,  -- upstream node
    dep_type    text NOT NULL DEFAULT 'control',
    PRIMARY KEY (run_id, node, dep_node)
);

Cyclone inserts these rows at submit time. Cyclone never updates these rows. When a step completes, Cyclone reads cyclone_deps for the downstream steps. The read finds each downstream step whose parents are all in state completed.

`cyclone_schedules`

sql

CREATE TABLE cyclone_schedules (
    id          text PRIMARY KEY,
    kind        text NOT NULL,
    input       jsonb NOT NULL,
    cron_spec   text NOT NULL,                 -- cron expression
    location    text NOT NULL DEFAULT 'Local', -- IANA timezone
    next_run_at timestamptz NOT NULL,
    last_run_id bigint,
    consecutive_failures  int NOT NULL DEFAULT 0,
    enabled     boolean NOT NULL DEFAULT true
);

`cyclone_schedule_fires`

sql

CREATE TABLE cyclone_schedule_fires (
    schedule_id   text NOT NULL REFERENCES cyclone_schedules(id),
    scheduled_for timestamptz NOT NULL, -- the slot that was fired
    run_id        bigint NOT NULL,
    PRIMARY KEY (schedule_id, scheduled_for)
);

The primary key on (schedule_id, scheduled_for) blocks a duplicate fire from a concurrent scanner.

Views

Four read-only views support monitoring and debugging. The views are cyclone_v_runs, cyclone_v_steps, cyclone_v_kinds, and cyclone_v_queues. The Schema Reference lists the output columns, including wait counts and panic_stack. The embedded migrations define those views.

Indexes

The migrations create these indexes:

Table	Index columns	Purpose
`cyclone_steps`	`(queue, priority DESC, scheduled_at)` WHERE state = 'available'	The claim loop reads this index.
`cyclone_steps`	`(lease_expiry)` WHERE state = 'running'	The recovery sweep reads this index.
`cyclone_steps`	`(sequence_key, scheduled_at, id)` for active sequenced rows	FIFO gate for an active sequence.
`cyclone_runs`	`(state, id DESC)`	List runs by state.
`cyclone_schedules`	`(next_run_at)` WHERE enabled = true	The schedule scanner reads this index.
`cyclone_schedule_fires`	PK `(schedule_id, scheduled_for)`	This key blocks a duplicate fire.

Locks Used

In normal work, Cyclone takes only row locks. Cyclone takes an advisory lock only during migration.

The claim loop uses SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1. That statement locks one cyclone_steps row. SKIP LOCKED means a worker does not wait behind another worker.
A CAS transition uses UPDATE ... WHERE state = 'expected'. The update lock is on one row. Cyclone holds that lock only until the transaction ends.
The schedule scanner uses SELECT ... FROM cyclone_schedules FOR UPDATE SKIP LOCKED. Each claim attempt locks one row.
Migrate takes an advisory lock. The lock allows only one migration call at a time.

Normal work does not take a table lock.

Transaction Boundaries

Operation	Transaction	Contents
`Enqueue`	Its own transaction	Inserts the run and the step. Cyclone sends `NOTIFY` after the commit.
`EnqueueTx`	The caller transaction	Inserts the run and the step. Cyclone sends no `NOTIFY`. The next poll sees the row after the caller commits. Or the caller calls `PublishWakeups` after a `TrackWakeups` commit.
`Submit`	Its own transaction	Inserts the run, the steps, and the deps. Cyclone sends `NOTIFY` after the commit.
`SubmitTx`	The caller transaction	Inserts the run, the steps, and the deps. Cyclone sends no `NOTIFY`. The next poll sees the rows after the caller commits. Or the caller calls `PublishWakeups` after a `TrackWakeups` commit.
`EnqueueCompletion` / `SubmitCompletion`	The handler completion transaction	The run insert commits with the step completion. Cyclone sends `NOTIFY` after that commit.
Claim step	Its own short transaction	Sets the step state to `running` and sets the lease.
Heartbeat	Its own transaction, one for each tick	The `UPDATE` checks the generation and sets `lease_expiry` for one step.
Complete step	Its own transaction	Sets the step state to `completed`, stores the output, and includes the `ctx.CompletionTx()` writes.
Recovery sweep	Its own transaction, one for each reclaim	Sets the step to `available` and adds 1 to `reclaims`, or sets the step to `discarded` past `WithMaxReclaims`.
Schedule fire	Its own transaction	Inserts the schedule fire, inserts the run, and updates `next_run_at`.

The completion transaction is the "own transaction" row for "Complete step". Only that transaction includes business writes. The handler does those writes on ctx.CompletionTx(). Every other transaction belongs to the engine.

Isolation Assumptions

Cyclone is designed for Read Committed isolation. The tests use Read Committed isolation. Read Committed is the Postgres default. Row locks and CAS guards are the serialization points.

Operational Implications

Claims, heartbeats, and completions update cyclone_steps often. You must set autovacuum to do this work often. The migration sets autovacuum_vacuum_scale_factor to 0.02. The migration sets the vacuum threshold to 1000. Those values match this rate of updates.

Dead tuples come from completed rows and from discarded rows. Dead tuples also come from frequent updates. Monitor pg_stat_user_tables.n_dead_tup for cyclone_steps. When the bloat is large, do VACUUM ANALYZE cyclone_steps. Or change the autovacuum parameters.

The claim-loop index serves every worker poll. Monitor index bloat in pg_stat_user_indexes. When you need to rebuild it, do REINDEX CONCURRENTLY idx_steps_claim during off-peak hours.

Each client requires MaxConns >= txSlots + 6. Compare the total for every instance with PostgreSQL max_connections.

Postgres Architecture ​

Tables Overview ​

cyclone_runs ​

cyclone_steps ​

cyclone_deps ​

cyclone_schedules ​

cyclone_schedule_fires ​

Views ​

Indexes ​

Locks Used ​

Transaction Boundaries ​

Isolation Assumptions ​

Operational Implications ​

Related ​