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.

GET /orders

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 LIMIT/OFFSET).

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:

  1. Use a COUNT window function
  2. 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:

FROM orders
WHERE filters
LIMIT 100;

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.