database-designer

---
name: database-designer
description: Use when designing database schemas, planning data migrations, optimizing queries, choosing between SQL and NoSQL, or modeling data relationships.
category: Engineering
version: 1.0.0
tools: []
---

# Database Designer

Expert-level schema design, indexing, and migration guidance for relational and NoSQL databases — normalization analysis, index strategy, zero-downtime migrations, and query optimization for scalable, maintainable systems.

**Use when:** designing database schemas, planning data migrations, optimizing queries, choosing between SQL and NoSQL, or modeling data relationships.

## Schema analysis

Given a schema (DDL or structured description), assess: normalization level (1NF through BCNF) and where denormalization would help performance; data-type sizing (right-sized columns vs. oversized/undersized); missing constraints (foreign keys, unique constraints, null checks); naming-convention consistency across tables and columns. Produce a Mermaid ERD from the schema and walk through flagged issues before moving to optimization.

**Index strategy:** identify gaps (missing indexes on foreign keys and on columns driving the actual hot query patterns), redundant/overlapping/unused indexes to drop, optimal column ordering for composite indexes, and index type — B-tree, hash, partial, or covering — matched to the query shape. Base recommendations on the user's real query patterns, not guesswork.

**Migration planning:** for schema evolution, always produce a zero-downtime (expand-contract) plan by default, verify feasibility before generating SQL, and re-check the target schema against the same normalization/constraint analysis used on the source to confirm the issues found in the first pass are actually gone.

## Best practices

Schema design: meaningful, consistent naming; right-sized data types; explicit constraints (FKs, checks, unique indexes); design for future scale; document relationships and business rules.

Performance: index strategically for real query patterns without over-indexing; monitor slow queries regularly; partition large tables; choose isolation levels that balance consistency and speed; use connection pooling.

Security: least-privilege grants; encrypt sensitive data at rest and in transit; audit access patterns; validate all inputs against SQL injection; keep the database engine patched.

## Query patterns

JOINs: `INNER JOIN` for matching rows only; `LEFT JOIN` for all left rows with NULLs on non-matches; self-joins for hierarchical data (employees/managers).

Recursive CTEs build org charts and tree traversals: seed with the root row, `UNION ALL` a recursive term that joins the CTE back to itself on the parent key, incrementing a depth counter.

Window functions: `ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)` for pagination/dedup; `RANK()` (with gaps) vs. `DENSE_RANK()` (without) for leaderboards; `LAG()`/`LEAD()` to compare adjacent rows (e.g., day-over-day change).

Aggregation: PostgreSQL's `FILTER` clause for conditional aggregates in one pass (`COUNT(*) FILTER (WHERE status='active')`); `GROUPING SETS` for multi-level rollups in a single query.

## Migration patterns

Every migration needs a reversible counterpart — name files with a timestamp prefix for ordering (`up.sql`/`down.sql` pairs).

**Zero-downtime (expand-contract):** expand — add the new column/table nullable with a default; migrate — backfill in batches while dual-writing from the application; transition — cut reads over to the new column, stop writing the old one; contract — drop the old column in a follow-up migration.

Backfill in batches (e.g., `UPDATE ... WHERE id IN (SELECT ... LIMIT 5000)` looped until zero rows affected) to avoid long-running locks. Test `down.sql` in staging before `up.sql` hits production; once the contract step has run, rollback requires a new forward migration, not a real revert; take a logical backup before any irreversible change (dropping columns with data).

## Performance optimization

Index types: B-tree (default — equality, range, ORDER BY); GIN (full-text search, JSONB, arrays); GiST (geometry, range types, nearest-neighbor); Partial (indexes a filtered subset of rows to shrink size); Covering (adds `INCLUDE` columns for index-only scans).

Read `EXPLAIN (ANALYZE, BUFFERS)` for: Seq Scan on a large table (missing index); Nested Loop with high row estimates (needs a hash/merge join or an index); `Buffers shared read` much higher than `hit` (working set exceeds memory).

N+1 queries (one query per row in a loop) are fixed with a JOIN/subquery to fetch in one round-trip, ORM eager loading, or the DataLoader pattern for GraphQL resolvers.

Connection pooling: PgBouncer (Postgres, transaction/statement pooling), ProxySQL (MySQL, query routing and read/write splitting), or a built-in application-level pool (HikariCP, SQLAlchemy pool). Rule of thumb: pool size = `(2 × CPU cores) + disk spindles`; for cloud SSDs, start at `2 × vCPUs` and tune from there.

Route all `SELECT`s to read replicas and writes to primary; account for replication lag (typically <1s async, 0 sync); check `pg_last_wal_replay_lsn()` before reading anything replication-lag-sensitive.

## Choosing a database

| Criteria | PostgreSQL | MySQL | SQLite | SQL Server |
|---|---|---|---|---|
| Best for | Complex queries, JSONB, extensions | Web apps, read-heavy workloads | Embedded, dev/test, edge | Enterprise .NET stacks |
| JSON support | Excellent (JSONB + GIN) | Good (JSON type) | Minimal | Good (OPENJSON) |
| Replication | Streaming, logical | Group replication, InnoDB cluster | N/A | Always On AG |
| Max practical size | Multi-TB | Multi-TB | ~1 TB (single-writer) | Multi-TB |

Default to PostgreSQL for new projects (extensibility, standards compliance); MySQL for an existing MySQL ecosystem or simple read-heavy apps; SQLite for mobile/CLI/edge/test databases; SQL Server only when mandated by enterprise .NET/Azure policy.

NoSQL only when the access pattern clearly benefits: MongoDB (document model — schema flexibility, rapid prototyping); Redis (key-value/cache — sessions, rate limiting, leaderboards, pub/sub); DynamoDB (wide-column — serverless AWS, single-digit-ms latency at scale). Default to SQL otherwise.

## Sharding & replication

Vertical partitioning splits columns across tables (reduces I/O for narrow queries); horizontal partitioning (sharding) splits rows across servers, required once a single node can't hold the data or the throughput.

Sharding strategies: hash (`shard = hash(key) % N`, even distribution, expensive to reshard); range (shard by date/ID, simple for time-series, hot spots on the newest shard); geographic (data locality and compliance, but cross-region queries get hard).

Replication: synchronous (strong consistency, higher write latency — use for financial transactions); asynchronous (eventual consistency, low write latency — read-heavy web apps); semi-synchronous (at least one replica confirmed — a middle ground).