Database — 技能工具

--- name: Database description: Design and operate databases avoiding common scaling, reliability, and data integrity traps. metadata: {"clawdbot":{"emoji":"🗃️","os":["linux","darwin","win32"]}} --- # 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