Analyze access patterns
Start by instrumenting the database and application to collect query logs, latency percentiles, QPS, and resource metrics (CPU, I/O, locks). Capture representative traffic over peak and off-peak windows; short samples miss seasonal skew. Use the slow-query log, tracing, or an APM to get full query text and execution plans.
Group queries by shape and frequency: point lookups, range scans, large joins, aggregations, and small transactional writes. Measure read/write ratio, row-selection cardinality, result size, and contention hotspots (tables/partitions receiving most traffic). Identify heavy, repetitive queries vs. rare analytical jobs.
Let the workload drive schema decisions. High-frequency point reads favor narrow rows and covering indexes; high-cardinality filters may need composite or partial indexes. Frequent joins across the same keys suggest denormalization or materialized views to avoid repeated expensive joins. Large scan/aggregation patterns are candidates for partitioning by access key or time, and for pre-aggregated tables. Heavy write bursts call for batching, smaller transactions, and append-friendly schemas.
Design for cacheability: make common queries return stable, immutable columns and add TTLs where appropriate. Use explain plans and histograms to validate that chosen indexes are used and to detect skewed distribution. Iterate: deploy schema changes in a staging environment with replayed traffic, and continue monitoring after release to catch regressions. Prioritize changes by user-impact and cost (latency saved vs. maintenance overhead).
Normalize vs denormalize
Normalization organizes data into small, single-purpose tables with foreign keys to eliminate redundancy and ensure strong consistency; it simplifies updates and minimizes storage but increases join cost. Denormalization copies or pre-joins data into wider rows or summary tables to serve frequent read paths with fewer joins, reducing latency at the cost of extra storage and more complex write/update logic. Choose by workload: for high read QPS with repeated joins (point reads or dashboards), denormalize or use materialized views to make queries covering and cache-friendly; for high-concurrency writes or systems requiring strict transactional consistency, favor normalization to reduce update surface and anomalies. Hybrid patterns work best: keep a normalized canonical model and expose denormalized read models updated via triggers, change-data-capture, or asynchronous workers; use partial denorm (store a few hot columns) rather than wholesale duplication. Consider operational factors: denorm increases maintenance (schema migrations, backfills, multi-row updates), impacts indexing and memory footprint, and complicates rollback; normalization pushes CPU and I/O into the query layer and benefits from good indexing and partitioning. Validate with metrics: measure end-to-end latency, p95, storage delta, and write amplification in staging traffic before committing to the change.
Primary and foreign keys
Choose a single, immutable column as the authoritative row identifier and keep it narrow and stable. Prefer surrogate keys (small sequential integers or sequential UUIDs/ULIDs) for clustered indexes and fast lookups; natural keys are fine only when they are truly stable and compact. Keep primary keys immutable—changing PKs cascades through indexes and foreign relationships and is costly.
Enforce referential integrity with foreign key constraints when correctness matters; they prevent orphaned rows and simplify reasoning. Be mindful: declarative FKs add locking and write overhead on high-concurrency workloads, so some high-scale systems enforce relationships in application logic or via background reconciliation, but only after measuring risk. Always index foreign key columns used in joins to avoid expensive lookups and to speed cascade operations.
Design PK/FK types and sizes for common access patterns. Avoid wide composite primary keys unless they reflect the true identity; composite keys are useful for join locality but increase index size and IO. If sharding or multi-node writes are required, include or align the shard key with the PK to limit cross-shard joins.
Use cascade rules deliberately: ON DELETE CASCADE is convenient for parent-child cleanups but can trigger large synchronous deletions—favor soft deletes plus background jobs for very large trees. Example:
CREATE TABLE parent (id BIGSERIAL PRIMARY KEY);
CREATE TABLE child (id BIGSERIAL PRIMARY KEY, parent_id BIGINT NOT NULL REFERENCES parent(id) ON DELETE RESTRICT);
CREATE INDEX ON child(parent_id);
Validate choices with production-like load: monitor lock contention, index bloat, and join latency before removing or adding constraints.
Optimize indexes and queries
Indexes accelerate reads but add write cost and storage—tune them to the workload. Prefer narrow, immutable keys (small integers or compact UUIDs) for indexed columns and avoid wide or variable-length types as primary index components. Order composite indexes to match the most selective WHERE predicates and the ORDER BY used by hot queries; the left-most prefix must align with query filters to be useful. Example: CREATE INDEX ON orders (user_id, created_at DESC);
Make common point-reads covering by including frequently selected columns in the index so the engine can return rows without a heap lookup (e.g., PostgreSQL INCLUDE or similar vendor-specific features). Use partial (filtered) indexes for skewed predicates to reduce size: CREATE INDEX ON orders (user_id) WHERE status = 'active';. Expression indexes let you index transformed values (e.g., lower(email)) so queries that apply the same function can use the index.
Tune queries to match indexes: avoid SELECT *, don’t wrap indexed columns in functions in WHERE clauses, and prefer equality predicates on indexed fields. Replace OFFSET pagination with keyset pagination to avoid large scans. Ensure foreign key and join columns are indexed to prevent nested-loop or hash spill costs. Use EXPLAIN ANALYZE and query execution plans to confirm index usage and to spot sequential scans or expensive sorts.
Keep statistics current (ANALYZE), monitor index usage and bloat, drop unused indexes, and weigh latency gains against write amplification. Validate index changes in staging with representative traffic and measure p50/p95 latency and write throughput before rolling to production.
Partitioning and sharding strategies
When a table outgrows single-node performance, split data so queries hit smaller, cache-friendly units. Use range (commonly time), hash, or list splits depending on access patterns: time ranges for TTL/archival windows and large scans, hash for even distribution and write-scale, list for small, well-known categories. Choose the partition/shard key to align with hot queries—filters, join keys, and ordering—so partition pruning and index usage are effective.
Avoid monotonic keys (e.g., pure increasing IDs) as shard keys because they create write hotspots; add a hash prefix or use a compound key (shard_id + id) to distribute load. Co-locate related rows by including the shard key in primary/foreign keys when cross-partition joins must be cheap; if cross-shard joins are frequent, consider denormalization or materialized summaries instead of forcing distributed transactions.
Remember index trade-offs: local per-partition indexes are fast to rebuild and scale with data, while global indexes simplify queries but complicate resharding and increase coordination. Design maintenance workflows around partitions: drop/truncate old ranges to reclaim space, run vacuum/reindex per-partition, and rely on partition pruning to reduce query IO.
Plan resharding and rebalancing before you need it. Techniques include consistent hashing with virtual nodes for smoother moves, application-side routing plus dual-writes during cutover, or CDC-driven backfills to migrate data with minimal downtime. For multi-tenant systems, isolate very large tenants into their own database instance; for smaller tenants, partition by tenant_id to limit blast radius.
Instrument for hotspots, cross-shard latency, and skew. Prefer simple schemes that match your workload and operational model: time-based for time-series, hash for write-scale, and co-location when transactional consistency and low-latency joins matter. Test rebalancing and backfill on staging with representative traffic before production rollouts.
Schema migrations and versioning
Treat schema changes as code: keep every migration script in version control, assign immutable IDs, and make migrations idempotent and reversible where possible. Deploy schema changes separately from application logic when they are destructive; prefer small, incremental migrations that are easy to test and roll back. Use a migration tool to enforce order and record applied versions in the database.
Always design changes to be backward-compatible for at least one deploy cycle. Add columns as nullable (or with a safe default), deploy the app to start writing or reading the new column, run a controlled backfill, then alter to NOT NULL and remove old fields. Create indexes with non-blocking or concurrent options if your engine supports them; avoid table rewrites on large tables during peak hours.
For large or long-running data transformations, prefer background jobs or CDC-driven backfills over single-statement mass updates. Process rows in bounded batches, use idempotent upserts, and monitor progress with checkpoints to allow safe resume. When needing to migrate large tables, consider shadow tables and dual-writes: write to the new schema alongside the old, keep the two in sync, switch reads after validation, then decommission the old schema.
Coordinate migrations with application releases using feature flags to toggle new behavior and reduce blast radius. Maintain a rollback plan and a tested runbook covering how to abort, revert, or continue a failed migration. Detect schema drift by comparing migration state across environments and fail CI when pending migrations exist. Instrument migrations: emit timing, row counts, lock waits, and error metrics so you can abort or throttle if production impact appears.
Test migrations against production-like data in staging, rehearse backfills and resharding, and treat migration maintenance as a regular operational task—small, reversible, observable changes minimize downtime and data risk.
