SaaS schema problems rarely announce themselves. The application works with ten tenants and 50,000 rows. Then a large customer imports a year of history, a dashboard starts timing out, a migration locks a table at noon, and someone finds a query that returns another tenant's invoice.
This guide covers the failures we see most often in growing SaaS products, what causes them, how to fix them without a rewrite, and the order in which to diagnose them. The examples assume a relational database, but the reasoning applies to managed services as well.
Symptoms that point to the schema
- Dashboard queries slow down as one tenant grows, even when total load is flat.
- A report for one customer delays requests for everyone else.
- Migrations take longer each release and occasionally fail halfway.
- A test finds a record belonging to another tenant.
- Customer, order or invoice records appear twice with slightly different values.
- Monthly totals do not match between the app, the invoice and the finance ledger.
- Deleted records reappear in reports or block a new record with the same email.
Cause 1: tenant isolation depends on application code
In a shared schema, every tenant-owned table needs a tenant identifier, and every query needs to filter on it. If the filter lives only in a controller, one forgotten where clause becomes a data leak. The fix has three layers: a tenant column on every tenant-owned table, a repository or query scope that cannot be bypassed, and database row-level security as a backstop where the engine supports it.
Test isolation explicitly: create two tenants with similar data, authenticate as one, and try to fetch the other's identifiers directly. Do this in automated tests, not only during a penetration test.
Cause 2: indexes built for single-tenant volume
An index on created_at helps one customer. In a shared table, the common query is "this tenant, this status, newest first". That needs a composite index with the tenant column first, followed by the filter and sort columns. Too often the tenant column is missing, or the query applies a function to an indexed column and prevents its use.
Use the query plan, not intuition. Run EXPLAIN ANALYZE with a realistic tenant and date range. If the plan shows a sequential scan on a large table, fix the index or the query before adding caching.
Cause 3: constraints are left to the application
Applications have bugs and background jobs bypass validation. Foreign keys, unique constraints and check constraints keep the data honest when a script, an import or a new service writes to the database. The exception is deliberate: high-volume ingestion may need deferred constraints or staging tables, but that should be a documented decision.
Watch the interaction with soft deletes. A plain unique constraint on email prevents a new customer from registering after the old record was "deleted". A partial unique index that applies only to active rows solves it.
Cause 4: soft deletes everywhere
Soft deletes are useful for recovery and audit. They become a problem when every query must remember deleted_at is null, when unique indexes ignore the flag, and when the table grows without a retention plan. Define which entities need recoverability, expose them through views that filter correctly, and archive the rest on a schedule.
Cause 5: entity-attribute-value or JSON for core fields
Flexible fields are useful for optional attributes and integration payloads. They are a poor home for tenant name, status, amount or date. Core fields stored as JSON are hard to index, hard to constrain and hard to report on. Promote the fields that appear in filters, totals and joins into real columns, and keep JSON for the long tail.
Cause 6: mutable natural keys
Using an email address, phone number or invoice number as a primary key looks convenient until the customer changes it. Use a stable surrogate key for relationships and keep the natural key as a uniquely constrained attribute. This also makes audit trails and merges far easier.
Cause 7: migrations without an expand-and-contract plan
A migration that adds a column with a default, backfills a million rows and creates an index in one transaction will lock or time out in production. Split it: add the nullable column, deploy code that writes both shapes, backfill in batches, add the constraint or index, then remove the old path. Set a lock timeout so a migration fails fast instead of blocking checkout.
Cause 8: reporting on the primary database
Analytics queries compete with customer traffic. As tenants grow, a single heavy report can starve the connection pool. Move reporting to a read replica or a warehouse, pre-aggregate the metrics that appear on dashboards, and put a statement timeout on ad hoc queries.
Cause 9: money and time stored loosely
Store money in integer minor units with an explicit currency code, or use a precise decimal type. Floating point totals drift, and a one-poisha mismatch breaks reconciliation. Store timestamps in UTC with the tenant's timezone as a separate setting, and be explicit about which reports use local calendar days.
Diagnostic sequence
- Reproduce the problem with a specific tenant, date range and record count.
- Read the query plan and compare it with the indexes that exist.
- Check every query in the path for tenant scoping, including joins and background jobs.
- Inspect constraint coverage on the tables involved.
- Review recent migrations for locks, backfills and default values.
- Run a cross-tenant access test in staging.
- Fix the smallest structural cause first, then re-measure.
Design rules worth keeping
- Tenant identifier on every tenant-owned table, indexed first in composite indexes.
- Stable surrogate primary keys and explicit foreign keys.
- Constraints in the database, not only in the application.
- Expand-and-contract migrations with a rollback path.
- One source of truth for money and time formats.
- Automated tests for tenant isolation and duplicate prevention.
Where this fits in our work
If you are fixing a schema that has already started to hurt, start with Custom Software and Integrations. The pricing page explains how we scope a review. For the interface layer, read the REST API design checklist.
Sources
- PostgreSQL Documentation, Constraints, accessed 17 November 2026.
- PostgreSQL Documentation, Row Security Policies, accessed 17 November 2026.
- AWS, SaaS Tenant Isolation Strategies, accessed 17 November 2026.
Responses (0)
No comments yet. Add the first one below.
Leave a response
Your email address will not be published. Required fields are marked *.