When One Database Server Is No Longer Enough
A single PostgreSQL server handles more than most applications will ever need: tens of thousands of transactions per second, terabytes of data, hundreds of concurrent connections. But when you reach the limits, the scaling strategies fundamentally change your schema design choices. Replication changes how you think about read consistency. Sharding changes how you think about primary keys and joins. Multi-tenancy changes how you think about every table in your system.
Replication: Reads on One Server, Writes on Another
Read replicas are the first and most effective scaling strategy for most web applications. The primary server handles all writes. One or more replica servers receive a continuous stream of changes and serve read queries. For applications that are 90% reads, this multiplies read capacity by the number of replicas while keeping write capacity unchanged.
The schema design implication is replication lag. A user writes a comment, then immediately fetches their comment list from a replica. The replica has not received the write yet. The comment is missing. Solutions include read-your-own-writes routing (send the original user's next read to the primary for a short window), synchronous replication (replicas confirm before the write succeeds, adding latency but eliminating lag), and explicit version tracking (the client sends a version token and the proxy waits for the replica to catch up).
Sharding: Splitting Data Horizontally
Sharding distributes rows across multiple database servers based on a shard key. All data for user #12345 lives on shard 3. All data for user #67890 lives on shard 7. Reads and writes for a given user hit only one server. Write throughput scales linearly with shard count.
The cost is architectural complexity that permeates every schema decision. JOIN queries across shards become application-level joins over multiple network requests. UNIQUE constraints only enforce uniqueness within a shard, not globally. Auto-increment IDs collide across shards. Cross-shard transactions require distributed transaction protocols with significantly higher failure rates. Schema migrations must run on every shard simultaneously or tolerate a transition period with schema version differences.
Shard key selection is the most consequential sharding decision. A poor shard key creates hot shards where 80% of traffic lands on one server. user_id is usually a good shard key because writes are distributed randomly across users. created_at is a terrible shard key because all new data lands on the most recent shard while historical shards sit idle.
Multi-Tenancy Patterns
SaaS applications serve many customers from shared infrastructure. Three patterns define how data isolation is implemented. Shared schema: all tenants' data in the same tables, separated by a tenant_id column. Every query filters by tenant_id. Row-Level Security policies automate this filtering in the database layer. Cheapest to operate, highest noisy-neighbor risk.
Separate schemas: each tenant gets their own set of tables within the same database server. Better query isolation and simpler tenant-specific customization. Schema migrations must run once per tenant, making deployment more complex as tenant count grows. Separate databases: maximum isolation, simplest per-tenant security model, highest operational cost. Appropriate when tenants are large enterprises with dedicated compliance requirements.
Multi-tenancy decisions are hard to reverse. Start with shared schema for small tenant counts. Migration to separate schemas or databases at scale is possible but painful. Choose based on the isolation requirements of your most demanding customers, not your median customer.
Replication before sharding. Replicas solve 80% of scaling problems with 20% of the complexity.
Shard key selection: optimize for uniform write distribution. Reject any key that creates temporal hot spots.
Multi-tenant shared schemas require tenant_id on every table, a partial index on tenant_id for performance, and RLS policies to prevent data leaks.
Cross-shard queries are the sharding tax. Design your schema to co-locate data that gets queried together on the same shard.
The Replication Lag Problem in Production
Replication lag is the most common scaling problem after adding read replicas. The typical scenario: a user updates their profile photo, the API returns 200, the user's next page load comes from a replica that has not yet received the change. The old photo appears. The user assumes their update failed and tries again. Now there are two update requests in flight. Read-your-own-writes routing solves this for the user who made the change: route their next few requests to the primary for a configurable window (typically 30-60 seconds). Other users still might see stale data, which is acceptable for eventually consistent information like profile photos.
Replication lag becomes critical in financial systems. After a debit, reading a stale balance from a replica that has not received the debit leads to incorrect available balance calculations. Solutions: always read balance data from the primary (accept higher primary load for critical reads), use synchronous replication for financial tables (replicas confirm before write returns, adds latency), or use optimistic locking with version numbers (reject requests that read a stale version).
Consistent Hashing for Shard Distribution
Naive modular sharding (shard = user_id % num_shards) creates a massive data migration problem when you add a shard. Adding a 5th shard to a 4-shard system means nearly every row needs to move. Consistent hashing solves this by mapping both keys and shards to positions on a circular hash ring. Adding a shard only moves the keys that fall between the new shard and its predecessor on the ring, typically 1/N of the data for N shards.
Consistent hashing is how Cassandra, DynamoDB, and most horizontally-scaled databases handle shard distribution. For PostgreSQL-based sharding (using Citus, for example), consistent hashing is handled by the sharding middleware rather than the application. The application-visible change is the shard key column, which must appear in every query that the middleware uses to route to the correct shard.
Multi-Tenancy Security Checklist
Every table in a shared-schema multi-tenant system must have a tenant_id column. No exceptions. Missing tenant_id on even one table creates a potential cross-tenant data leak.
Row-Level Security policies in PostgreSQL can enforce tenant isolation automatically. Set a session variable for the current tenant and create policies that filter all tables by that variable.
Partial indexes on tenant_id improve query performance when combined with other filters. The index (tenant_id, created_at) serves the typical 'get this tenant's recent records' query pattern efficiently.
Test tenant isolation with adversarial test cases: attempt to read another tenant's data via direct SQL, attempt to read another tenant's data via API, attempt to join across tenant boundaries in complex queries.
Schema migrations in multi-tenant systems: test on a single tenant schema first, then apply to all tenant schemas in batches. Never apply a migration to all tenants simultaneously without testing.
The Replication Lag Problem in Production
Replication lag is the most common scaling problem after adding read replicas. The typical scenario: a user updates their profile photo, the API returns 200, the user's next page load comes from a replica that has not yet received the change. The old photo appears. The user assumes their update failed and tries again. Now there are two update requests in flight. Read-your-own-writes routing solves this for the user who made the change: route their next few requests to the primary for a configurable window (typically 30-60 seconds). Other users still might see stale data, which is acceptable for eventually consistent information like profile photos.
Replication lag becomes critical in financial systems. After a debit, reading a stale balance from a replica that has not received the debit leads to incorrect available balance calculations. Solutions: always read balance data from the primary (accept higher primary load for critical reads), use synchronous replication for financial tables (replicas confirm before write returns, adds latency), or use optimistic locking with version numbers (reject requests that read a stale version).
Consistent Hashing for Shard Distribution
Naive modular sharding (shard = user_id % num_shards) creates a massive data migration problem when you add a shard. Adding a 5th shard to a 4-shard system means nearly every row needs to move. Consistent hashing solves this by mapping both keys and shards to positions on a circular hash ring. Adding a shard only moves the keys that fall between the new shard and its predecessor on the ring, typically 1/N of the data for N shards.
Consistent hashing is how Cassandra, DynamoDB, and most horizontally-scaled databases handle shard distribution. For PostgreSQL-based sharding (using Citus, for example), consistent hashing is handled by the sharding middleware rather than the application. The application-visible change is the shard key column, which must appear in every query that the middleware uses to route to the correct shard.
Multi-Tenancy Security Checklist
Every table in a shared-schema multi-tenant system must have a tenant_id column. No exceptions. Missing tenant_id on even one table creates a potential cross-tenant data leak.
Row-Level Security policies in PostgreSQL can enforce tenant isolation automatically. Set a session variable for the current tenant and create policies that filter all tables by that variable.
Partial indexes on tenant_id improve query performance when combined with other filters. The index (tenant_id, created_at) serves the typical 'get this tenant's recent records' query pattern efficiently.
Test tenant isolation with adversarial test cases: attempt to read another tenant's data via direct SQL, attempt to read another tenant's data via API, attempt to join across tenant boundaries in complex queries.
Schema migrations in multi-tenant systems: test on a single tenant schema first, then apply to all tenant schemas in batches. Never apply a migration to all tenants simultaneously without testing.
The Replication Lag Problem in Production
Replication lag is the most common scaling problem after adding read replicas. The typical scenario: a user updates their profile photo, the API returns 200, the user's next page load comes from a replica that has not yet received the change. The old photo appears. The user assumes their update failed and tries again. Now there are two update requests in flight. Read-your-own-writes routing solves this for the user who made the change: route their next few requests to the primary for a configurable window (typically 30-60 seconds). Other users still might see stale data, which is acceptable for eventually consistent information like profile photos.
Replication lag becomes critical in financial systems. After a debit, reading a stale balance from a replica that has not received the debit leads to incorrect available balance calculations. Solutions: always read balance data from the primary (accept higher primary load for critical reads), use synchronous replication for financial tables (replicas confirm before write returns, adds latency), or use optimistic locking with version numbers (reject requests that read a stale version).
Consistent Hashing for Shard Distribution
Naive modular sharding (shard = user_id % num_shards) creates a massive data migration problem when you add a shard. Adding a 5th shard to a 4-shard system means nearly every row needs to move. Consistent hashing solves this by mapping both keys and shards to positions on a circular hash ring. Adding a shard only moves the keys that fall between the new shard and its predecessor on the ring, typically 1/N of the data for N shards.
Consistent hashing is how Cassandra, DynamoDB, and most horizontally-scaled databases handle shard distribution. For PostgreSQL-based sharding (using Citus, for example), consistent hashing is handled by the sharding middleware rather than the application. The application-visible change is the shard key column, which must appear in every query that the middleware uses to route to the correct shard.
Multi-Tenancy Security Checklist
- Every table in a shared-schema multi-tenant system must have a tenant_id column. No exceptions. Missing tenant_id on even one table creates a potential cross-tenant data leak.
- Row-Level Security policies in PostgreSQL can enforce tenant isolation automatically. Set a session variable for the current tenant and create policies that filter all tables by that variable.
- Partial indexes on tenant_id improve query performance when combined with other filters. The index (tenant_id, created_at) serves the typical 'get this tenant's recent records' query pattern efficiently.
- Test tenant isolation with adversarial test cases: attempt to read another tenant's data via direct SQL, attempt to read another tenant's data via API, attempt to join across tenant boundaries in complex queries.
- Schema migrations in multi-tenant systems: test on a single tenant schema first, then apply to all tenant schemas in batches. Never apply a migration to all tenants simultaneously without testing.