How to get COUNT(*) in Supabase

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:

// Planner estimate on large results, exact COUNT(*) on small ones
const { count } = await supabase
  .from('events')
  .select('*', { count: 'estimated', head: true })

• planned returns the query planner's estimate. It is effectively instant but can be stale until the table is analyzed.
• estimated uses 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.length as 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#

• Stack Overflow: How to get "COUNT(*)" in Supabase
• Supabase docs: select() and count option

Why count can be lower than expected — RLS applies to it too#

Every failure mode covered above assumes the count you get back reflects the rows that actually exist. It doesn't have to. count: 'exact' runs the same COUNT(*) a normal SELECT would, over the same rows the connecting role is allowed to see — which means Row Level Security policies filter the rows a count sees exactly like they filter rows a regular select() returns. If a table has RLS enabled and a policy that only lets a user see their own rows, count for that request returns the count of their rows, not the table's total, even though nothing in the count-related code looks wrong.

This produces a specific, confusing symptom: the count from the client SDK looks suspiciously low (or comes back 0) while a query run as the service_role — which bypasses RLS — shows the real total. That's not a bug in the count option; it's RLS doing exactly what it's supposed to do. The fix isn't in the count call at all — it's in the policy, and working through why a policy scopes fewer rows than expected is exactly what Debugging Supabase RLS Policies: A Simple Checklist walks through step by step. Before assuming a count query is broken, rerun the same filter as a plain select() with the anon/authenticated client (not service_role) and compare the row count you actually get back against what count reports — if they match, the count option is working correctly and the real question is whether the RLS policy should be scoping that tightly in the first place.

Counting distinct values or across a join needs an RPC, not the count option#

The count option on select() does exactly one thing: COUNT(*) over the rows that match your filters on that table. It cannot express COUNT(DISTINCT column), and it cannot count rows across a join condition that spans more than the embedded-resource relationship PostgREST already understands. If you need "how many distinct customers placed an order this month" or "how many orders reference a product from a specific category," reaching for .select('*', { count: 'exact' }) will not get you there — you will either get the wrong number or an error, because there is no client-side option that maps to a DISTINCT or a custom JOIN ... GROUP BY. The same embedding layer has another sharp edge: if two foreign keys connect the same pair of tables, PostgREST cannot guess which one you mean and throws "more than one relationship was found" until you disambiguate it in select().

The documented way to get that number from Supabase is a Postgres function exposed through supabase.rpc():

create function distinct_customer_count(month_start date)
returns bigint
language sql
stable
as $$
  select count(distinct customer_id)
  from orders
  where created_at >= month_start
    and created_at < month_start + interval '1 month';
$$;

const { data, error } = await supabase.rpc('distinct_customer_count', {
  month_start: '2026-09-01',
})

This isn't a workaround — it's the intended pattern any time the count logic is more than "how many rows match these filters." The Supabase join guide covers the embedded-resource query shape this complements: use PostgREST's embedded resources when you need related rows returned, and drop to a SQL function the moment you need an aggregate that isn't a plain filtered COUNT(*).