> For the complete AWA documentation index, see [`llms.txt`](../../llms.txt).

# ADR-012: Split Hot and Deferred Job Storage

## Status

Superseded by ADR-019

## Context

The original schema stored every job lifecycle state in one `awa.jobs` table. That worked well for modest queue sizes, but performance investigations exposed a bad scaling shape once large deferred frontiers were introduced.

The critical issue was not just due-row lookup. With millions of future-dated rows present, every job still churned through the same heap and indexes:

- `scheduled -> available`
- `available -> running`
- `running -> completed`

Even after adding due-time indexes, cold deferred rows and hot execution rows shared the same physical structure. This made dispatch query plans more fragile, increased write amplification, and let background promotion work interfere with hot-path dispatch performance.

## Decision

Split the physical storage into:

- `awa.jobs_hot`: available, running, and terminal-state rows
- `awa.scheduled_jobs`: deferred `scheduled` and `retryable` rows
- `awa.jobs`: compatibility `UNION ALL` view across both tables

This is a manual hot/cold split, not native table partitioning.

### Routing Rules

- Immediate inserts (`available`) go to `awa.jobs_hot`
- Future-dated inserts (`scheduled`) go to `awa.scheduled_jobs`
- Retry backoff rows (`retryable`) live in `awa.scheduled_jobs`
- Due promotion moves rows from `awa.scheduled_jobs` into `awa.jobs_hot`
- Dispatch, heartbeat, completion, and rescue operate on `awa.jobs_hot`

### Compatibility Surface

`awa.jobs` remains available as a compatibility view so raw SQL, tests, and external producers do not need an immediate breaking change. `INSTEAD OF` triggers route writes to the correct physical table.

### Uniqueness

Cross-table uniqueness is enforced through `awa.job_unique_claims` rather than through a partial unique index on the jobs heap. This keeps the uniqueness boundary intact across both physical tables.

## Consequences

### Positive

- Keeps the hot execution table small and planner-friendly
- Lets promotion use dedicated due-time indexes on the deferred table
- Separates cold deferred storage from high-churn execution updates
- Preserves backward compatibility for most SQL surfaces via `awa.jobs`

### Negative

- Adds trigger/view complexity to the schema
- Introduces some risk that compatibility-view queries hide physical-table costs if benchmarks or runtime code accidentally use the view on the hot path
- Requires explicit care in tests and admin paths to query the right physical table when measuring behavior
- Lock-taking queries (`SELECT ... FOR UPDATE`, rescue operations) must target the physical tables directly, not the `awa.jobs` view — `FOR UPDATE` is not reliably supported on UNION ALL views, and the view's INSTEAD OF trigger uses DELETE+INSERT which does not provide true row-level update atomicity
- Reduces queue-local heap churn, but does not protect the primary from unrelated long-lived snapshots pinning MVCC cleanup on `awa.jobs_hot`

## Notes

This decision deliberately favors explicit physical tables over native Postgres partitioning. State transitions would cause frequent row movement under state-based partitioning, while range partitioning would still leave hot and cold workloads intertwined. The manual split gives clearer operational control and simpler hot-path query tuning.

## Relationship to ADR-019

This ADR remains the historical explanation for the canonical hot/deferred split. ADR-019 replaces it as the primary storage direction for the queue storage engine: mutable state rows become append-only queue segments, `active_leases` hold the narrow dispatch/rescue fields, `attempt_state` holds optional per-attempt mutable data, and `lane_state` + segment cursors make up the control plane. The uniqueness decision in this ADR (claims in `awa.job_unique_claims`) carries through unchanged. See [ADR-019](../019-queue-storage-redesign/index.md).