Schema loading optimiztion

[yyqgituser]

When the system is busy, PostgREST takes a long time to load DB schema. Are there any ways to optimize schema loading, such as lazy loading, loading from files instead of from the database, or other methods?

[steve-chavez]

When the system is busy, PostgREST takes a long time to load DB schema

Can you provide more information about that? We log the time it took as mentioned on https://docs.postgrest.org/en/v13/references/observability.html#logs, it should show something like:

08/Jul/2025:11:32:20 -0500: Schema cache queried in 16.0 milliseconds
08/Jul/2025:11:32:20 -0500: Schema cache loaded 270 Relations, 232 Relationships, 145 Functions, 15 Domain Representations, 45 Media Type Handlers, 1194 Timezones
08/Jul/2025:11:32:20 -0500: Schema cache loaded in 18.7 milliseconds

Can you share those log traces?

[yyqgituser]

PostgREST takes about one minute to load the schema. I am using PostgREST v10.0.2, so the log is a bit different.

May 28 13:39:49.280430 postgrest[127702]:  starting postgREST...
May 28 13:39:49.802534 postgrest[128062]:  28/May/2025:13:39:49 +0000: Attempting to connect to the database...
May 28 13:39:50.127658 postgrest[128063]:  started postgREST (pid 668)
May 28 13:39:53.159933 postgrest[128062]:  28/May/2025:13:39:53 +0000: Listening on port 3000
May 28 13:39:53.166598 postgrest[128062]:  28/May/2025:13:39:53 +0000: Connection successful
May 28 13:39:53.461876 postgrest[128062]:  28/May/2025:13:39:53 +0000: Config reloaded
May 28 13:39:55.082580 postgrest[128062]:  28/May/2025:13:39:55 +0000: Listening for notifications on the pgrst channel
May 28 13:40:10.524638 postgrest[128062]:  127.0.0.1 - enduser [28/May/2025:13:40:10 +0000] "GET / HTTP/1.1" 503 - "" "..."
....
May 28 13:41:04.301382 postgrest[128062]:  28/May/2025:13:41:04 +0000: Schema cache loaded
May 28 13:41:13.512690 postgrest[128062]:  127.0.0.1 - enduser [28/May/2025:13:41:13 +0000] "GET / HTTP/1.1" 200 - "" "..."

From the postgres log, we can see that most of time is used for executing SQL

May 28 13:40:05.809172 postgres[128335]:  LOG:  duration: 11103.118 ms  execute 2:
May 28 13:40:50.143987 postgres[128335]:  LOG:  duration: 43908.534 ms  execute 3:
May 28 13:40:55.500190 postgres[128335]:  LOG:  duration: 4095.660 ms  execute 4: 
May 28 13:41:03.531651 postgres[128335]:  LOG:  duration: 4701.777 ms  execute 5:

[steve-chavez]

Can you upgrade to latest version? There were many improvements since v10.0.2 and schema cache speed could improve.

Then please share the new traces to check if getting the schema cache is still slow.

[yyqgituser]

Thanks for your suggestion. This issue does not occur every time, and I will update the results after we upgrade to the latest version.