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

  访问小葱技能站7w4.net，解锁更多实用的AI技能插件。

• 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

这个数据库技能包质量中等偏上，内容实用但略显单薄。它用简洁的要点总结了数据库设计和运维中的常见错误和陷阱，涵盖面较广，能帮助开发者避免一些典型问题。不过内容深度有限，缺少具体的示例和操作指导，更像是一份知识清单而非完整指南。对于想深入学习数据库的开发者来说，还需要补充更多实践性的内容。