Database Engineering

A disciplined framework for designing, optimizing, and evolving relational databases in production. This skill covers schema design, indexing strategies, query optimization, safe migrations, and operational concerns like connection pooling and partitioning. It is opinionated about PostgreSQL but most principles apply to any SQL database. The goal is to help you make the right trade-off at each decision point, not just hand you a syntax reference.

---

When to use this skill

Trigger this skill when the user:

• Designs a database schema or needs normalization guidance
• Asks about creating or tuning indexes (composite, partial, covering)
• Wants to understand or optimize a slow query or EXPLAIN plan
• Plans a database migration (adding columns, renaming, dropping, backfilling)
• Implements soft deletes, audit trails, or temporal data patterns
• Sets up connection pooling (PgBouncer, application-level pools)
• Partitions a large table by time, hash, or range
• Chooses between replication strategies (read replicas, logical replication)
• Investigates deadlocks, connection exhaustion, or lock contention

Do NOT trigger this skill for:

• NoSQL / document store design (MongoDB, DynamoDB) - different trade-off space
• ORM-specific configuration questions unrelated to the underlying SQL

---

Key principles

1. Normalize first, then denormalize with a documented reason - Start in third normal form. Every denormalization must be a conscious decision backed by a measured performance requirement, not a guess. Write a comment explaining why.

2. Index for your queries, not your tables - An index that does not serve a query is write overhead and bloat. Before adding an index, write out the query it serves and confirm with EXPLAIN ANALYZE that it is actually used.

3. Migrations must be reversible - Every schema change should have a rollback path. Use the expand-contract pattern for breaking changes: add the new shape, migrate data, deprecate the old shape, then drop it in a later release.

4. Measure before optimizing - EXPLAIN ANALYZE is the ground truth. Never tune a query without first reading the plan. A query that looks slow may be fast; a query that looks fast may be causing invisible downstream load.

5. Plan for growth at schema design time - Ask: "What happens at 100x rows? At 10x write throughput?" Identify which columns will need indexes, which tables might need partitioning, and which joins will become expensive before the schema is locked.

---

Core concepts

Normalization forms

Form	What it eliminates	When to stop here
1NF	Repeating groups, non-atomic columns	Almost never - baseline only
2NF	Partial dependencies on composite keys	Rare - get to 3NF
3NF	Transitive dependencies	Default target for OLTP schemas
BCNF	Remaining anomalies in 3NF edge cases	When you have overlapping candidate keys

Denormalize (with intent) for read-heavy aggregations, pre-computed summaries, or when JOINs across normalized tables are measured to be a bottleneck.

Index types

Type	Structure	Best for
B-tree	Balanced tree	Equality, range, ORDER BY, IS NULL - the default
Hash	Hash table	Equality-only lookups (rarely faster than B-tree in Postgres)
GIN	Inverted index	JSONB keys, full-text search, array containment
GiST	Generalized search tree	Geometric data, range types, nearest-neighbor
BRIN	Block range index	Very large append-only tables sorted by a natural order (e.g. timestamps)

Composite B-tree indexes follow the leftmost prefix rule: an index on (a, b, c) serves queries filtering on a, (a, b), or (a, b, c) - but not (b, c) alone.

ACID and WAL

ACID (Atomicity, Consistency, Isolation, Durability) guarantees that transactions are all-or-nothing, maintain invariants, are isolated from each other, and survive crashes. PostgreSQL implements these via MVCC (Multi-Version Concurrency Control) - readers never block writers and vice versa.

WAL (Write-Ahead Log) is the mechanism for durability and replication. Every change is written to the WAL before it hits the data file. Streaming replication ships WAL segments to replicas. Logical replication decodes WAL into row-level change events.

Connection pooling

Each PostgreSQL connection is a forked OS process (~5-10 MB RAM). At 500 direct connections, the database is spending more time on connection overhead than queries. PgBouncer in transaction mode is the standard solution - it multiplexes many application connections onto a small pool of server connections. Target 10-20 server connections per core as a starting point.

Read replicas

Streaming replicas receive WAL in near-real-time (seconds of lag typical, configurable). Use them to offload analytics, reporting, and read-heavy background jobs. Replication lag means replicas can return stale data - never send reads that require post-write consistency to a replica.

---

Common tasks

Design a normalized schema

Start from an e-commerce domain. Identify entities, attributes, and relationships before writing DDL.

-- 1. Core entities in 3NF
CREATE TABLE customers (
  id          BIGSERIAL PRIMARY KEY,
  email       TEXT        NOT NULL UNIQUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id          BIGSERIAL PRIMARY KEY,
  sku         TEXT        NOT NULL UNIQUE,
  name        TEXT        NOT NULL,
  price_cents INT         NOT NULL CHECK (price_cents >= 0)
);

-- 2. Orders reference customers - foreign key with index
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT      NOT NULL REFERENCES customers(id),
  status      TEXT        NOT NULL DEFAULT 'pending'
                          CHECK (status IN ('pending','confirmed','shipped','cancelled')),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- 3. Junction table for order line items
CREATE TABLE order_items (
  id          BIGSERIAL PRIMARY KEY,
  order_id    BIGINT      NOT NULL REFERENCES orders(id),
  product_id  BIGINT      NOT NULL REFERENCES products(id),
  quantity    INT         NOT NULL CHECK (quantity > 0),
  unit_price_cents INT    NOT NULL
);

CREATE INDEX idx_order_items_order_id ON order_items(order_id);

unit_price_cents is intentionally denormalized from products.price_cents. Prices change over time; the order must record what the customer was charged.

Create effective indexes

-- Composite index: filter first on equality columns, then range/sort
-- Serves: WHERE org_id = ? AND status = ? ORDER BY created_at DESC
CREATE INDEX idx_orders_org_status_created
  ON orders(org_id, status, created_at DESC);

-- Partial index: only index the rows you actually query
-- Saves space and stays small even as the table grows
CREATE INDEX idx_orders_pending
  ON orders(customer_id, created_at)
  WHERE status = 'pending';

-- Covering index: include non-filter columns to avoid heap fetch
-- The query can be answered entirely from the index (index-only scan)
CREATE INDEX idx_products_sku_covering
  ON products(sku)
  INCLUDE (name, price_cents);

-- Check index usage - drop indexes with low scans
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Read and optimize EXPLAIN plans

-- Always use EXPLAIN ANALYZE (BUFFERS) for real execution data
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.email, sum(oi.quantity * oi.unit_price_cents)
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'pending'
GROUP BY o.id, c.email;

Key things to read in the plan output:

Signal	What it means	Action
Seq Scan on a large table	No usable index	Add an index on the filter column
rows=10000 vs actual rows=3	Bad statistics	Run ANALYZE tablename
Hash Join with large Batches	Spilling to disk	Increase work_mem or add index
Nested Loop with large outer set	N+1 at the SQL level	Rewrite as hash join or batch
High Buffers: shared hit	Data in cache - good	No action needed
High Buffers: shared read	Data read from disk	Consider more cache or BRIN index

Write safe migrations

Use the expand-contract pattern for zero-downtime changes:

-- Phase 1 (expand): add nullable column, old code ignores it
ALTER TABLE orders ADD COLUMN notes TEXT;

-- Phase 2 (backfill): run in batches to avoid locking
DO $$
DECLARE batch_size INT := 1000;
        last_id    BIGINT := 0;
BEGIN
  LOOP
    UPDATE orders
    SET notes = ''
    WHERE id > last_id AND id <= last_id + batch_size AND notes IS NULL;

    GET DIAGNOSTICS last_id = ROW_COUNT;
    EXIT WHEN last_id = 0;
    PERFORM pg_sleep(0.05); -- yield to avoid lock contention
    last_id := last_id + batch_size;
  END LOOP;
END $$;

-- Phase 3 (contract): add NOT NULL constraint after all rows are filled
ALTER TABLE orders ALTER COLUMN notes SET NOT NULL;
ALTER TABLE orders ALTER COLUMN notes SET DEFAULT '';

Never ALTER TABLE ... ADD COLUMN ... NOT NULL without a DEFAULT on Postgres < 11. On Postgres 11+ it is safe only if the default is a constant. On older versions it rewrites the entire table and takes an exclusive lock.

Implement soft deletes vs hard deletes

-- Soft delete pattern
ALTER TABLE customers ADD COLUMN deleted_at TIMESTAMPTZ;

-- Partial index keeps active-record queries fast
CREATE INDEX idx_customers_active ON customers(email) WHERE deleted_at IS NULL;

-- Application queries always filter
SELECT * FROM customers WHERE deleted_at IS NULL AND email = $1;

-- Hard delete with archival (for GDPR / data retention)
WITH deleted AS (
  DELETE FROM customers WHERE id = $1 RETURNING *
)
INSERT INTO customers_archive SELECT *, now() AS archived_at FROM deleted;

Prefer hard deletes with an archive table for compliance-sensitive data. Use soft deletes only when you need "undo" semantics or audit trails.

Set up connection pooling

# pgbouncer.ini - transaction mode is best for most web workloads
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
pool_mode          = transaction
max_client_conn    = 1000   ; application connections in
default_pool_size  = 25     ; server connections per database
min_pool_size      = 5
reserve_pool_size  = 5
server_lifetime    = 3600
server_idle_timeout = 600
log_connections    = 0      ; disable in high-throughput environments

In transaction mode, prepared statements and SET commands do not persist across connections. Use DEALLOCATE ALL or disable prepared statements in your driver (prepared_statement_cache_size=0 in JDBC).

Partition large tables

-- Range partition by month (good for time-series, logs, events)
CREATE TABLE events (
  id         BIGSERIAL,
  created_at TIMESTAMPTZ NOT NULL,
  type       TEXT        NOT NULL,
  payload    JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_01
  PARTITION OF events FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02
  PARTITION OF events FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Automate with pg_partman extension
SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control      => 'created_at',
  p_type         => 'native',
  p_interval     => 'monthly'
);

-- Partition pruning - Postgres skips partitions outside the WHERE range
EXPLAIN SELECT * FROM events WHERE created_at >= '2024-01-15';
-- Should show: Append -> Seq Scan on events_2024_01 (only one child scanned)

---

Error handling

Error	Root cause	Resolution
deadlock detected	Two transactions acquiring the same locks in opposite order	Enforce a consistent lock acquisition order; use SELECT ... FOR UPDATE SKIP LOCKED for queue patterns
too many connections	App creating connections faster than they close	Add PgBouncer; audit connection pool settings; check for connection leaks
canceling statement due to conflict with recovery	Long query on replica conflicts with WAL replay	Increase max_standby_streaming_delay; move analytics to a dedicated replica
could not serialize access due to concurrent update	SERIALIZABLE isolation write conflict	Retry the transaction; this is expected behavior, not a bug
index bloat / slow index scans	Dead tuples not vacuumed, bloated index pages	Run VACUUM ANALYZE; tune autovacuum_vacuum_scale_factor for high-churn tables
Query slow after data growth	Missing index or stale planner statistics	Run ANALYZE tablename; check with EXPLAIN (ANALYZE, BUFFERS)

---

Gotchas

1. ALTER TABLE ... ADD COLUMN ... NOT NULL locks the table on Postgres < 11 - Without a constant DEFAULT, Postgres rewrites the entire table under an exclusive lock. On Postgres 11+ with a constant default it is safe, but a runtime-computed default still triggers a rewrite. Use the expand-contract pattern instead.

2. Composite index leftmost prefix is strictly enforced - An index on (a, b, c) does not help a query that filters only on b or c. A common mistake is adding an index for a multi-column query and then using it in queries that don't start from the leftmost column. Always verify with EXPLAIN ANALYZE.

3. PgBouncer transaction mode breaks prepared statements - Many ORMs (Prisma, JDBC) use prepared statements by default. In PgBouncer transaction mode, prepared statements don't persist across connections, causing prepared statement "s1" does not exist errors. Disable prepared statements in your driver (prepared_statement_cache_size=0 for JDBC, pgbouncer_mode: transaction for Prisma).

4. VACUUM doesn't reclaim disk space by default - Regular VACUUM marks dead tuples as reusable but doesn't shrink the file. Only VACUUM FULL returns disk space to the OS, but it acquires an exclusive lock and rewrites the table. Use pg_repack for online space reclamation on production tables.

5. Partition pruning only works when the partition key is in the WHERE clause - Joining a partitioned table on a non-partition key column causes Postgres to scan all partitions. Always include the partition column in range queries or the planner cannot prune.

---

References

For detailed patterns and implementation guidance, load the relevant file from references/:

• references/query-optimization.md - EXPLAIN ANALYZE deep dive, index types, join strategies, common bottlenecks

Only load a references file if the current task requires it - they are long and will consume context.