Here is the uncomfortable truth most developers learn the hard way: storage decisions are the hardest thing to change in any system. You can refactor business logic in a sprint. You can swap a frontend framework in a week. But migrating 500 million rows from a relational database to a document store while keeping production alive? That is a multi-month ordeal that scares entire engineering teams.
Yet most storage decisions are still made casually - often in the first hour of a project, by whoever opens their laptop first and types CREATE TABLE or mongoose.connect(...).
This article changes that. We will walk through a structured, proven mental model for designing storage from scratch - before writing a single line of application code. The goal is not to pick the "best" database. The goal is to ask the right questions in the right order so you end up with a storage architecture that scales with your product, not against it.
Who is this for? Software engineers, tech leads, and architects who want a repeatable decision framework for storage design - applicable to greenfield projects, startup MVPs, and enterprise system rebuilds alike.
1. Why Storage Decisions Outlive Everything Else
Software has layers. The presentation layer is the most flexible - you can redesign a UI with a designer and a weekend. The application layer is moderately flexible - microservices, hexagonal architecture, and dependency injection all exist specifically to make business logic swappable. But the data layer is load-bearing. It holds state. Real state: user accounts, financial transactions, medical records, audit logs.
Changing it requires schema migrations that must run safely on live data, data transformation scripts with zero tolerance for bugs, coordination across every service that touches that data, and rollback plans that are often impossible to execute cleanly.
The cost of a wrong storage decision compounds over time. A startup that chooses MongoDB for a financial ledger because "it's flexible" will spend months retrofitting transactional guarantees that PostgreSQL offers for free. An enterprise that stores all events in a relational database will hit I/O walls the moment event volume spikes.
The engineers who avoid these traps share one habit: they treat storage design as a first-class design discipline, not an afterthought that happens during sprint one.
2. The Five Questions You Must Answer Before Choosing Anything
Before opening a database comparison chart, sit down and answer these five questions with your team. Write the answers down - they will drive every decision that follows.
What is the shape of your data? Is it tabular with a fixed, well-known schema? Hierarchical and document-like with optional fields? A graph of interconnected entities? Time-stamped measurements? Blobs like images, videos, and PDFs? Different shapes have different optimal engines, and forcing the wrong shape into the wrong engine is where most storage pain originates.
How will the data be accessed? List every query your system will run - not just the ones you know about on day one, but the ones your product roadmap implies. Will you look up individual records by primary key? Scan ranges? Full-text search? Aggregate across millions of rows? Traverse relationships? The access pattern list is more important than the data model.
What are your consistency requirements? If two users simultaneously update the same record, what should happen? If a payment is debited but the confirmation email fails, should the debit roll back? If you write data in one region, how quickly must it be visible in another? Different parts of the same system often have different answers to these questions.
What scale are you designing for? Not aspirationally - realistically. How many records in year one? Year three? What is the peak write rate, the peak read rate, the data retention requirement? Scale estimates let you calculate storage costs, identify partition boundaries, and decide whether you need a distributed system at all.
What are the operational constraints? What is your team's operational expertise? Who will manage the database in production? What is your disaster recovery SLA? What is your budget for managed services versus self-hosted? A technically superior storage engine that nobody on the team knows how to operate under pressure is not a good choice.
3. Data Shape: Structured, Semi-Structured, or Unstructured?
The first axis of storage design is data shape. This is the most intuitive factor, yet it is routinely ignored in favor of familiarity.
Structured data
Fixed schema, tabular, well-defined relationships between entities. Classic examples: users, orders, products, invoices, subscriptions.
CREATE TABLE users (
id UUID NOT NULL PRIMARY KEY,
email VARCHAR(254) NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
plan_id INT REFERENCES plans(id)
);
CREATE TABLE orders (
id UUID NOT NULL PRIMARY KEY,
user_id UUID NOT NULL REFERENCES users(id),
total_cents INT NOT NULL,
status order_status NOT NULL DEFAULT 'pending'
);
This is the natural home of relational databases like PostgreSQL, MySQL, and SQL Server. The schema enforces integrity. Joins are efficient. Transactions are first class.
Semi-structured data
Variable schema, optional fields, nested hierarchies. A product catalog is a classic example - a running shoe has sizes, drop_mm, and lug_depth_mm, while an electronics product in the same catalog has voltage, wattage, and wifi_standard. A fixed relational schema would require either a massive nullable-column table or an EAV anti-pattern. A document store handles this naturally.
{
"id": "prod_7x9k2",
"name": "Trail Running Shoe",
"category": "footwear",
"attributes": {
"sizes": [7, 8, 9, 10, 11, 12],
"waterproof": true,
"drop_mm": 6,
"lug_depth_mm": 4.5
}
}
MongoDB, CouchDB, and Firestore are the natural fit here. PostgreSQL's JSONB column type is excellent for hybrid cases where most of your data is structured but a few entities need flexibility.
Unstructured data
Raw bytes - images, videos, audio, documents, backups. These should almost never live in your application database. Your database stores only a pointer (URL or storage key). The blob store like AWS S3, Azure Blob Storage, or Google Cloud Storage handles redundancy, versioning, and CDN delivery.
Time-series data
Timestamped measurements at high ingestion rates - metrics, sensor readings, financial ticks, application logs. Relational databases handle time-series poorly at scale because they are not optimized for append-heavy workloads with time-range queries. TimescaleDB, InfluxDB, ClickHouse, and VictoriaMetrics are purpose-built for this pattern.
4. Access Patterns Are the Real Design Driver
Here is the principle most database textbooks bury in chapter twelve: design your storage around how data is read, not how it is written. Writes are a one-time cost. Reads are a recurring cost that compounds at scale.
Mapping access patterns to storage keys
Before defining any schema, build an access pattern matrix. Write down every operation your system must perform, what it takes as input, what it returns, how frequently it runs, and what latency it must meet.
| # | Operation | Input | Output | Frequency | Latency SLA |
|---|---|---|---|---|---|
| 1 | Get user profile | user_id |
Single user record | Very High | < 5ms |
| 2 | List orders by user |
user_id, date range |
Paginated order list | High | < 20ms |
| 3 | Full-text search products | Search query string | Ranked product list | Medium | < 100ms |
| 4 | Aggregate revenue by month | Date range | Aggregated totals | Low | < 2s |
| 5 | Get recent activity feed | user_id |
Last 50 events | High | < 10ms |
| 6 | Find users by email | Email address | Single user record | High | < 5ms |
Now examine what each row tells you. Operations 1 and 6 are primary key and unique index lookups - any database handles this trivially. Operation 2 needs a composite index on (user_id, created_at) in a relational store, or a DynamoDB partition key of user_id with a sort key of created_at. Operation 3 requires a search engine - Elasticsearch, Typesense, or Postgres full-text search for smaller datasets. Operation 4 is an OLAP query that will crush your OLTP database under load - it belongs in a read replica, a materialized view, or a dedicated analytics engine. Operation 5 is a feed: pre-computed feeds cached in Redis outperform on-demand SQL joins by orders of magnitude.
The access pattern matrix forces you to confront these separations before you have committed to a single schema.
The golden rule of storage design: every non-trivial query that runs more than once per second against more than a million rows deserves its own index, materialized view, cache, or dedicated storage engine.
5. Consistency vs. Availability: Making the CAP Trade-Off Explicit
The CAP theorem states that a distributed system can guarantee at most two of Consistency, Availability, and Partition Tolerance. Since network partitions are a physical reality in any distributed system, the practical choice is always between consistency and availability. This is not a theoretical concern - it is a product decision masquerading as a technical one.
When consistency is non-negotiable
Financial systems, inventory management, booking and reservation systems, and authentication require strong consistency. If two users simultaneously attempt to book the last seat on a flight, the system must guarantee that exactly one succeeds. Eventual consistency is not acceptable here.
-- PostgreSQL SERIALIZABLE isolation - the highest consistency guarantee
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT available_seats FROM flights WHERE id = $1 FOR UPDATE;
UPDATE flights
SET available_seats = available_seats - 1
WHERE id = $1
AND available_seats > 0;
INSERT INTO bookings (flight_id, user_id, seat) VALUES ($1, $2, $3);
COMMIT;
-- If two transactions race, one rolls back automatically. No double-booking possible.
PostgreSQL, CockroachDB, Google Spanner, and SQL Server are the right engines here.
When availability is more important
Social feeds, analytics dashboards, product recommendations, and notification systems can tolerate eventual consistency. A user seeing a like count that is two seconds out of date has no meaningful impact. Blocking a write to maintain perfect consistency in these cases wastes infrastructure and hurts user experience.
Write path: User likes a post
→ Write to primary (DynamoDB / Cassandra)
→ Async fan-out to followers' feed caches
→ Eventual read consistency: ~1–3 seconds
Read path: User reads their feed
→ Read from Redis cache
→ Cache miss: read from replica, populate cache
→ No blocking, no locking, no coordination overhead
DynamoDB, Cassandra, Couchbase, and Redis are well-suited to this tier.
Document the trade-off explicitly
Every team should have a written record of which parts of their system require strong consistency and why. This document prevents future engineers from "optimizing" a financial ledger by switching it to an eventually consistent store without understanding the implications.
6. Read/Write Ratio and the Architecture It Implies
The read-to-write ratio of your system predicts your scaling strategy more accurately than almost any other single metric.
| Ratio | System type | Common examples | Recommended pattern |
|---|---|---|---|
| 100:1+ reads | Read-heavy | News sites, product catalogs | Aggressive caching, CDN, read replicas |
| 10:1 reads | Typical SaaS | CRM, project management | Primary + replica, Redis for hot data |
| 1:1 balanced | Transactional | Banking, bookings, inventory | Optimize for write throughput and consistency |
| 1:10 writes | Write-heavy | IoT telemetry, event sourcing | Append-only stores, time-series DB, Kafka |
| 1:100+ writes | Extreme write | Financial ticks, high-frequency metrics | ClickHouse, InfluxDB, LSM-tree engines |
CQRS: separating the write and read models
For systems with a lopsided read/write ratio, Command Query Responsibility Segregation is not just an academic pattern - it is a practical necessity. The write side maintains the source of truth in a transactional database. The read side maintains purpose-built projections in engines optimized for each specific read use case.
WRITE SIDE (Commands)
┌─────────────────────────────────┐
│ POST /orders │
│ → Validate command │
│ → Write to PostgreSQL │
│ (source of truth) │
│ → Emit OrderCreated event │
└──────────────┬──────────────────┘
│ async event
▼
READ SIDE (Queries)
┌─────────────────────────────────┐
│ OrderCreated consumer │
│ → Denormalize into │
│ Elasticsearch (search) │
│ → Populate Redis (feed cache) │
│ → Update ClickHouse (analytics)│
└─────────────────────────────────┘
GET /orders?user=X → Elasticsearch (not PostgreSQL)
GET /dashboard → ClickHouse (not PostgreSQL)
GET /feed → Redis (not PostgreSQL)
This pattern keeps your transactional database lean and fast while giving each read use case an engine that is built exactly for it.
7. The Storage Taxonomy: Choosing the Right Engine
Relational databases
Use relational databases when your data has a fixed, well-understood schema, when relationships between entities matter, and when transactions across multiple tables are required. PostgreSQL is the right default for most greenfield projects in 2026 - it handles relational, document (JSONB), full-text search, and time-series (via TimescaleDB extension) workloads in a single engine. CockroachDB and Google Spanner are the right choice when you need globally distributed strong consistency.
Avoid relational databases when the schema changes every sprint, when write throughput exceeds roughly 50K rows per second on a single node, or when the data is fundamentally graph-shaped.
Document databases
Use document databases when schema varies across records, when data is naturally hierarchical, and when rapid iteration matters more than rigid integrity. MongoDB is the most full-featured option. PostgreSQL JSONB is the pragmatic hybrid choice - relational and document in one engine - that lets small teams avoid operational overhead without sacrificing flexibility.
Avoid document databases when you need heavy cross-document transactional integrity and when your team conflates "flexible schema" with "no schema." Document stores still require schema discipline - they just enforce it in application code rather than at the database level.
Key-value stores
Use key-value stores for sub-millisecond access to hot data by a known key: caching, session management, rate limiting counters, feature flags, and pub/sub messaging. Redis is the industry standard with its rich data structure support - sorted sets, streams, bitmaps, and hyperloglogs make it far more than a simple cache.
Avoid key-value stores when you need to query by anything other than the primary key or when data requires complex relationships.
Wide-column stores
Use wide-column stores when you need massive write throughput at scale, time-series-like access patterns, or geographically distributed writes. Apache Cassandra and ScyllaDB are the proven options. They are extraordinary at what they do and difficult to operate incorrectly, so ensure your team has Cassandra expertise or budget before choosing this path.
Search engines
Use a dedicated search engine whenever users need to search by arbitrary text, apply facets, or receive ranked results. Typesense is the right choice for most teams building in 2026 - it is simple to operate, fast, and has excellent developer ergonomics. Elasticsearch and OpenSearch offer more power for complex log analytics and enterprise-scale search. Algolia is the fastest path to production if you are on a tight timeline and have budget for a managed SaaS.
Never use a search engine as a primary database. Search indexes are derived stores that must be populated from an authoritative source and can be rebuilt from scratch if corrupted.
Graph databases
Use graph databases when your domain is fundamentally relational in the graph sense - social networks, knowledge graphs, recommendation engines, fraud detection, and dependency resolution. Neo4j and Amazon Neptune are the mature options. For fewer than a few hundred thousand nodes and edges, PostgreSQL recursive CTEs handle graph traversal efficiently without the operational overhead of a separate engine.
Time-series databases
Use time-series databases when data is append-only, timestamped, and queried by time range. TimescaleDB is the gentlest entry point - it is a PostgreSQL extension, so you get time-series performance without learning a new operational stack. ClickHouse is the right choice when you need OLAP analytics on top of the time-series data. InfluxDB and VictoriaMetrics are excellent when you need Prometheus-compatible scraping.
8. Designing for Growth: Partitioning and Sharding from Day One
You do not need to implement sharding on day one. But you need to design as if you will. The partition key you choose at the start determines whether your future horizontal scaling is straightforward or catastrophic.
The partition key is everything
A partition key is the field by which data is distributed across nodes. Choosing it incorrectly causes hotspots - one node drowning in traffic while others sit idle.
BAD partition key: created_at (date)
All writes today go to today's partition.
99% of traffic hits one shard. Classic hotspot.
BAD partition key: status ('pending', 'completed', 'failed')
Only 3 possible values. All completed records on one node.
GOOD partition key: user_id (UUID)
High cardinality. Millions of unique values.
Writes distribute evenly. A user's data hits exactly one shard.
Design your primary key for partition-friendliness
Sequential integer primary keys are a write hotspot in distributed systems - all inserts go to the "latest" page of the B-tree index. UUID v4 solves the hotspot but makes keys non-sortable by time. UUID v7, which is time-ordered and random, gives you the best of both worlds: even distribution across shards and lexicographic sorting by creation time.
-- Anti-pattern: sequential integer - hotspot on the index leaf page
CREATE TABLE events (
id SERIAL PRIMARY KEY,
user_id UUID NOT NULL,
payload JSONB
);
-- Better: UUID v4 - random distribution, no hotspot
CREATE TABLE events (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id UUID NOT NULL,
payload JSONB
);
-- Best for distributed systems: UUIDv7 - time-ordered AND hotspot-free
CREATE TABLE events (
id UUID DEFAULT uuidv7() PRIMARY KEY,
user_id UUID NOT NULL,
payload JSONB
);
-- Multi-tenant systems: composite partition key
-- Partition by tenant, sort by time - perfect locality
CREATE TABLE events (
tenant_id UUID NOT NULL,
id UUID DEFAULT uuidv7() NOT NULL,
payload JSONB,
PRIMARY KEY (tenant_id, id)
) PARTITION BY HASH (tenant_id);
Write queries first, then define indexes
The most common schema design mistake is defining tables and then writing queries. Reverse the process. Write every query the system needs to execute, then add exactly the indexes those queries require.
-- Query: get a user's orders sorted newest first
CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC)
INCLUDE (status, total_cents); -- covering index, no heap fetch needed
-- Query: admin dashboard filtered by status
CREATE INDEX idx_orders_status
ON orders (status, created_at DESC)
WHERE status IN ('pending', 'processing'); -- partial index, smaller and faster
9. The Polyglot Persistence Pattern
Modern systems almost never need just one storage engine. Using multiple purpose-specific databases within the same system is called polyglot persistence, and it is the architectural norm for any non-trivial product.
Here is how a mature SaaS application's storage layer typically looks:
┌──────────────────────────────────────────────────────────────────┐
│ SaaS Platform │
├──────────────┬──────────────┬──────────────┬─────────────────────┤
│ Accounts & │ Product │ Search & │ Analytics & │
│ Billing │ Catalog │ Discovery │ Reporting │
│ PostgreSQL │ MongoDB │ Typesense │ ClickHouse │
│ ACID, │ flexible │ full-text, │ columnar, │
│ relations │ schema │ facets │ aggregations │
├──────────────┴──────────────┴──────────────┴─────────────────────┤
│ Sessions & │ Media & │ Events & │ Cache │
│ Auth tokens │ File uploads │ Audit logs │ layer │
│ Redis │ S3 / Azure │ TimescaleDB │ Redis │
│ sub-ms, TTL │ Blob Storage │ time-series │ hot data │
└─────────────────┴─────────────────┴─────────────────┴─────────────┘
Source of truth: PostgreSQL + Event stream (Kafka / SQS)
All other stores are derived projections, eventually consistent
The source of truth principle
In a polyglot architecture, the single most important rule is: every piece of data has exactly one authoritative source. All other copies are derived projections. If your search index and your relational database disagree on a product's price, you know which one is correct - the relational database. The search index is eventually consistent with it, and it can be rebuilt from scratch at any time.
This principle prevents the nightmare of split-brain systems where engineers are unsure which database contains the real data.
10. The Storage Design Document: A Template
Before any code is written, produce a Storage Design Document. It does not need to be long. It needs to be explicit.
# Storage Design Document
Project: [Project Name]
Version: 1.0
Date: [Date]
Authors: [Names]
Status: Draft | Reviewed | Approved
## 1. System Overview
[2–3 sentences describing what the system does and its scale expectations]
## 2. Data Inventory
| Entity | Shape | Size estimate | Retention |
|-----------------|----------------|-----------------|---------------|
| Users | Structured | ~1M rows/year | Indefinite |
| Orders | Structured | ~10M rows/year | 7 years |
| Product catalog | Semi-struct. | ~500K docs | Indefinite |
| Audit events | Time-series | ~100M rows/year | 90 days hot |
| Media uploads | Unstructured | ~5TB/year | Indefinite |
## 3. Access Pattern Matrix
[Full table of every read and write operation with frequency and latency SLA]
## 4. Consistency Requirements
| Domain | Required consistency | Justification |
|-----------------|----------------------|------------------------------|
| Payments | Serializable | Financial integrity |
| Product prices | Read committed | Slight staleness acceptable |
| Activity feed | Eventual (~2s) | UX only, no financial impact |
## 5. Storage Engine Decisions
| Store | Engine | Justification |
|-----------------|------------------|--------------------------------|
| Primary store | PostgreSQL 17 | ACID, relations, team expertise|
| Search | Typesense | Simple, fast, managed |
| Cache | Redis 8 | Sub-ms reads, TTL support |
| Media | AWS S3 | Durability, CDN integration |
| Metrics | TimescaleDB | PostgreSQL extension, low ops |
## 6. Partition Strategy
[Describe partition keys, sharding approach, and growth triggers]
## 7. Backup and Recovery
RPO (Recovery Point Objective): [e.g. 1 hour]
RTO (Recovery Time Objective): [e.g. 4 hours]
Backup strategy: [e.g. daily snapshots + WAL streaming]
## 8. Open Questions
[Decisions deferred for later, each with an owner and resolution deadline]
11. Common Anti-Patterns and How to Avoid Them
The God Table
Every entity lives in a single table with 80 columns, most of them NULL for most records. This usually results from "we'll add columns as we go" thinking during a fast-moving sprint zero. The table becomes unmaintainable and query performance degrades as the row size explodes.
The fix is to normalize correctly from the start. Use PostgreSQL table partitioning for large-volume entities and JSONB columns for genuinely variable attributes - that way you get schema discipline where you need it and flexibility where the domain demands it.
Using the Application Database for Everything
Using PostgreSQL for full-text search at scale, session storage, rate limiting counters, file storage, analytics aggregations, and event streaming simultaneously. The database becomes a bottleneck across every concern because it was never designed to be all of these things at once.
The fix is polyglot persistence. The application database owns transactional writes. Purpose-built engines own everything else. Each engine is sized and scaled independently.
The N+1 Schema Design
Designing a schema that makes the most natural query an N+1 - one query to fetch a list, then one query per row to fetch related data - and then adding caching to paper over the resulting performance problem. The cache masks the symptom without fixing the cause, and the latency spike returns the moment the cache is cold.
The fix is to design schemas and indexes so that common read operations are satisfiable in a single query. Joins, covering indexes, and materialized views all exist for this purpose. Run EXPLAIN ANALYZE on every query that touches more than a thousand rows before calling the schema done.
Premature Sharding
Implementing a sharded database cluster on day one for a product with 200 users because "we might need to scale." Sharding adds massive operational complexity - cross-shard transactions become coordination nightmares, and debugging a distributed write failure is orders of magnitude harder than debugging a single-node one.
The fix is to design your partition keys correctly from the start so that future sharding is a straightforward horizontal operation, but run on a single node until you have a measured, concrete reason to distribute. "It might be slow someday" is not a measured reason.
No Migration Strategy
Treating schema changes as a one-time ALTER TABLE executed manually on the production database by whoever has DBA access that day. This approach guarantees eventual disaster - a missed migration on a new environment, a rollback that left the schema in an inconsistent state, or a destructive change that had no approval process.
The fix is to treat every schema change as a versioned migration file checked into source control, executed by an automated tool like Flyway, Liquibase, EF Core Migrations, or Alembic, and tested against a production-like dataset before deployment.
Storing Secrets and PII Without Thought
Storing passwords in plaintext, API keys in a config table, or personally identifiable information in the same tables as operational data with no additional access controls. This is both a security vulnerability and, in most jurisdictions, a regulatory compliance failure.
The fix is to hash passwords with bcrypt or Argon2, store secrets in a secrets manager such as AWS Secrets Manager or HashiCorp Vault, encrypt PII at the column level, and isolate sensitive data in a dedicated store with strict RBAC policies and audit logging on every access.
12. A Real-World Walkthrough: Designing Storage for a SaaS Platform
Let us apply the entire framework to a concrete example: a B2B project management SaaS, think a lightweight Jira. Here is the process from scratch.
Step 1 - Answer the five questions
The data is mostly structured: workspaces, users, projects, tasks, and comments. Custom task fields per project template make one entity semi-structured. Access patterns include reading a user's task list, searching tasks by text, displaying a project activity feed, exporting project data, and aggregating team velocity metrics for reporting.
Consistency requirements split cleanly - task creation and assignment must be consistent since conflicts here would corrupt project state, while the activity feed can be eventually consistent. Scale targets are 500 paying teams at launch growing to 10,000 in 18 months, roughly 50 million tasks at scale and around 1 billion activity events per year.
The team is small - four engineers - so managed cloud services are strongly preferred and PostgreSQL expertise already exists on the team.
Step 2 - Select engines based on the access pattern matrix
| Operation | Frequency | Latency SLA | Notes |
|---|---|---|---|
| List tasks in project | Very High | < 30ms | Filtered by status and assignee |
| Get task detail | Very High | < 10ms | With comments and attachments |
| Search tasks by text | High | < 200ms | Cross-project for enterprise tier |
| Get activity feed | High | < 50ms | Last 100 events per user |
| Export project to CSV | Low | < 10s | Async background job |
| Velocity report | Low | < 3s | Aggregate closed tasks by sprint |
The access pattern matrix leads to the following engine decisions. PostgreSQL 17 on Supabase handles all structured data - workspaces, users, projects, tasks, and comments - as the source of truth. A JSONB column on the tasks table covers custom fields per project template without needing a separate document store at this scale. Typesense provides cross-project full-text task search, synced from PostgreSQL via a change-data-capture worker. Redis on Upstash stores pre-computed activity feeds per user (last 100 events) and session tokens. S3-compatible object storage holds task attachments with PostgreSQL storing only the key. Velocity reports are served by materialized views in PostgreSQL, refreshed nightly.
Step 3 - Design the schema for growth
-- Multi-tenant task table partitioned by workspace
CREATE TABLE tasks (
id UUID DEFAULT uuidv7() NOT NULL,
workspace_id UUID NOT NULL,
project_id UUID NOT NULL,
title TEXT NOT NULL,
status task_status NOT NULL DEFAULT 'todo',
assignee_id UUID,
custom_fields JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (workspace_id, id)
) PARTITION BY HASH (workspace_id);
-- 16 hash partitions - right-sized for expected tenant growth
CREATE TABLE tasks_p0 PARTITION OF tasks FOR VALUES WITH (modulus 16, remainder 0);
CREATE TABLE tasks_p1 PARTITION OF tasks FOR VALUES WITH (modulus 16, remainder 1);
-- tasks_p2 through tasks_p15 follow the same pattern
-- Composite index supports the most frequent read operation
CREATE INDEX idx_tasks_project_status
ON tasks (workspace_id, project_id, status, created_at DESC);
-- Partial index for assignee queries - excludes unassigned tasks
CREATE INDEX idx_tasks_assignee
ON tasks (workspace_id, assignee_id, status)
WHERE assignee_id IS NOT NULL;
The workspace_id as the partition key ensures that one large enterprise customer's data is physically isolated and their heavy queries do not scan partitions belonging to other tenants.
Step 4 - Document and review
The final step is producing the Storage Design Document, getting it reviewed by at least one engineer who was not involved in writing it, and resolving any open questions before the first migration runs. A fresh pair of eyes catches access patterns you forgot and consistency requirements you underspecified.
13. The Decision Checklist
Use this before committing to any storage architecture.
Discovery phase: Have you answered the five questions and written down the answers? Is the access pattern matrix complete? Are consistency requirements mapped per domain? Have you calculated scale estimates for rows, bytes, and requests per second? Have you identified operational constraints including team skills, budget, and SLAs?
Design decisions: Has every data entity been assigned to a storage engine with a documented justification? Have you identified the source of truth for every entity? Have you designed partition and sharding keys even if you are not implementing sharding yet? Has an index strategy been defined for every access pattern? Have backup, RPO, and RTO been defined?
Anti-pattern checks: Are there any God Tables? Is the application database being asked to serve as a search engine, cache, or analytics platform? Is any sharding premature? Has schema migration tooling been selected and configured? Has PII and secrets handling been defined?
Review gate: Is the Storage Design Document written? Has it been reviewed by at least one team member not involved in the design? Have open questions been listed with owners and resolution deadlines? Has the decision been approved by the tech lead or architect?
Wrapping Up
Great storage design is not about picking the trendiest database. It is about asking disciplined questions before the excitement of writing code takes over.
The engineers and teams that avoid expensive storage migrations share a common discipline. They slow down before sprint one. They build an access pattern matrix. They make their consistency trade-offs explicit. They write a Storage Design Document that everyone can refer back to. They design for the partition key they will need in year three even if they do not implement sharding until year two. They embrace polyglot persistence instead of forcing every use case into a single engine.
The cost of this upfront discipline is a few hours. The cost of skipping it is months of painful migrations, performance emergencies, and architectural debt that compounds every quarter.
Design the storage first. Then write the code.
Found this useful? Share it with your team before your next greenfield project starts. Drop your own storage war stories in the comments - every anti-pattern you have survived is a lesson someone else needs to hear.
Top comments (0)