This guide covers the database design patterns that matter for SaaS products in 2026 — from the foundational decisions that are expensive to undo to the production patterns every growing product needs. Key takeaways: - **Normalize first, denormalize when you've measured a problem.** Most premature denormalization trades data integrity for performance gains that don't exist yet. - **UUID v7 or ULIDs are the 2026 default** for primary keys — sequential like integers, globally unique like UUIDs, safe to generate client-side. - **Shared schema with `tenant_id`** is right for most SaaS products at launch. Switch to separate schemas or databases only when compliance or isolation demands it. - **Soft deletes belong in every SaaS product.** Hard-deleting records costs you audit history, support capability, and GDPR flexibility. - **The expand-and-contract migration pattern** is the only safe way to change a column type or rename a column in a live production database. - Schema decisions made on day one are the hardest to undo at scale. The choice between BIGINT and UUID for your primary key, whether to normalize aggressively or embed related data, how to partition a table that will grow to 500 million rows — these choices compound over years and table rewrites are expensive. ([source](https://www.jusdb.com/blog/database-schema-design-for-scalability-summary-and-best-practices))

## Why Database Decisions Are Expensive to Undo Most engineering mistakes are recoverable. A bad component architecture gets refactored. A poorly written API endpoint gets rewritten. A wrong library choice gets replaced. Database schema decisions are different. The table structure you ship to production with 100 users is the table you're migrating with 10 million rows two years later. The primary key type you chose in week one is the type you're working around in year three when you need to federate across multiple databases. The normalization decisions you made when you were building fast are the join chains you're profiling when things get slow. This guide covers the patterns that experienced engineers reach for when building SaaS products — not as theory, but as practical schema design with real SQL. The goal is to help you make the right decisions before the data makes them expensive to change.

## 1. Normalization vs Denormalization **Normalize first. Denormalize only when you've measured a specific, real performance problem.** Normalization is organizing a schema so that every fact is stored exactly once. If a customer's email address is in ten tables and they change it, you have ten updates to make and ten ways for the data to drift. In a normalized schema, the email lives in one place. Everything else references it. The three normal forms that matter in practice: **1NF:** Every column holds one value. No comma-separated lists inside a cell. `tags: "react,typescript,saas"` in a single column is a 1NF violation — it can't be queried cleanly, can't be indexed properly, and forces string parsing in your application layer. **2NF:** Every non-key column depends on the whole primary key, not just part of it. Matters mostly in tables with composite primary keys. **3NF:** Every non-key column depends only on the primary key, not on other non-key columns. The classic example: storing `country_code` and `country_name` in the same table. Country name depends on country code, not on the row's primary key — it belongs in a separate countries table. ```sql -- Violates 3NF — country_name depends on country_code, not on user_id CREATE TABLE users ( id BIGINT PRIMARY KEY, email TEXT NOT NULL, country_code CHAR(2), country_name TEXT -- this belongs in a countries table ); -- Normalized CREATE TABLE countries ( code CHAR(2) PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE users ( id BIGINT PRIMARY KEY, email TEXT NOT NULL, country_code CHAR(2) REFERENCES countries(code) ); ``` **When to denormalize:** When a specific query has been profiled and measured as slow, and the join is the bottleneck. Not before. Denormalization is a performance trade-off with a concrete cost — you now have duplicate data that must be kept in sync. Pay that cost only when the benefit is real and measured. The most common legitimate denormalization in SaaS: storing a pre-computed `total_amount` on an invoices table rather than summing invoice line items on every read. Acceptable when the sum is read frequently and the line items rarely change. Not acceptable as a preemptive optimization before you've confirmed the sum is a bottleneck.

## 2. Primary Keys: UUID vs BIGINT vs ULID **This decision affects query performance, URL security, distributed system capability, and migration cost. Most teams underestimate how much it matters.** ### BIGINT (auto-increment) ```sql id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY ``` Sequential integers are the fastest primary key for B-tree indexes. Each new row appends to the end of the index — no page splits, no fragmentation. For a single-node PostgreSQL database, BIGINT is the fastest option. The problem: sequential IDs are guessable. A user who knows their invoice is `/invoices/1042` can try `/invoices/1041` and discover data that shouldn't be accessible. You can mitigate this with proper authorization checks, but the ID itself leaks information about your total record count and creation order. ### UUID v4 ```sql id UUID DEFAULT gen_random_uuid() PRIMARY KEY ``` Globally unique, non-guessable, safe to generate on the client side before the record is created. The problems: random UUIDs cause B-tree index fragmentation because new inserts land at random positions rather than appending to the end. At tens of millions of rows this causes measurable write amplification. UUIDs are also 16 bytes vs BIGINT's 8 bytes — double the storage for every index that includes the primary key. ### UUID v7 or ULID — the 2026 default ```sql -- UUID v7 — time-ordered, globally unique -- Generate with the pg_uuidv7 extension or in your application id UUID DEFAULT uuid_generate_v7() PRIMARY KEY -- Or use a ULID stored as TEXT or UUID id TEXT DEFAULT gen_ulid() PRIMARY KEY ``` UUID v7 and ULIDs are time-ordered — new values sort higher than old ones, so they append to B-tree indexes like integers rather than landing at random positions. They're globally unique and non-guessable like UUID v4. For most SaaS products starting in 2026, UUID v7 or ULID is the right default. ([source](https://www.jusdb.com/blog/database-schema-design-for-scalability-summary-and-best-practices)) **When to use each:** - **BIGINT:** internal junction tables, high-write tables where you control URL generation, single-node databases that will never need federation - **UUID v4:** legacy systems, external IDs provided by third-party systems - **UUID v7 / ULID:** new SaaS products as the default — sequential performance, global uniqueness, non-guessable

## 3. Multi-Tenancy: The Four Patterns Multi-tenancy is the ability to serve multiple customers (tenants) from a single application instance while keeping their data separate. There are four approaches, and the right one depends entirely on your isolation requirements and scale. ### Pattern 1: Shared Schema (tenant_id column) Every table has a `tenant_id` column. All tenants share the same tables. Queries filter by `tenant_id`. This is the simplest, cheapest, and most common approach — the right default for most SaaS products. ```sql CREATE TABLE projects ( id UUID DEFAULT uuid_generate_v7() PRIMARY KEY, tenant_id UUID NOT NULL REFERENCES tenants(id), name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); -- Every query must filter by tenant_id -- Add it to every index CREATE INDEX idx_projects_tenant ON projects(tenant_id, created_at DESC); ``` **The critical rule with shared schema:** Row Level Security (RLS) in PostgreSQL enforces tenant isolation at the database level — not just the application layer. Without it, a bug in your application code can expose one tenant's data to another. ```sql -- Enable RLS on every tenant-scoped table ALTER TABLE projects ENABLE ROW LEVEL SECURITY; -- Policy: users can only see rows where tenant_id matches their session setting CREATE POLICY tenant_isolation ON projects USING (tenant_id = current_setting('app.current_tenant_id')::UUID); ``` Set `app.current_tenant_id` at the start of every database transaction. RLS then enforces isolation on every query automatically. **Trade-offs:** Simple to operate, cheap at any scale, one migration touches all tenants at once. The downside: one slow tenant's queries can affect others, and you can't easily give a tenant their own database backup. ### Pattern 2: Shared Database, Separate Schemas Each tenant gets their own Postgres schema (`tenant_a.projects`, `tenant_b.projects`). Tables are structurally identical but data is physically separate. ```sql -- Create a schema per tenant CREATE SCHEMA tenant_acme; -- Each schema gets the same table structure CREATE TABLE tenant_acme.projects ( id UUID DEFAULT uuid_generate_v7() PRIMARY KEY, name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() -- No tenant_id needed — isolation is at the schema level ); ``` **Trade-offs:** Better isolation than shared schema. Migrations must be run per schema (more complex). Good middle ground for 50-500 tenants with moderate isolation requirements. ### Pattern 3: Database-per-Tenant Each tenant gets a completely separate database instance. Maximum isolation — perfect for enterprise SaaS with strict compliance requirements (HIPAA, SOC 2 Type II with data residency requirements). Database-per-tenant is the gold standard for isolation, used by enterprise SaaS products that must satisfy strict compliance requirements. Migration management becomes a distributed systems problem and costs scale linearly with tenant count. Reserve this for situations where isolation is a hard requirement, not just a preference. ([source](https://erflow.io/en/blog/database-design-patterns-saas)) **Trade-offs:** Maximum isolation and security. Operational complexity scales with tenant count. Cost scales linearly. Right for enterprise, wrong for most early-stage products. ### Pattern 4: Branch-per-Tenant (Neon / Turso) The newest approach — serverless databases with branch-per-tenant isolation. Neon lets you create a Postgres database branch per tenant in milliseconds. Turso provides per-tenant SQLite databases distributed at the edge. Both sit between shared schema (too little isolation) and separate databases (too expensive at scale) — they're worth evaluating before defaulting to the hybrid approach for new SaaS projects in 2026. ([source](https://daily.dev/blog/multi-tenant-database-design-patterns-2024/)) **Trade-offs:** Emerging tooling, less operational precedent. Worth evaluating if you're starting fresh and need better isolation than shared schema without the cost of full database-per-tenant. ### Decision Matrix | Stage | Tenants | Compliance | Use | |---|---|---|---| | Pre-launch / MVP | Any | Low | Shared schema + RLS | | Growth (series A) | <500 | Medium | Shared schema + RLS | | Enterprise motion | <200 | High | Separate schemas or DB-per-tenant | | Edge / global | Any | Varies | Branch-per-tenant (Neon/Turso) |

## 4. Soft Deletes **In SaaS, hard-deleting records is almost always the wrong default.** A customer accidentally deletes something and expects you to restore it. You need billing records for compliance. You want to analyze churn by examining deleted accounts. Soft deletes solve this with a single column: ```sql ALTER TABLE projects ADD COLUMN deleted_at TIMESTAMPTZ NULL; -- NULL = active. A timestamp = soft-deleted. ``` Every query that should exclude soft-deleted records adds `WHERE deleted_at IS NULL`. In your ORM, add a default scope so this filter is applied automatically and you don't accidentally expose deleted records. **The composite index rule:** `deleted_at` goes at the end of composite indexes — not the beginning. Most queries filter on high-cardinality columns first: ```sql -- Good — tenant_id and name filter first, deleted_at refines CREATE INDEX idx_projects_active ON projects(tenant_id, name) WHERE deleted_at IS NULL; -- partial index: only indexes active rows -- Even better — partial index excludes deleted rows from the index entirely -- Smaller index, faster lookups for the 99% of queries that want active rows ``` A partial index with `WHERE deleted_at IS NULL` is particularly powerful — it only indexes the records most queries actually want, keeping the index small even as your historical deleted data grows. **GDPR and the purge window:** Soft deletes and GDPR compliance work together with a retention policy. Set a data retention window (90 days, 1 year — depends on your compliance requirements). A scheduled job hard-deletes rows where `deleted_at` is older than the retention window. This gives you the recovery window for support while complying with "right to erasure" requirements. ```sql -- Scheduled job: purge records deleted more than 90 days ago DELETE FROM projects WHERE deleted_at IS NOT NULL AND deleted_at < NOW() - INTERVAL '90 days'; ```

## 5. Audit Logging and Temporal Data **Who changed what, and when.** Every SaaS product eventually needs this — for support, compliance, debugging, and customer-facing activity feeds. Two approaches: a dedicated audit log table, or Postgres temporal tables. ### Approach 1: Audit Log Table Simple, explicit, queryable: ```sql CREATE TABLE audit_logs ( id UUID DEFAULT uuid_generate_v7() PRIMARY KEY, tenant_id UUID NOT NULL REFERENCES tenants(id), actor_id UUID NOT NULL, -- who did it actor_type TEXT NOT NULL, -- 'user' or 'system' action TEXT NOT NULL, -- 'created', 'updated', 'deleted' resource TEXT NOT NULL, -- 'project', 'task', 'invoice' resource_id UUID NOT NULL, -- which record changes JSONB, -- {field: [old_value, new_value]} metadata JSONB, -- IP, user agent, etc created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL ); CREATE INDEX idx_audit_tenant_resource ON audit_logs(tenant_id, resource, resource_id, created_at DESC); ``` Write to this table after every meaningful mutation. The `changes` JSONB column stores what changed — the old and new values for each field that was modified: ```json { "name": ["Old Project Name", "New Project Name"], "status": ["active", "archived"] } ``` **What to log:** state changes, permission changes, billing events, data exports, admin actions, authentication events. **What not to log:** read operations (SELECT queries), internal system operations with no user impact, high-frequency background jobs that would bury meaningful events. ### Approach 2: Temporal Tables (Postgres 17+ native, or with extensions) Temporal tables store the full history of every row automatically. Instead of maintaining an audit log separately, Postgres maintains a system-period temporal table that records start and end times for every version of every row. ```sql -- With the temporal_tables extension (pre-PG17) CREATE TABLE projects ( id UUID DEFAULT uuid_generate_v7() PRIMARY KEY, tenant_id UUID NOT NULL, name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active', sys_period TSTZRANGE NOT NULL DEFAULT tstzrange(NOW(), NULL) ); -- History table stores old versions CREATE TABLE projects_history (LIKE projects); -- Trigger maintains history automatically CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON projects FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'projects_history', true); ``` Temporal tables are more automatic but harder to query for "what changed" specifically. The audit log table is more work to maintain but produces more useful, human-readable records. For most SaaS products, the audit log table is the right choice — it's simpler to implement, easier to query, and the data model matches what you'll actually expose to users in an activity feed.

## 6. Subscription Billing Schema Every SaaS product needs a billing schema. Here are the four core tables: ```sql -- Plans: what you offer CREATE TABLE plans ( id UUID DEFAULT uuid_generate_v7() PRIMARY KEY, name TEXT NOT NULL, -- 'Pro', 'Team', 'Enterprise' price_monthly_cents INT, -- NULL for annual-only plans price_yearly_cents INT, -- NULL for monthly-only plans features JSONB NOT NULL DEFAULT '{}', -- feature flags per plan trial_days INT NOT NULL DEFAULT 0, is_active BOOLEAN NOT NULL DEFAULT TRUE, created_at TIMESTAMPTZ DEFAULT NOW() ); -- Subscriptions: what each tenant is on CREATE TABLE subscriptions ( id UUID DEFAULT uuid_generate_v7() PRIMARY KEY, tenant_id UUID NOT NULL REFERENCES tenants(id), plan_id UUID NOT NULL REFERENCES plans(id), status TEXT NOT NULL CHECK (status IN ( 'trialing', 'active', 'past_due', 'canceled', 'paused', 'incomplete' )), trial_ends_at TIMESTAMPTZ, current_period_start TIMESTAMPTZ NOT NULL, current_period_end TIMESTAMPTZ NOT NULL, canceled_at TIMESTAMPTZ, payment_gateway_subscription_id TEXT UNIQUE, -- Stripe subscription ID created_at TIMESTAMPTZ DEFAULT NOW() ); -- Invoices: what was billed CREATE TABLE invoices ( id UUID DEFAULT uuid_generate_v7() PRIMARY KEY, tenant_id UUID NOT NULL REFERENCES tenants(id), subscription_id UUID NOT NULL REFERENCES subscriptions(id), amount_cents INT NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'USD', status TEXT NOT NULL CHECK (status IN ( 'draft', 'open', 'paid', 'void', 'uncollectible' )), due_date DATE, paid_at TIMESTAMPTZ, payment_gateway_invoice_id TEXT UNIQUE, -- Stripe invoice ID created_at TIMESTAMPTZ DEFAULT NOW() ); -- Payment events: every payment attempt CREATE TABLE payment_events ( id UUID DEFAULT uuid_generate_v7() PRIMARY KEY, tenant_id UUID NOT NULL REFERENCES tenants(id), invoice_id UUID NOT NULL REFERENCES invoices(id), event_type TEXT NOT NULL, -- 'succeeded', 'failed', 'refunded' amount_cents INT NOT NULL, gateway_event_id TEXT UNIQUE, -- Stripe event ID for idempotency error_code TEXT, created_at TIMESTAMPTZ DEFAULT NOW() ); ``` **Three rules for billing schema:** **Store prices in cents, not decimals.** Floating point arithmetic on currency produces rounding errors. `$10.00` is stored as `1000`. All arithmetic stays in integers. **Never store prices from your code.** Stripe is the source of truth. Store Stripe's plan and price IDs. When you need the current price, pull it from Stripe (or cache it). If you store prices in your database and change them in Stripe without syncing, your records lie. **Status as a CHECK constraint, not free text.** `CHECK (status IN ('trialing', 'active', ...))` prevents invalid states at the database level. An application bug that tries to write `status = 'activee'` fails at the database, not silently in production.

## 7. Indexing That Actually Matters **Bad indexing is one of the most common causes of slow queries in production.** Too few indexes and queries table-scan. Too many and every write must update them all. ### Composite Index Column Order The columns in a composite index must be ordered from most selective to least — and must match the left-prefix of your WHERE clauses: ```sql -- Query: find active projects for a tenant, ordered by date SELECT * FROM projects WHERE tenant_id = $1 AND deleted_at IS NULL ORDER BY created_at DESC; -- Index that serves this query CREATE INDEX idx_projects_lookup ON projects(tenant_id, created_at DESC) WHERE deleted_at IS NULL; -- partial index excludes deleted rows ``` The rule: `tenant_id` goes first (most selective — filters to one tenant's data), `created_at` goes second (supports ORDER BY). `deleted_at IS NULL` in the WHERE clause of the index definition creates a partial index that only includes active rows. ### Covering Indexes A covering index includes all columns a query needs, eliminating the need to fetch the full row from the table: ```sql -- Query that only needs id and name SELECT id, name FROM projects WHERE tenant_id = $1 AND status = 'active'; -- Covering index — query satisfied entirely from the index CREATE INDEX idx_projects_covering ON projects(tenant_id, status) INCLUDE (id, name); ``` ### The Cost of Unused Indexes Every index you add is written to on every INSERT, UPDATE, and DELETE. An unused index is pure write overhead. Audit your indexes periodically: ```sql -- Find indexes that haven't been used since the last stats reset SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexname NOT LIKE '%_pkey'; ``` Zero scans on a non-primary-key index is a signal the index may be unused. Verify with `EXPLAIN ANALYZE` before dropping.

## 8. Zero-Downtime Migrations: The Expand-and-Contract Pattern **`ALTER TABLE` on a large table takes an exclusive lock.** During that lock, all reads and writes to the table block. On a table with millions of rows, this can take minutes — which is minutes of downtime in production. The expand-and-contract pattern avoids this by splitting every breaking schema change into three deployable steps: **Scenario: rename a column from `full_name` to `name`** **Step 1 — Expand:** Add the new column. Write to both old and new. Read from old. ```sql -- Migration 1: add the new column ALTER TABLE users ADD COLUMN name TEXT; -- Application code: write to both columns INSERT INTO users (full_name, name) VALUES ($1, $1); UPDATE users SET full_name = $1, name = $1 WHERE id = $2; ``` **Step 2 — Backfill:** Populate the new column from the old column, in batches to avoid locking: ```sql -- Backfill in batches — never update the whole table at once DO $$ DECLARE batch_size INT := 1000; offset_val INT := 0; BEGIN LOOP UPDATE users SET name = full_name WHERE id IN ( SELECT id FROM users WHERE name IS NULL LIMIT batch_size ); EXIT WHEN NOT FOUND; PERFORM pg_sleep(0.01); -- brief pause between batches END LOOP; END $$; ``` **Step 3 — Contract:** Once all application code reads from `name` and the backfill is complete, drop the old column: ```sql -- Migration 3: drop the old column (safe — nothing reads it anymore) ALTER TABLE users DROP COLUMN full_name; ``` This same pattern applies to column type changes, adding NOT NULL constraints, and splitting or merging tables. Use expand-and-contract patterns for safe changes. Online schema changes implement techniques for zero-downtime schema evolution. ([source](https://www.jusdb.com/blog/database-schema-design-for-scalability-summary-and-best-practices)) **Tools that help:** `pgroll` (Postgres-native online schema changes), `pg_osc` (online schema change similar to pt-osc), Drizzle's `drizzle-kit` for generating migration files from schema diffs.

## 9. pgvector and AI Features **If your SaaS product has any AI features — semantic search, RAG, recommendations, similar-item detection — you need a place to store embeddings.** In 2026, `pgvector` is the right default: store embeddings directly in Postgres alongside your relational data, in the same database you already operate. ```sql -- Enable the extension CREATE EXTENSION IF NOT EXISTS vector; -- Add an embedding column to any table ALTER TABLE documents ADD COLUMN embedding VECTOR(1536); -- 1536 dimensions for OpenAI text-embedding-3-small -- 3072 dimensions for text-embedding-3-large -- Index for approximate nearest neighbor search CREATE INDEX idx_documents_embedding ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100); -- tune lists based on row count: sqrt(rows) is a good start ``` Semantic search across a tenant's documents: ```sql -- Find the 5 most semantically similar documents to a query embedding SELECT id, title, content, 1 - (embedding <=> $1::vector) AS similarity FROM documents WHERE tenant_id = $2 AND deleted_at IS NULL ORDER BY embedding <=> $1::vector LIMIT 5; ``` **The tenant-aware vector pattern:** Always filter by `tenant_id` before the vector similarity search. Without it, a user in tenant A can semantically match documents from tenant B. The `tenant_id` filter must be in the WHERE clause, not applied after the similarity sort. **When pgvector is enough:** semantic search over hundreds of thousands to low millions of documents, RAG for a product with typical SaaS scale, recommendation systems, duplicate detection. **When to reach for a dedicated vector database (Pinecone, Weaviate, Qdrant):** hundreds of millions of vectors, sub-10ms latency requirements at scale, multi-modal embeddings (images + text), or when vector operations are the primary workload rather than a secondary feature. For most SaaS products in 2026, pgvector is sufficient and dramatically simpler to operate — one less database to manage, one less failure point, and your vector data is immediately joinable with your relational data.

## 10. Common Mistakes **Storing derived data.** If a value can be computed from other columns, don't store it. `total_invoiced_cents` on a customer record that sums their invoice amounts creates a synchronization problem — the stored value drifts from the real sum whenever invoices are created, updated, or voided. Compute it in a query. Materialize it as a view. Cache it in Redis with a short TTL. Don't store it in a column that requires manual synchronization. **Entity-Attribute-Value (EAV) tables.** EAV stores data as rows of `(entity_id, attribute_name, attribute_value)` instead of columns. It feels flexible. In practice it's unmaintainable at scale — queries are complex, indexes don't work efficiently, type safety disappears, and every report requires multiple self-joins. If you need flexible per-tenant custom fields, use JSONB columns — they're indexed, queryable, and typed. EAV is almost never the right answer in a Postgres-backed SaaS product. **Polymorphic associations without partial indexes.** A `comments` table with `commentable_type` and `commentable_id` columns (to associate comments with projects, tasks, or invoices) is a polymorphic association. Without a composite index on both columns and partial indexes per type, every query table-scans. Add the index: ```sql CREATE INDEX idx_comments_project ON comments(commentable_id) WHERE commentable_type = 'project'; ``` **Skipping migrations in favor of manual ALTER.** Every schema change should go through a migration file in version control — not typed directly into a production console. Manual changes are unrepeatable, unreviewed, and invisible to the rest of the team until something breaks. **Using TEXT for status columns without a constraint.** `status TEXT` with no constraint means your application can write any string and Postgres will accept it. `status TEXT CHECK (status IN ('active', 'canceled', 'paused'))` means invalid states fail at the database level. Always constrain status columns.

## Wrap Up The patterns in this guide are not academic. They're the decisions that compound — the ones that look small when you're building and feel large when you're scaling. The primary key type you choose, whether you use RLS for tenant isolation, whether you implement soft deletes from day one — these decisions shape what's easy and what's painful two years from now. Start normalized. Use UUID v7 or ULIDs. Add `tenant_id` and Row Level Security from the first table. Soft delete by default. Build an audit log early. Write migrations, never manual ALTERs. Add indexes when you've measured, not before. The database decisions that feel like overhead at 100 users are the decisions you're grateful for at 100,000. If you're building a SaaS and want your project management, GitHub integration, deployment tracking, and analytics in one workspace, check out [AdeptDev](https://adeptdev.io). Free for 7 days.

## What is a database design pattern? A database design pattern is a reusable solution to a common data modeling problem. It's a structural decision about how tables relate to each other, how data gets split or combined, and where you draw the boundaries between entities. Some patterns optimize for write safety (normalization), some for read speed (denormalization and covering indexes), some for flexibility (JSONB columns), and some for keeping one customer's data isolated from another's (multi-tenancy). Choosing the right pattern early is significantly cheaper than migrating to it later. ## What is the best multi-tenancy database pattern for a SaaS product? For most SaaS products at launch, shared schema with a `tenant_id` column and Postgres Row Level Security is the right choice — simple to operate, cheap at any scale, and secure when RLS is enabled. Move to separate schemas (one Postgres schema per tenant) when you have compliance requirements that need stronger isolation but can't yet justify full database-per-tenant. Use database-per-tenant for enterprise products with strict data residency or compliance requirements. Branch-per-tenant (Neon, Turso) is an emerging option worth evaluating for new products that need better isolation without full database-per-tenant cost. ## Should I use UUID or BIGINT as a primary key in PostgreSQL? For new SaaS products in 2026, UUID v7 or ULID is the best default. UUID v4 causes B-tree index fragmentation because random values insert at random positions rather than appending sequentially. BIGINT is faster but sequential and guessable. UUID v7 and ULIDs are time-ordered like integers (better B-tree performance) but globally unique and non-guessable like UUID v4. Use BIGINT for high-write internal tables where you control URL generation. Use UUID v7 or ULID for user-facing records. ## What are soft deletes and should I use them? Soft deletes add a `deleted_at TIMESTAMPTZ NULL` column to tables instead of removing records with DELETE. NULL means active; a timestamp means the record was deleted. In SaaS, hard-deleting records costs you support capability (users expect you to restore things), audit history (you need to know what was deleted), compliance flexibility (GDPR requires purging after a retention window, not immediate deletion), and churn analysis (deleted accounts are signal). Use soft deletes on every user-facing table. Implement a scheduled purge job that hard-deletes records where `deleted_at` falls outside your data retention window. ## What is the expand-and-contract migration pattern? Expand-and-contract is a technique for making breaking schema changes (renaming columns, changing types, splitting tables) without downtime. Instead of one migration that locks the table, you split the change into three deployable steps: Expand (add the new column, write to both old and new), Backfill (populate the new column from the old, in batches), and Contract (drop the old column once all code uses the new one). This avoids the exclusive table lock that a direct ALTER TABLE would cause on a large table — a lock that can produce minutes of downtime on a table with millions of rows. ## When should I use pgvector instead of a dedicated vector database? Use pgvector when your SaaS product needs semantic search, RAG, or similarity features alongside relational data, and your vector count is in the hundreds of thousands to low millions. pgvector keeps everything in one database — simpler to operate, fewer failure points, and embeddings are immediately joinable with your relational tables. Move to a dedicated vector database (Pinecone, Weaviate, Qdrant) when you have hundreds of millions of vectors, need sub-10ms latency at extreme scale, or when vector operations are the primary workload rather than a secondary feature of your SaaS product. ## What is EAV and why should I avoid it? EAV (Entity-Attribute-Value) is a database pattern that stores data as rows of (entity_id, attribute_name, attribute_value) instead of columns. It appears flexible — you can add any attribute without a schema migration — but it produces unmaintainable queries, defeats indexes, loses type safety, and requires complex self-joins for any report. In PostgreSQL, JSONB columns are the right alternative when you need flexible, per-tenant custom fields. JSONB columns are indexed with GIN indexes, queryable with operators like `->>` and `@>`, and preserve type information. EAV is almost never the right choice in a modern Postgres-backed SaaS application.