Time entries are append-only. Nobody deletes them, every person adds several a day, and after two years a mid-sized team has a few million rows.

The timesheet view was written when the table had four thousand rows and it was fast. It is still fast on page one. Page eight hundred takes four seconds, and the report that walks the whole table for an export times out.

Why offset degrades

OFFSET does not mean “jump to row 50,000”. The database has no way to jump — it reads rows in order, counts them, throws away the first 50,000 and returns the next 50.

So the work grows linearly with how deep you page. Page one costs 50 rows, page 1,000 costs 50,050.

This is why it is never caught before production. Nobody opens page 1,000 in testing, and the first page stays fast forever.

Keyset pagination

Instead of “skip 50,000 then give me 50”, ask “give me the 50 that come after this row”. The database seeks straight to that point in the index and reads 50 rows. The cost is the same whether it is page two or page two thousand.

The one detail that matters: include a tiebreaker.

Sorting by timestamp alone breaks when two rows share a timestamp, which in a time tracker is common — a bulk import, or two entries created in the same second. Rows get skipped or repeated across page boundaries, and the bug is intermittent enough to be blamed on anything.

Compare the tuple of sort column and primary key together. Most databases support the row-value syntax directly, and where they do not, the expanded form with an OR is equivalent.

The three awkward parts

No page numbers. Keyset gives you next and previous, not “jump to page 40”. Usually fine — nobody meaningfully navigates to page 40 of a timesheet — but it is a real interface change, and the answer to “jump to page 40” is almost always a date filter instead.

Going backwards. Previous means reversing the comparison and the sort, then reversing the result set before display. It works, it is fiddly, and it is where most keyset implementations have their bug.

Total count. SELECT COUNT(*) on a large table is its own slow query, and it is usually displayed next to the pagination for no reason anybody can defend. Either drop it, or compute it separately and cache it — the user wants to find a row, not know there are 2,847,391 of them.

Filters change the index you need

A timesheet is rarely unfiltered. It is one person, or one project, or one month.

That filter has to be the leading column of the index, with the sort columns after it. An index on the timestamp alone does not help a query filtered by user and sorted by time — the database either scans or sorts in memory, and you are back where you started.

Check the query plan for the filters people actually use, not the unfiltered case. The unfiltered case is the one nobody runs.

Exports are a different problem

Keyset fixes the interface. It does not fix “export this year to CSV”, which wants every row.

Do not page through an export. Stream it: run one query, iterate the cursor, write rows as they arrive. The memory stays flat whether it is a thousand rows or a million, and there is no pagination to get wrong.

If the export is large enough to time out the request, it belongs in a background job that writes the file and emails a link. That is a bigger change and it is the correct one — a user watching a browser tab for four minutes will reload it, and now the job is running twice.

Retention helps, up to a point

Screenshots and raw activity samples age out on a schedule, which keeps those tables bounded.

Time entries do not. They are the business record and they have to survive for years. So the timesheet table only ever grows, and that is correct — which is exactly why the pagination has to be the kind that does not care how big it gets.