From normalization to sharding, the patterns that matter in production and the mistakes that cost teams millions.
Get new posts delivered to your inbox. No spam, unsubscribe anytime.
PostgreSQL is the most popular database among professional developers in 2025, used by 55.6% according to the Stack Overflow Developer Survey. The database management market is projected to reach $406 billion by 2034, growing at 13.29% annually. Yet despite all this investment, most production database problems I've encountered come down to the same handful of design mistakes made in week one of a project.
The patterns in this article aren't new. Some date back to the 1970s. But knowing which one to apply, when to avoid it, and what happens when you get it wrong separates the engineers who build systems that last from those who rebuild them every eighteen months.
In 1970, Edgar F. Codd, a British computer scientist at IBM, published "A Relational Model of Data for Large Shared Data Banks", the paper that started everything. Before Codd, databases used hierarchical and network models. IBM's Information Management System (IMS) forced you to think about data as trees. If your query didn't follow the tree structure, tough luck.
Codd's insight was deceptively simple: store data in tables with rows and columns, and let the query language figure out how to retrieve it. He introduced normalization, first normal form (1NF) in 1970, followed by second and third normal forms in 1971. Raymond Boyce and Codd defined Boyce-Codd Normal Form (BCNF) in 1974.
These ideas took years to gain traction. IBM itself resisted them because IMS was a cash cow. It wasn't until Larry Ellison read Codd's papers and built Oracle that relational databases became commercial products. The irony: the company that employed Codd was the last major vendor to ship a relational database.
Fifty-plus years later, the core patterns that Codd's work enabled are still the foundation of most production systems. But we've also learned where they break.
Normalization eliminates data redundancy. You've heard the rules: every non-key attribute depends on the key, the whole key, and nothing but the key. In practice, this means splitting data across multiple tables connected by foreign keys.
For a 5-person startup building a CRUD app, third normal form (3NF) is almost always the right starting point. Your data is consistent, your storage is efficient, and your JOINs are fast because your tables are small.
The trouble starts at scale. When your product catalog hits millions of rows and your homepage needs to display product name, price, category name, and seller rating in a single card, that's four JOINs. Multiply by thousands of concurrent requests. The database spends more time joining than it does reading.
Netflix and Uber engineers share a consistent pattern in conference talks: normalize early for correctness, denormalize later for performance once access patterns are clear.
The mistake I see most often is denormalizing too early. A team profiles their queries at 100 users and decides to flatten everything into a single table. Six months later, they have data inconsistencies everywhere because updating a category name means touching 50,000 product rows. The original JOINs would have been fine at their actual scale.
The rule of thumb: normalize first. Denormalize only when you have real query performance data showing JOINs are the bottleneck, not CPU, not network, not application code. And when you do denormalize, document which fields are redundant copies so future developers don't try to use them as sources of truth.
Soft delete means adding a deleted_at or is_deleted column instead of actually removing rows. The appeal is obvious: accidental deletions become reversible. Compliance teams can audit what was removed. Nobody has to sweat through a DELETE that might cascade through half the database.
I used soft deletes everywhere for years. I was wrong.
Brandur Leach, a Stripe engineer, put it directly: soft deletion adds complexity to every query, breaks unique constraints, and gives you the illusion of data safety while creating real problems.
Consider this: a user deletes their account. Later, they want to sign up again with the same email. Your UNIQUE constraint on email blocks them because the "deleted" row still exists. Now you need a partial unique index that excludes deleted rows. Every query in your application needs a WHERE deleted_at IS NULL filter. Miss one, and you're showing deleted data to users.
-- The partial index workaround
CREATE UNIQUE INDEX idx_users_email_active
ON users (email)
WHERE deleted_at IS NULL;
-- Every single query needs this
SELECT * FROM orders
WHERE user_id = 123
AND deleted_at IS NULL;
Foreign key constraints stop working as intended. The whole point of a foreign key is referential integrity: if an order references a user, that user exists. With soft delete, the user "exists" but is logically gone. Your application layer has to enforce what the database used to guarantee.
The GDPR angle makes it worse. "Right to be forgotten" requires actual erasure. Soft-deleted data is still recoverable, which arguably violates the regulation. So you end up needing a hard delete process anyway.
When soft delete actually makes sense: financial records that require audit trails (use an audit log table instead), or systems where deletion is a business process with an "undo" period (think Gmail's trash). For everything else, consider an event log or archive table rather than marking rows as deleted in place.
| Approach | Pros | Cons | Best For |
|---|---|---|---|
| Soft Delete | Reversible, simple to implement | Breaks constraints, bloats tables, GDPR risk | Short undo windows |
| Archive Table | Clean main table, full history | Extra write on delete, more complex queries for history | Compliance/audit requirements |
| Event Log | Complete history, immutable | Storage cost, replay complexity | Financial systems, event-sourced architectures |
| Hard Delete | Clean, simple, constraint-friendly | Irreversible | Most CRUD applications |
Command Query Responsibility Segregation (CQRS) separates read operations from write operations into different models. Greg Young coined the term, drawing on Bertrand Meyer's Command-Query Separation principle. Young's original motivation was algorithmic trading: he needed deterministic systems with provable audit logs, common requirements in regulated financial industries.
The idea is sound. Reads and writes have different performance characteristics. Your write model can be fully normalized and optimized for consistency. Your read model can be denormalized, pre-computed, cached. Each side scales independently.
Martin Fowler offered a blunt warning on his widely-read blog post: "For some situations, this separation can be valuable, but beware that for most systems CQRS adds risky complexity." He continued: "I've certainly seen cases where it's made a significant drag on productivity, adding an unwarranted amount of risk to the project, even in the hands of a capable team."
One project Fowler described was especially telling. The project manager, someone he described as capable and experienced, told him that event sourcing paired with CQRS "had been a disaster." Every change required twice the work because both the read and write models needed updating.
That matches what I've seen. Teams adopt CQRS because they read about it in a conference talk, then discover their application is a straightforward CRUD app that reads and writes to the same tables. Adding separate read and write models doubles the surface area for bugs with no performance benefit.
Where CQRS pays off: systems with dramatically different read and write loads. A fintech platform processing thousands of transactions per second but serving analytics dashboards to hundreds of users benefits from separate models. A real-world fintech case study showed a team using CQRS selectively; they applied it only to services where it justified the complexity, not across every microservice.
Where CQRS fails: when the read/write ratio is roughly even, when your team is small, or when you're still figuring out your domain model. For a startup with five engineers, a single PostgreSQL database with read replicas will outperform a CQRS architecture that those same engineers must build, debug, and maintain.
| Team Size | Read/Write Ratio | Recommendation |
|---|---|---|
| Under 10 engineers | Balanced | Single database, read replicas |
| 10-50 engineers | Read-heavy (10:1+) | Consider CQRS for specific services |
| 50+ engineers | Highly asymmetric | CQRS with dedicated teams per model |
When microservices became the industry default, the advice was clear: each service owns its data. No shared databases. This ensures loose coupling. Services communicate through APIs, not through direct table access.
The theory is clean. The reality is painful.
Walmart's engineering team described their approach using the Saga pattern to coordinate transactions across services. It works, but it's not simple. A saga that spans five services requires compensating transactions for each step, timeout handling for every call, and monitoring for partial completion states. Debugging a failed saga means tracing events across five separate databases.
The operational overhead is real. One database needs one backup system, one monitoring setup, one upgrade process. Five services with five databases need five of everything. AWS's own documentation acknowledges that implementing business transactions spanning multiple services "is not straightforward."
For transaction-heavy systems like banking applications, multiple databases make the problem harder, not easier. A single RDBMS with proper transaction isolation often handles these use cases better than a distributed system with eventual consistency.
The pragmatic approach: don't split your database too early. Start with a shared database and logical schema boundaries. When a specific service genuinely needs independence (different scaling requirements, different data models, different teams), extract its data. The database-per-service pattern works best when it evolves from actual pain points, not from architectural diagrams drawn before writing any code.
Sharding splits a database horizontally across multiple servers. Each shard holds a subset of the data, determined by a shard key. If your users table has 100 million rows, you might shard by user ID across ten servers, each holding 10 million rows.
Shopify runs one of the largest sharded MySQL deployments in existence. On Black Friday 2024, they processed 173 billion requests with peaks of 284 million requests per minute. They built custom tooling (Ghostferry) to move data between shards, and they organized their infrastructure into "pods," isolated slices with dedicated MySQL, Redis, and Memcached instances.
But even Shopify is evolving away from heavy sharding. As their workloads shifted toward AI and agentic commerce, they found they needed a less-sharded infrastructure that could present a full view of merchant data across platforms. They chose YugabyteDB for its distributed SQL capabilities.
Then there's the cautionary tale. A SaaS project management platform with 2.4 million users spent 18 months and $2.1 million implementing sharding. The result? A system that performed worse than before. Monthly rebalancing consumed 40 engineer-hours and frequently broke production. The CEO ordered a rollback; the CTO estimated another 12 months and $800K to undo it.
The problem was their shard key. They chose company_id, but enterprise customers had 10,000+ users while small customers had 1-5. One shard grew to 2.8 million records while another held 156,000. The hot shard became the bottleneck, defeating the entire purpose.
-- Bad: Low-cardinality shard key with skewed distribution
-- company_id creates hot spots with enterprise tenants
SHARD BY company_id;
-- Better: Hash-based sharding on high-cardinality key
-- Distributes more evenly but makes range queries harder
SHARD BY HASH(user_id) % num_shards;
-- Also consider: Composite shard keys
-- Combines distribution with locality
SHARD BY (region, HASH(user_id));
Poorly chosen shard keys account for over 60% of re-sharding projects, leading to an average 4-month delay in scaling initiatives.
Decision framework for sharding:
Every system that processes payments, sends emails, or triggers external side effects needs idempotency. The pattern is simple in concept: assign a unique key to each operation, store the result, and return the cached result for duplicate requests.
Stripe's implementation is the industry reference. They described the pattern publicly: when a client makes a request, it includes a unique idempotency key. The server stores the result associated with that key. If the same key appears again (because of a network retry, a timeout, a client crash), the server returns the stored result without re-executing the operation.
# Pseudocode for idempotency key handling
def process_payment(request):
key = request.headers.get('Idempotency-Key')
# Check if we've seen this request before
cached = redis.get(f"idempotency:{key}")
if cached:
return json.loads(cached)
# Process the payment
result = payment_gateway.charge(request.amount, request.card)
# Store the result for 24 hours
redis.setex(f"idempotency:{key}", 86400, json.dumps(result))
return result
The nuance most implementations miss: idempotency keys should store failures too. Stripe caches the result "regardless of whether it succeeds or fails." If a request fails with a 500 error and the client retries with the same key, Stripe returns the same 500. This prevents a scenario where a transient failure on the first attempt succeeds on retry, but the client thinks it failed and retries again, creating a duplicate.
-- Database-level idempotency tracking
CREATE TABLE idempotency_keys (
key VARCHAR(255) PRIMARY KEY,
status_code INTEGER NOT NULL,
response_body JSONB NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
-- Auto-expire after 24 hours
expires_at TIMESTAMP DEFAULT NOW() + INTERVAL '24 hours'
);
-- Use in a transaction with your main operation
BEGIN;
INSERT INTO idempotency_keys (key, status_code, response_body)
VALUES ('pay_abc123', 200, '{"id": "ch_xyz"}')
ON CONFLICT (key) DO NOTHING;
-- Only proceed if the insert succeeded (new key)
-- Otherwise return the existing response
COMMIT;
This pattern is especially important in distributed systems where network partitions make "exactly once" delivery impossible. Idempotency gives you "at least once" delivery with "effectively once" semantics.
When your application needs to update a database AND send a message to a queue, you face a fundamental problem: those are two separate systems. If the database write succeeds but the message fails, your systems are inconsistent. If you send the message first and the database write fails, you've told downstream services something that didn't happen.
The Outbox pattern solves this by writing both the data change and the message to the same database in a single transaction.
BEGIN;
-- Update the order status
UPDATE orders SET status = 'shipped' WHERE id = 42;
-- Write the event to the outbox table in the same transaction
INSERT INTO outbox (
aggregate_type, aggregate_id, event_type, payload
) VALUES (
'Order', 42, 'OrderShipped',
'{"order_id": 42, "shipped_at": "2026-04-08T10:00:00Z"}'
);
COMMIT;
A separate process (a poller or a change data capture tool like Debezium) reads the outbox table and publishes messages to the actual message broker. If the publisher crashes, it picks up where it left off because the messages are still in the database.
This pattern emerged from real production pain. GitLab's January 2017 database outage demonstrated what happens when database operations and external systems get out of sync. An engineer ran a deletion command on the primary database instead of the secondary. The secondary couldn't sync fast enough, and their backup system had been broken for weeks. They lost 300GB of production data: roughly 5,000 projects, 5,000 comments, and 700 user accounts. Recovery took 18 hours using a 6-hour-old LVM snapshot.
The Outbox pattern won't prevent accidental deletions. But it addresses the broader class of problems where database state and external system state diverge.
There's a strong argument that most of these patterns are over-engineering for most applications. The database market is projected to hit $329 billion by 2031. A lot of that money goes toward solving problems that simpler architectures wouldn't have.
A 2024 retrospective from a startup that raised $2.5 million specifically to build a database gateway product ended in failure. They built the technically correct solution, but the market didn't need it. Most teams wanted simpler tools, not more infrastructure.
Not every application needs CQRS. Not every 50-person company needs sharding. Not every table needs soft delete. The most effective database design pattern I've seen is also the most boring: a well-normalized PostgreSQL database with proper indexes, running on a machine with enough RAM to keep the working set in memory.
Here's what that looks like for different scales:
| Scale | Users | Data Size | Recommended Architecture |
|---|---|---|---|
| Side project | Under 1K | Under 1GB | Single PostgreSQL instance, managed hosting |
| Early startup | 1K-100K | 1-50GB | PostgreSQL with read replica, connection pooling |
| Growth stage | 100K-1M | 50-500GB | PostgreSQL with partitioning, caching layer, CDN |
| Scale-up | 1M-10M | 500GB-5TB | Consider sharding or distributed SQL (CockroachDB, YugabyteDB) |
| Enterprise | 10M+ | 5TB+ | Multi-region, sharded, CQRS where justified |
Instead of pattern-shopping, start from your constraints:
Step 1: Measure before optimizing. Run EXPLAIN ANALYZE on your slow queries. Most "database problems" are missing indexes, N+1 queries in the application layer, or network latency to the database. None of these require architectural patterns.
-- Before adding complexity, check the basics
EXPLAIN ANALYZE
SELECT o.id, o.total, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;
-- Missing index? That's a 5-second fix, not a new pattern
CREATE INDEX idx_orders_created_at ON orders (created_at DESC);
Step 2: Exhaust simple solutions. Read replicas handle most read scaling needs. Connection pooling (PgBouncer, PgCat) handles connection exhaustion. Materialized views handle complex aggregation queries. Partitioning handles large tables.
Step 3: Apply patterns surgically. When you do need CQRS, apply it to one service, not your entire architecture. When you need sharding, shard one table, not every table. The pattern should be smaller than the problem.
Step 4: Plan your exit. Every pattern you adopt is a pattern you'll eventually need to change. Document why you chose it, what assumptions it rests on, and what signals would indicate it's time to move on.
The database market is growing at 13% annually. Cloud database revenue alone will hit $120 billion by 2034. New tools launch weekly. The pressure to adopt the latest pattern is constant.
Resist it.
The most damage I've seen in production databases came from premature complexity, not from starting too simple. That $2.1 million sharding project failed because the team sharded before they understood their data distribution. The CQRS implementations Fowler described failed because teams added architectural complexity before they had architectural problems.
Edgar Codd gave us normalization in 1970. Over fifty years later, a properly normalized PostgreSQL database, the one that 55.6% of developers already use, handles more workloads than most engineers realize. The patterns in this article are powerful. They're also dangerous in the wrong context.
Start with the boring solution. Add complexity only when you have evidence, not theory, that the boring solution isn't enough. And when you do add a pattern, add the smallest version of it that solves your actual problem.
The best database design pattern is the one you didn't need to apply.