Supabase and Postgres: Database Best Practices That Keep Your App Fast

The database is where apps get slow. Indexes, connection pooling, and row-level security: the Postgres practices that separate a prototype from a product.

A client's dashboard loaded in three seconds when it launched and twelve seconds a month later. The code had not changed. The database had: a few thousand rows of new data, and not a single index to find them with. Postgres had been doing a full scan of the table on every request, reading every row just to find a handful.

Supabase puts a real Postgres database behind your app, which means you get all the power and all the responsibility. These are the practices that keep a database fast as it grows.

Why databases get slow and stay slow

There is a psychology to database neglect that is worth naming, because it explains why the pattern repeats. A database is fast when it is small, so the app feels great at launch. Growth happens gradually, one row at a time, so the slowdown is invisible until it is dramatic. By the time anyone notices, the fix requires work that was trivial a month earlier, and the cost of that delay (the sunk cost of the slow version, the fear of touching a working system) keeps it unfixed.

The uncomfortable truth is that databases do not get slow by accident. They get slow because every query that lacks an index is a debt that accrues interest. The interest is paid in milliseconds, quietly, until it is seconds, and then it is your users' patience.

Index what you filter, join, and sort

An index is a sorted lookup structure that lets Postgres find rows without scanning the whole table. Every column you filter by, join on, or sort by is a candidate. Start with the obvious ones, the foreign keys and the frequently filtered fields, and check the slow queries to find what you missed. A missing index is the single most common cause of a database that gets slower over time.

The way to know which columns matter is not to guess. Supabase shows you the slow queries and their plans; look for the steps that scan the most rows. Every full scan in a plan is an index you have not created yet. You are not optimising blind, you are reading the database telling you exactly what it wants.

Use partial indexes for the hot subset

Sometimes you query a narrow slice of a table constantly, such as active orders or published posts. A partial index covers only those rows, so it stays small and fast even as the table grows. Create it with a WHERE clause and Postgres uses it only where it applies.

This is the difference between indexing your whole history and indexing the part that matters today. If ninety percent of your queries touch active records and ten percent touch the archive, an index on everything is mostly wasted effort. The partial index is the 80/20 rule applied to the database: put the index exactly where the work is.

Keep connections pooled

Every database connection costs memory and time to open. Serverless functions that each open a fresh connection are the fastest way to exhaust a database. Use a connection pooler so requests share a small set of long-lived connections, and keep the number of simultaneous connections modest. Supabase provides a pooler for exactly this reason.

Connection storms are the classic intermittent failure: everything works in testing, then a traffic spike opens two hundred connections at once, and the database spends its energy on handshakes instead of queries. The failure looks random from the outside and is completely predictable from the inside. Pooling is a few lines of configuration that removes an entire class of mystery.

Make security a schema concern

Row-level security lets you write rules into the database itself: a user can read only their own rows, an admin can read everything, anonymous visitors can see only published content. This is not optional hardening. It is the correct way to structure multi-user data, because the protection travels with the data no matter which code path reads it. If your tables have no RLS policies and hold anything private, that is the first thing to fix.

The argument for RLS is architectural, and it is also a defence against the mistakes of the future. Every future developer, every future query, every future feature inherits the rule. A developer who writes a new endpoint next year cannot accidentally expose another user's data if the database refuses to return it. You are not just securing today's code; you are securing code that has not been written yet.

Know your queries

Supabase gives you a query planner view for a reason. When something is slow, look at the plan and find the step that scans the most rows. A query that filters on a column with no index will tell you plainly. Learn to read the plan before you guess at optimisations, and you will stop applying fixes to the wrong layer.

Reading a query plan is not a specialist skill. You are looking for two numbers: how many rows the query examined, and how many it returned. When those numbers are wildly different, something is doing more work than it should, and the plan names the step. Most performance fixes are just this: find the step doing too much, and give it what it needs.

Avoid the obvious antipatterns

Selecting every column when you need one, fetching related rows one at a time in a loop, and storing JSON where a real table belongs are the habits that turn a fast database into a slow one. In Supabase, use the join and select features to get exactly the shape you need in one round trip.

The loop is the one worth calling out specifically, because it feels innocent. "For each user, fetch their orders" looks like clean code and is, in the worst case, a thousand round trips that a single join would finish in one. The code review that catches this is worth more than any tuning, because the fix is not faster execution, it is fewer executions.

Your monthly database checkup

Keep the health of your database from drifting by spending fifteen minutes a month on it:

1. Open the slow query log and read the top five. For each, ask what index or shape change would remove it.
2. Check table growth. The tables that grew fastest are where missing indexes will hurt soonest.
3. Review connection usage. If your peak connections are close to the limit, pooling or code changes are due.
4. Re-read your RLS policies after any feature that touched user data, to confirm they still cover it.

The bottom line

Postgres is forgiving, which is why so many apps run fine with none of this. It is also unforgiving at scale, which is why so many get slow. Index the columns you actually query, pool your connections, write RLS into the schema, and read the query plans. Those four habits took that dashboard from twelve seconds back to under one, and they will keep it there as the data keeps growing.

If your app is slowing down and you would rather not learn query plans the hard way, that is the kind of rescue we do regularly. Send us a message and we'll find the full scans hiding in your database.