PostgreSQL Schema Design for SaaS: The Early Decisions That Determine Your Scale Ceiling

Most SaaS databases are designed in a hurry. The team wants to ship, the schema gets sketched in an afternoon, and the migrations pile up from there. That is normal, and it is not even wrong: speed-to-launch matters. The problem is that a handful of schema decisions made in week one are close to irreversible once you have production data, paying customers, and a live system you cannot take offline.

Good PostgreSQL schema design for SaaS is not about getting everything right upfront. It is about recognizing the eight decisions that are expensive to change later and spending your limited design time on exactly those. After auditing dozens of SaaS databases in various states of health, we see the same choices separating the schemas that scale quietly from the ones that force a migration project at the worst possible moment.

1. ID strategy: UUID vs BIGSERIAL vs ULID

The primary key type is the single most viral decision in your schema. Every foreign key, every index, every URL, and every API response inherits it.

BIGSERIAL is compact (8 bytes), naturally ordered, and index-friendly. Its downsides: IDs are guessable (enumeration attacks against /invoices/10432 are a real audit finding), and they leak business volume to anyone who signs up twice and compares IDs.

Random UUIDs (v4) fix guessability but hurt at scale in a way teams rarely anticipate: inserts land at random positions in the B-tree index. Once the index no longer fits in memory, every insert touches a cold page. Write-heavy tables with UUIDv4 primary keys show measurably worse insert throughput and much larger indexes than sequential keys.

ULIDs and UUIDv7 give you the best of both: random enough to be unguessable, time-ordered so inserts append to the right edge of the index like a sequence does. PostgreSQL 18 ships uuidv7() natively; on earlier versions a small extension or application-side generation does the job.

Our default recommendation: UUIDv7 (or ULID stored as UUID) for anything exposed in URLs or APIs, BIGSERIAL for internal high-volume tables like event logs that never leave the backend.

2. Soft delete vs hard delete, and what GDPR does to the choice

The deleted_at timestamp column feels safe: nothing is ever really gone, restores are trivial. But soft deletes have compounding costs. Every query needs a WHERE deleted_at IS NULL filter, which every ORM occasionally forgets. Unique constraints stop working ("this email is already taken" by a deleted account), forcing partial unique indexes. And tables accumulate dead rows that inflate every index and scan.

Then GDPR arrives. Article 17 erasure requests do not accept "we set a flag" as deletion of personal data. If you built your entire application on soft deletes, you now need a second mechanism that actually removes or anonymizes personal data across every table that references a user, which is exactly the hard-delete cascade you were avoiding.

The pattern that holds up: hard-delete personal data (with an anonymized tombstone row if referential integrity requires it), soft-delete business objects like projects or documents where restore is a genuine product feature. Decide per table, not globally. We covered the erasure mechanics in depth in our guide to GDPR right-to-erasure engineering.

3. Audit columns and where they break under async workers

created_at, updated_at, created_by: every table gets them, usually via ORM listeners. Two failure modes show up in production.

First, ORM-managed timestamps only fire when writes go through the ORM. The moment you add a bulk UPDATE in raw SQL, a COPY import, or a database-level cascade, updated_at silently stops being true. If anything downstream syncs on updated_at (search indexing, cache invalidation, exports), those rows never sync. Use database triggers or DEFAULT now() at the schema level instead of trusting the application layer.

Second, created_by conventionally means "the current authenticated user," which is meaningless inside an async worker. A queue consumer processing a job has no request context, so frameworks either write NULL or, worse, the last user some stale context remembers. Model actors explicitly: a created_by_type discriminator (user, system, api_key) alongside the ID, decided when the job is dispatched, not when it executes.

4. Timestamps: always timestamptz, and one more rule

The short version everyone knows: use timestamptz, never timestamp. Plain timestamp stores a wall-clock value with no zone, and the first time a server, a CI runner, and a developer laptop disagree about timezones, you get data that cannot be repaired because the original zone information was never stored.

The rule teams miss: timestamptz stores an instant, not a local time. For anything that must recur at a local wall-clock time (send the report at 09:00 in the customer's timezone), store the timezone name (Europe/Berlin, not an offset) in a separate column and compute the instant at scheduling time. Offsets break twice a year when DST shifts; zone names do not.

5. JSONB vs normalized columns

JSONB is the escape hatch that makes PostgreSQL viable for flexible, customer-defined attributes, and it is routinely overused. The heuristic that works: if your application logic branches on a field, it is a column. If you filter, sort, or join on it, it is a column. JSONB is for data whose shape you do not control (webhook payloads, third-party API responses) and for genuinely dynamic per-tenant custom fields.

The costs that surface later: no real foreign keys from inside a document, no column statistics for the planner (JSONB predicates get generic selectivity estimates, which produces bad plans on large tables), and every write rewrites the entire document, which bloats WAL and interacts badly with frequent small updates. A GIN index helps containment queries but is expensive to maintain on write-heavy tables.

6. Enums vs lookup tables, and migrating between them live

Native PostgreSQL enums are compact and self-documenting, but they are painful on a live system: you cannot remove a value, renames are awkward, and before PostgreSQL 12 even adding a value inside a transaction was restricted. Lookup tables are more flexible but add a join and let inconsistent data creep in if nobody enforces the reference.

A third option is often best for SaaS: a plain text column with a CHECK constraint. Adding a value is dropping and re-adding the constraint, which is instant with NOT VALID followed by VALIDATE CONSTRAINT. Values stay readable in every query and every export.

If you are stuck with a native enum on a large live table, the migration path is expand-contract: add a text column, backfill in batches, dual-write from the application, swap reads, then drop the enum column. The same choreography we describe in our zero-downtime migration playbook applies directly.

7. Multi-tenant index strategy: tenant_id leads

In a shared-schema multi-tenant database, nearly every query carries WHERE tenant_id = ?. That has a blunt consequence for PostgreSQL schema design in SaaS products: composite indexes should lead with tenant_id, because an index on (tenant_id, created_at) serves the per-tenant listing query, while separate single-column indexes force a bitmap-AND at best.

Leading with tenant_id also gives you locality: one tenant's rows cluster in the same index pages, so a busy tenant's working set stays hot. It keeps you compatible with row-level security policies and gives you a clean path to partitioning by tenant later, without rewriting queries. The mistake we find most often in audits is a large table with eight single-column indexes, none of which start with tenant_id, and a query log full of 200 ms scans that should be 2 ms lookups.

8. Foreign keys vs soft references for event-shaped data

Foreign keys are non-negotiable for core relational data. But for event-shaped tables (audit logs, activity feeds, analytics events), a strict FK to the user or order row creates real problems: you cannot delete or archive the referenced row without cascading into your history, every insert pays the referential check, and bulk loads slow down measurably.

The pragmatic split: enforce FKs on the transactional core, use soft references (the ID plus enough denormalized context to remain meaningful, like user_email_at_event_time) for immutable event streams. The event log's job is to describe what happened, and what happened does not change when the user is later deleted. This is also what makes GDPR erasure tractable: anonymize the denormalized fields, keep the event.

The pattern behind all eight

Notice the theme: none of these decisions are hard to get right on day one, and all of them are expensive to change on day four hundred, because by then the wrong choice is load-bearing under millions of rows. Schema migrations on live systems are possible (expand-contract makes almost anything doable), but every one you avoid is weeks of careful engineering you can spend on product instead.

If you are starting a SaaS build and want the data layer designed for the scale you are aiming at, that is the core of our custom software development work. If you already have a schema and a creeping suspicion about some of these decisions, a focused review as part of a code and architecture audit will tell you which ones are actually urgent and which can wait.

Questions about a specific schema decision? Write to hello@wolf-tech.io or find us at wolf-tech.io. We are happy to look at a schema diagram and tell you what we would change before it gets expensive.