PostgreSQL and Supabase at scale: what nobody tells you

PostgreSQL is the best default database for most applications, and Supabase makes it a genuinely fast way to ship. We reach for both constantly. But the quickstart that gets you to a working app in an afternoon does not prepare you for the day the users table hits ten million rows, the connection pool melts at peak traffic, and a row-level security policy quietly turns every query into a sequential scan.

This is the article we wish every team read before they scaled. None of it is exotic. All of it is boring, recurring, and entirely avoidable if you know it's coming.

Connection management is the first wall you hit

PostgreSQL connections are expensive. Each one is a backend process with real memory overhead, and Postgres tops out at a few hundred concurrent connections before it degrades — far fewer than a busy serverless app will try to open.

This is the failure mode that surprises teams most, because it has nothing to do with query speed. Serverless functions, edge runtimes, and autoscaling app servers each open their own connections, and under load they collectively exhaust the limit. Queries that ran in milliseconds start timing out, not because they're slow but because they can't get a connection.

The fix is a pooler in transaction mode (Supabase ships PgBouncer-style pooling for exactly this). The rules that matter:

• Use the transaction-mode pooler for serverless and edge. It hands a connection to a transaction and reclaims it immediately, so thousands of clients share a small pool.
• Know what transaction mode forbids. Session-level features — prepared statements held across statements, SET that persists, LISTEN/NOTIFY on a pooled connection — don't survive in transaction mode. Design around it.
• Use the direct connection for migrations and long-lived jobs, not for per-request traffic.

Get pooling right and a huge class of "the database is down" incidents simply never happens.

Row-level security is a feature and a performance footgun

RLS is one of the best things about Postgres and Supabase — security enforced at the data layer, not bolted on in application code. It is also one of the easiest ways to wreck performance if you write policies without thinking about how the planner executes them.

A policy is a predicate appended to every query against the table. If that predicate isn't cheap and indexable, every read pays for it. The common mistakes:

• Wrapping auth.uid() in a way that re-evaluates per row. Postgres can sometimes treat the function call as volatile and run it for every row. Wrapping it as a scalar subquery — (select auth.uid()) — lets the planner evaluate it once. On large tables this is the difference between a millisecond and a timeout.
• Policies that join other tables. A policy that does a subquery against a team_members table on every row of a million-row query is a self-inflicted N+1 at the database layer. Make sure the joined columns are indexed, and keep the predicate simple.
• No index on the policy's filter column. If your policy filters by user_id, that column needs an index. Without it, RLS turns every query into a sequential scan that also evaluates a predicate per row.

RLS is worth it. But treat policies as part of your query performance budget, because that's exactly what they are.

Indexing is not optional, and not free

The single most common cause of a database that "suddenly got slow" is a query doing a sequential scan over a table that grew. It was instant at ten thousand rows and unusable at ten million. Nothing changed except the data.

The discipline:

• Index your foreign keys and your filter/sort columns. Anything in a WHERE, JOIN, or ORDER BY on a large table is a candidate.
• Use composite indexes that match query shape. An index on (tenant_id, created_at) serves "this tenant's recent rows" far better than two separate single-column indexes.
• Read the query plan. EXPLAIN ANALYZE tells you the truth. A Seq Scan on a big table in a hot path is a bug. A Bitmap Heap Scan or Index Scan is what you want.
• Don't over-index. Every index slows writes and consumes storage. Index for the queries you actually run, then stop.

Indexes are not free — they cost write throughput and disk — but an un-indexed hot query costs you the whole application.

The N+1 problem moves but never dies

ORMs and convenient client libraries make it trivially easy to fetch a list of records and then, in a loop, fetch a related record for each one. A hundred parent rows become a hundred and one queries. It's invisible in development with ten rows and catastrophic in production with ten thousand.

Supabase's client makes the right thing easy if you use it — nested selects that resolve a relationship in a single query rather than a loop of round-trips. The habit to build: whenever you render a list with related data, ask "is this one query or N queries?" and confirm with the network tab or the Postgres logs. The fix is almost always a join or a nested select, not more caching.

Migrations and zero-downtime changes

Schema changes are where confident teams cause outages. The traps:

• Adding a NOT NULL column with a default on a huge table can rewrite the whole table and hold a lock. On modern Postgres a constant default is cheap, but a volatile one is not — know the difference.
• Adding an index without CONCURRENTLY locks writes for the duration of the build. On a large table that's a visible outage. CREATE INDEX CONCURRENTLY avoids the lock at the cost of running longer.
• Renaming or dropping columns the app still references breaks the moment the migration lands. Use expand-and-contract: add the new shape, migrate reads and writes, then remove the old shape in a later deploy.

Treat migrations as production changes with their own review and rollback plan, not as an afterthought stapled to a feature.

Observability before you need it

You cannot tune what you can't see. The instrumentation that pays for itself many times over:

• pg_stat_statements to find the queries consuming the most total time — usually a handful of offenders dominate.
• Slow query logging so regressions surface as data, not as user complaints.
• Connection and pool metrics so you see saturation coming before it becomes timeouts.
• Table and index size tracking so the day a table crosses into "needs partitioning" territory isn't a surprise.

The teams that scale smoothly are not the ones who guessed right. They're the ones who could see what was happening and fixed the top offender before it became an incident.

When a single Postgres isn't enough

Eventually some applications outgrow a single primary. Before you reach for sharding or a different database — which is a large, often premature step — exhaust the boring options first:

• Read replicas for read-heavy workloads, with the application aware of replication lag.
• Partitioning for tables where data is naturally time- or tenant-bounded, so queries and maintenance touch one partition instead of the whole table.
• Connection pooling and query tuning, which solve far more "we've outgrown Postgres" claims than the claims would suggest.
• Moving cold data out of the hot path entirely — archival tables, object storage for blobs, a separate analytics store for reporting queries that don't belong on the transactional database.

A well-tuned single PostgreSQL instance handles workloads far larger than most teams expect. The decision to go beyond it should be forced by measured limits, not by anxiety.

The boring conclusion

Postgres and Supabase scale further than most applications will ever need — but only if you treat the database as a system to be operated, not a black box to be queried. Pool your connections, index your hot paths, keep RLS policies cheap, kill N+1s, migrate carefully, and watch the right metrics. None of it is glamorous. All of it is the difference between a database that quietly does its job and one that becomes the reason for your 2 a.m. page.

---

We design and tune Postgres and Supabase backends for production load. Let's talk about yours.