Slow database queries and indexes, explained for founders

When an app that felt instant with test data becomes slow with real users, the cause is usually a handful of database queries that read far more rows than they need, most often because the column they filter or sort by has no index. An index is a sorted lookup structure, like the index at the back of a book, that lets the database jump to the matching rows instead of reading the whole table. Finding those few queries and adding the right indexes often fixes the slowness without touching the rest of the app.

This guide explains, without assuming you're a developer, how to recognise a database problem, where to find the slow queries in Postgres (including Supabase), which indexes to add, and how to confirm the fix.

How do I know the database is the problem?

Some signs point clearly at the database:

• Pages got slower gradually as data grew, not suddenly after a release.
• Lists, searches and dashboards are slow; static pages are fine.
• In the browser's Network tab, the request to your API or to Supabase takes most of the time, while the page itself loads quickly.
• Things are worse for your oldest or biggest customers, who have the most rows.

If instead everything is slow, including pages with no data, look at the front end first. Our guide to Core Web Vitals for your app covers that side.

Why do queries get slow?

Without an index, Postgres answers WHERE user_id = 42 by reading every row in the table and checking each one. That's called a sequential scan. With 500 rows nobody notices. With five million, every page view waits for it.

The usual causes in apps built with Lovable, Bolt, Replit or Cursor:

1. No index on foreign keys. Postgres creates an index for primary keys automatically, but not for columns that point to another table, like orders.customer_id or tasks.project_id.
2. Row Level Security policies on unindexed columns. A policy such as user_id = auth.uid() runs on every query. If user_id has no index, every request scans the table.
3. The same query in a loop. The page fetches 50 projects, then makes one request per project to count its tasks. That's 51 queries where one would do (often called the "N+1" problem).
4. Fetching everything. select * on a table with large text or JSON columns, or loading a whole list instead of the first page.
5. Sorting large results by a column with no index, for example "newest first" on created_at.

How do I find the slow queries?

In Supabase

• Open the Performance Advisor in your project's Advisors section. It flags issues such as unindexed foreign keys, duplicate indexes and slow security policies.
• With the Supabase CLI linked to your project, run supabase inspect db outliers to list the queries with the highest execution time, and supabase inspect db seq-scans to see which tables are read in full most often.
• The index_advisor extension suggests indexes for a specific query.

In any Postgres database

The pg_stat_statements extension records how often each query runs and how long it takes. Sort by total time, not by the single slowest call: a query that takes 40 milliseconds but runs 100,000 times a day matters more than one that takes two seconds once a week.

Then take one query and run it with EXPLAIN ANALYZE in front. If the plan says Seq Scan on a large table and the actual time is high, you've found a candidate.

explain analyze
select id, title, created_at
from tasks
where project_id = 'a1b2c3'
order by created_at desc
limit 20;

Which indexes should I add?

Start from what the slow queries filter, join and sort on.

A few rules of thumb:

• Column order matters in a combined index. Put the column you filter by with equality first, then the one you sort or range over.
• Partial indexes (with a where clause) are smaller and faster when queries always target the same subset, as long as the query's condition matches the index's.
• On a busy production table, use create index concurrently. A normal create index blocks writes to the table while it builds. The concurrent version takes longer but lets the app keep working. It can't run inside a transaction, so run it on its own.
• Don't index everything. Each index takes space and slows down inserts and updates. Supabase's advisors also report unused and duplicate indexes you can remove after checking.

Fix the policy, too

Supabase recommends writing (select auth.uid()) instead of auth.uid() inside RLS policies, so Postgres evaluates it once per query rather than once per row. Our guide to Supabase Row Level Security shows policies written that way.

How do I prove the index helped?

1. Run the same EXPLAIN ANALYZE again. You should see Index Scan or Bitmap Index Scan instead of Seq Scan, and a much lower execution time.
2. Check the page in the browser's Network tab with the same account and data as before.
3. Look at the outliers list again after a day of real traffic. The query should have dropped down the list.

If the plan still shows a sequential scan, the query may not match the index (a different column order, a function wrapped around the column, or a type mismatch), or the table may be small enough that Postgres decides a full read is cheaper. That's normal for small tables.

What if indexes aren't enough?

Sometimes the query itself needs changing: replace the per-item loop with a single query using a join or count ... group by, add pagination, or precompute a total that every dashboard recalculates. If the database is simply out of memory or CPU at peak times, a larger compute size helps, but do that after the indexes, not instead of them. We go into what breaks as traffic grows in scaling from 100 to 10,000 users.

Quick answers

Will adding an index break my app? No. Indexes don't change query results, only speed. The risk is locking a busy table while it builds, which create index concurrently avoids.

How many indexes is too many? There's no fixed number. If an index never shows up in query plans or usage stats, it's costing you writes for nothing.

Can I ask the AI tool to add indexes? You can, but give it the exact slow query and check the result with EXPLAIN ANALYZE. Indexes added without a real query often don't match how the app reads data.

Do I need a bigger database plan? Maybe later. Missing indexes make any plan look too small.

Want someone to find them for you?

If your app slowed down and you'd like a second pair of eyes, our maintenance and support team can review your slowest queries and add the right indexes safely, and for Supabase projects a Supabase security review checks your policies at the same time. You can also contact us with what feels slow. Supabase's own query optimisation guide is a good next read.