I’m building a REST API backed by PostgreSQL. I'm encountering something I don’t think is very niche, but I’m having trouble finding any discussion of it. Good REST API practice dictates that if a request comes in for a collection, e.g.
That you respond with a limited number of results (say, 100), and allow users to iterate over it with query string parameters, like
limit=100&offset=200 (which could directly translate to SQL
What I’m struggling to implement is a row count to tell the consumer how many resources match their query. The two options I have come up with are:
- Use a
- Issue a second query, for just the count that matches this query
COUNT window function
This would look something like:
SELECT *, COUNT(*) OVER () AS count FROM orders WHERE filters LIMIT 100;
Now I have a row count that does accurately tell me how many rows are involved (I don’t think this is affected by the limit/offset clauses, but I only think that because I tried a few times manually).
But this feels wasteful—every row I get back has a count that I only needed once, not with every row. This is wasteful of network bandwidth.
Issue a second query
In this case, I would issue two queries:
SELECT * FROM orders WHERE filters LIMIT 100; SELECT COUNT(*) FROM orders WHERE filters GROUP BY columns;
This also works, in the sense that it gives me an accurate answer. But this too feels wasteful! Now we’re wasting database resources by issuing the same query again. If this is an expensive query, I would be worried about impacting performance this way. I suppose the database will cache the results somewhat, but this is a detail I don’t love relying on.
I suppose what I’m looking for is a way to set up a transaction and say “for one query, give me just the output, but while you still have the data loaded, give me the count”. I fully recognize that as I type this, it sounds slightly ridiculous. But I also feel like this is a reasonably common use case, that I can’t imagine I’m the first to struggle here. Is there a better way?