Unbounded Result Sets

An unbounded result set is a query that returns an open-ended number of rows — no LIMIT, no pagination — and an application that loads them all into memory at once. SELECT * FROM events works perfectly when the table has 50 rows and becomes a time bomb as it grows to millions. The query's cost and memory footprint scale with table size, which only ever increases.

Why It Happens

During development the table is tiny, so loading everything is fast and convenient. "Get all the records" is the natural way to express many features. Filtering or sorting is sometimes done in application code after fetching everything, which requires pulling the whole table. The growth that makes this dangerous happens slowly in production, long after the code shipped.

Why It Hurts

Loading an ever-growing table into memory eventually exhausts heap and crashes the process with an out-of-memory error. Before it crashes, it inflates latency and garbage-collection pressure, and ties up a connection while transferring huge result sets. The failure is a scalability cliff: stable for months, then sudden and total once data crosses a threshold. Filtering in the application after fetching everything wastes database I/O and network bandwidth on rows that are immediately discarded. Because the trigger is data volume rather than a code change, the cause is often hard to diagnose during the incident.

Warning Signs

• Queries omit LIMIT and return all matching rows.
• Code loads an entire table or large query result into a list in memory.
• Filtering or sorting happens in application code over a full fetch.
• Out-of-memory errors or latency spikes correlate with data growth, not deployments.

Better Alternatives

Bound every potentially large query. Use pagination to return fixed-size pages, and prefer keyset (cursor) pagination over large OFFSETs because OFFSET still scans skipped rows and degrades on deep pages. For processing large datasets, use streaming or server-side cursors that read rows in chunks with constant memory rather than materializing everything. Push filtering and sorting into SQL so the database returns only the rows you need.

How to Refactor Out of It

Audit queries for missing LIMIT clauses and code that materializes whole tables. Add pagination to user-facing list endpoints, using keyset pagination for large or deep datasets. Convert bulk processing jobs to stream rows in batches with bounded memory. Move application-side filtering into WHERE clauses. Add guardrails such as a maximum row cap and tests that run against production-scale data so unbounded queries fail in CI rather than in production.