Why Is My Supabase / Neon Postgres Slow? The Complete Diagnosis Guide

TL;DR: Slow Postgres on Supabase, Neon, or RDS is almost never a hardware problem. In order of how often they're the cause, the culprits are: (1) a missing index on a frequently filtered column, (2) an unindexed foreign key (Postgres auto-indexes primary keys but not foreign keys), (3) untuned config — most often shared_buffers left at the 128MB default and random_page_cost left at 4 on SSD storage, (4) dead-tuple bloat from autovacuum falling behind, and (5) N+1 query patterns that are individually fast but collectively crushing. You can diagnose every one of these with read-only queries against the system catalogs and pg_stat_statements, and most fixes are a single line of SQL. This guide walks through each, in order, with the exact commands.

If you'd rather have this done automatically: run a free read-only scan, paste the JSON, and get a graded report with the fixes attached. But you don't need it to follow along.

---

First, a mental model: slow vs. expensive

Before you change anything, internalize one distinction, because it determines which fix you reach for.

A slow query takes a long time per execution — a 2-second report query, a join over an unindexed table. You fix these with indexes or query rewrites.

An expensive query might be fast per execution but is run so often that it dominates your database's total CPU. The textbook case is a 3-millisecond query called two million times a day — that's 95 minutes of pure DB CPU hiding inside a query that looks perfect in isolation. You fix these by changing how the application calls the database (usually batching an N+1).

You cannot tell these apart by feel. You need pg_stat_statements, which we'll set up in a moment.

Quotable: A slow query and an expensive query are different problems. The first needs an index; the second needs a code change. pg_stat_statements is the only thing that tells them apart.

---

Step 0: Enable pg_stat_statements

This is the single highest-leverage extension in Postgres for performance work. It records every query shape (with literals stripped) alongside its call count, mean execution time, and total execution time.

Check whether it's on:

SELECT * FROM pg_stat_statements LIMIT 1;

If that errors, enable it. On Supabase and Neon the library is preloaded, so you usually just need:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

On RDS / Aurora, add pg_stat_statements to shared_preload_libraries in the parameter group, reboot the instance, then CREATE EXTENSION. On a self-managed Postgres, set it in postgresql.conf and restart:

shared_preload_libraries = 'pg_stat_statements'

Everything below assumes it's available. If it isn't, you can still do the index and bloat work — you just won't see your query-level cost centers.

---

Cause #1: A missing index on a sequentially scanned table

This is the most common cause of "it was fast, now it's slow." The table grew past the point where Postgres can afford to scan the whole thing, but there's no index to let it jump straight to the rows you want.

How to find it

SELECT relname,
       seq_scan,
       idx_scan,
       n_live_tup
FROM   pg_stat_user_tables
WHERE  seq_scan > 0
ORDER  BY seq_scan DESC
LIMIT  20;

Read it like this: a table with a high seq_scan, low idx_scan, and large n_live_tup is being repeatedly read end-to-end. That's a missing index. A small lookup table with high seq_scan is fine — scanning 50 rows is cheap, and an index would be ignored anyway.

To confirm on a specific query, run EXPLAIN ANALYZE and look for a Seq Scan node where you expected an Index Scan:

EXPLAIN ANALYZE
SELECT * FROM order_items WHERE status = 'pending';

If you see Seq Scan on order_items (rows=1400000) for a query that should return a handful of rows, that's your culprit.

How to fix it

CREATE INDEX CONCURRENTLY idx_order_items_status
  ON order_items (status);

Always use CONCURRENTLY on a live database. A plain CREATE INDEX takes an ACCESS EXCLUSIVE-style lock that blocks writes for the entire build; CONCURRENTLY builds the index without blocking your application. It's slower and can't run inside a transaction block, but it won't take your app down.

Quotable: On a production database, always CREATE INDEX CONCURRENTLY. A plain CREATE INDEX blocks writes for the entire build.

---

Cause #2: An unindexed foreign key

This deserves its own section because it surprises almost everyone.

Postgres automatically creates an index for primary keys and unique constraints. It does NOT automatically create one for foreign keys.

So if order_items.order_id references orders(id) and you never explicitly indexed order_items.order_id, two bad things happen:

1. Every join or filter on order_id is a sequential scan.
2. Every delete or update of a parent row forces Postgres to scan every child table to enforce the foreign key constraint — without an index, that's a full scan, frequently while holding locks. On a busy table, parent deletes turn into lock storms.

How to find every unindexed foreign key

SELECT c.conrelid::regclass AS table_name,
       a.attname            AS column_name
FROM   pg_constraint c
JOIN   pg_attribute a
       ON a.attrelid = c.conrelid
      AND a.attnum   = ANY(c.conkey)
WHERE  c.contype = 'f'
AND    NOT EXISTS (
         SELECT 1
         FROM   pg_index i
         WHERE  i.indrelid = c.conrelid
         AND    a.attnum   = ANY(i.indkey)
       );

How to fix it

One index per offending column:

CREATE INDEX CONCURRENTLY idx_order_items_order_id
  ON order_items (order_id);

In real applications, indexing a single hot foreign key is routinely worth a 100x improvement on the affected queries — we've seen a join drop from ~340ms to under 3ms from this one change.

Quotable: Postgres auto-indexes primary keys but not foreign keys. An unindexed foreign key is the most common single cause of a slow Supabase or Neon app.

---

Cause #3: Config left at Postgres's tiny defaults

Postgres ships with conservative defaults so it can boot on almost any machine. On a real instance, several of them are actively hurting you.

shared_buffers stuck at 128MB

shared_buffers is Postgres's dedicated page cache. The default is 128MB — sized so Postgres can start on a Raspberry Pi. On an instance with several gigabytes of RAM, 128MB means your hot tables and indexes don't fit in cache, so Postgres re-reads them from disk constantly.

Check it:

SHOW shared_buffers;

If it says 128MB and you have real RAM, raise it to roughly 25% of total RAM:

ALTER SYSTEM SET shared_buffers = '1GB';   -- then restart

(shared_buffers requires a restart to take effect. On Supabase/Neon/RDS this is managed through their dashboard or parameter groups rather than ALTER SYSTEM directly, but the target is the same.)

random_page_cost still at 4 on SSD

random_page_cost tells the planner how expensive a random disk read is relative to a sequential one. The default of 4 assumes a spinning disk. Every modern cloud provider — Supabase, Neon, RDS — runs on SSD or NVMe, where random reads are nearly as cheap as sequential ones.

When random_page_cost is too high, the planner under-values your indexes and chooses sequential scans even when an index scan would be faster. You can have the right index and still get a seq scan because the planner doesn't believe the index is worth it.

ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();   -- no restart needed for this one

work_mem too low → temp-file spills

work_mem is how much memory a single sort or hash operation may use before it spills to a temporary file on disk. The default is 4MB. When a sort doesn't fit, you get a disk spill, which is brutally slow.

You'll see it in EXPLAIN (ANALYZE, BUFFERS) as:

Sort Method: external merge  Disk: 24096kB

If you see "external merge Disk", that operation spilled. Raise work_mem until it stays in memory:

ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

Caution: work_mem is per operation, not per connection. A single complex query can use it several times over, and it multiplies across concurrent connections. Don't set it to gigabytes — raise it deliberately, watch for spills, and consider setting it per-session for known-heavy reporting queries instead of globally.

Quotable: The three config defaults that quietly cripple Postgres performance: shared_buffers=128MB, random_page_cost=4 on SSD, and a work_mem too small to keep sorts off disk.

---

Cause #4: Dead-tuple bloat

Postgres uses MVCC (Multi-Version Concurrency Control). When you UPDATE a row, Postgres doesn't overwrite it — it writes a new version and marks the old one dead. A DELETE just marks the row dead. Those dead tuples remain until VACUUM removes them, and autovacuum runs in the background to do that.

On write-heavy tables, dead tuples can accumulate faster than autovacuum clears them. Then every scan wades through dead rows, the table balloons on disk (we've seen tables 3x larger than their live data), and your cache fills with garbage. Adding RAM doesn't help — it just caches the bloat faster.

How to find it

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup, 0), 2) AS dead_ratio
FROM   pg_stat_user_tables
ORDER  BY dead_ratio DESC NULLS LAST
LIMIT  10;

A dead_ratio above 0.2 on a large table is worth acting on.

How to fix it

Reclaim space and refresh planner statistics now:

VACUUM ANALYZE my_bloated_table;

Then prevent recurrence by making autovacuum more aggressive on that specific table (don't lower it globally — that wastes resources on calm tables):

ALTER TABLE my_bloated_table
  SET (autovacuum_vacuum_scale_factor = 0.02);

(VACUUM FULL reclaims disk fully but takes an exclusive lock and rewrites the table — reserve it for maintenance windows. Routine VACUUM is non-blocking and usually enough.)

Quotable: "We added RAM and it's still slow" usually means dead-tuple bloat. More memory just caches the dead rows faster. The fix is VACUUM, not hardware.

---

Cause #5: Unused and duplicate indexes

Indexes aren't free. Every index must be updated on every INSERT, UPDATE, and DELETE that touches its columns, and it consumes disk and cache. Teams add indexes "just in case" and never remove them, accumulating an index graveyard that taxes every write for zero read benefit.

Find indexes that have never been scanned

SELECT indexrelname,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0
ORDER  BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 means the index has never been used since stats were last reset. Before dropping, confirm it isn't backing a unique or foreign-key constraint and that your stats window is long enough to be representative.

Drop them safely

DROP INDEX CONCURRENTLY unused_index_name;

CONCURRENTLY here too, so the drop doesn't block reads. Duplicate indexes — two indexes covering the same column set — are the same waste and harder to spot by eye; a tool will catch them faster than you will.

---

Cause #6: N+1 query patterns

Back to the slow-vs-expensive distinction. Pull your top cost centers:

SELECT query,
       calls,
       mean_exec_time,
       total_exec_time
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;

If the top entry has a tiny mean_exec_time but an enormous calls count, that's an N+1: the application fetched a list, then looped to fetch a related row for each item one query at a time. The fix is in the app, not the database — batch the lookups:

SELECT * FROM avatars WHERE user_id = ANY($1);

Collapsing two million tiny queries into a few hundred batched ones routinely drops database CPU from the high-70s to the low-20s overnight.

---

The complete checklist

Run these in order. Items 1, 2, and 3 fix the majority of "Supabase/Neon is slow" cases on their own.

1. ☐ Enable pg_stat_statements if it isn't on.
2. ☐ Find sequentially scanned large tables → add the missing index (CONCURRENTLY).
3. ☐ Find unindexed foreign keys → index every one (CONCURRENTLY).
4. ☐ Check shared_buffers — if it's 128MB, raise to ~25% of RAM.
5. ☐ Check random_page_cost — if it's 4 on SSD, set to 1.1.
6. ☐ Check for temp-file spills → raise work_mem (carefully).
7. ☐ Measure dead_ratio → VACUUM ANALYZE and tighten autovacuum on bloated tables.
8. ☐ Find unused/duplicate indexes → DROP INDEX CONCURRENTLY.
9. ☐ Sort pg_stat_statements by total_exec_time → fix N+1 patterns in the app.
10. ☐ Re-measure and confirm the win.

---

A worked example

A real Supabase app: the orders page took 4 seconds. The cause was two of the items above stacked.

• order_items.order_id was an unindexed foreign key → a seq scan over 1.4M rows on every load. Indexing it dropped that path from ~340ms to under 3ms.
• shared_buffers was at the 128MB default and random_page_cost was at 4 → the working set kept falling out of cache and the planner avoided indexes. Raising shared_buffers and setting random_page_cost = 1.1 fixed both.

Final result: 4,000ms → 180ms. No new hardware. No rewrite. Three lines of SQL. That's typical, not lucky.

---

You don't have to do this by hand

Every diagnostic above is a read-only query against the system catalogs and pg_stat_statements. The hard part isn't running them — it's knowing the right thresholds (a dead_ratio of 0.2 matters on a 50M-row table and is noise on a 200-row one) and knowing which finding to fix first.

That's exactly what pg-checkup automates. You run a single read-only collector script, paste the JSON, and get a letter grade (A+ to F) plus a ranked list of findings — each with copy-paste remediation SQL and the expected win. It never asks for your connection string and never stores your data.

If your Postgres feels slow, run a free scan and find out what grade it gets. Worst case, you walk away with the checklist above already run for you.