Database Design — Advanced
Partitioning strategy for large tables
Once a table grows large enough that queries and maintenance operations (vacuum, index rebuilds) become slow even with good indexing, partitioning splits it into smaller physical pieces while presenting a single logical table to queries. Range partitioning (by date, most commonly — a sales table partitioned by month) is the most common real pattern, since it aligns naturally with how time-series data is usually queried (recent data far more often than old data) and archived (dropping an old partition is far cheaper than a DELETE scanning and removing matching rows individually). List partitioning (by a discrete category — region, tenant ID) and hash partitioning (for even distribution when there's no natural range/list key) cover other access patterns. The design tradeoff: partitioning adds real operational complexity (partition maintenance, ensuring queries actually use partition pruning rather than scanning every partition) in exchange for query and maintenance performance at scale — not something to reach for before you actually have the scale that justifies it.
Sharding — partitioning across separate database instances, not just within one
Where partitioning (above) splits a table within a single database instance, sharding splits data across multiple, separate database instances/servers entirely — necessary once a single instance's write throughput or storage genuinely can't scale further vertically. This introduces a hard, unavoidable design decision: the shard key (what determines which shard a given row lives on) needs to be chosen carefully, since queries that need to join or aggregate across shards become significantly more expensive (cross-shard joins generally aren't efficient, often requiring application-level aggregation instead of a single database query). A poorly chosen shard key can also produce "hot shards" — uneven load distribution where one shard receives disproportionate traffic while others sit comparatively idle.
Multi-tenant schema design: shared table vs. schema-per-tenant vs. database-per-tenant
A recurring advanced design decision for SaaS-style applications: how to isolate different customers' (tenants') data. Shared tables with a tenant_id column (most common) — simplest to operate and scale, but requires disciplined, consistent enforcement of tenant isolation in every single query (a missing WHERE tenant_id = ? is a serious cross-tenant data leak, not a minor bug) — row-level security policies at the database level, rather than relying solely on application-code discipline, are a meaningfully stronger real safeguard here. Schema-per-tenant — stronger isolation, harder to manage at scale (hundreds/thousands of schemas to migrate consistently). Database-per-tenant — strongest isolation and easiest per-tenant scaling/backup, but the most operational overhead and typically only justified for enterprise tenants with genuine isolation or compliance requirements, not as a default for every tenant.
Read replicas and the write/read scaling split
At advanced scale, a single primary database instance handling all reads and writes becomes a bottleneck even with good indexing and partitioning. Read replicas (asynchronously replicated copies of the primary) let read-heavy workloads scale horizontally by distributing reads across replicas, while writes still go to the primary. The design implication that trips people up in practice: replicas are asynchronously updated, meaning there's real (if usually small) replication lag — a write followed immediately by a read from a replica can return stale data. Designing around this correctly means being explicit about which reads can tolerate that lag (most analytics/reporting queries can) versus which reads genuinely need the just-written data (a user immediately viewing their own just-submitted update, which may need to read from the primary specifically, or use a "read your own writes" consistency pattern).
Database design for event sourcing / append-only models
An alternative to traditional "store current state, overwrite on update" design: store every state change as an immutable, append-only event, and derive current state by replaying/aggregating events (or maintaining a separately-updated materialized view, connecting directly to CQRS's read-side pattern from the Cloud Architecture Patterns technology on this site). This trades simplicity (traditional update-in-place is much simpler to reason about) for a genuine audit trail (every historical state is preserved, nothing is ever destructively overwritten) and the ability to rebuild derived views from scratch by replaying history — valuable specifically when audit requirements or the need to add new derived views later outweigh the added design and storage complexity.