Unpaginated Queries Are Loading Entire Tables on Every Request
Why does my API endpoint get slower every week even though the code hasn't changed? Your endpoint is running a query with no LIMIT or cursor, so it returns every row in the table and the response grows linearly with your data. As rows accumulate, the query, the network transfer, and the JSON serialization all get slower. Fix it with keyset pagination and a hard row cap, then confirm with `EXPLAIN ANALYZE` and a load test.
Seen in 14 of 450 scanned projects (3%). Counts projects where at least one production route issued a query with no LIMIT, cursor, or row cap against a table expected to grow.
How to tell you have it
- A list endpoint that used to return in 50ms now takes 2-4 seconds with no code changes
- A detail page pulls tens of thousands of analytics rows and sums them in JavaScript instead of SQL
- The blog or resources index renders every published post in one response while a cron keeps adding more
- An append-only log or export file is re-read and re-parsed from byte zero on every request
- An in-memory cache or map keeps growing and is never evicted, until the process runs out of memory
Why it matters
Any query without a LIMIT clause is implicitly promising to return the whole table forever. That promise is fine at 200 rows and a production incident at 200,000. The failure mode is not a crash, it is a slow creep: response time rises with row count until a page that felt instant in staging times out in production during a traffic spike.
Aggregating in application code instead of SQL doubles the cost. The database has to serialize every row to the wire, the app has to deserialize it, and only then does it sum or group what SQL could have reduced to one row. This also multiplies memory pressure on the app server, which is usually the smaller, more expensive-to-scale machine.
Offset-based pagination (LIMIT 20 OFFSET 40000) does not fix this either. Postgres still has to scan and discard the first 40000 rows to find the next page, so the deep pages of a paginated list stay slow even after pagination is added. Keyset pagination, ordering and filtering on an indexed column like id or created_at, avoids the scan entirely.
Unbounded in-memory caches and maps are the same bug wearing a different hat: nothing ever tells the process how much of a resource it is allowed to hold. A cache with no eviction policy is a memory leak with good intentions, and it eventually forces a restart or gets OOM-killed under load.
How Heygents detects it
Deep Scan reads the repository for route handlers and ORM/query calls that lack LIMIT, cursor, or offset parameters, and greps for in-memory Map or object caches with no size cap or TTL. Where a database connection is available it runs read-only EXPLAIN ANALYZE against the suspect queries and checks table row counts with psql to confirm the query cost scales with table size.
How to fix it
- Confirm the query has no bound Run EXPLAIN ANALYZE on the exact query the endpoint issues and compare the row estimate to the actual table size. If the planner reports a sequential scan returning the full row count, you have confirmed the bug before touching any code.
- Switch to keyset pagination Replace OFFSET with a WHERE clause on the last seen indexed column. This keeps every page's cost roughly constant instead of growing with page depth, because Postgres can use the index to jump straight to the cursor position.
- Move aggregation into SQL If a detail page is pulling raw rows to sum or group in JavaScript, do it in the query instead. This turns a multi-thousand-row transfer into a single-row response and lets Postgres use indexes the application code never could.
- Add a hard cap and evict caches Even with pagination, enforce a server-side max page size so a client can't request LIMIT 1000000. For in-memory maps used as caches, add a max size and TTL so old entries are evicted instead of accumulating for the life of the process.
- Load-test the fix Seed a copy of the table to a realistic future size, then hit the endpoint under concurrency to confirm response time stays flat as row count grows, not just correct on today's small dataset.
Confirm the query has no bound
EXPLAIN ANALYZE SELECT * FROM <table> ORDER BY created_at DESC;Switch to keyset pagination
SELECT id, title, created_at
FROM <table>
WHERE created_at < $1
ORDER BY created_at DESC
LIMIT 20;Move aggregation into SQL
SELECT date_trunc('day', occurred_at) AS day, count(*) AS events
FROM <table>
WHERE occurred_at > now() - interval '30 days'
GROUP BY 1
ORDER BY 1;Add a hard cap and evict caches
const MAX_PAGE_SIZE = 100;
const pageSize = Math.min(Number(req.query.limit) || 20, MAX_PAGE_SIZE);Load-test the fix
npx autocannon -c 20 -d 30 https://example.com/api/<endpoint>Find this in your own projects, automatically
Heygents runs a read-only Deep Scan across every project you own, finds issues like this one, and hands you a ready-to-run fix an AI agent can execute and verify. A solo developer gets the audit, the backlog and the fix loop in one place.
Open Heygents →Frequently asked questions
Isn't OFFSET pagination good enough for a small table?
It works fine while the table is small, but it degrades silently. There's no forcing function that tells you when OFFSET pagination has crossed from fine to slow, so teams usually find out from a support ticket rather than a test. Keyset pagination costs the same to build and never has this failure mode, so it's worth doing from the start.
Do I need to paginate an internal admin-only endpoint too?
Yes, especially that one. Admin tools are often the least tested and most likely to be pointed at the full production table by someone debugging an incident, which is exactly when you can't afford a multi-second query competing for database resources with live traffic.
How do I pick between keyset pagination and a materialized view?
Keyset pagination fixes list endpoints where users page through recent-first results. A materialized view is the better fit for expensive aggregate dashboards that don't need to be real-time, since you can refresh it on a schedule instead of recomputing on every request.
What's a safe hard cap for page size?
Somewhere between 50 and 200 rows per page covers nearly every UI use case. Anything a client claims to need above that is almost always better served by a dedicated export or reporting endpoint that streams results instead of returning them as one JSON blob.