Production Checklist, Decision Maps, SQL Reference, and Glossary

Production PostgreSQL expertise is the ability to make and verify context-specific decisions across correctness, access, performance, change, recovery, and AI quality. This reference condenses the book into release gates, decision paths, essential commands, and shared vocabulary. It is a starting checklist, not a substitute for workload evidence, current documentation, or rehearsed operations.

What will you be able to do?

• Review a PostgreSQL system before production change.
• Navigate common architecture decisions without defaulting to fashionable complexity.
• Recall essential SQL and psql operations safely.
• Build a continuing-learning and drill program.

Production-readiness checklist

Correctness and model

• Every table has a row predicate, key, owner, lifecycle, and tenant scope.
• Durable invariants have constraints or tested transactional enforcement.
• Time, money, null, collation, case, and delete semantics are explicit.
• Derived data has canonical source, versions, reconciliation, and rebuild.

Security and governance

• Runtime roles do not own objects; grants and defaults are reviewed.
• RLS/query scope is tested as actual roles with negative canaries.
• Values are parameterized; dynamic syntax is allowlisted.
• TLS, secrets, logging, retention, erasure, audit, and admin access are tested.

Performance and capacity

• Top query fingerprints have representative plans and SLOs.
• Indexes have query owners, measured value, and write/storage cost.
• Pool budget includes replicas, workers, deploys, admin, and failover.
• Vacuum, WAL, checkpoints, slots, disk, table/index growth, and skew are monitored.

Change and recovery

• Migrations are backward-compatible, bounded, rehearsed, observable, and reversible until cutoff.
• Backups are encrypted/retained/monitored and restores meet RPO/RTO.
• HA includes quorum, fencing, routing, validation, and rejoin.
• Upgrade, disk-full, pool exhaustion, bad migration, and failover runbooks are drilled.

AI-native data systems

• Sources, chunks, embeddings, prompts, models, policies, datasets, and evaluators are versioned.
• Authorization occurs before lexical/vector ranking and covers caches/citations/traces.
• Exact baselines quantify approximate recall; hybrid/rerank choices are evaluated.
• Tools are semantic, typed, least-privileged, idempotent, approved, and audited.
• Quality, security, latency, cost, drift, fallback, deletion, restore, and rebuild have gates.

Decision maps

Do you need another data system?

1. Name the failing SLO or required capability.
2. Prove query/schema/index/config/capacity fixes are insufficient.
3. Test PostgreSQL extension/partition/replica options.
4. Quantify synchronization, consistency, security, backup, skill, and cost of the new system.
5. Adopt only if measured benefit exceeds permanent operational complexity.

Do you need an agent?

1. Can a deterministic query/workflow solve it? Use that.
2. Can a model select from typed tools? Bound it.
3. Does open-ended planning add measured value? Sandbox and evaluate.
4. Does any action affect money, access, people, deletion, or external communication? Require policy/human approval.

Which consistency control?

• single-row invariant → constraint or atomic conditional statement;
• known row workflow → FOR UPDATE or version check;
• cross-row invariant → redesign, explicit locks, exclusion/unique constraint, or serializable;
• external effect → outbox plus idempotent consumer;
• long model work → proposal/job state outside transaction, short revalidation commit.

Essential psql and SQL reference

Never run a reference command merely because it is listed; resolve target, privilege, impact, and verification first.

Compact glossary

• ACID: atomicity, consistency, isolation, durability properties of transactions.
• Bloat: allocated table/index space that is poorly utilized after workload/history.
• Candidate key: minimal attribute set that uniquely identifies a fact.
• Checkpoint: recovery boundary coordinating dirty pages and WAL progress.
• CTE: named subquery within a statement, optionally recursive.
• Embedding: derived numeric representation produced by a model.
• Foreign key: constraint requiring referenced key existence.
• Grain: meaning of one result row.
• MVCC: versioned visibility enabling concurrent snapshots.
• PITR: point-in-time recovery from base backup and WAL.
• RAG: retrieval-augmented generation using selected evidence.
• RLS: row-level security policies inside PostgreSQL.
• RPO/RTO: allowable data loss / allowable recovery time.
• WAL: write-ahead log used for durability, recovery, and replication.

Why does this matter in AI-native systems?

AI increases the number of derived artifacts, dynamic queries, external calls, and probabilistic decisions. The checklist keeps durable truth, authorization, recovery, and evidence ahead of model fluency. It also prevents “AI-native” from becoming permission to bypass proven database engineering.

Prove continued mastery

Run a quarterly cycle: one restore, one failover or continuity drill, one migration rehearsal, one tenant/red-team suite, one query-plan review, one AI regression comparison, and one architecture simplification review. Record evidence, owners, findings, and completed improvements.

Common mistakes

• Checklist marked from documentation: no observed evidence → link every claim to a test, plan, metric, or drill.
• Complexity only grows: no removal review → retire unused indexes, projections, tools, and systems.
• Docs become stale: version/provider changes ignored → assign review dates and owners.

AI Pair-Programmer Prompt

Hands-on exercise

Score one real or designed PostgreSQL system from 0 (missing) to 3 (drilled) across the five checklist groups, then choose the three highest-risk gaps.

Acceptance criterion: priorities combine impact, likelihood, detectability, recovery difficulty, and dependency—not ease or novelty.

Knowledge check

1. When should another database/search system be added?
2. What is the strongest checklist evidence?
3. What is the final expert habit?

---

Answers

1. After a measured requirement exceeds simpler PostgreSQL options and added operational cost is justified.
2. A reproducible observed test or drill under relevant conditions.
3. Continuously state assumptions, measure, falsify, recover, document, and simplify.

Completion checklist

• I can use every decision map.
• My production claims link to evidence.
• Quarterly drills have owners and dates.
• I know which complexity to remove next.

Primary references

• PostgreSQL current manual
• PostgreSQL release notes
• PostgreSQL wiki
• pgvector