Responses are slow when the schema cache reloads

[steve-chavez]

Problem

Whenever the db is complex, loading the schema cache takes a while (this is #3046). One consequence of that is that requests that use the schema cache are slow:

# $ nix-shell

$ PGRST_SERVER_TIMING_ENABLED=1 PGRST_DB_SCHEMAS="apflora" postgrest-with-postgresql-16 -f test/io/big_schema.sql postgrest-run

14/Mar/2024:11:22:15 -0500: Starting PostgREST 12.1 (160caaf)...
...
14/Mar/2024:11:22:15 -0500: Schema cache queried in 33.7 milliseconds

$ curl 'localhost:3000/tpopmassn?select=*,tpop(*)' -i
Server-Timing: jwt;dur=0.0, parse;dur=0.1, plan;dur=11371.5, transaction;dur=0.6, response;dur=0.0

[]

# see the "plan;dur=11371.5", it's ~11 seconds

(Note that requests that don't use the schema cache, such as curl 'localhost:3000/tpop, aren't affected by this, they're fast)

That is expected of #3046. However, the same problem happens when reloading the schema cache.

postgres=# notify pgrst;

14/Mar/2024:11:26:10 -0500: Attempting to connect to the database...
14/Mar/2024:11:26:10 -0500: Successfully connected to PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.3.0, 64-bit
14/Mar/2024:11:26:10 -0500: Config reloaded
14/Mar/2024:11:26:10 -0500: Schema cache queried in 45.8 milliseconds

$ curl 'localhost:3000/tpopmassn?select=*,tpop(*)'
Server-Timing: jwt;dur=0.0, parse;dur=0.1, plan;dur=12828.9, transaction;dur=0.5, response;dur=0.0

Solution

Use the stale schema cache while waiting for the new one to be loaded.

Edit: The fix is soo simple. And the test is sooo much work.

Related

• Computed Relations slow at first request or after idle #3313 (comment)

[steve-chavez]

Use the stale schema cache while waiting for the new one to be loaded.

This definitely can create some stale schema cache errors. In some cases it'd be better to wait instead of err.

Perhaps we should revert this once #3046 is solved.

[steve-chavez]

In some cases it'd be better to wait instead of err.

This is definitely always the desired behavior I think. It's better to be correct/slower and not let clients run into some non-existent column error. I'll revert back this change.

---

Then perhaps we should remove the below claim on https://postgrest.org/en/v12/references/schema_cache.html#schema-cache-reloading

There’s no downtime when reloading the schema cache. The reloading will happen on a background thread while serving requests.

Or clarify that responses will be slower for a while.

[wolfgangwalther]

This is definitely always the desired behavior I think. It's better to be correct/slower and not let clients run into some non-existent column error. I'll revert back this change.

👍