Supabase Database Indexing: The Performance Work AI Won't Do For You

The Supabase dashboard looks calm right up until the moment it does not. A Lovable or Bolt project ships on a Tuesday, collects a few hundred rows across a dozen tables, and feels instant. Three months later there are eighty thousand rows in messages, the profile page that used to return in twelve milliseconds is now taking nine hundred, and nothing in the code changed. The only thing that grew was the data, and the only thing that was missing from day one was the set of indexes a human database engineer would have added without thinking.

This is the single most common performance pattern we see on vibe-coded Supabase projects. The schema migration the AI wrote has primary keys, has foreign key declarations, and has row level security policies that check auth.uid(). It has no secondary indexes at all. Postgres is doing a sequential scan on every filtered query, every RLS check, and every join, and it has been doing so since the first row was inserted. The app was fast because the tables were small, not because the database was correctly built.

This article walks through the exact indexing work we do on takeover projects. How to read an EXPLAIN ANALYZE output and spot a missing index. The four index types that cover almost every query a SaaS app will produce. The zero-downtime migration pattern for adding indexes to a live production table. And four patterns that search results on this topic almost always skip: partial indexes for hot-row lookups, covering indexes with INCLUDE, RLS-aware index planning, and the expression index trick that makes case-insensitive lookups tractable.

Why AI-Generated Schemas Arrive Without Indexes

Large language models know CREATE INDEX exists and will happily write one if you ask. The problem is that a model generating a schema from a prompt like "build a messaging app with users, conversations, and messages" will produce CREATE TABLE statements with primary keys, foreign key constraints, and maybe a UNIQUE on email, and then stop. There is no second pass where the model asks itself which columns will appear in WHERE clauses, which will appear in ORDER BY, and which will be filtered by RLS. Humans who build databases for a living do that second pass automatically. Models, today, do not.

The consequence is predictable. Every WHERE user_id = $1 triggers a sequential scan. Every ORDER BY created_at DESC LIMIT 20 sorts the entire table in memory. Every RLS policy that filters by auth.uid() forces a full-table check before the real query runs. On a staging database with five hundred rows this is invisible. On a production database with two hundred thousand rows it is a one-second response time and a compute bill that will not stop growing.

A second trap catches even experienced developers. Postgres does not automatically index foreign key columns. It indexes the primary key, because a primary key implies a unique btree, but a column declared REFERENCES users(id) gets no index unless you write one. This single gap explains most slow joins and most slow ON DELETE CASCADE operations in an AI-generated schema.

Reading EXPLAIN ANALYZE Like a Database Engineer

Before adding any index you need to see what the planner is actually doing. The shortest useful incantation is EXPLAIN (ANALYZE, BUFFERS) <your query>. ANALYZE actually executes the query and reports real timings. BUFFERS reports how many pages were read from the shared buffer cache versus from disk, which is how you tell a cold query from a hot one.

Suppose you run this against a messages table that has grown to a hundred thousand rows:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, body, created_at
FROM messages
WHERE conversation_id = 'c7f1a2b8-1e4d-4f12-8b3a-2f9e7c1d4a6b'
ORDER BY created_at DESC
LIMIT 20;

A missing-index plan looks like this:

Limit  (cost=3521.84..3521.89 rows=20 width=84) (actual time=412.118..412.124 rows=20 loops=1)
  Buffers: shared read=2891
  ->  Sort  (cost=3521.84..3527.31 rows=2188 width=84) (actual time=412.116..412.120 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 28kB
        ->  Seq Scan on messages  (cost=0.00..3463.50 rows=2188 width=84) (actual time=0.019..402.447 rows=2104 loops=1)
              Filter: (conversation_id = 'c7f1a2b8-1e4d-4f12-8b3a-2f9e7c1d4a6b'::uuid)
              Rows Removed by Filter: 97896
Planning Time: 0.183 ms
Execution Time: 412.168 ms

Four signals in that output matter. Seq Scan on messages means Postgres read the whole table. Rows Removed by Filter: 97896 means it touched ninety-eight thousand rows only to throw them away. The Filter: line under the scan rather than an Index Cond: line is the difference between filtering after the scan and pushing the predicate into the index. Sort Method: top-N heapsort means Postgres had to sort in memory because no index delivered rows already ordered. All four together are an obvious missing composite index.

After creating an index on (conversation_id, created_at DESC) the same query replans to an Index Scan Backward with Buffers: shared hit=5, total runtime under two milliseconds, and no sort node at all. That is the shape of a working query.

The Four Index Types That Cover Almost Everything

Postgres ships with a handful of index types but in practice four of them handle almost every query a SaaS application will throw at Supabase. Knowing which one to reach for is most of the skill.

The default is btree, and if you only ever learn one index type it should be this one. Btree handles equality, inequality, range, IS NULL, ORDER BY, and the leftmost-prefix of composite keys. When you write CREATE INDEX ON messages (conversation_id, created_at DESC) you are building a btree, and that single index serves WHERE conversation_id = $1, WHERE conversation_id = $1 ORDER BY created_at DESC, and WHERE conversation_id = $1 AND created_at > $2 equally well. Postgres can use a composite index if your WHERE clause covers a left-anchored prefix of the columns, which is why the column you always filter on goes first and the column you sort by goes last. Getting that order wrong is the most common mistake on composite indexes.

When your column holds jsonb, text arrays, or tsvector, btree stops being useful because it indexes values as opaque blobs. The answer is gin, an inverted index that stores one entry per indexable token and points back to the rows that contain it. CREATE INDEX ON posts USING gin (tags) makes WHERE tags @> ARRAY['pricing'] fast on a table where each row holds an array of tags. CREATE INDEX ON events USING gin (payload jsonb_path_ops) makes WHERE payload @> '{"plan":"pro"}' fast on an events table that stores jsonb metadata. The jsonb_path_ops operator class is smaller and faster when you only ever use containment queries. For fuzzy text search, install pg_trgm and create CREATE INDEX ON users USING gin (email gin_trgm_ops) to make WHERE email ILIKE '%acme%' return in milliseconds.

Gist is the generalized search tree, and the places you need it are geospatial data, range types, and nearest-neighbor queries. If the app stores a geography(Point, 4326) for listing locations and asks ST_DWithin(location, $1, 5000) to find everything within five kilometers, you want CREATE INDEX ON listings USING gist (location). Gist also backs exclusion constraints, which is how you prevent overlapping bookings by declaring EXCLUDE USING gist (room_id WITH =, during WITH &&) on a tstzrange column.

The fourth index type most vibe-coded projects can benefit from is brin, the block range index. Brin stores the minimum and maximum value of each physical block range rather than indexing every row, which makes the index orders of magnitude smaller and the maintenance cost close to zero. Brin only helps when the column is strongly correlated with physical row order, which is almost always true of created_at on an append-only events table. On a twenty-million-row events table a btree on created_at might be eight hundred megabytes; the brin equivalent will be under a megabyte and will filter a seven-day window faster than the seq scan it replaces. If rows are inserted in time order and never updated, brin on the timestamp column is almost free and almost always helpful.

Adding Indexes to a Live Table Without Downtime

The obvious way to add an index is CREATE INDEX ON messages (conversation_id, created_at DESC). The non-obvious problem is that the plain form takes an ACCESS EXCLUSIVE lock on the table for the duration of the build, which blocks every read and every write until it finishes. On a ten-thousand-row table this is invisible. On a one-million-row table it is an outage.

Postgres supports CREATE INDEX CONCURRENTLY for exactly this case. It builds the index in two passes, never blocks writes, and takes only a brief share lock. The cost is that it cannot run inside a transaction block. In a Supabase migration that means the statement has to be the only thing in its migration file. A clean pattern is one migration per index with a single statement:

CREATE INDEX CONCURRENTLY IF NOT EXISTS messages_conversation_created_idx
  ON public.messages (conversation_id, created_at DESC);

The IF NOT EXISTS clause is important because a failed concurrent build leaves an invalid index behind that blocks subsequent attempts. After running the migration, verify the index is valid:

SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indexrelid = 'messages_conversation_created_idx'::regclass;

If indisvalid is false, drop it with DROP INDEX CONCURRENTLY messages_conversation_created_idx and rebuild. This is the only safe way to add indexes on a production Supabase database with real users and real writes happening during the migration window.

Partial and Covering Indexes

A pattern most search results never cover is the partial index. If ninety-five percent of queries against an orders table only look at rows where status = 'active', you do not need an index covering the other ninety-five percent of data. A partial index with a WHERE clause only contains rows that satisfy the predicate, which makes it smaller, faster, and cheaper to maintain:

CREATE INDEX CONCURRENTLY orders_user_active_idx
  ON orders (user_id)
  WHERE status = 'active';

This index will be a fraction of the size of a full btree on user_id and will serve WHERE user_id = $1 AND status = 'active' perfectly. It will not serve queries that ask for other statuses, which is the entire point. Partial indexes are the right answer whenever a column has a skewed distribution and a hot subset drives most of the traffic. Feature flags, soft-delete columns, role filters, and status enums are all good candidates.

When an index contains every column a query reads, Postgres can satisfy the query from the index alone without fetching the row from the heap. This is an index-only scan, and the EXPLAIN output will say so explicitly. You can engineer for this with the INCLUDE clause, which adds non-key columns to the index payload without adding them to the sort key:

CREATE INDEX CONCURRENTLY orders_user_created_cover_idx
  ON orders (user_id, created_at DESC)
  INCLUDE (total, status);

A query like SELECT total, status FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 20 can now be served entirely from the index. The tradeoff is a larger index and slightly higher write cost, which is why you use INCLUDE on hot read paths and not on everything. On a dashboard that runs the same list query on every page load, it is the difference between twenty milliseconds and two.

Indexing for RLS Policies

Supabase row level security is the feature that makes vibe-coded apps safe and also the feature most likely to silently tank query performance. A typical policy reads:

CREATE POLICY "users read own messages" ON messages
  FOR SELECT USING (sender_id = auth.uid());

Every query against messages, even one with its own WHERE clause, is effectively rewritten to also filter by sender_id = auth.uid(). If sender_id is not indexed, every query does a sequential scan. The discipline is to read every policy, note every column used in USING and WITH CHECK, and make sure each has an index.

A related trap is that auth.uid() is a function call and Postgres sometimes evaluates it per row rather than once per query. Wrapping it as (SELECT auth.uid()) in the policy forces a single evaluation, which combined with a proper index on sender_id turns RLS overhead into single-digit microseconds.

One last pattern worth naming. If the app does case-insensitive lookups like WHERE lower(email) = lower($1), a plain btree on email will not help because the index stores the original values and the query transforms them. The fix is an expression index:

CREATE INDEX CONCURRENTLY users_email_lower_idx ON users (lower(email));

Now any query that writes WHERE lower(email) = lower($1) can use the index. The same pattern applies to date truncation (date_trunc('day', created_at)), computed tags, and any other predicate that wraps a column in a function. If the function appears in the WHERE clause, the index needs to match it exactly.

What We Actually Do on a Supabase Performance Audit

When WitsCode takes over a vibe-coded Supabase project for a performance audit the process is mechanical. We pull query stats from pg_stat_statements and sort by total time spent. We run EXPLAIN (ANALYZE, BUFFERS) on the top twenty queries and record every Seq Scan, every Rows Removed by Filter, every Sort Method: top-N heapsort, and every RLS-induced full-table scan. We read every policy and note every column used in USING or WITH CHECK. We look at foreign key columns and confirm which have indexes. We check jsonb columns for containment queries and text columns for ILIKE usage. Then we write one migration per index, all of them using CREATE INDEX CONCURRENTLY, attacking the slowest queries first. We verify each index with pg_index.indisvalid, re-run the original EXPLAIN to confirm the plan changed, and measure the result in real response times.

The typical outcome on a three to six month old vibe-coded project is that the list page, the profile page, and the admin aggregate queries all drop below fifty milliseconds, compute usage falls by half, and the app stops feeling slow under load. None of the application code changes. Only the indexes are different, and the indexes are what the AI was never going to write.

If your Supabase database is past ten thousand rows in its biggest tables, has never had an index review, and feels slower than it did a month ago, the fix is almost always this work. Book a Supabase performance audit and we will run EXPLAIN on every slow query, hand back a prioritized index plan, and ship the migrations concurrently against your production database without downtime. →