Pagination and Count

Pagination controls the number of rows returned for an API resource response. Combined with the count, you can traverse all the rows of a response.

Limits and Pagination

PostgREST uses HTTP range headers to describe the size of results. Every response contains the current range and, if requested, the total number of results:

HTTP/1.1 200 OK
Range-Unit: items
Content-Range: 0-14/*

Here items zero through fourteen are returned. This information is available in every response and can help you render pagination controls on the client. This is an RFC7233-compliant solution that keeps the response JSON cleaner.

Query Parameters

One way to request limits and offsets is by using query parameters. For example:

GET /people?limit=15&offset=30 HTTP/1.1

This method is also useful for embedded resources, which we will cover in another section. The server always responds with range headers even if you use query parameters to limit the query.

Range Header

You can use headers to specify the range of rows desired. This request gets the first twenty people:

GET /people HTTP/1.1
Range-Unit: items
Range: 0-19

Note that the server may respond with fewer if unable to meet your request:

HTTP/1.1 200 OK
Range-Unit: items
Content-Range: 0-17/*

You may also request open-ended ranges for an offset with no limit, e.g. Range: 10-.

Counting

In order to obtain the total size of the table (such as when rendering the last page link in a pagination control), you can specify a Prefer: count=<value> header. The values can be exact, planned and estimated.

This also works on views and Table-Valued Functions.

Exact Count

To get the exact count, use Prefer: count=exact.

HEAD /bigtable HTTP/1.1
Range-Unit: items
Range: 0-24
Prefer: count=exact

Note that the larger the table the slower this query runs in the database. The server will respond with the selected range and total

HTTP/1.1 206 Partial Content
Range-Unit: items
Content-Range: 0-24/3573458

Planned Count

To avoid the shortcomings of exact count, PostgREST can leverage PostgreSQL statistics and get a fairly accurate and fast count. To do this, specify the Prefer: count=planned header.

HEAD /bigtable?limit=25 HTTP/1.1
Prefer: count=planned

HTTP/1.1 206 Partial Content
Content-Range: 0-24/3572000

Note that the accuracy of this count depends on how up-to-date are the PostgreSQL statistics tables. For example in this case, to increase the accuracy of the count you can do ANALYZE bigtable. See ANALYZE for more details.

Estimated Count

When you are interested in the count, the relative error is important. If you have a planned count of 1000000 and the exact count is 1001000, the error is small enough to be ignored. But with a planned count of 7, an exact count of 28 would be a huge misprediction.

In general, when having smaller row-counts, the estimated count should be as close to the exact count as possible.

To help with these cases, PostgREST can get the exact count up until a threshold and get the planned count when that threshold is surpassed. To use this behavior, you can specify the Prefer: count=estimated header. The threshold is defined by db-max-rows.

Here’s an example. Suppose we set db-max-rows=1000 and smalltable has 321 rows, then we’ll get the exact count:

HEAD /smalltable?limit=25 HTTP/1.1
Prefer: count=estimated

HTTP/1.1 206 Partial Content
Content-Range: 0-24/321

If we make a similar request on bigtable, which has 3573458 rows, we would get the planned count:

HEAD /bigtable?limit=25 HTTP/1.1
Prefer: count=estimated

HTTP/1.1 206 Partial Content
Content-Range: 0-24/3572000