Database

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

Security Scan

This is a purely instructional skill that provides database operational guidance and does not request extra privileges, credentials, or install anything, so it is internally consistent with its stated purpose.

Purpose & Capability

The name and description match the SKILL.md content: practical DB scaling, reliability, and integrity guidance. There are no unrelated requirements (no env vars, binaries, or installs).

Instruction Scope

SKILL.md is static guidance (best-practices notes). It does not instruct the agent to read files, access environment variables, call external services, or run commands beyond conceptual mentions, so scope stays within the stated purpose.

Install Mechanism

No install spec and no code files — instruction-only — so nothing is written to disk or downloaded during install.

Credentials

The skill requests no environment variables, credentials, or config paths; this is proportionate for a guidance-only skill.

Persistence & Privilege

always: false and default invocation settings. The skill does not request persistent or elevated privileges and does not modify other skills or system config.

Assessment

This skill is low-risk: it only contains written best-practice advice for databases and does not ask for credentials or install software. If you expect an automated tool (for migrations, backups, or monitoring), note this skill is documentation-only — it won't perform actions. As always, be cautious when combining any skill with other automation that does have credential or system access.

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