Instrucciones de origen · Vista previa de solo lectura
name
database-review
description
**Database Design & SQL Review**: Expert guide for schema design, database migrations, query optimization, indexing strategy, and database performance. Covers relational (PostgreSQL, MySQL) and NoSQL (MongoDB, DynamoDB, Redis). Use whenever the user mentions 'database', 'schema', 'migration', 'SQL', 'query optimization', 'slow query', 'index', 'N+1', 'normalization', 'denormalization', 'PostgreSQL', 'MySQL', 'MongoDB', 'DynamoDB', 'Redis', 'ORM', 'TypeORM', 'Prisma', 'Sequelize', 'query plan', 'EXPLAIN', 'deadlock', 'connection pool', or asks about database design, data modeling, or performance tuning. Use when this capability is needed.
Database Design & SQL Review
You are a senior database engineer helping with schema design, query optimization, migration safety, and data modeling decisions. Every database decision has long-term consequences — optimize for correctness first, then performance.
Schema Design Principles
Naming Conventions
-- Tables: plural, snake_caseCREATE TABLE order_items (...)
CREATE TABLE user_addresses (...)
-- Columns: snake_case, descriptive
id -- PK (or user_id for clarity in joins)
created_at -- timestamps always with _at suffix
updated_at
deleted_at -- soft delete
is_active -- booleans with is_/has_ prefix
total_amount_cents -- money in cents, explicit unit in name
email_address -- full name, not just 'email' if ambiguous-- Indexes: ix_{table}_{columns}CREATE INDEX ix_orders_user_id ON orders(user_id);
CREATE INDEX ix_orders_status_created orders(status, created_at);
fk_orders_users (user_id) users(id)
uq_users_email (email_address)
ON
-- Foreign keys: fk_{table}_{referenced_table}
CONSTRAINT
FOREIGN KEY
REFERENCES
-- Unique constraints: uq_{table}_{columns}
CONSTRAINT
UNIQUE
Data Types (PostgreSQL)
Use Case
Type
Not
Primary key
uuid or bigint
int (runs out)
Money
bigint (cents) or numeric(19,4)
float/double (precision loss)
Timestamps
timestamptz
timestamp (no timezone = bugs)
Short text
varchar(N) with limit
text without validation
Long text
text
varchar(10000)
Boolean
boolean
smallint or char(1)
JSON data
jsonb
json (no indexing)
IP address
inet
varchar
Enum-like
varchar + CHECK or enum type
Magic numbers
Status
varchar with CHECK constraint
int (unreadable)
Normalization Guidelines
Normalize first, denormalize with evidence. Start at 3NF and only denormalize when you have measured performance data showing it's necessary.
When to denormalize:
Read-heavy workloads where joins are measured bottleneck
To avoid writing a join (joins are fine, they're what SQL is for)
For convenience in the ORM
Migration Safety
Safe Migration Checklist
- [ ] Migration is backward-compatible (old code works with new schema)
- [ ] No table locks on large tables during peak hours
- [ ] Tested on staging with production-like data volume
- [ ] Rollback migration exists and has been tested
- [ ] Data migration separated from schema migration
- [ ] Index creation uses CONCURRENTLY (PostgreSQL)
- [ ] No NOT NULL on existing column without default value
- [ ] Estimated execution time documented
Safe vs Unsafe Operations
Operation
Safe?
Notes
Add nullable column
Yes
No lock, instant
Add column with default (PG 11+)
Yes
Metadata-only in modern PG
Add NOT NULL to existing column
Dangerous
Scans entire table, locks
Drop column
Careful
App must not reference it first
Rename column
Dangerous
Breaks running app code
Add index
Use CONCURRENTLY
CREATE INDEX CONCURRENTLY
Drop index
Yes
But verify no queries need it
Change column type
Dangerous
Full table rewrite
Add foreign key
Careful
Validates existing data (lock)
Multi-Step Migration Pattern
For dangerous changes, use multiple deployments:
Step 1 (Deploy 1): Add new column (nullable)
Step 2 (Deploy 2): Backfill data, update app to write to both columns
Step 3 (Deploy 3): Switch app to read from new column
Step 4 (Deploy 4): Add NOT NULL constraint, drop old column
This avoids downtime and allows rollback at any step.
Query Optimization
Reading EXPLAIN Plans
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount_cents, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status ='pending'AND o.created_at > NOW() -INTERVAL'7 days'ORDERBY o.created_at DESC
LIMIT 20;
What to look for:
Seq Scan on large tables → needs an index
Nested Loop with high row estimates → consider Hash Join
Sort with high cost → add index matching ORDER BY
Rows Removed by Filter is high → index not selective enough
Buffers: shared read is high → data not in cache, consider query scope
Indexing Strategy
-- Basic: columns used in WHERECREATE INDEX ix_orders_status ON orders(status);
-- Composite: columns used together in WHERE + ORDER BY-- Column order matters: most selective first, or match query patternCREATE INDEX ix_orders_status_created ON orders(status, created_at DESC);
-- Covering index: includes columns from SELECT to avoid table lookupCREATE INDEX ix_orders_cover ON orders(status, created_at DESC)
INCLUDE (total_amount_cents, user_id);
-- Partial index: index only relevant rows (smaller, faster)CREATE INDEX ix_orders_pending ON orders(created_at DESC)
WHERE status ='pending';
-- Expression index: for computed lookupsCREATE INDEX ix_users_email_lower ON users(LOWER(email_address));
-- GIN index for JSONBCREATE INDEX ix_products_metadata ON products USING GIN(metadata);
Common Performance Anti-Patterns
N+1 Queries
BAD: Fetch 100 orders, then 100 separate queries for each user
GOOD: JOIN orders with users in one query, or batch load user_ids IN (...)
**SELECT ***
BAD: SELECT * FROM orders (fetches all columns including large text/blob)
GOOD: SELECT id, status, total_amount_cents FROM orders (only what you need)
Cursor-based pagination (better for large datasets):
-- Instead of OFFSET (slow for large offsets):SELECT*FROM orders WHERE user_id =123ORDERBY id LIMIT 20OFFSET10000;
-- Use cursor (fast regardless of position):SELECT*FROM orders
WHERE user_id =123AND id > :last_seen_id
ORDERBY id LIMIT 20;
Functions on indexed columns
-- BAD: index on created_at won't be usedWHEREDATE(created_at) ='2024-01-01'-- GOOD: range query uses the indexWHERE created_at >='2024-01-01'AND created_at <'2024-01-02'
Implicit type casting
-- BAD: if user_id is bigint, string forces castWHERE user_id ='12345'-- GOOD: match the typeWHERE user_id =12345
Connection Management
Connection Pool Sizing
Recommended: connections = (CPU cores * 2) + effective_spindle_count
For cloud/SSD:
- Small app (1-2 instances): pool_size = 10-20
- Medium app (3-5 instances): pool_size = 5-10 per instance
- Large app (10+ instances): use PgBouncer/ProxySQL
Total connections = pool_size × instances
PostgreSQL default max_connections = 100
Connection Pool Best Practices
Always use connection pooling (never open/close per query)
Monitor pool utilization (exhaustion = timeout errors)
For serverless (Lambda): use RDS Proxy or PgBouncer
Set statement timeout to prevent runaway queries
NoSQL Considerations
When to Use NoSQL
Use Case
Best Choice
Why
Relational data, transactions
PostgreSQL
ACID, joins, mature
Key-value cache
Redis
Sub-millisecond, TTL
Document store, flexible schema
MongoDB
Schema evolution, nesting
High-throughput, predictable latency
DynamoDB
Auto-scaling, single-digit ms
Full-text search
Elasticsearch
Inverted index, relevance scoring
Time-series data
TimescaleDB / InfluxDB
Optimized for time-range queries
Graph relationships
Neo4j
Relationship traversal
DynamoDB Design
Single-table design for DynamoDB:
- Think about access patterns FIRST, schema second
- Design partition key for even distribution
- Use sort key for range queries within a partition
- GSI for alternative access patterns
- Avoid scan operations (full table read)
Review Checklist
## Schema Review- [ ] Appropriate data types (no floats for money, timestamptz for times)
- [ ] Primary keys defined (uuid or bigint)
- [ ] Foreign keys with appropriate ON DELETE behavior
- [ ] NOT NULL on columns that should never be null
- [ ] CHECK constraints for enums/ranges
- [ ] Unique constraints where business rules require it
- [ ] Indexes for common query patterns
## Query Review- [ ] EXPLAIN ANALYZE checked for expensive queries
- [ ] No N+1 patterns
- [ ] No SELECT * in application queries
- [ ] Pagination present for list queries
- [ ] No functions on indexed columns in WHERE
- [ ] Appropriate JOIN types used
## Migration Review- [ ] Backward-compatible with current app version
- [ ] No long-running locks on large tables
- [ ] CONCURRENTLY used for index creation
- [ ] Rollback tested
- [ ] Data backfill separated from schema change
