Your SaaS does not need a large or complex database architecture to launch. It needs a sound one: data modelled around real business concepts, clear ownership of every record, constraints that stop bad data getting in, indexes for the queries you actually run, migrations you can repeat, and backups you have restored. These are cheap to get right with a few hundred users and painful to retrofit with a few hundred thousand rows. Here is what to check.
Key takeaways
- Model the business, not the screens. Tables should match real concepts and relationships.
- Decide tenancy early. Knowing which organisation owns each row is the hardest thing to add later.
- Let the database enforce rules. Constraints make invalid data impossible instead of merely unlikely.
- Scale is usually a query problem. Fix schema and queries before buying bigger servers.
Why the database deserves attention now
Most early code can be rewritten cheaply. Data is different: once real customers have created records, every structural change needs a migration that transforms existing data safely while the product keeps running. The decisions below are the ones that tend to cause the most expensive migrations in young SaaS products, especially those whose schema was generated alongside the first screens by an AI tool.
1. Model your data relationally
If your data has relationships, such as customers with projects, projects with tasks, tasks with comments, a relational database like PostgreSQL is the natural fit. Each concept gets a table, relationships are expressed with foreign keys, and the database can answer questions across them reliably.
A frequent shortcut is storing important data as JSON blobs because it was quick. JSON columns are useful for genuinely flexible data such as settings or third-party payloads. A practical rule: if you will filter, join, sort, report on or validate a field, it should be a real column.
2. Decide how tenancy works
If customers sign up as teams or companies, almost every table needs to know which organisation a row belongs to. The common pattern for early SaaS is a shared database with an organisation_id on every tenant-owned table, enforced either in your application layer or with database policies such as PostgreSQL row-level security, which Supabase builds on in its RLS guide.
Retrofitting this later means touching every table, every query and every permission check, often while also migrating data that was created per user. If there is any chance you will sell to teams, design for it now even if the first version only shows one user per account.
3. Use constraints to keep data valid
Application code changes constantly; database constraints keep holding. PostgreSQL’s constraints let you declare that a column cannot be empty, that a value must be unique (one account per email within an organisation, say), that a status must be one of a known set, and that a referenced record must exist. Without them, a single bug or an AI-generated code path that skips validation can write data your reports and integrations will trip over for months.
4. Index for the queries you actually run
Indexes let the database find rows without scanning whole tables, but the PostgreSQL documentation notes that they also add overhead and should be used sensibly. Start with columns you filter, join and sort on frequently. One detail that catches many teams: a foreign key does not automatically index the referencing column. PostgreSQL’s docs point out that deleting a parent row requires scanning the child table, so it is often a good idea to index foreign key columns too.
| Symptom | Likely cause | Fix |
|---|---|---|
| List pages slow as data grows | Missing index on filter or sort columns | Add a targeted index, check the query plan |
| Deletes are slow or lock up | Unindexed foreign key columns | Index the referencing columns |
| One page makes hundreds of queries | N+1 pattern in the code | Fetch related data in one query |
| Inserts slow down over time | Too many unused indexes | Remove indexes nothing uses |
| Reports time out | Analytical queries on the live database | Pre-aggregate, or use a read replica or warehouse |
5. Change the schema through migrations
Every schema change should live in a versioned migration file, applied in order by your deploy process, in every environment. Editing production tables by hand is how staging and production drift apart. For live databases, make changes in safe steps:
- Add new columns as nullable or with a default, so existing rows stay valid.
- Backfill data in batches, rather than one huge update that locks the table.
- Add constraints once the data satisfies them.
- Build indexes on large tables with
CREATE INDEX CONCURRENTLY, which avoids blocking writes (and check for invalid indexes if a build fails). - Remove old columns only after no code reads them.
6. Separate permissions properly
Your application should connect with a role that has only the access it needs. Admin credentials belong to migrations and operations, not to request handling. If your frontend talks to the database directly, as many Supabase apps do, row-level policies are your primary security boundary and deserve the same care as your API.
7. Backups and recovery
Know your recovery point (how much data you could lose) and recovery time (how long a restore takes). PostgreSQL’s point-in-time recovery lets you restore to any moment since a base backup, and most managed providers offer it, sometimes as a paid option. Turn it on before you need it, and rehearse a restore into a separate environment at least once.
8. Monitor before it hurts
Track slow queries, connection counts, storage growth by table and, if you use replicas, replication lag. Most managed databases surface these. Reviewing the slowest queries once a month catches problems while they are still cheap.
Common mistakes, and when not to worry
- Premature sharding or microservice databases. A single well-indexed PostgreSQL instance serves far more load than most startups reach for years.
- Hard deletes everywhere. For records users might want back, a
deleted_atcolumn and a retention policy prevent many support tickets. - No audit trail for important changes. For money, permissions and status changes, recording who changed what and when is cheap and invaluable.
- Over-normalising early. You do not need a table for every attribute on day one. Normalise what you query and enforce; keep genuinely flexible settings in JSON.
If your business data still lives in spreadsheets and you are planning the move, our guide on when to replace spreadsheets with custom software is a good companion. For the rest of the production foundations, see our prototype-to-production roadmap.