The symptom is usually code like this:
const { data } = await supabase.from('orders').select('*')
const count = data?.length ?? 0
It works on tiny tables, but it is the wrong approach.
The root cause is that COUNT(*) in Supabase comes back as metadata, not as something you should derive by downloading every row first.
The correct pattern
Supabase documents the count option directly on select().
const { count, error } = await supabase
.from('orders')
.select('*', { count: 'exact', head: true })
if (error) throw error
console.log(count)
Use this when you only need the number.
Why head: true matters
Without head: true, you can still request a count:
const { data, count, error } = await supabase
.from('orders')
.select('id', { count: 'exact' })
That returns both rows and count metadata.
With head: true, you are telling Supabase: "give me the count, skip the row payload."
That is usually the better production move for dashboards, pagination totals, and badge counters.
Counting filtered rows
The filters still apply before the count is calculated.
const { count, error } = await supabase
.from('orders')
.select('*', { count: 'exact', head: true })
.eq('status', 'paid')
if (error) throw error
console.log(count)
That gives you the count of paid orders, not the count of the whole table.
Why the naive approach gets expensive fast
Supabase's select docs note that hosted projects return a maximum of 1,000 rows by default unless you change the setting. So even before performance becomes a problem, data.length can be the wrong answer for "how many rows exist?" unless you are intentionally counting only the returned page.
This is exactly why the count option exists.
exact is not free on large tables
count: 'exact' runs a real COUNT(*) over the filtered set on every call. On a
table with a few thousand rows that is nothing. On a table with millions of rows,
an exact count forces Postgres to scan every matching row, and it will quietly
become the slowest part of your dashboard query — you are paying full scan cost
just to render "12,483 results".
Supabase exposes two cheaper modes for exactly this problem:
// Fast, approximate — reads the planner's estimate, no scan
const { count } = await supabase
.from('events')
.select('*', { count: 'estimated', head: true })
-
plannedreturns the query planner's estimate. It is effectively instant but can be stale until the table is analyzed. -
estimateduses the planned estimate for large tables and falls back to an exact count for small ones, giving you a sensible default.
For a "roughly how many" badge on a huge table, estimated is the right call.
Reserve exact for cases where the number must be precise — pagination totals on
moderately sized result sets, invoice counts, anything a user would notice being
off by a few hundred.
The pagination pattern
This is the simplest split:
const pageSize = 20
const from = 0
const to = from + pageSize - 1
const { data, count, error } = await supabase
.from('orders')
.select('id, total, status', { count: 'exact' })
.order('created_at', { ascending: false })
.range(from, to)
if (error) throw error
return { rows: data, total: count ?? 0 }
Rows for the current page. Total for the whole filtered result set. No manual counting hack needed.
The rule to remember
- Need rows and total:
count: 'exact' - Need only the total:
count: 'exact', head: true - Do not use
data.lengthas a stand-in for a database count unless you intentionally mean "rows returned in this response"
For adjacent query tuning:
- Why Your Supabase Queries Are Slow (And Exactly How to Fix Them)
- Database Design and Optimization for Next.js and Supabase Applications
- Next.js Data Fetching Patterns with Supabase: Server Components, Streaming, and Caching
- Next.js Supabase Type Safety Guide
References
Related
Originally published at https://www.iloveblogs.blog
Top comments (0)