PostgreSQL-First Architecture

Use PostgreSQL first only when measured requirements show it is sufficient. This skill is an evidence gate, not a mandate to replace every specialized system.

Non-negotiable rule

Before adding, removing, or replacing a data service, collect evidence for:

1. Performance — expected peak throughput, p95/p99 latency, data size, query shape, growth margin.
2. Semantics — ordering, replay, fan-out, durability, delivery guarantees, consistency and availability needs.
3. Operations — blast radius, restore objectives, team skill, regional/compliance constraints, cost and failure isolation.

If these are unknown, classify the decision as NOT_PROVEN; do not claim that PostgreSQL is sufficient.

Decision workflow

1. Identify the workload

Classify the requirement before choosing infrastructure:

2. Define the SLO and exit criteria

Write the boundary before implementation. Example:

queue_slo:
  sustained_jobs_per_second: 250
  p99_claim_latency_ms: 100
  replay_required: false
  cross_region_active_active: false
  max_recovery_time_minutes: 15

exit_to_specialist_if:
  - p99 exceeds target under representative load
  - database contention harms transactional workloads
  - required semantics cannot be implemented economically

3. Benchmark the representative path

Do not substitute row-count folklore or generic CPU formulas for measurement. Connection counts, pool sizes, partition thresholds, cache policies and indexes are workload-specific. Use EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements, realistic concurrency, and failure tests.

4. Test failure modes

At minimum, test:

• worker crash after claim and before completion;
• duplicate delivery/idempotency;
• transaction rollback;
• lock contention;
• pool exhaustion;
• restore/recovery path for durable state;
• tenant isolation when RLS is used;
• degradation of the primary workload when auxiliary work grows.

5. Record the decision

Return one of:

• VERIFIED_POSTGRES_FIT — representative tests meet explicit requirements with headroom.
• PARTIAL — promising pattern, incomplete evidence.
• KEEP_SPECIALIST — a specialist provides required semantics/isolation economically.
• NOT_PROVEN — requirements or runtime evidence are missing.

Never use production-ready as a synonym for “the SQL parses.”

Core patterns

Durable queue

Use a durable row as the source of truth and claim work transactionally:

WITH claimed AS (
  SELECT id
  FROM job_queue
  WHERE status IN ('pending', 'retrying')
    AND run_at <= now()
  ORDER BY priority DESC, run_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE job_queue AS q
SET status = 'processing',
    started_at = now(),
    attempts = attempts + 1
FROM claimed
WHERE q.id = claimed.id
RETURNING q.*;

SKIP LOCKED is appropriate for queue-like consumers; it intentionally does not provide a consistent general-purpose snapshot.

Idempotency

Use a unique business/idempotency key rather than an in-memory duplicate guard:

INSERT INTO processed_events (idempotency_key, payload)
VALUES ($1, $2)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING id;

No returned row means the event was already accepted.

Durable workflow replay

When a workflow must survive retries, crashes or deployments, persist the run's specification version, resumable state and externally relevant receipts. Use expiring execution leases and stable idempotency keys for side effects; never replay an unfinished old run implicitly under the newest code. See patterns/durable-workflow-replay.md.

Durable external side effects

A reclaimed workflow lease does not make every unfinished mutation safe to execute again. Commit the effect intent first, classify it as REPLAY_SAFE, IDEMPOTENT_WITH_KEY, NON_REPLAYABLE, or UNKNOWN_SIDE_EFFECT, and fail closed to interruption/reconciliation when the remote outcome is uncertain. See patterns/durable-side-effect-recovery.md.

Temporal authorization

When permission depends on prior outcomes, use an ordered durable ledger rather than model memory or mutable current state. Persist the action request, evaluate against the policy version active at that request, bind evidence to the exact revision/tenant, and keep the enforcement point outside the model. See patterns/temporal-authorization.md.

Cache

Use reconstructible cache tables only when losing them is acceptable. UNLOGGED tables are not durable and are not replicated to standbys; never use them for authoritative state.

Search

Start with PostgreSQL search when the corpus and relevance requirements fit. Benchmark before replacing a dedicated search engine; do not use document count alone as an exit threshold.

Observability

Enable pg_stat_statements when the platform supports it, then measure planning/execution statistics and query concentration. Remember that it requires shared_preload_libraries and a server restart to add or remove.

Guardrails

• PostgreSQL is not a CDN, email deliverability platform, model-serving runtime, or universal event log.
• LISTEN/NOTIFY is a wake-up signal, not durable storage.
• Queue rows need retry, timeout/reaper, dead-letter and idempotency semantics.
• Multi-tenant examples need explicit RLS policies and tests under a non-bypass role.
• pg_cron, pgvector, pg_partman, PostGIS and similar extensions are deployment capabilities, not assumptions.
• Managed PostgreSQL providers may restrict extensions and superuser operations.
• A simpler stack is valuable only if it still meets reliability and recovery requirements.

Read the references only when needed

• Advanced patterns: patterns/README.md
• Migration playbooks: migrations/README.md
• Failure patterns: anti-patterns/README.md
• Preserved pre-refactor skill: references/SKILL.pre-2026-08-15.md
• Verification matrix and source dates: VERIFICATION.md
• Reproducible eval cases: evals/cases.jsonl

Required verification before a recommendation

1. Run python scripts/validate_skill.py.
2. Run the PostgreSQL runtime tests in tests/ against PostgreSQL 18 or the target production major.
3. Record measured workload evidence and provider constraints.
4. State any untested property as NOT_PROVEN.
5. Re-run validation after changing PostgreSQL major versions, extensions, or referenced upstream behavior.