Designs database schemas, indexing strategies, query optimization, and migration patterns for SQL and NoSQL databases. Use when designing tables, optimizing queries, fixing N+1 problems, planning migrations, or when asked about database performance, normalization, ORMs, or data modeling.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Designs database schemas, indexing strategies, query optimization, and migration patterns for SQL and NoSQL databases. Use when designing tables, optimizing queries, fixing N+1 problems, planning migrations, or when asked about database performance, normalization, ORMs, or data modeling.
1NF: Atomic values, no repeating groups
2NF: 1NF + no partial dependencies (all non-key columns depend on full PK)
3NF: 2NF + no transitive dependencies (non-key columns don't depend on other non-key columns)
-- WRONG: UnnormalizedCREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_name TEXT,
customer_email TEXT, -- duplicated across orders
product1_name TEXT, -- repeating groups
product1_qty INT,
product2_name TEXT,
product2_qty INT
);
-- CORRECT: Normalized to 3NFCREATE TABLE customers (
id SERIAL ,
name TEXT ,
email TEXT
);
orders (
id SERIAL ,
customer_id customers(id),
created_at TIMESTAMPTZ NOW()
);
order_items (
id SERIAL ,
order_id orders(id),
product_id products(id),
quantity (quantity )
);
PRIMARY KEY
NOT NULL
UNIQUE
NOT NULL
CREATE TABLE
PRIMARY KEY
INT
REFERENCES
DEFAULT
CREATE TABLE
PRIMARY KEY
INT
REFERENCES
INT
REFERENCES
INT
NOT NULL
CHECK
>
0
When to Denormalize
Denormalize only when you have measured proof of performance issues:
-- Acceptable denormalization: precomputed counter to avoid COUNT(*)ALTER TABLE posts ADDCOLUMN comment_count INTDEFAULT0;
-- Update via trigger or application codeCREATEFUNCTION update_comment_count() RETURNSTRIGGERAS $$
BEGIN
IF TG_OP ='INSERT'THENUPDATE posts SET comment_count = comment_count +1WHERE id = NEW.post_id;
ELSIF TG_OP ='DELETE'THENUPDATE posts SET comment_count = comment_count -1WHERE id = OLD.post_id;
END IF;
RETURNNULL;
END;
$$ LANGUAGE plpgsql;
Indexing Strategy
Index Types and When to Use
B-tree (default): Equality, range, sorting, LIKE 'prefix%'
Hash: Equality only (rarely better than B-tree)
GIN: Full-text search, JSONB, arrays
GiST: Geometry, range types, full-text
BRIN: Large tables with naturally ordered data (timestamps)
Composite Indexes
-- Column order matters: leftmost prefix ruleCREATE INDEX idx_users_status_created ON users (status, created_at);
-- This index supports:-- WHERE status = 'active' -- YES-- WHERE status = 'active' AND created_at > '2024' -- YES-- WHERE created_at > '2024' -- NO (skips first column)
Partial and Covering Indexes
-- Partial index: only index rows matching conditionCREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status ='pending'; -- smaller index, faster lookups-- Covering index: include columns to avoid table lookupCREATE INDEX idx_users_email_covering ON users (email)
INCLUDE (name, avatar_url); -- index-only scan for profile lookups
Index Anti-patterns
-- WRONG: Index on low-cardinality column aloneCREATE INDEX idx_users_active ON users (is_active); -- boolean = 2 values-- WRONG: Too many indexes (slows writes)-- Every INSERT/UPDATE must update ALL indexes-- CORRECT: Composite index targeting actual queriesCREATE INDEX idx_users_active_created ON users (is_active, created_at DESC)
WHERE is_active =true;
Query Optimization
Reading EXPLAIN Plans
EXPLAIN ANALYZE SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.status ='active'GROUPBY u.name;
-- Key things to look for:-- Seq Scan -> missing index (on large tables)-- Nested Loop -> fine for small sets, bad for large joins-- Hash Join -> good for large equi-joins-- Sort -> consider index to avoid sort-- actual time -> real execution time-- rows -> if estimated vs actual differ wildly, run ANALYZE
N+1 Query Detection and Prevention
# WRONG: N+1 queries (1 query for users + N queries for orders)
users = db.query(User).all()
for user in users:
orders = db.query(Order).filter(Order.user_id == user.id).all() # N queries!# CORRECT: Eager loading with SQLAlchemy
users = db.query(User).options(joinedload(User.orders)).all()
# CORRECT: Batch query
user_ids = [u.idfor u in users]
orders = db.query(Order).filter(Order.user_id.in_(user_ids)).all()
orders_by_user = defaultdict(list)
for order in orders:
orders_by_user[order.user_id].append(order)
1. Never rename a column in one step (add new, migrate data, drop old)
2. Never drop a column that's still read by running code
3. Add columns as nullable or with defaults
4. Create indexes CONCURRENTLY to avoid locking
5. Test rollback before deploying
Zero-Downtime Migration Example
-- Step 1: Add new column (safe, no lock)ALTER TABLE users ADDCOLUMN display_name TEXT;
-- Step 2: Backfill data (do in batches)UPDATE users SET display_name = name WHERE display_name ISNULLAND id BETWEEN1AND10000;
-- Step 3: Deploy code that writes to BOTH columns-- Step 4: Deploy code that reads from new column-- Step 5: Drop old column (after confirming no reads)ALTER TABLE users DROPCOLUMN name;
Index Creation
-- WRONG: Blocks writes on the tableCREATE INDEX idx_orders_user ON orders (user_id);
-- CORRECT: Non-blocking (PostgreSQL)CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);
Connection Pooling
Rule of thumb: connections = (CPU cores * 2) + disk spindles
For most apps: 10-20 connections per application instance
# SQLAlchemy connection pool
engine = create_engine(
DATABASE_URL,
pool_size=10, # maintained connections
max_overflow=20, # extra connections under load
pool_timeout=30, # seconds to wait for connection
pool_recycle=1800, # recycle connections every 30 min
pool_pre_ping=True, # verify connection before use
)
// Prisma datasource// In schema.prisma:// datasource db {// provider = "postgresql"// url = env("DATABASE_URL")// }// Connection limit via URL: ?connection_limit=10&pool_timeout=30
ORM Best Practices
Select Only What You Need
# WRONG: Fetches all columns
users = db.query(User).all()
# CORRECT: Select specific columns
users = db.query(User.id, User.name).all()
// Design for access patterns, not normalization// Embed when: 1:1, 1:few, data read together// Reference when: 1:many, many:many, data grows unbounded// WRONG: Normalizing in MongoDB like SQL// users collection: { _id, name }// addresses collection: { _id, userId, street } // requires joins// CORRECT: Embed bounded, co-accessed data
{
_id: ObjectId("..."),
name: "Alice",
addresses: [
{ street: "123 Main St", city: "NYC", type: "home" },
{ street: "456 Work Ave", city: "NYC", type: "work" }
]
}
// CORRECT: Reference unbounded or independent data// user: { _id, name, orderIds: [ObjectId("...")] }// orders: { _id, userId, items: [...], total: 99.99 }
Key-Value / Redis Patterns
# Cache-aside pattern
1. Check cache for key
2. If miss, query database
3. Store result in cache with TTL
4. Return result
# Cache invalidation
- TTL-based: SET key value EX 3600 (1 hour)
- Event-based: Delete key on write
- Write-through: Update cache on every write
Common Anti-Patterns Summary
AVOID DO INSTEAD
-------------------------------------------------------------------
SELECT * SELECT specific columns
OFFSET pagination Cursor-based pagination
N+1 queries Eager load or batch queries
Indexing every column Index based on query patterns
UUID v4 as primary key UUID v7 or BIGSERIAL (better locality)
Storing money as FLOAT Use DECIMAL / BIGINT (cents)
No foreign keys "for speed" Use foreign keys (data integrity)
Giant migrations Small, reversible steps
No connection pooling Always pool connections
Premature denormalization Normalize first, denormalize with data
