# Supabase RPC Silently Capped at 1000 Rows (PostgREST)

> PostgREST's max_rows cut my RPC result off at 1,000 rows with no error. Returning one row per group fixed it.

- Author: Curtis Miller
- Published: 2026-10-16
- Categories: gotchas, postgres, supabase
- Canonical URL: https://millarian.com/posts/supabase-rpc-silently-capped-at-1000-rows/

I have a project with an admin dashboard that shows, for each of about 300 data sources, a bar of its last 30 job runs. That's up to around 9,000 rows. Some sources were showing an empty bar on the list page, even though viewing the detail page showed plenty of runs.

## What I saw

The list page got all its data from one Postgres function, called through supabase-js:

```js
const { data, error } = await supabase.rpc("recent_runs");
// error is null, data.length is 1000
```

The call completed successfully with no errors, but it returned exactly 1,000 rows. The detail page queried runs for a single source, so it was never affected. That's why the data looked fine in one place and missing in another. The empty bars were not random either: they were the sources late in the alphabet.

## Why PostgREST truncates RPC results at max\_rows

Supabase's REST API is [PostgREST](https://docs.postgrest.org/en/stable/), and it has a [`db-max-rows`](https://docs.postgrest.org/en/stable/references/configuration.html#db-max-rows) setting that caps how many rows any response can contain. Supabase's default is 1,000. In a local project it shows up in `supabase/config.toml`:

```toml
[api]
max_rows = 1000
```

The cap applies to function calls too. My function looked like this:

```sql
select s.id, r.started_at, r.status
from sources s
cross join lateral (
  select * from runs
  where runs.source_id = s.id
  order by started_at desc
  limit 30
) r
order by s.id, r.started_at;
```

The lateral join is how we get "last 30 per source" without scanning every run. But it returns one row per run, not one row per source. That day it returned 1,441 rows for 308 sources, and it would grow toward 30 x 308 = 9,240. Everything past row 1,000 was silently dropped. Since the result was ordered by source id, the sources at the end got nothing.

## The fix: return one row per group

The row count has to stay bounded by something that doesn't grow with the data. Here that's the number of sources. Aggregate each source's runs into a JSON array so the function returns one row per source:

```sql
drop function if exists recent_runs();

create function recent_runs()
returns table (source_id text, runs jsonb)
language sql
stable
as $$
  select
    s.id,
    coalesce(
      (select jsonb_agg(
                jsonb_build_object('started_at', r.started_at, 'status', r.status)
                order by r.started_at
              )
       from (
         select * from runs
         where runs.source_id = s.id
         order by started_at desc
         limit 30
       ) r),
      '[]'::jsonb
    )
  from sources s
  order by s.id;
$$;

grant execute on function recent_runs() to authenticated;
```

Two details matter here. Changing a function's return type needs `drop function` followed by `create`, because `create or replace` can't change it. The drop also removes the old grants, so we need to run `grant execute` again. And the `coalesce` turns a source with no runs into an empty array instead of `null`, so the client never has to special-case it.

On the client, one line flattens the result back into the shape the UI already used:

```js
const runs = data.flatMap((row) => row.runs);
```

The rest of the UI code didn't change.

## Test the shape, not just the values

My original tests passed because the fixtures had a handful of sources and a few runs each. Nothing came near 1,000 rows. I added three tests for the new function:

- The function returns exactly one row per source.
- A source with no runs gets an empty list, not `null`.
- The 30-run cap per source still holds.

The first one is the guard that matters. As long as the row count equals the source count, the function can't grow past the cap until I have a thousand sources, and that's a number I can see coming.

## Takeaway

PostgREST truncation is silent, and any RPC whose row count grows with your data will eventually hit it. You have three options, in order of preference:

- **Aggregate per group** , as above, so the row count is bounded by the number of groups.
- **Paginate** with `.range()` on the client when the data really is a long flat list.
- **Raise `max_rows`**. This is only a bandaid.

Whichever you pick, write at least one test whose result size scales with the data. A function that is correct on small fixtures can still be wrong in production.