Schema cache speed went down after refactor

[steve-chavez]

          > This has happened somewhere outside this PR on main though.

Found the loadtest in which head and main started to differ: https://github.com/PostgREST/postgrest/actions/runs/9846138600/job/27183597190

After some local tests, I think the commit f31848f is where the throughput started to go down (the previous one returned similar values to v12.2.3):

param	v12.2.3	735e1ed	f31848f	86c3257
throughput	468	462	420	437

Originally posted in #3640 (comment)

[steve-chavez]

I think we should try to fix this or otherwise revert f31848f. Reasons:

• We already have an issue with the schema cache perf Large schema cache reloads cause 100% CPU spike when schema cache metadata is accessed. #3046.
• Losing perf sets up a bad precedent for new PRs feat: produce OpenTelemetry traces with hs-opentelemetry #3140 (comment).

[wolfgangwalther]

Note that this is only about the root endpoint / OpenAPI schema, not about any regular requests. And it's also not about the schema cache, imho.

What we need here: Better reporting for the loadtest results, so that we can interpret them better.

We already have an issue with the schema cache perf

This is 100% not related to the SQL queries, but to the haskell processing of the result set.

[steve-chavez]

This is 100% not related to the SQL queries, but to the haskell processing of the result set.

Hm, so on f31848f, changing the queries resulted in more Haskell processing?

(It's not apparent by looking at the code change)

[steve-chavez]

Note that this is only about the root endpoint / OpenAPI schema, not about any regular requests. And it's also not about the schema cache, imho.

I always understood the schema cache as: SQL queries + Haskell processing, this is why now we even log the latter time too: #3779. So in that way, it is about the schema cache.

[wolfgangwalther]

No, but the OpenAPI request on the root endpoint also runs SQL queries. Those are slower now, that's why we lose performance here. But when we migrate away from OpenAPI-in-core, that will change entirely anyway.

The SQL queries for the schema cache are also slower, yes. But the SQL queries make up only a tiny percentage of the Schema Cache load time, I think. So it will not slow down the overall Schema Cache load time.

[wolfgangwalther]

I always understood the schema cache as: SQL queries + Haskell processing, this is why now we even log the latter time too: #3779. So in that way, it is about the schema cache.

#3046 shows spikes in CPU usage for PostgREST, not PostgreSQL. If the query was the problem, PostgreSQL resource usage would be spiking, not PostgREST.

While debugging #3733 it was very clear that the Schema Cache queries are almost always fast and it's the processing in PostgREST that takes time. Most likely for relationships.