# Database Practices > Schema, query and migration standards for relational databases (PostgreSQL, MySQL, SQL Server, SQLite), with a section on non-relational stores. Written for the reality that migrations run against production while it serves traffic: column types and constraints as the last line of defense, indexing, query performance, keyset pagination, lost-update prevention, and zero-downtime migrations through expand/contract, batched backfills and a deliberate procedure for destructive changes. Its safe-versus-dangerous operation list prevents table-locking outages, and it catches the batch loop that silently skips half its rows. Ships with a review checklist for schema and query diffs. Category: Agents Playbook Source: https://github.com/omarchouman/agents-playbook Install the whole agents-playbook plugin in Claude Code: ``` /plugin marketplace add omarchouman/agents-playbook /plugin install agents-playbook ``` Or this skill alone: download https://omarchouman.com/resources/skills/database-practices/download and unzip it into ~/.claude/skills/. Files in the package: database-practices/SKILL.md, database-practices/references/review-checklist.md ## SKILL.md --- name: database-practices description: >- Engineering standards for schema and query work: column types, constraints, indexing, query performance, keyset pagination, transactions and lost-update prevention, and zero-downtime migrations including expand/contract, batched backfills, squashing and destructive-change procedure. Use this before shipping any migration and whenever the user designs a table, adds a column or index, writes a non-trivial query, or hits ORM query-performance trouble. The safe-versus-dangerous operation list prevents table-locking outages, and the batch-iteration section covers a loop that silently skips half its rows. Read references/review-checklist.md when reviewing a schema or query diff. --- # Database and Migrations Practices Applies to relational databases primarily (PostgreSQL, MySQL, SQL Server, SQLite), with a section on non-relational stores. Examples use PostgreSQL syntax; translate as needed. **Read this before:** designing a table, adding a column or index, writing a non-trivial query, or shipping any migration. --- ## 1. Core principles 1. **The schema outlives the application.** Code gets rewritten; data gets migrated. A bad schema is the most expensive mistake in this document to reverse. 2. **The database is the last line of defense for data integrity.** Application checks race, get bypassed by scripts, and get forgotten in the second service. Constraints don't. 3. **Migrations run against production while it is serving traffic.** Every migration must be safe to apply to a live, large table with old code still running. 4. **You cannot un-lose data.** Destructive operations get a deliberate, separate, reversible-by-backup process. --- ## 2. Schema design ### 2.1 Types - **Choose the most restrictive type that fits.** The type system is free validation. - **Never store money in a float.** Use `NUMERIC(19,4)` / `DECIMAL`, or integer minor units (cents) with the currency stored alongside. Binary floating point cannot represent 0.10. - **Store timestamps as `TIMESTAMPTZ` (UTC).** Never a naive local timestamp, never a string, never a Unix integer in a column you'll want to range-query in SQL. Store the user's timezone separately when you need to render or schedule in local time. - **Prefer native types over strings**: `BOOLEAN`, `UUID`, `INET`, `JSONB`, `DATE`, `INTERVAL`, arrays, enums. `VARCHAR` holding `"true"` is a bug generator. - **Always bound `VARCHAR`**, but bound it generously and for a real reason. (In PostgreSQL, `TEXT` with a `CHECK` length is equivalent and easier to change.) - **Choose your primary key deliberately:** - `BIGINT` identity: smallest, fastest, but sequential and enumerable. Never expose it publicly (see `backend-practices` §2.1). - `UUIDv7` / `ULID`: time-sortable, safe to expose, index-friendly. **Preferred default for new systems.** - `UUIDv4`: random. Safe to expose, but random insertion order causes B-tree page splits and index bloat on large tables. Avoid as a clustered/primary key at scale. - **Never use a natural key** (email, username, SKU) as a primary key. Business meaning changes; primary keys shouldn't. - **Enums**: a lookup table with a foreign key is the most flexible (values can carry metadata and change without a migration). A native enum is faster but adding/removing values needs DDL. A `VARCHAR` with a `CHECK` constraint is a reasonable middle ground. Choose one; never let an unconstrained string hold a fixed set of values. ### 2.2 Constraints Every one of these is a bug that becomes impossible: - **`NOT NULL` by default.** Make nullability the exception you can justify. `NULL` means "unknown/not applicable". Never use it for "empty", "zero", or "false". - **`FOREIGN KEY` on every reference**, with an explicit `ON DELETE` action: - `RESTRICT` (default choice): refuse to orphan. - `CASCADE`: only when the child genuinely has no life without the parent, and you have thought about how many rows one delete could touch. - `SET NULL`: only where the column is nullable and that means something. - **`UNIQUE` on anything that must be unique.** Application-level "check then insert" is a race condition, always. Let the constraint fail and handle the error. - **`CHECK` constraints for domain rules**: `amount > 0`, `ends_at > starts_at`, `status IN (…)`, `discount BETWEEN 0 AND 100`. - **`DEFAULT` for values that should never be absent**: `created_at DEFAULT now()`, `status DEFAULT 'pending'`. ```sql CREATE TABLE subscriptions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), account_id UUID NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT, plan TEXT NOT NULL REFERENCES plans(code), status TEXT NOT NULL DEFAULT 'trialing' CHECK (status IN ('trialing','active','past_due','canceled')), amount_cents BIGINT NOT NULL CHECK (amount_cents >= 0), currency CHAR(3) NOT NULL, starts_at TIMESTAMPTZ NOT NULL, ends_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), CHECK (ends_at IS NULL OR ends_at > starts_at) ); -- Only one active subscription per account. CREATE UNIQUE INDEX ON subscriptions (account_id) WHERE status = 'active'; ``` ### 2.3 Normalization and denormalization - **Normalize first (3NF).** Duplicated data drifts out of sync; that is a correctness problem, not a performance trade-off. - **Denormalize only with measurements and a maintenance plan.** A cached `comment_count` must be updated by a trigger or a transaction that also writes the comment, never by hopeful application code in one of the three places that inserts comments. - **Keep short-lived state out of your core tables.** One-time codes, verification tokens, password-reset tokens, rate-limit counters, and "we emailed them at" markers do not need to be nullable columns on `users`. Each one widens a hot row, needs its own cleanup job to stop accumulating, and turns expiry into application logic that someone will forget. A key-value store with a native TTL expresses "valid for fifteen minutes" directly and disposes of the data itself. Keep in the table only what you must query, join, or report on. (Tokens still need hashing at rest wherever they live: see `security-practices` §2.2.) - **Use `JSONB` for genuinely schemaless data**: third-party payloads, user-defined fields, event bodies. Do **not** use it to avoid writing a migration: you lose type checking, constraints, and query planning, and you will end up validating in five places. If you query or filter on a field regularly, promote it to a column. ### 2.4 Conventions Pick one convention per repo and never mix: - `snake_case` for tables and columns; plural table names (`orders`, `order_items`). - Foreign keys named `_id` (`account_id`). - Booleans read as assertions: `is_active`, `has_verified_email`. - Timestamps end in `_at`; dates in `_on`; durations carry units (`timeout_seconds`). - Every table gets `created_at` and `updated_at`. - Never name a column with a reserved word (`order`, `user`, `group`, `type`). ### 2.5 Deletion - **Prefer soft delete for anything user-visible or referenced elsewhere** (`deleted_at TIMESTAMPTZ`), but understand the cost: every query must filter it, and unique constraints must become partial (`… WHERE deleted_at IS NULL`). Missing that filter in one query is the classic soft-delete leak. - **Hard-delete transient, high-volume, or legally-must-be-erased data.** Retention and erasure obligations (GDPR "right to erasure") override convenience; see `security-practices` §8. - **Consider an archive table** rather than either, when history matters but volume doesn't belong in the hot table. --- ## 3. Indexing An index is a permanent write cost and storage cost paid to make some reads fast. Both sides matter. ### 3.1 What to index - **Every foreign key column.** Most databases (including PostgreSQL) do **not** index them automatically, and an unindexed FK makes parent deletes and joins table-scan. - **Columns in `WHERE`, `JOIN`, `ORDER BY`, and `GROUP BY`** on queries that matter. - **Composite indexes follow the leftmost-prefix rule**: an index on `(a, b, c)` serves `WHERE a`, `WHERE a AND b`, and `WHERE a AND b AND c`, but not `WHERE b`. Order columns by: equality predicates first, then range predicates, then sort columns. - **Partial indexes for skewed queries**, far smaller and cheaper: ```sql CREATE INDEX ON jobs (created_at) WHERE status = 'pending'; ``` - **Covering indexes** (`INCLUDE`) let the database answer from the index alone, avoiding the heap fetch. Useful for hot, narrow read paths. ### 3.2 What not to index - **Low-cardinality columns alone** (a boolean, a 3-value status): usually useless as a standalone index; useful as the *leading* column of a composite or as a partial index predicate. - **Duplicate and redundant indexes.** `(a)` is redundant when `(a, b)` exists. Audit periodically and drop unused indexes; they cost every write. - **Everything, reflexively.** Ten indexes on a write-hot table will dominate its write latency. ### 3.3 Things that silently disable an index ```sql WHERE lower(email) = $1 -- ✗ unless you index lower(email) WHERE created_at::date = $1 -- ✗ use a range: created_at >= $1 AND < $1 + 1 day WHERE status != 'done' -- ✗ negation is rarely selective WHERE name LIKE '%foo' -- ✗ leading wildcard; use a trigram/full-text index WHERE user_id = $1::text -- ✗ type mismatch forces a cast on the column ``` **Verify with `EXPLAIN (ANALYZE, BUFFERS)`**. Never assume an index is being used. A sequential scan on a large table in a hot query is the finding you're looking for. --- ## 4. Query practices ### 4.1 Correctness and safety - **Always use parameterized queries.** Never build SQL by string concatenation with user input. This is SQL injection, the highest-impact and most preventable vulnerability in this document. See `security-practices` §4.2. When a table or column name must be dynamic, allow-list it against a fixed set; parameters cannot bind identifiers. - **Never `SELECT *` in application code.** It breaks when columns are added, transfers data you don't need, prevents covering-index-only scans, and hides the fact that you're pulling a 2MB blob column on every request. - **Every query that can return many rows needs a `LIMIT`.** - **`UPDATE` and `DELETE` must have a `WHERE` clause.** Review these specifically: run the equivalent `SELECT` first and confirm the row count. - **When you expect exactly one row, assert exactly one.** Taking the first result of a query that should be unique hides duplicates indefinitely: the bug is not that the query returned nothing, it is that it returned two and you silently picked one. Fetch with a limit of two and fail loudly on the second row, or add the unique constraint that makes the situation impossible. Reserve "first match" for queries that are genuinely ordered and genuinely allowed to match many rows. - **Include the keys your relations need when selecting specific columns.** Narrowing a `SELECT` to the columns you display will silently break any subsequent relation loading that depends on a key you dropped: the ORM has nothing to match on, so it returns empty relations rather than an error. Whenever you restrict columns, keep the primary key and every foreign key involved in the loads that follow. ### 4.2 Performance - **N+1 queries are the most common backend performance bug.** One query for a list plus one per row. Fix by eager-loading (join or a second batched `WHERE id = ANY($1)`), not by caching. Assert query counts in tests on hot paths. - **Filter and aggregate in the database, not in application memory.** Pulling 100k rows to count them in code is orders of magnitude slower and will eventually OOM. - **Batch writes.** One `INSERT` with 1,000 rows beats 1,000 `INSERT`s by a wide margin. Batch, but bound the batch size; a single 100k-row statement creates its own problems. - **Keep bulk copies inside the database.** Moving rows from one table to another by selecting them into the application, looping, and inserting them back costs two network round trips per row plus object hydration for data you never look at. `INSERT INTO … SELECT` does it in one statement without the rows ever leaving the server. The same goes for bulk updates computed from other rows: express them as one statement rather than a read-modify-write loop, which is also a race (see §5). - **Prefer `EXISTS` to `COUNT(*) > 0`**, since it can stop at the first match. - **Beware `COUNT(*)` on large tables** for pagination totals; it scans. Use an approximate count, a cached count, or cursor pagination that doesn't need a total. - **Stream or chunk large result sets** with a server-side cursor. Never materialize a full table export in memory. Two mechanisms, with different trade-offs: a **server-side cursor** holds one query open and streams rows individually, giving the lowest memory use but occupying a connection and a snapshot for the whole traversal; **chunked fetching** issues a fresh bounded query per batch, releasing the connection between batches at the cost of seeing writes that land mid-traversal. Long-running maintenance work usually wants chunking; a single large export usually wants a cursor. - **Never filter or transform in application memory what the database can do.** Loading rows and then filtering them in code fetches, transfers, and hydrates every row you were about to discard. Push the predicate into the query. ### 4.3 Pagination **Offset pagination** is fine for small, static data and admin screens: ```sql SELECT … ORDER BY created_at DESC LIMIT 20 OFFSET 4000; -- scans 4020 rows ``` It degrades linearly with depth and **skips or duplicates rows** when data changes between page requests. **Keyset (cursor) pagination** is constant-time and stable: ```sql SELECT … FROM orders WHERE (created_at, id) < ($1, $2) -- last row of the previous page ORDER BY created_at DESC, id DESC LIMIT 20; ``` Include a unique tiebreaker (`id`) in both the sort and the cursor, or rows with identical timestamps will be skipped. Index on `(created_at DESC, id DESC)`. **Iterating in batches while modifying the rows you are iterating over is a special case, and offset gets it silently wrong.** If the batch loop filters on a column the loop itself updates, every processed row leaves the result set, the remaining rows shift down by one page, and the next `OFFSET` skips exactly one page worth of records: ```sql -- Loop: process users where processed = false, 100 at a time, marking them processed. -- ✗ Offset. After batch 1 marks 100 rows processed, they leave the result set; -- OFFSET 100 now starts one page past where the unprocessed rows begin. SELECT … WHERE processed = false ORDER BY id LIMIT 100 OFFSET 100; -- skips 100 rows -- ✓ Keyset. Position is anchored to the last id seen, not to a count of rows. SELECT … WHERE processed = false AND id > $last_id ORDER BY id LIMIT 100; ``` Half the records are silently missed, the job reports success, and nobody notices until the data is audited. **Whenever a batch loop writes to a column that appears in its own `WHERE` clause, iterate by key.** Most frameworks expose both variants under similarly named helpers, and the offset one is usually the default, so this is worth checking explicitly during review. The same rule applies to backfills (§6.4). **Use keyset for anything user-facing, infinite-scrolling, large, or frequently written.** --- ## 5. Transactions - **Wrap a single logical business operation**, no more and no less. - **Keep them short. Never perform I/O inside**: no HTTP calls, no email, no queue publishes. A remote timeout inside a transaction holds locks for its full duration. - **Know your isolation level and its anomalies.** Most databases default to `READ COMMITTED`, which permits non-repeatable reads and phantoms. If your logic depends on reading a value and acting on it, `READ COMMITTED` will not protect you. Use an explicit lock, an atomic conditional update, or `SERIALIZABLE` (and be ready to retry on serialization failure). - **Prevent lost updates explicitly.** Read-modify-write across a round trip is a race: ```sql -- ✗ racy SELECT balance FROM accounts WHERE id = $1; -- app computes balance - 10 UPDATE accounts SET balance = $2 WHERE id = $1; -- ✓ atomic UPDATE accounts SET balance = balance - 10 WHERE id = $1 AND balance >= 10; -- check rows affected ``` - **Avoid deadlocks by acquiring locks in a consistent order** across all code paths (e.g. always the lower account id first). - **Retry serialization/deadlock failures** with backoff, since they are expected, not exceptional. Only retry when the transaction is safe to replay. - **Never hold a transaction open across a user interaction.** Use optimistic concurrency (a `version` column) instead; see `backend-practices` §6. --- ## 6. Migrations ### 6.1 Non-negotiables - **Every schema change is a versioned migration file in version control.** No manual production DDL, ever. If you ran something by hand to fix an incident, write the migration immediately afterward so environments don't diverge. - **Migrations are append-only and immutable once merged.** Never edit a migration that has run anywhere. Fix forward with a new one. - **One logical change per migration**, with a descriptive name and timestamped ordering. - **Test every migration against a production-shaped copy**: the same data volume, the same indexes. A migration that takes 50ms on 100 rows can take 40 minutes and lock a table on 100 million. - **Separate schema migrations from data backfills.** Backfills are long-running, batched, resumable, and often better run as a job than as a migration that blocks deploys. - **Know your rollback.** Either a tested `down` migration, or an explicit, documented "forward-only, restore from backup if this goes wrong". Untested `down` migrations are false comfort. ### 6.2 Zero-downtime: the expand/contract pattern During a deploy, old and new application code run **simultaneously**. Every migration must be compatible with both. This is the single most important operational rule here. The pattern, in three deploys: | Phase | Migration | Code | |---|---|---| | **Expand** | Add the new structure, nullable/defaulted, alongside the old | Write to both, read from old | | **Migrate** | Backfill in batches | Read from new, keep writing both | | **Contract** | Drop the old structure, add `NOT NULL`/constraints | Read and write new only | **Renaming a column** is not one migration. It is: add `new_name` → dual-write → backfill → switch reads → stop writing `old_name` → drop `old_name`. Any shortcut breaks in-flight requests during the deploy window. ### 6.3 Safe vs. dangerous operations **Generally safe on a live table:** - Adding a nullable column (with no volatile default) - Adding a new table - Adding an index **concurrently** (`CREATE INDEX CONCURRENTLY`, outside a transaction) - Adding a constraint as `NOT VALID`, then `VALIDATE CONSTRAINT` separately - Widening a type (`INT` → `BIGINT` is *not* free in most engines; check yours) **Dangerous. These will lock, rewrite, or break running code:** - Dropping or renaming a column or table (breaks old code instantly; `SELECT *` and ORMs will fail) - Adding a `NOT NULL` column with a default (rewrites the table on older engines) - Adding a foreign key or `CHECK` without `NOT VALID` (full validation scan under lock) - `CREATE INDEX` without `CONCURRENTLY` (blocks writes for the duration) - Changing a column type (usually a full rewrite plus an `ACCESS EXCLUSIVE` lock) - Any unbatched `UPDATE`/`DELETE` on a large table (long transaction, lock escalation, replication lag, bloat) ```sql -- Adding a NOT NULL column safely: ALTER TABLE users ADD COLUMN locale TEXT; -- 1. nullable, instant UPDATE users SET locale = 'en' WHERE locale IS NULL -- 2. batched backfill AND id IN (SELECT id FROM users WHERE locale IS NULL LIMIT 5000); ALTER TABLE users ADD CONSTRAINT users_locale_nn -- 3. no full scan CHECK (locale IS NOT NULL) NOT VALID; ALTER TABLE users VALIDATE CONSTRAINT users_locale_nn; -- 4. no write lock ``` - **Always set a short `lock_timeout` (and `statement_timeout`) before DDL.** A migration that waits for a lock queues every subsequent query behind it and takes the site down. Failing fast and retrying is far safer: ```sql SET lock_timeout = '3s'; SET statement_timeout = '30s'; ``` ### 6.4 Backfills - **Batch them** (1k–10k rows), commit each batch, and sleep briefly between batches to let replication catch up. - **Make them resumable and idempotent**, because they will be interrupted. - **Drive them off an indexed column** with a stable order, and log progress. - **Run them out-of-band** for large tables, not inside the deploy's migration step. ### 6.5 Destructive changes Dropping a column or table deserves its own ritual: 1. Confirm nothing reads it: grep the codebase, and check query logs / `pg_stat_statements` over a full business cycle. 2. Ship a release that stops writing it. 3. Wait long enough to roll back that release without needing the data (at least a full deploy cycle, often a week). 4. Take a verified backup. 5. Drop it in its own migration, separate from anything else. **Never combine a destructive change with anything else in one migration.** ### 6.6 Squashing a long migration history After a few years, replaying several hundred migrations to build a fresh database is slow, and the early ones often no longer run at all against a current engine. Collapse them: 1. Build a database by running every existing migration, and verify it matches production's schema. 2. Dump that schema to a single baseline file, checked into version control. 3. Configure the tooling to load the baseline instead of replaying migrations when starting from empty, then continue applying any migration newer than the baseline. 4. Keep the squashed migration files, at least for a release or two. Deleting them breaks any environment that is mid-history. Rules: **squash only migrations that have run everywhere**, including the slowest-moving environment; never squash across a release boundary that anything might still roll back to; and re-verify that a from-scratch build and a from-baseline build produce identical schemas before trusting it. Squashing is a developer-experience optimization, so it must not change what production's schema is. --- ## 7. Operations - **Backups are only real if you have restored one.** Schedule a periodic restore drill and measure how long it takes. That number is your actual RTO. Verify point-in-time recovery works and that the retention window meets your RPO. - **Least-privilege database users.** The application user should not be able to `DROP` a table. Migrations run under a separate, more privileged user. Read replicas get a read-only user. - **Connection pooling is mandatory**, sized against the database's connection limit, not against how many app instances you happen to run. Each connection has a real memory cost. - **Monitor**: slow query log, replication lag, connection saturation, cache hit ratio, dead tuples/bloat, longest-running transaction, deadlock count, and disk headroom. - **Reads on a replica are eventually consistent.** A write followed immediately by a read from a replica can miss it, so route read-after-write to the primary. - **Never point a test suite, script, or local environment at production.** Enforce it with separate credentials, and make the production DSN visibly distinct. - **Make destructive commands refuse to run in production.** Drop-and-recreate, wipe, reset, truncate, and "reseed" commands exist for development convenience and are one careless shell history entry away from deleting production. Do not rely on people being careful: have the command itself check the environment and abort, requiring an explicit override flag plus a typed confirmation to proceed. Apply the same guard to your own destructive maintenance scripts, which are usually more dangerous than the framework's, and note that automation and coding agents run commands without the pause a human would take. - **Guard the operations that are destructive without looking it up**: a migration rollback that drops columns, a "sync" that deletes rows absent from the source, and any bulk delete driven by a filter that could match everything if a parameter comes through empty. - **Seed and anonymize.** Development data comes from a generator or a scrubbed dump, never a raw production copy containing real personal data. See `security-practices` §8. --- ## 8. Non-relational stores Most of the above still applies; these are the differences that catch people out. - **Document stores need a schema too**; it just lives in your application and is enforced by validation. Version your documents (`schema_version`) so you can migrate them lazily on read. - **Design the model around your access patterns**, not around your entities. In key-value and wide-column stores, the query you need determines the key you choose, and adding a new access pattern later usually means a new index or a data rewrite. - **Understand the consistency model you actually have.** "Eventually consistent" means a read can return stale data; design the UI and business logic for it, or use the store's strong-consistency option where correctness requires it. - **Cross-document transactions are limited or absent.** Model together what must change together. - **Set TTLs and eviction policies on caches deliberately**, and never treat a cache as a system of record; it can vanish entirely at any moment. - **Redis specifics**: `KEYS` is O(n) and blocks the server, so use `SCAN`. Set `maxmemory` and an eviction policy explicitly. Persistence is off or partial by default. --- ## 9. Anti-patterns - Money in `FLOAT`/`DOUBLE`. - Naive/local timestamps, or timestamps stored as strings. - Nullable columns everywhere "just in case". - Missing foreign keys, or missing indexes on foreign keys. - Uniqueness enforced only in application code. - `SELECT *` in application code. - String-concatenated SQL. - Offset pagination on a large, actively-written table. - Offset-based batch loops that update the column they filter on (silently skips rows). - Taking the first row of a query that should have matched exactly one. - Narrowing `SELECT` columns and dropping the keys that relation loading needs. - Copying rows between tables by looping through the application. - Transient tokens and counters stored as nullable columns on core tables. - `COUNT(*)` on a large table for every page of results. - An unbatched `UPDATE`/`DELETE` over millions of rows. - Editing a migration that has already run somewhere. - Renaming or dropping a column in a single deploy. - `CREATE INDEX` without `CONCURRENTLY` on a live table. - DDL with no `lock_timeout`. - Backups that have never been restored. - The application user having `DROP` privileges. - A shared database written by multiple services (see `backend-practices` §5.3). - `JSONB` used to avoid designing a schema. --- ## Review pass The condensed form of everything above lives in [`references/review-checklist.md`](references/review-checklist.md). Read it when reviewing a diff rather than writing one, or when you want a pull-request template. It is deliberately kept out of this file so a review pass costs one short read instead of re-loading every rule.