# PostgREST Stops at 1,000 Rows and the Dashboard Never Says So

> PostgREST silently caps unpaged selects at 1,000 rows. An admin dashboard counting synced jobs in memory showed 0 for newer users once the table grew past that limit.

- Author: Dil Ahdan (https://dil.dev/)
- Published: 2026-11-01
- Updated: 2026-11-01
- Tags: supabase, postgrest, debugging, admin
- Canonical: https://dil.dev/notes/supabase-1000-row-cap-silent-count/

## Key takeaways

- PostgREST returns at most 1,000 rows from an unpaged select and gives you no error, no warning and no indication the result is truncated.
- Counting in memory after a bulk fetch is only correct while the table stays small enough to fit inside that cap.
- The fix is count: 'exact', head: true per displayed user, which asks the database for a single integer and is independent of table size.
- Limiting the displayed list before counting (10 most recently seen users) keeps the fix cheap at any scale.
- A regression test that fakes the 1,000-row cap is the only reliable way to catch this class of bug before it reaches production.

PostgREST caps unpaged selects at 1,000 rows and returns no error when it hits that limit. If your code counts the rows it gets back, it will produce the right answer right up until the table grows past 1,000, and then it will silently produce the wrong one. That is exactly what happened to the CargoMode admin dashboard.

## What the Dashboard Was Doing

The admin panel shows recent user activity, including a Synced Jobs column that counts how many successful or partial sync-log entries each user has accumulated.

The original implementation fetched every relevant row from the sync log in a single unpaged select, then counted the results in memory per user. It is a natural thing to write. It works fine in development, where the table has a few dozen rows.

The problem is that it only works while the table stays small.

## Where PostgREST Draws the Line

PostgREST has a default maximum of 1,000 rows for any select that does not include explicit pagination. When a query hits that limit:

- **It returns exactly 1,000 rows.** Not an error. Not a warning. Not a truncation flag.
- **The HTTP status is still 200.** Nothing in the response tells the caller it missed anything.
- **The caller has no way to detect this** unless it explicitly checks whether the count equals 1,000 and treats that as suspicious.

By the time this surfaced in production, the sync-log table had grown to 2,625 rows. PostgREST was returning the oldest 1,000. Newer users had no rows in that slice at all, so the in-memory count for them came back as zero.

## Why Zero Is the Worst Possible Wrong Answer

A wrong number that is obviously wrong, say 4 instead of 4,000, tends to get noticed. Zero looks like a valid state. It looks like the user simply has not synced anything yet.

There was no error in the logs. No exception in the API response. No alert anywhere in the stack. The Synced Jobs column just showed 0 for the newest users, and without knowing the table size at the time the query ran, there was nothing to point at.

This is the same class of problem as a [silent redirect swallowing uploads](/notes/silent-redirect-swallowed-uploads/): the system does something technically valid, returns a success code, and the data loss only becomes visible when you look at the outcome rather than the operation.

## The Fix: One Head Count Per User

The correct approach is to ask the database for a count directly, rather than fetching rows and counting them yourself.

In the Supabase client, that looks like this:

```ts
const { count } = await supabase
  .from('sync_logs')
  .select('*', { count: 'exact', head: true })
  .eq('user_id', userId)
  .in('status', ['success', 'partial'])
```

What `count: 'exact', head: true` actually does:

- **`head: true`** sends a HEAD request instead of a GET. No rows are transferred.
- **`count: 'exact'`** adds a `Prefer: count=exact` header. PostgREST runs a COUNT query and returns the result in the `Content-Range` response header.
- **The result is a single integer.** It does not matter whether the table has 100 rows or 100,000.

The new `server/utils/syncedJobCounts.ts` utility wraps this and is called once per displayed user. The list is also limited to the 10 most recently seen users before counting begins, so the total number of database round-trips stays small and predictable regardless of how large the table grows.

## The Files That Changed

The fix landed in commit `9100d7d9` on 2026-10-15 across three files:

- **`server/api/admin/recent-user-activity.get.ts`**: removed the bulk fetch and in-memory count, added the call to the new utility, added the limit to 10 most recently seen users.
- **`server/utils/syncedJobCounts.ts`**: new file, contains the per-user head count logic.
- **`tests/server/utils/syncedJobCounts.test.ts`**: regression test that fakes the 1,000-row cap.

## The Regression Test

The test is the part I want to spend a moment on, because it is easy to write a test that passes even when the bug is present.

A naive test seeds a few rows and checks the count. That will pass whether you use a head count or a bulk fetch, because the table never gets close to 1,000 rows in a test environment.

The test in `syncedJobCounts.test.ts` fakes the cap. It mocks the Supabase client so that any select returns exactly 1,000 rows, regardless of what the query actually asks for. Then it asserts that a user whose rows would fall outside that 1,000-row window still gets the correct count.

If you revert to the bulk-fetch approach, that test fails. That is the point. The test encodes the specific failure mode, not just the happy path.

## Spotting This Pattern Elsewhere

The 1,000-row cap will bite you anywhere you do all of these things together:

- Fetch from a table that will grow over time
- Do not paginate or add a `.limit()` that is smaller than 1,000
- Derive a number or a list from the rows you get back
- Treat an empty result as a valid state rather than an error

Common places this hides: audit logs, event tables, join tables for many-to-many relationships, anything that accumulates one row per user action. The table looks fine in staging. It looks fine in the first weeks of production. Then it quietly crosses 1,000 and the numbers start drifting.

If you are using Supabase and you are counting things, check whether you are counting rows or counting a count. They are not the same operation once the table is large enough.

## What I'd do differently

The bulk fetch was never the right tool for counting. I should have used a head count from the start, but the table was small enough that it never failed during development or early production, so there was no signal.

The thing I would actually change is the test strategy. I would write the fake-cap regression test at the same time as the feature, not after the bug surfaces in production. It is a small test to write and it makes the constraint explicit in the codebase rather than leaving it as invisible tribal knowledge.

I would also add a runtime check in any bulk-fetch utility that logs a warning when the result length equals exactly 1,000. That is not a guarantee, but it is a cheap signal that something might be truncated, and it would have surfaced this one much earlier.

## Quick answers

**What is the PostgREST default row limit?**

PostgREST caps unpaged selects at 1,000 rows by default. It returns exactly 1,000 rows and no error, so the caller has no way to tell the result was truncated unless it checks for that limit explicitly.

**How do I count rows in Supabase without fetching them all?**

Pass count: 'exact' and head: true to your Supabase query. This sends a HEAD request with a Prefer: count=exact header. The database returns the count in a response header and transfers zero rows, so it works correctly regardless of table size.

**Why did the dashboard show 0 synced jobs for newer users?**

The query fetched every success and partial sync-log row in one unpaged request. Once the table exceeded 1,000 rows, PostgREST returned only the oldest 1,000. Newer users had no rows in that slice, so the in-memory count for them was zero.

**How do you write a regression test for a silent row cap?**

The test in syncedJobCounts.test.ts fakes the 1,000-row cap by mocking the Supabase client to return exactly 1,000 rows regardless of what the query asks for, then asserts that the count for a user whose rows fall outside that window is still correct.

**Does limiting the user list before counting help performance?**

Yes. The fix first narrows the list to the 10 most recently seen users, then fires one HEAD count query per user. That keeps the total number of database round-trips small and predictable, rather than growing with the table.

**Is this a Supabase bug or expected behaviour?**

It is documented PostgREST behaviour. The cap exists to protect the server from accidentally returning enormous result sets. The problem is that nothing in the response signals truncation, so application code that assumes it received everything will silently produce wrong answers.