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