DOMAIN:DATABASE — PITFALLS AND ANTI-PATTERNS¶
OWNER: boris, yoanna
UPDATED: 2026-03-24
SCOPE: database mistakes to avoid — PostgreSQL, Drizzle ORM, and LLM-specific
PITFALLS:INDEX_ANTI_PATTERNS¶
MISSING INDEX ON FOREIGN KEY¶
SEVERITY: HIGH
SYMPTOM: slow JOINs, slow DELETE on parent table (PostgreSQL scans child table for FK check)
CAUSE: PostgreSQL does NOT automatically create indexes on foreign key columns
FIX: create index on EVERY foreign key column
-- ALWAYS do this when adding a foreign key
ALTER TABLE orders ADD COLUMN user_id UUID REFERENCES users(id);
CREATE INDEX idx_orders_user_id ON orders (user_id); -- MUST ADD MANUALLY
CHECK: run this query to find missing FK indexes:
SELECT
conrelid::regclass AS table_name,
a.attname AS column_name,
conname AS constraint_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid
AND a.attnum = ANY(i.indkey)
)
ORDER BY conrelid::regclass::text;
OVER-INDEXING¶
SEVERITY: MEDIUM
SYMPTOM: slow INSERT/UPDATE/DELETE, excessive storage usage
CAUSE: adding indexes "just in case" without query evidence
FIX: only add indexes for queries that actually exist in the application
CHECK: monitor pg_stat_user_indexes.idx_scan — drop indexes with 0 scans after 30 days
WRONG INDEX TYPE¶
SEVERITY: MEDIUM
SYMPTOM: index exists but query does full table scan
CAUSE: B-tree index on JSONB containment query, or GIN index on equality comparison
FIX: match index type to query operator — see schema-design.md index section
INDEX ON LOW-CARDINALITY COLUMN¶
SEVERITY: LOW
SYMPTOM: PostgreSQL ignores the index, does seq scan instead
CAUSE: B-tree index on boolean or status column with 3 values — not selective enough
FIX: partial index: CREATE INDEX idx_orders_pending ON orders (id) WHERE status = 'pending';
PITFALLS:ORM_ANTI_PATTERNS¶
N+1 QUERY PROBLEM¶
SEVERITY: CRITICAL
SYMPTOM: page load time grows linearly with data — 10 items = 11 queries, 100 items = 101 queries
CAUSE: looping over results and querying related data one-by-one
// BAD — N+1
const users = await db.select().from(usersTable);
for (const user of users) {
const profile = await db.select().from(profilesTable).where(eq(profilesTable.userId, user.id));
// This runs 1 + N queries
}
// GOOD — single query with join
const usersWithProfiles = await db
.select()
.from(usersTable)
.leftJoin(profilesTable, eq(usersTable.id, profilesTable.userId));
// ALSO GOOD — Drizzle relational query
const usersWithProfiles = await db.query.users.findMany({
with: { profile: true },
});
DETECT: enable query logging in development, count queries per page load
TOOL: pg_stat_statements — look for queries called N times with nearly identical parameters
SELECT * EVERYWHERE¶
SEVERITY: MEDIUM
SYMPTOM: unnecessary data transfer, prevents index-only scans, higher memory usage
CAUSE: Drizzle db.select() without column specification returns all columns
FIX: specify columns: db.select({ id: users.id, email: users.email })
EXCEPTION: when you genuinely need all columns — but audit this assumption
MISSING TRANSACTION FOR MULTI-STEP OPERATIONS¶
SEVERITY: CRITICAL
SYMPTOM: partial data updates — some rows updated, others not. Inconsistent state.
CAUSE: multiple related queries executed outside a transaction
// BAD — no transaction, partial failure leaves inconsistent state
await db.update(ordersTable).set({ status: 'shipped' }).where(eq(ordersTable.id, orderId));
await db.insert(shipmentsTable).values({ orderId, trackingNumber }); // if this fails, order says "shipped" but no shipment exists
// GOOD — atomic transaction
await db.transaction(async (tx) => {
await tx.update(ordersTable).set({ status: 'shipped' }).where(eq(ordersTable.id, orderId));
await tx.insert(shipmentsTable).values({ orderId, trackingNumber });
});
RAW SQL WITHOUT PARAMETERIZATION¶
SEVERITY: CRITICAL (security)
SYMPTOM: SQL injection vulnerability
CAUSE: string concatenation instead of parameterized queries
// BAD — SQL injection
const results = await db.execute(sql.raw(`SELECT * FROM users WHERE email = '${userInput}'`));
// GOOD — parameterized
const results = await db.execute(sql`SELECT * FROM users WHERE email = ${userInput}`);
NOTE: Drizzle's query builder always parameterizes — this only applies to raw SQL usage
NOTE: sql.raw() should NEVER contain user input — it is for static SQL fragments only
PITFALLS:CONNECTION_MANAGEMENT¶
CONNECTION POOL EXHAUSTION¶
SEVERITY: CRITICAL
SYMPTOM: application hangs, "too many connections" errors, timeouts
CAUSE: connections opened but never returned to pool, long-running transactions holding connections
FIX_IMMEDIATE: check for idle in transaction connections and terminate them:
SELECT pid, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - xact_start > interval '5 minutes';
-- Terminate the worst offender
SELECT pg_terminate_backend(pid);
FIX_CONFIG: set
idle_in_transaction_session_timeout = '30s' in postgresql.conf
DIRECT CONNECTIONS BYPASSING PGBOUNCER¶
SEVERITY: HIGH
SYMPTOM: max_connections hit even though PgBouncer should be multiplexing
CAUSE: some service connects directly to PostgreSQL instead of PgBouncer
FIX: audit all connection strings — ALL must point to PgBouncer, not directly to PostgreSQL
NOTE: only migrations and emergency admin access should bypass PgBouncer
CONNECTION LEAK IN ERROR PATHS¶
SEVERITY: HIGH
SYMPTOM: connection count slowly grows over time, eventual exhaustion
CAUSE: error in application code prevents connection return to pool
// BAD — error before release
const client = await pool.connect();
const result = await client.query('...'); // if this throws, client is never released
client.release();
// GOOD — try/finally
const client = await pool.connect();
try {
const result = await client.query('...');
} finally {
client.release();
}
// BEST — use Drizzle (handles connection lifecycle)
const result = await db.select().from(table);
PITFALLS:VACUUM_AND_BLOAT¶
MISSING VACUUM ON HIGH-WRITE TABLES¶
SEVERITY: HIGH
SYMPTOM: table size grows much larger than data warrants, queries get slower over time
CAUSE: autovacuum defaults too conservative for high-write tables
FIX: tune autovacuum per table — see performance.md vacuum section
DETECT:
SELECT relname, n_dead_tup, n_live_tup,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE n_dead_tup > n_live_tup * 0.2
ORDER BY n_dead_tup DESC;
LONG-RUNNING TRANSACTIONS BLOCKING VACUUM¶
SEVERITY: HIGH
SYMPTOM: dead tuples accumulate despite autovacuum running
CAUSE: long-running transaction holds xmin horizon — vacuum cannot clean tuples newer than oldest transaction
FIX: find and fix long-running transactions:
SELECT pid, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start ASC LIMIT 5;
BEGIN in psql blocks vacuum globally
TABLE BLOAT AFTER BULK DELETE¶
SEVERITY: MEDIUM
SYMPTOM: table size unchanged after deleting 90% of rows
CAUSE: PostgreSQL does not return space to OS after DELETE — space is reused by future inserts
FIX: VACUUM FULL reclaims space but takes ACCESS EXCLUSIVE lock — schedule during maintenance window
ALTERNATIVE: pg_repack extension — online table repacking without exclusive lock
PITFALLS:SCHEMA_DESIGN¶
MIXING TIMEZONES¶
SEVERITY: HIGH
SYMPTOM: times are wrong, off by hours, inconsistent across the application
CAUSE: using timestamp instead of timestamptz, or storing local times
FIX: ALWAYS use timestamptz. ALWAYS store UTC. Convert in the application layer for display.
DETECT: find non-timezone columns:
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type = 'timestamp without time zone'
AND table_schema = 'public';
timestamptz — this is a type change migration (see migrations.md)
JSONB OVERUSE¶
SEVERITY: MEDIUM
SYMPTOM: complex queries against JSONB fields, slow performance, no type safety
CAUSE: using JSONB as a "schemaless" escape hatch instead of proper relational modeling
RULE: if you query a JSONB field in WHERE/JOIN/ORDER BY more than occasionally, extract it to a column
RULE: if a JSONB field has a consistent structure across all rows, it should be columns
ACCEPTABLE: truly variable metadata, third-party API responses, user preferences, feature flags
ENUM REGRET¶
SEVERITY: MEDIUM
SYMPTOM: need to rename or remove an enum value, discover it requires painful migration
CAUSE: using PostgreSQL enums for frequently changing value sets
RULE: use enums for STABLE sets (status: active/inactive, role: admin/user/viewer)
RULE: use lookup tables for CHANGING sets (categories, tags, product types)
FIX: see migrations.md for enum migration patterns
MISSING DEFAULT VALUES¶
SEVERITY: LOW
SYMPTOM: application errors when inserting rows without specifying every column
CAUSE: columns without sensible defaults
FIX: add defaults for every column that has an obvious default value
EXAMPLES: is_active DEFAULT true, created_at DEFAULT now(), quantity DEFAULT 0
PITFALLS:LOCKING¶
IMPLICIT LOCKS FROM ALTER TABLE¶
SEVERITY: CRITICAL
SYMPTOM: all queries blocked during migration, application appears down
CAUSE: ALTER TABLE acquires ACCESS EXCLUSIVE lock — blocks ALL reads AND writes
AFFECTED_OPERATIONS: ADD COLUMN (with volatile default pre-PG11), DROP COLUMN, ALTER TYPE, RENAME
FIX: see migrations.md for zero-downtime patterns
IMMEDIATE_MITIGATION: always set lock_timeout = '5s' before DDL — fail fast, retry later
LOCK QUEUE BUILDUP¶
SEVERITY: HIGH
SYMPTOM: one ALTER TABLE blocks queries, blocked queries pile up, cascade failure
CAUSE: long-running query holds AccessShare lock, DDL waits for exclusive lock, new queries queue behind DDL
SEQUENCE: Query A (running 5min) → DDL waits → Query B waits behind DDL → Query C waits → pool exhaustion
FIX: kill long-running queries before DDL, or use lock_timeout and retry
FIX: schedule DDL during low-traffic window
DEADLOCKS¶
SEVERITY: MEDIUM
SYMPTOM: ERROR: deadlock detected — one transaction is killed
CAUSE: two transactions lock resources in different order
FIX: always acquire locks in consistent order (e.g., by primary key ascending)
FIX: keep transactions short — less time holding locks = less deadlock opportunity
DETECT: log_lock_waits = on in postgresql.conf, check pg_stat_activity for blocked queries
PITFALLS:LLM_SPECIFIC¶
LLM GENERATES INEFFICIENT QUERIES¶
SEVERITY: HIGH
SYMPTOM: LLM-generated code uses correlated subqueries, unnecessary CTEs, or DISTINCT instead of proper JOINs
CAUSE: LLMs optimize for correctness, not performance. Training data includes bad SQL.
EXAMPLES_TO_WATCH:
- SELECT DISTINCT used to mask a wrong JOIN (produces correct results but hides the bug)
- Correlated subquery where a JOIN would work: WHERE id IN (SELECT ... FROM ... WHERE outer.id = inner.id)
- CTE used for simple subquery (PostgreSQL may not inline CTEs before PG 12, and even after, not always)
- ORDER BY on unindexed column in large result set
FIX: ALWAYS review LLM-generated SQL with EXPLAIN ANALYZE before deploying
FIX: Boris/Yoanna should review any complex query in code review
LLM FORGETS TRANSACTIONS¶
SEVERITY: CRITICAL
SYMPTOM: multi-step database operations without transaction wrapping
CAUSE: LLMs often generate individual queries without considering atomicity
EXAMPLE: create user + create profile + send welcome email — if profile creation fails, orphaned user
FIX: code review checklist: "are multi-step DB operations wrapped in a transaction?"
LLM IGNORES CONNECTION LIMITS¶
SEVERITY: HIGH
SYMPTOM: LLM generates code that opens connections inside loops, or creates new pools per request
CAUSE: LLMs do not understand connection pooling as a limited resource
FIX: ensure database client is a singleton, instantiated once at application startup
FIX: never new Pool() or new Client() inside request handlers
LLM GENERATES MIGRATION WITHOUT SAFETY¶
SEVERITY: CRITICAL
SYMPTOM: LLM writes ALTER TABLE RENAME COLUMN directly — causes downtime
CAUSE: LLMs do not know about expand-contract pattern unless prompted
FIX: all migrations must go through DBA review — Boris/Yoanna catch unsafe patterns
FIX: provide this wiki page context when LLM agents write migrations
LLM MISUSES JSONB¶
SEVERITY: MEDIUM
SYMPTOM: LLM stores structured, queryable data in JSONB to avoid schema design
CAUSE: JSONB feels "flexible" and LLMs default to flexibility over correctness
FIX: challenge every JSONB column: "will this be queried? does it have consistent structure?"
LLM GENERATES WRONG DRIZZLE SYNTAX¶
SEVERITY: MEDIUM
SYMPTOM: Drizzle code that looks right but uses outdated or incorrect API
CAUSE: LLM training data may predate current Drizzle version
COMMON_MISTAKES:
- Using .values() without proper type assertion for JSONB
- Missing .$type<T>() on JSONB columns
- Using .returning() syntax incorrectly
- Wrong import paths (drizzle-orm/pg-core vs drizzle-orm/postgres-js)
FIX: verify against current Drizzle documentation — do not trust LLM memory of API
FIX: Zod v4 uses .issues not .errors — this also applies to Drizzle-Zod integration
PITFALLS:OPERATIONAL¶
RUNNING MIGRATIONS DURING PEAK TRAFFIC¶
SEVERITY: HIGH
SYMPTOM: lock contention, timeout errors, degraded performance during migration
CAUSE: DDL statements compete with application queries for locks
FIX: schedule migrations during low-traffic periods
FIX: for zero-downtime migrations, follow expand-contract pattern (migrations.md)
FORGETTING TO ANALYZE AFTER BULK DATA LOAD¶
SEVERITY: MEDIUM
SYMPTOM: query planner makes bad decisions after large data import
CAUSE: table statistics are stale — planner thinks table has old row count
FIX: ANALYZE table_name; after any bulk INSERT/UPDATE/DELETE
NOTE: autovacuum will eventually run ANALYZE, but there is a delay — manual ANALYZE is faster
BACKUP NOT TESTED¶
SEVERITY: CRITICAL
SYMPTOM: discover backup is corrupt or incomplete during actual disaster
CAUSE: "we have backups" without verification
FIX: monthly backup restore test — see compliance-evidence.md
RULE: a backup that has not been tested is not a backup
NO MONITORING ON DISK SPACE¶
SEVERITY: CRITICAL
SYMPTOM: PostgreSQL crashes with "no space left on device" — data loss possible
CAUSE: table bloat, WAL accumulation, forgotten temp files
FIX: monitor disk usage, alert at 80% capacity
FIX: UpCloud managed DB handles this — but verify alerting is configured
PRODUCTION DATA IN DEVELOPMENT¶
SEVERITY: CRITICAL (compliance)
SYMPTOM: PII in developer laptops, violation of data protection regulations
CAUSE: copying production database to local development
FIX: NEVER copy production data to development. Use synthetic seed data or anonymized subsets.
FIX: if anonymized production data is needed, use a one-way anonymization pipeline
PITFALLS:CHECKLIST¶
Before any production database change, verify:
- [ ] Migration tested on staging with representative data volume
- [ ] No ACCESS EXCLUSIVE lock on tables with active traffic (or lock_timeout set)
- [ ] Indexes created CONCURRENTLY
- [ ] Foreign keys added with NOT VALID + VALIDATE pattern
- [ ] No raw SQL with string concatenation (parameterized only)
- [ ] Transactions wrap multi-step operations
- [ ] Connection pooling configured (not direct connections)
- [ ] EXPLAIN ANALYZE run on new queries with >1000 expected rows
- [ ] LLM-generated SQL reviewed by human or DBA
- [ ] Backup verified within last 30 days