GET /reports?from=2020&to=2026 on 80 million rows is not a SQL puzzle first

Index the date column. Then ask why six years of data must return in one response. Pagination, streaming, and a product constraint beat a heroic query.

GET /reports?from=2020&to=2026 on 80 million rows is not a SQL puzzle first

You see:

GET /api/v1/reports?from=2020-01-01&to=2026-01-01

Table: 80 million rows.

The instinct is to open EXPLAIN and start indexing. That is question two.

First: can the database even find the range?

If from / to filter a date (or timestamp) column, that column needs an index the planner can use. Without it, you are sequential-scanning tens of millions of rows for every report.

That part is boring and mandatory.

Then: why does anyone need six years in one shot?

A report spanning 2020–2026 is not “a slightly bigger SELECT.”

It is a product decision: dump a warehouse into an HTTP response.

Even with a perfect index, returning that much data in one payload will time out, blow memory on the app server, or freeze a browser. Pagination or a stream (cursor, chunked export, background job + file) is the shape that matches the size.

If this endpoint is for an interactive UI, the UI asked for something the backend should refuse or reshape. The “backend challenge” started as a product sentence: “show everything.”

Takeaway

Two questions, in this order:

1. Is the range indexed?
2. Does this request *need* to be unbounded?

Indexes make a range cheap. They do not make a six-year dump a good API.

When the query is enormous, push back on the product. That is backend engineering, not obstruction.