Database

Design and operate databases avoiding common scaling, reliability, and data integrity traps.

Database Gotchas

Connection Traps

• Connection pools exhausted = app hangs silently — set max connections, monitor pool usage
• Each Lambda/serverless invocation may open new connection — use connection pooling proxy (RDS Proxy, PgBouncer)
• Connections left open block schema changes — ALTER TABLE waits for all transactions
• Idle connections consume memory — set connection timeout, kill idle connections

Transaction Gotchas

• Long transactions hold locks and bloat MVCC — keep transactions short
• Read-only transactions still take snapshots — can block vacuum/cleanup in Postgres
• Implicit autocommit varies by database — explicit BEGIN/COMMIT is safer
• Deadlocks from inconsistent lock ordering — always lock tables/rows in same order
• Lost updates from read-modify-write without locking — use SELECT FOR UPDATE or optimistic locking

Schema Changes

• Adding column with default rewrites entire table in old MySQL/Postgres — use NULL default, backfill, then alter
• Index creation locks writes in some databases — use CONCURRENTLY in Postgres, ONLINE in MySQL 8+
• Renaming column breaks running application — add new column, migrate, drop old
• Dropping column with active queries causes errors — deploy code change first, then schema change
• Foreign key checks slow bulk inserts — disable constraints, insert, re-enable

Backup and Recovery

• Logical backups (pg_dump, mysqldump) lock tables or miss concurrent writes — use consistent snapshot
• Point-in-time recovery requires WAL/binlog retention — configure before you need it
• Backup verification: restore regularly — backups that can't restore aren't backups
• Replication lag during backup can cause inconsistency — backup from replica, verify consistency

Replication Traps

• Replication lag means stale reads — check lag before trusting replica data
• Writes to replica corrupt replication — make replicas read-only
• Schema changes can break replication — replicate schema changes, don't apply separately
• Split-brain after failover loses writes — use fencing/STONITH to prevent
• Promoting replica doesn't redirect connections — application must reconnect to new primary

Query Patterns

• N+1 queries from ORM lazy loading — eager load relationships or batch queries
• Missing indexes on foreign keys slows joins and cascading deletes
• Large IN clauses become slow — batch into multiple queries or use temp table
• COUNT(*) on large tables is slow — use approximate counts or cache
• SELECT without LIMIT on unbounded data risks OOM

Data Integrity

• Application-level unique checks have race conditions — use database constraints
• Check constraints often disabled for "flexibility" then data corrupts — keep them on
• Orphan rows from missing foreign keys — add constraints retroactively, clean up first
• Timezone confusion: store UTC, convert on display — never store local time without zone
• Floating point for money causes rounding errors — use DECIMAL or integer cents

Scaling Limits

• Single table over 100M rows needs sharding strategy — plan before you hit it
• Autovacuum falling behind causes table bloat — monitor dead tuple ratio
• Query planner statistics stale after bulk changes — ANALYZE after large imports
• Connection count doesn't scale linearly — more connections = more lock contention
• Disk IOPS often bottleneck before CPU — monitor I/O wait