Decouple count=estimated and db-max-rows

[wolfgangwalther]

Currently to use count=estimated we'll need to use db-max-rows. But db-max-rows applies to all requests, which is pointless.

I want to be able to:

• Download the full result set without any limit applied (up to 300k rows in my case)
• Have the ability to use count=estimated for a much lower limit, when I paginate through the resultset.

Maybe we can introduce an option to pass the "estimation limit" to the prefer header directly?

Something like Prefer: count=estimated(1000), which would apply the LIMIT 1000 only inside the pgrst_source_count CTE:

pgrst_source_count AS (
  SELECT 1
  FROM "pgrst_source" AS "training_report"
  LIMIT 1000
)

[wolfgangwalther]

It seems like the LIMIT is already applied inside pgrst_source_count for regular requests. In my case I am requesting from an RPC, and it seems db-max-rows is not applied at all.

[wolfgangwalther]

It seems like the LIMIT is already applied inside pgrst_source_count for regular requests.

Thus, it would then be more like "apply a LEAST(1000, db-max-rows)" limit inside the count CTE.

[wolfgangwalther]

Something like Prefer: count=estimated(1000),

Alternatively, just a new config option db-max-count, which defaults to db-max-rows, but allows us to set the max count limit without enforcing a max-rows limit in general.

[steve-chavez]

Perhaps db-max-count could solve https://github.com/PostgREST/postgrest/issues/3576 too.