database

Use online DDL techniques: CREATE INDEX CONCURRENTLY for indexes, lock_timeout with retry loops for schema changes, and NOT VALID plus VALIDATE CONSTRAINT for foreign keys and NOT NULL checks. Destructive changes should ship as separate expand-contract phases with a tested rollback.

Run EXPLAIN with ANALYZE and BUFFERS to get real timing and I/O numbers. Watch for Seq Scans on large tables, row estimate mismatches over 100x, sorts spilling to disk, and rows removed by filter, which indicate missing or wrong indexes.

Read Committed is the default for OLTP workloads. Use Repeatable Read for consistent multi-row reads within one transaction, and Serializable with a retry loop when invariants must hold across concurrent transactions, since it prevents write skew.

SQLite suits embedded, local-first, or operationally simple apps; enable WAL mode and busy_timeout for concurrent readers. Move to Postgres when you need multiple independent writers, background jobs, reporting workloads, or operational scaling.

Application-layer uniqueness checks race under concurrency, allowing duplicate rows. A DB-level UNIQUE, EXCLUDE, composite, or partial constraint enforces the invariant atomically regardless of how many clients write simultaneously.

Safe backfills are batched, resumable, observable, and reversible, with each batch holding locks briefly. Rehearse the migration against a production-sized copy and document the rollback or recovery path before shipping.