Paginating admin tables without a client library

An admin table starts as select * from users. It is fine for months. Then a page load takes nine seconds and the browser holds forty thousand rows, and the first fix people reach for is a table library with client-side paging.

You do not need one. Pagination is a limit, a where and a link. The only real decision is offset or keyset, and an admin panel needs both.

Users: offset pages with a total

select id, email, name, role, created_at
from "user"
where email ilike $1 or name ilike $1
order by created_at desc, id desc
limit 20 offset 40;                               -- page 3

select count(*) from "user" where email ilike $1 or name ilike $1;

Offset is usually the wrong answer, and here it is the right one:

• Operators expect "41 to 60 of 134" and page numbers. They jump to page 7, share a link to it, and read the total as a fact about the business.
• Hosted auth providers only page by offset. Clerk's getUserList takes limit and offset and returns totalCount. If one provider can only do offset, the shared UI does offset.
• The table is small and people search. A users table in the tens of thousands, filtered by a search box, never reaches the depths where OFFSET gets slow. Cap the page number anyway (10,000 is a hand-edited URL, not a person) so nobody can ask for a slow query on purpose.

Two details make it correct:

• A unique tiebreaker. order by created_at desc, id desc. Without id, two accounts created in the same instant swap places between requests and one of them never shows.
• The same where for rows and count. Build it once and use it twice. A total that counts something the rows do not is a bug report waiting to be filed.

Search with ILIKE and escape the input first: %, _ and \ are wildcards, so a search for dara_x also finds daraYx. Check what your ORM does. Drizzle's ilike() passes your pattern through as written; Prisma's contains with mode: "insensitive" adds the % but escapes nothing.

The audit log: keyset cursors

The audit log is the opposite case: it only grows, it gets big, people read the newest entries, and new rows arrive while someone pages. Offset is wrong twice over there: cost grows with depth, and a row inserted at the top shifts every page, so entries repeat or vanish.

select id, action, actor_id, entity_id, metadata, created_at
from audit_log
where (created_at, id) < ($1::timestamptz, $2::uuid)   -- the cursor
order by created_at desc, id desc
limit 51;                                              -- one extra: is there more?

• The row comparison (created_at, id) < (...) is one condition Postgres can answer from an index on created_at.
• Fetch limit + 1 rows to know whether to show "Older entries" without a count(*), which on a large log costs more than the page.
• Carry the timestamp as text with microseconds. Postgres stores timestamptz to the microsecond; a JavaScript Date keeps milliseconds. A cursor built from a Date skips every row written in the same millisecond as the last one shown. Select to_char(created_at at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') for the cursor.
• Keyset gives "Older" and "Newest", not page numbers. For a log that is right: nobody wants page 212, they filter.

State lives in the URL

/admin/users?q=acme&role=admin&page=3
/admin/audit?action=admin.user.&person=ana%40x.io&cursor=MjAyNi0...

• The page stays a Server Component with no client state.
• A filtered view is a link you can paste into a ticket, and the back button works.
• Changing a filter resets to page one. A cursor or page number is only valid for the filter that produced it.
• Every param is hostile input. Parse with a schema and fall back to "no filter" on anything odd: a mangled link should show page one, not a 500. Encode the cursor (base64url of timestamp|id) and validate both halves, so a forged value never reaches a ::uuid cast.
• Use real links for pages. They work without JavaScript, open in a new tab and prefetch.

Checking your work

• explain analyze on the audit query with a deep cursor: an index scan and 51 rows, not a sequential scan.
• Insert audit rows while paging: no duplicate, no gap.
• A search for _ or % matches only those characters.
• ?page=abc, ?page=-1 and a truncated cursor all render the first page.
• The total under the users table matches the rows when you page to the end.