Postgres for SaaS: Indexes, Constraints, and the Mistakes That Cost You

If your SaaS uses Postgres, you will eventually learn the same lessons the rest of us have: some of them at 2 AM, most of them in production, and all of them in the worst possible week. This article is a field report of the ones that matter.

The mistakes that don’t matter (yet)

A lot of Postgres advice is written for companies running at Google scale. You are not Google. You probably have ten thousand rows. The advice that matters for you is different.

1. Connection pooling from day one

You probably heard “use a connection pool” and then ran your dev app with a single Prisma client and never thought about it again. That’s fine — until you deploy to a serverless platform, every function instance opens a connection, and Postgres runs out of file descriptors.

The fix: pool at the connection layer, not at the application layer. Use PgBouncer, or use a managed pooler like Supabase’s pgbouncer endpoint, or use Prisma’s directUrl + pgbouncer = true. Pick one before you launch. Don’t wait.

2. Backups you never tested

If your backup process is “Supabase takes a daily snapshot,” that’s the same as having no backup. The only thing that matters is: can you restore from it?

Test your restore quarterly. Document the steps. The first time you do this, it will take longer than you think. The second time, less. The third time, you actually know whether your backups work.

3. Migrations as one giant script

When you start, you have one database and one environment. You write a migration file. You run it. Done.

Six months in, you have staging, production, a test database, and three engineers all writing migrations. The day you ship a migration that drops a column someone’s running query depends on, you learn why forward-compatible migrations exist.

Use the expand → migrate → contract pattern for any non-trivial change:

1. Expand: add the new column. Write to both old and new.
2. Migrate: backfill old data into the new column.
3. Contract: drop the old column. (In a separate, later deploy.)

This takes three deploys. The alternative is downtime.

The mistakes that matter from day one

These are the things you’ll wish you did on day one, because retrofitting them later is expensive.

1. Foreign keys with cascading deletes

It’s tempting to set ON DELETE CASCADE everywhere. It cleans up your tables. It “just works.”

It also means a single bug in your application code can wipe out a customer’s entire account history because you forgot to check a checkbox somewhere. Use ON DELETE RESTRICT (or NO ACTION) for almost everything. Soft-delete instead, and only hard-delete in a scheduled job you control.

2. Money columns as floats

DECIMAL(10, 2) for money. FLOAT or REAL for everything else. Never the other way around.

FLOAT is an IEEE 754 binary floating-point number. 0.1 + 0.2 != 0.3. If you store money in a float, you’ll eventually have a customer whose balance is 0.0000000000000004 off, and your accounting team will not be able to reconcile it. The fix is to use a numeric type. The fix to your existing data is to migrate every row. Pick the right type on day one.

3. Indexes on the columns you query

This sounds obvious. The reason it’s a mistake is that you don’t know which columns you’ll query until you have users.

For a SaaS, the columns you’ll query are:

• The user’s email (sign in)
• The user’s organization ID (every authenticated request)
• The stripe customer ID (webhook handler)
• The session ID (every request)
• The subscription’s currentPeriodEnd (cron job)

Add an index on each of these before you launch. If you don’t, the day you hit 10,000 users, your dashboard takes 8 seconds to load and you spend a week debugging it.

4. Timezones as TIMESTAMPTZ everywhere

TIMESTAMP WITHOUT TIME ZONE is a footgun. Postgres stores the value as-is. Your application interprets it as whatever timezone the server is in. When you move to a different server, every row is now wrong by some number of hours.

Use TIMESTAMPTZ for everything user-facing. Store everything in UTC. Convert to the user’s local timezone at the edge of your application, never in the database.

The two TIMESTAMP types in Postgres exist because of historical reasons. The fact that one of them is the default is a mistake that the Postgres team has acknowledged in public. Use the one that does the right thing by default.

The one thing you should obsess over

Connection limits.

Postgres can handle roughly 100 connections per CPU core before context-switching overhead starts to dominate. Your serverless functions, your background workers, your long-running reports, and your admin tools all share those connections.

The naive setup — every app process opens its own connection — works fine until it doesn’t. The first sign of trouble is “the database is slow.” The second sign is “the database is unreachable.” By the time you see the second sign, you’re already down.

Pool at the connection layer. Monitor the pool. Alert when you’re above 80% utilization. This is the single Postgres decision that separates a SaaS that scales from one that falls over the first time Hacker News links to it.

The rest of the decisions are important. None of them are as urgent. Start with the pool. Then add the indexes. Then deal with the rest. In that order.