- Start with PostgreSQL. It handles relational data, JSON, full-text search, and vector embeddings. You will not outgrow it for a very long time. - Normalize first. Denormalize only when you have measured a specific query that is actually slow. - Add `created_at`, `updated_at`, and soft delete columns to every table from day one. You will thank yourself later. - Never store money as a float. Use `DECIMAL(10,2)` or store amounts as integers in cents. - Multi-tenancy is the most important design decision for a SaaS. Get it wrong and you will rewrite your entire schema. - Index the columns you filter on. Use `EXPLAIN` to confirm queries are using them. - Over-engineering your database before launch is one of the most common startup mistakes. Start simple. Scale when the data tells you to.

## The Mistake Most Founders Make With Databases Most database design articles are written for senior engineers at companies already dealing with millions of rows. They cover sharding, CQRS, event sourcing, and polyglot persistence. These are real techniques for real problems. They are not the problems you have right now. The database problems founders actually face are simpler and more painful: a schema that made sense at launch but doesn't accommodate new features, a missing index discovered six months in when queries are crawling, money stored as a float causing rounding errors in billing, or a multi-tenancy model that let one customer see another's data. None of these are exotic. All of them are avoidable with the right patterns from the start. This guide covers database design patterns specifically for founders building their first web product or SaaS. Every pattern is practical. Every mistake is one someone already made in production.

## Start With PostgreSQL Before patterns: database choice. In 2026, for a founder building a web product, the answer is PostgreSQL. Not because it is the most scalable database in every scenario. Because it is the most capable general-purpose database you can start with and grow into without rewriting your application. PostgreSQL handles relational data with foreign keys and joins. It handles semi-structured data with JSONB columns. It handles full-text search natively. It handles vector embeddings via the `pgvector` extension for AI features. It handles time-series data, geospatial queries, and graph-like relationships with the right schema design. The databases you might reach for instead. MongoDB for flexibility, Redis for speed, Pinecone for vectors. are all valid for specific workloads. But reaching for them before you have a clear, measured reason adds operational complexity without solving a problem you actually have. Start with PostgreSQL. You can store JSON in Postgres with JSONB columns while keeping relational capabilities. When you outgrow the free tier of whatever managed Postgres service you're on, you're moving data. not rewriting your application.

## Pattern 1: Normalization (and When to Break It) **What it is:** Normalization organizes a schema so that every fact is stored exactly once. If something needs to change, it changes in one place. **Why it matters:** When the same data is stored in multiple places, updating it becomes risky. You change a customer name in one table and forget another, and now your system has two conflicting versions of the truth. These are called update anomalies and they are one of the most common causes of hard-to-trace bugs. **The rules in plain terms:** Each table should represent one thing. A `users` table stores user data. An `orders` table stores orders. An `order_items` table stores the individual items in each order. Not a single `user_orders_with_items` table with repeated user data on every row. Each column should hold one value. Not a comma-separated list in a single column. Not `"tag1,tag2,tag3"` in a `tags` field. A separate `tags` table with a row per tag per item. Each row should be uniquely identifiable. Every table needs a primary key. In practice, use a UUID or a sequential integer. UUIDs are more portable and don't expose row counts to users. ```sql -- Normalized: users and orders are separate CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email TEXT UNIQUE NOT NULL, name TEXT NOT NULL ); CREATE TABLE orders ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID REFERENCES users(id) ON DELETE CASCADE, total DECIMAL(10,2) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); -- Not normalized: repeating user data on every order row -- user_email, user_name, user_id all duplicated per order = update anomalies ``` **When to break it:** Denormalization. intentionally storing redundant data. is sometimes the right call for read performance. If you have a dashboard that shows a user's name on every item in a list, and fetching that name requires a JOIN across three tables, caching the name on the item table might be worth the trade-off. The rule: Start normalized for integrity, selectively denormalize for performance. Let data drive your optimization decisions. Measure the slow query. Confirm the JOIN is the bottleneck. Then denormalize that specific case. You can also over-normalize. One fintech startup decomposed currency codes, transaction types, and timestamp components into separate lookup tables, and the system buckled under peak load. A `currency_codes` lookup table is not normalization. it is ceremony. Keep lookup values inline as a constrained column or an enum unless there is a specific reason they need to be a separate table.

## Pattern 2: Always Add These Columns The columns you will always wish you had added from day one: ```sql CREATE TABLE projects ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- ... your actual columns ... -- Track when rows were created and last modified created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- Soft delete: mark rows as deleted without removing them deleted_at TIMESTAMPTZ, -- Track who created and last modified each row created_by UUID REFERENCES users(id), updated_by UUID REFERENCES users(id) ); ``` **`created_at` and `updated_at`:** Every table, no exceptions. These columns answer questions you will have in six months: when was this created, when was it last changed, has anything updated in the last 24 hours. They cost nothing to add and are extremely expensive to reconstruct. **`deleted_at` (soft deletes):** When you delete a row, it is gone. If a user deletes a project, then contacts support asking for it back, hard deletion means you cannot help them. Soft deletes mark rows as deleted without removing them. A deleted row has a `deleted_at` timestamp. A live row has `deleted_at IS NULL`. Add a default filter to your queries and you get the behavior of deletion without the permanence. ```sql -- Soft delete a row UPDATE projects SET deleted_at = NOW() WHERE id = $1; -- Query only active rows SELECT * FROM projects WHERE deleted_at IS NULL; -- Restore a deleted row UPDATE projects SET deleted_at = NULL WHERE id = $1; ``` **`created_by` and `updated_by`:** Who created this record? Who last changed it? Audit trails are required by compliance frameworks (GDPR, SOC 2) and are invaluable for debugging production issues. Add them from the start. Automatically update `updated_at` with a trigger so you never have to remember: ```sql CREATE OR REPLACE FUNCTION update_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER set_updated_at BEFORE UPDATE ON projects FOR EACH ROW EXECUTE FUNCTION update_updated_at(); ```

## Pattern 3: Never Store Money as a Float This one causes real billing bugs. Floating-point arithmetic introduces rounding errors. 0.1 + 0.2 = 0.30000000000000004 in most languages. Use DECIMAL(10,2) for monetary values. ```sql -- Wrong: FLOAT introduces rounding errors in calculations price FLOAT, -- Right: DECIMAL stores exact values price DECIMAL(10, 2), -- Also valid: store amounts as integers in the smallest currency unit -- $19.99 stored as 1999 (cents) -- Avoids decimal arithmetic entirely price_cents INTEGER, ``` The integer approach is common in payment systems because it completely eliminates floating-point arithmetic. Stripe, for example, works in cents internally. `$19.99` is sent to Stripe as `1999`. All arithmetic happens on integers, which are exact. Choose one approach and apply it consistently across the entire schema. Mixing DECIMAL and cents in the same database causes conversion bugs at the boundary.

## Pattern 4: Multi-Tenancy (The Most Important Decision for SaaS) If you are building a SaaS product, multi-tenancy is the most important database design decision you will make. Get it wrong and you will rewrite your entire schema. **What is multi-tenancy?** Multiple customers (tenants) sharing the same application and the same database, with complete data isolation between them. Tenant A must never be able to see or modify Tenant B's data. There are three approaches: **Silo isolation: separate database per tenant.** The strongest boundary. Each tenant has their own database. A compromise of one tenant cannot reach another. The trade-off is cost and operational overhead. managing 500 separate databases is a full-time job. Right for regulated industries (healthcare, finance) where physical isolation is a compliance requirement. **Pool isolation: shared tables with a tenant ID column.** All tenants share the same tables. Every row has an `org_id` or `tenant_id` column that identifies which tenant owns it. Enforced by filtering every query and, in PostgreSQL, by Row Level Security policies. This is the right default for most SaaS products. ```sql -- Every table that holds tenant data gets an org_id column CREATE TABLE projects ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), org_id UUID NOT NULL REFERENCES organizations(id), name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); -- Row Level Security enforces tenant isolation at the database level ALTER TABLE projects ENABLE ROW LEVEL SECURITY; CREATE POLICY tenant_isolation ON projects USING (org_id = current_setting('app.current_org_id')::UUID); ``` The advantage of enforcing isolation at the database level is that application bugs cannot accidentally expose cross-tenant data. A query that forgets the `WHERE org_id = ?` filter returns nothing rather than everything. **Bridge isolation: shared database, separate schemas.** Each tenant gets their own schema within the same database. A middle ground between pool and silo. More isolation than pool, less cost than silo. Common at mid-market scale when some enterprise customers require dedicated schemas but full silo isolation is not yet justified. **The right starting point:** Pool isolation with Row Level Security. It is cost-effective, scales to thousands of tenants, and is secure when implemented correctly. Move to bridge or silo isolation for specific customers who require it, driven by their compliance requirements. not by a hypothetical future need.

## Pattern 5: Indexing **What indexes do:** An index on a column makes queries that filter or sort by that column fast. Without an index, the database reads every row in the table to find matches. a full table scan. At a thousand rows this is fine. At a million rows it is a problem. **What indexes cost:** Every index slows down writes because the index must be updated alongside the row. Index everything and your writes degrade. Index nothing and your reads degrade. **The practical rule:** Index columns that appear in `WHERE` clauses, `JOIN` conditions, and `ORDER BY` clauses for queries that run frequently or on large tables. ```sql -- Index foreign keys. JOINs on unindexed foreign keys are full table scans CREATE INDEX idx_projects_org_id ON projects(org_id); -- Index columns you frequently filter on CREATE INDEX idx_users_email ON users(email); -- Composite index for queries that filter on multiple columns -- Order matters: put the most selective column first CREATE INDEX idx_orders_user_status ON orders(user_id, status); -- Use EXPLAIN to see if a query is using your index EXPLAIN SELECT * FROM projects WHERE org_id = 'some-uuid'; ``` **Common indexing mistakes:** No index on foreign key columns. Every table that has a foreign key should have an index on that column. Joins without indexes do full table scans. Too many indexes. A table with 15 indexes will have noticeably slower writes. Add indexes for queries you can measure, not for queries you imagine. Indexing low-cardinality columns. Indexing a `status` column with three possible values ("active", "inactive", "pending") is often useless. The database may prefer a full table scan because most rows match. Index high-cardinality columns (UUIDs, emails, timestamps).

## Pattern 6: Use the Right Data Types Dates stored as VARCHAR block range queries. Enums stored as freeform strings invite typos. Use DATE/TIMESTAMP for times, DECIMAL for money, and ENUM or lookup tables for fixed value sets. A practical cheat sheet: ```sql -- IDs: UUID over sequential integers for portability id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Money: DECIMAL over FLOAT for exactness, or INTEGER cents price DECIMAL(10, 2), -- Timestamps: TIMESTAMPTZ (with timezone) over TIMESTAMP created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- Booleans: BOOLEAN, not TINYINT or VARCHAR is_active BOOLEAN NOT NULL DEFAULT TRUE, -- Fixed option sets: use an enum or a CHECK constraint status TEXT CHECK (status IN ('active', 'inactive', 'cancelled')), -- Flexible structured data: JSONB over TEXT metadata JSONB, -- Long text: TEXT over VARCHAR with arbitrary limits -- In PostgreSQL, TEXT and VARCHAR(n) have the same performance -- Use VARCHAR(n) only when you need to enforce a max length name TEXT NOT NULL, -- Email addresses: TEXT with a UNIQUE constraint and validation at app layer email TEXT UNIQUE NOT NULL ``` **JSONB for flexible data:** When part of your schema is genuinely dynamic. user-configurable settings, metadata that varies per record, feature flags. JSONB is the right choice. It stores JSON as a binary format that is indexed and queryable. ```sql -- Store arbitrary settings per user ALTER TABLE users ADD COLUMN settings JSONB DEFAULT '{}'; -- Query inside JSONB SELECT * FROM users WHERE settings->>'theme' = 'dark'; -- Index a specific JSONB key CREATE INDEX idx_users_theme ON users ((settings->>'theme')); ``` Do not use JSONB as an escape hatch from schema design. A column that always holds the same fields should be real columns with proper types and constraints. JSONB is for genuinely variable data.

## Pattern 7: Caching **What caching solves:** Some data is read far more often than it changes. Your application's pricing plans, feature flags, public product listings, a user's profile data shown on every page load. these are fetched constantly but rarely updated. Fetching them from the database every time adds latency and load. Caching stores a copy of the data in a fast in-memory store (typically Redis) so repeated reads bypass the database entirely. ```js // Cache-aside pattern. the most common approach async function getUserById(userId) { // 1. Check the cache first const cached = await redis.get(`user:${userId}`); if (cached) return JSON.parse(cached); // 2. If not in cache, fetch from database const user = await db.query("SELECT * FROM users WHERE id = $1", [userId]); // 3. Store in cache with a TTL (expiry) await redis.set(`user:${userId}`, JSON.stringify(user), "EX", 300); // 5 minutes return user; } // Invalidate the cache when the user is updated async function updateUser(userId, data) { await db.query("UPDATE users SET name = $1 WHERE id = $2", [data.name, userId]); await redis.del(`user:${userId}`); // Clear cached version } ``` **The cache invalidation problem:** The hardest part of caching is knowing when to clear it. Stale data in a cache (showing a user their old name after they updated it) is a product bug. The simplest strategy: invalidate the cache entry whenever the underlying data changes. Short TTLs (5 to 15 minutes) reduce the impact of stale data if invalidation is missed. **What not to cache:** Data that changes frequently, data that must be consistent in real time (account balances, seat availability), or data filtered per user without careful key design.

## Pattern 8: Plan for Schema Changes Your database schema will change. Features you didn't anticipate will require new tables. Columns you added speculatively will turn out to be unnecessary. The schema that made sense at launch will look different at month six. **Migrations are how you change the schema without breaking the application.** Every schema change. adding a table, adding a column, dropping an index. should be written as a migration file that can be run consistently across development, staging, and production. ```sql -- Migration: add a subscription_status column to users -- Filename: 20260617_add_subscription_status_to_users.sql ALTER TABLE users ADD COLUMN subscription_status TEXT NOT NULL DEFAULT 'free' CHECK (subscription_status IN ('free', 'trial', 'active', 'cancelled')); ``` **Rules for safe migrations:** Add columns before using them in code. Deploy the migration, then deploy the code that reads the new column. Never deploy code that references a column that doesn't exist yet. Use `DEFAULT` values when adding `NOT NULL` columns to existing tables. Adding a `NOT NULL` column with no default to a table with existing rows fails immediately. Avoid long-running migrations in production. Renaming a column, adding an index to a large table, or dropping a column all take varying amounts of time and may lock the table. For large tables, add indexes `CONCURRENTLY` to avoid locking: ```sql -- Adds the index without locking the table for reads/writes CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at); ```

## The Startup Database Anti-Patterns **Designing for scale you don't have.** Sharding, CQRS, event sourcing, polyglot persistence. these solve real problems at scale. They also add enormous complexity for a product with 200 users. One of the biggest mistakes developers make is over-engineering their initial design. They try to anticipate every possible future requirement and build a system that's flexible enough to handle anything. Build for what you have. Refactor when the data tells you to. **Skipping foreign key constraints.** Foreign keys enforce that a referenced row actually exists. Without them, you accumulate orphaned rows: orders with no user, project members pointing to deleted users, payments referencing non-existent subscriptions. These cause bugs that are painful to track down. Add foreign keys. Let the database maintain referential integrity. **Storing everything in one table.** A single `data` table with a `type` column and a JSON blob for every entity in the system is an anti-pattern that avoids thinking about schema design. It makes every query complex, makes validation impossible, and makes reporting extremely slow. **No created_at columns.** Without timestamps, you cannot answer basic product questions: how many users signed up in the last 30 days, what was the most recently created order, has this record been updated since the webhook fired. Add timestamps to every table. **Ambiguous column names.** `status`, `type`, `value`, `data`. columns named this in every table make queries hard to read and easy to confuse across joins. Use specific names: `subscription_status`, `user_type`, `order_total`, `project_metadata`.

## When to Actually Scale the Database Most startups will not hit database scaling problems before hitting product-market fit. If you are doing fewer than a million queries per day on a properly indexed schema, your database is not your bottleneck. When you do hit limits, the progression is roughly: **1. Add the missing index.** Use `EXPLAIN ANALYZE` to find slow queries. Ninety percent of the time, the fix is a missing index. **2. Add a read replica.** A read replica is a copy of your database that handles read queries. Most managed Postgres providers (Supabase, Neon, RDS) offer read replicas. Direct analytics and reporting queries to the replica, writes to the primary. **3. Add caching.** Put frequently-read, rarely-changing data in Redis. Reduce database load on hot paths. **4. Partition large tables.** A table with 500 million rows benefits from partitioning. splitting it into smaller chunks (by date, by tenant, by status) that the database can query independently. **5. Consider a separate analytics database.** OLTP databases (PostgreSQL) are optimized for transactional workloads: individual row reads and writes. Analytics queries (aggregates across millions of rows) are better served by a columnar store like ClickHouse, Redshift, or BigQuery. Most startups reach this point after significant scale. Every step has a cost. Do not jump to step 5 when you have a missing index.

## Wrap Up Database design is one of the highest-leverage technical decisions a founder makes. A well-designed schema gives you flexibility to add features without rewrites. A poorly designed one creates friction that compounds every week. The patterns in this guide are not theoretical. They are the decisions that save you from the rewrites: proper normalization, soft deletes, DECIMAL for money, multi-tenancy done right, indexes on the columns you filter on, and schema changes via migrations. Start simple. Use PostgreSQL. Add timestamps and soft deletes from day one. Get multi-tenancy right before you have ten customers. Index when you measure a slow query, not before. If you're building a web product and want your project management, deployment tracking, and analytics in one workspace, check out [AdeptDev](https://adeptdev.io). Free for 7 days.

## What database should a startup use in 2026? PostgreSQL is the right default for most startups building web products in 2026. It handles relational data with joins and foreign keys, semi-structured data with JSONB columns, full-text search natively, and vector embeddings via the pgvector extension. Most managed Postgres providers (Supabase, Neon, RDS) get you up and running in minutes without managing infrastructure. Start with Postgres and reach for specialized databases (Redis for caching, ClickHouse for analytics, Pinecone for vectors) only when you have a measured reason to. ## What are the most important database design patterns for a SaaS product? The most important patterns for a SaaS product are: multi-tenancy isolation (every tenant's data separated by an org_id column, enforced with Row Level Security), soft deletes (deleted_at timestamp instead of hard deletion), timestamp columns on every table (created_at, updated_at), DECIMAL instead of FLOAT for monetary values, proper normalization to avoid update anomalies, and indexes on every foreign key and frequently filtered column. These patterns prevent the most common rewrites and bugs founders encounter after launch. ## What is multi-tenancy in database design? Multi-tenancy means multiple customers (tenants) share the same database with complete data isolation between them. The most common pattern for SaaS is pool isolation: every table has an org_id column, every query filters by org_id, and Row Level Security policies in PostgreSQL enforce isolation at the database level. This prevents application bugs from accidentally exposing one customer's data to another. The alternative approaches are silo isolation (separate database per tenant, expensive but strongest boundary) and bridge isolation (separate schema per tenant, a middle ground). ## Should I use SQL or NoSQL for my startup database? Start with SQL (PostgreSQL specifically). NoSQL databases like MongoDB are flexible for unstructured data but lose referential integrity, complex query capabilities, and the ability to JOIN across entities. Most MongoDB projects eventually migrate to PostgreSQL when the schema stabilizes. PostgreSQL can store flexible JSON data via JSONB columns while keeping relational capabilities for the parts of your schema that benefit from them. Reach for NoSQL only when you have a specific, measured reason. not because you anticipate needing flexibility. ## What is a soft delete and why should startups use it? A soft delete marks a row as deleted without removing it from the database, using a deleted_at timestamp column. Rows with deleted_at IS NULL are active. Rows with a timestamp are deleted. This means deleted data is recoverable (users who accidentally delete something can be helped), historical records are preserved (useful for audits and analytics), and foreign key relationships remain intact. Hard deletion. removing the row entirely. means deleted data is gone permanently and foreign key constraints may cascade unexpectedly. Add a deleted_at column to every table that holds data users might want to recover. ## How should I store money in a database? Never use FLOAT or DOUBLE for monetary values. Floating-point arithmetic introduces rounding errors. 0.1 + 0.2 equals 0.30000000000000004 in most languages, not 0.30. Use DECIMAL(10,2) for exact decimal storage, or store amounts as integers in the smallest currency unit (cents). Storing $19.99 as 1999 cents eliminates decimal arithmetic entirely. Apply whichever approach you choose consistently across the entire schema. mixing DECIMAL and integer cents causes conversion bugs at the boundary. ## When should a startup think about database scaling? Most startups will not hit database scaling problems before product-market fit. Focus on correct schema design. proper indexes, normalization, multi-tenancy. rather than premature optimization. When you do encounter limits, the progression is: add the missing index first (fixes 90% of slow queries), then add a read replica for analytics queries, then add Redis caching for frequently-read data, then consider table partitioning for very large tables. Only reach for distributed databases, sharding, or a separate analytics database when you have measured data showing you need it.