Handling huge requests

[AirTobe91]

Hello,

I'm currently thinking about using Postgrest for requesting data via REST API from our PostgreSQL/TimescaleDB instance.
We have a huge amount of data inside and I'm wondering how Postgrest is handling requests which queries huge datasets like >1M rows sometimes even >5M rows.
For sure the request will last a little bit, but is it able to handle it or are there some limitations in respect to REST response?

Thank you for your help.

BR, Tobias

[steve-chavez]

For now pagination(limit/offset) is a must as we still need to improve perf for large result sets(#2770).

It'd be great if you could do some testing and provide feeback on the issue. We need reproducible examples.

[wolfgangwalther]

As a data point: Some of my setups serve files up to 1 GB via PostgREST just fine.

[steve-chavez]

@wolfgangwalther Hm, but if you have 10 concurrent users requesting those files, PostgREST will need 10GB of memory right?

With regular small/medium rows, we have LIMIT/OFFSET to regulate memory consumption.. but we cannot do the same for big columns.

How can we solve this? (related #2770) I wonder if it's just a matter of docs.

• For big json/jsonb columns: the user can split the big json using arrow operators (with care to avoid unnecessary deTOASTing as mentioned here) and create a view. Or perhaps using a function to allow querying the big json column but not return it.
• For big text columns: if the user wants the full text, perhaps a function that returns text/plain and has arguments to return the text in chunks? Then the user would have to make multiple requests.. should we do this process ourselves somehow? Perhaps with an special content-type over a table?
• For big bytea columns: the process should be similar to big text, I think.

Lastly, I wonder if we can provide some metric to tell users they're going to run into this problem on some API requests. Or perhaps a safeguard.

[steve-chavez]

For big bytea/text, looks like we can just use substring.

For json there's nothing native it seems, but there's ndjson for json streaming.

I'm not sure how we would solve the problem of the resource being mutated while the response is streaming. Maybe we need Etag support for that. A while ago I found https://github.com/ergo70/pg_etag.

[wolfgangwalther]

For non-json columns, it would be great if we could avoid the aggregation entirely and stream the data from the database through PostgREST - without ever holding a full copy of the response. The user would then have to set up some kind of chunked response - either by storing their files in chunks themselves or maybe by reading from the toast table directly (not sure if that works).

[ggregoire]

I have a related issue, not sure if you can give me some hints @steve-chavez @wolfgangwalther

I have a function (i.e. POST /rpc/...) that returns a generated series of 3600 rows. Each row contains 2 columns, a timestamp and a base64 string containing ~30,000 chars. From what I see in my browser, the response is about 100 MB.

The query by itself is really fast, postgres executes it in less than 1 second. Doing an EXPLAIN ANALYZE confirms that it is really fast.

But it takes about 20-30 seconds for PostgREST to return the JSON to the browser (in green below).

I tried adding more cores to the instance but it doesn't seem to help.

Is there anything else I could do to get the response faster?

Edit: not really sure what changed, but it's way faster now, it takes about 5 sec.

Maybe it's because we were several persons calling this endpoint and I think I'm the only one right now.

[wolfgangwalther]

The query by itself is really fast, postgres executes it in less than 1 second. Doing an EXPLAIN ANALYZE confirms that it is really fast.

Which query - just calling your function with a SELECT * FROM func() or the full query that PostgREST actually runs?

[steve-chavez]

I tried adding more cores to the instance but it doesn't seem to help.

Adding more memory should help, as PostgREST will store the result set in memory before sending it to the user. We rely on LIMIT/OFFSET to regulate that, but that's not available for big columns.

Maybe you could try chunking your big text and doing multiple requests too, as mentioned on #2831 (reply in thread).

[ggregoire]

@wolfgangwalther both actually, when I call the endpoint I don't see the query generated by PostgREST in pg_stat_activity. It's probably under the second in execution.

As additional info, the results of my function are always 3,600 rows, but sometimes some of the rows have NULL values inside the TEXT column that is supposed to contain ~30,000 characters strings. If this is the case for a lot of rows, then the response is way smaller, like 30 MB instead of the usual 100 MB. In this case, I noticed that PostgREST is really fast to return the 3,600 rows to the browser. So it looks like it's indeed something related to the size of the response and/or the number of large strings in the response.

[ggregoire]

@steve-chavez I was indeed thinking about returning 20 min instead of 1 hour of data and then make 3 requests to get the full hour. It's more logic on the frontend but it should help as you said, since it seems fast when the response is 30 MB.