PostgREST timeouts when loading schema cache in multi-tenant environment with large user base and schemas

[akshaysasidrn]

I am facing an issue where PostgREST times out when attempting to load the schema cache in a multi-tenant PostgreSQL environment with approximately 100k schemas. Each schema has a dedicated PostgreSQL user, and PostgREST is configured to impersonate the corresponding user when querying its schema. The timeout occurs around 60 seconds after the request begins I think.

I have tried changing up the timeouts with SET statement_timeout = '5min'; with no luck

Environment:

• PostgreSQL Version: 13.16
• PostgREST Version: 12.2.0
• Configuration:
  • PGRST_DB_URI: postgres://postgres:xxxxxx@<DB_HOST>:5432/tooljet_db
  • PGRST_DB_ANON_ROLE: postgres (added to debug better)
  • PGRST_DB_POOL_ACQUISITION_TIMEOUT: 120s
  • PGRST_DB_PRE_CONFIG: postgrest.pre_config

CREATE OR REPLACE FUNCTION postgrest.pre_config()
RETURNS void AS $$
 SELECT
   set_config('pgrst.db_aggregates_enabled', 'true', false);
 SELECT
   set_config('pgrst.db_schemas', string_agg(nspname, ','), true)
 FROM pg_namespace
 WHERE nspname LIKE 'workspace_%';
$$ LANGUAGE sql;

PostgREST logs:

08/Nov/2024:12:51:21 +0000: Starting PostgREST 12.2.0 (ec89f6b)...
08/Nov/2024:12:51:21 +0000: Admin server listening on port 3002
08/Nov/2024:12:51:21 +0000: Listening on port 3000
08/Nov/2024:12:51:21 +0000: Attempting to connect to the database...
08/Nov/2024:12:51:53 +0000: Successfully connected to PostgreSQL 13.16 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit
08/Nov/2024:12:52:24 +0000: Listening for notifications on the "pgrst" channel
192.168.65.1 - user_8d18eead-eac3-4f57-ba66-4ec368bfaccf [08/Nov/2024:12:56:44 +0000] "GET /954f82c0-2796-4b04-b520-16deb36a7714?&limit=50&offset=0 HTTP/1.1" 503 - "" ""
192.168.65.1 - user_8d18eead-eac3-4f57-ba66-4ec368bfaccf [08/Nov/2024:12:56:44 +0000] "GET /954f82c0-2796-4b04-b520-16deb36a7714?&limit=50&offset=0 HTTP/1.1" 503 - "" ""
08/Nov/2024:12:57:27 +0000: {"code":"57014","details":null,"hint":null,"message":"canceling statement due to statement timeout"}
08/Nov/2024:12:57:27 +0000: Failed to query the role settings. {"code":"57014","details":null,"hint":null,"message":"canceling statement due to statement timeout"}
08/Nov/2024:12:57:27 +0000: Config reloaded
08/Nov/2024:12:59:30 +0000: {"code":"57014","details":null,"hint":null,"message":"canceling statement due to statement timeout"}
08/Nov/2024:12:59:30 +0000: Failed to load the schema cache. {"code":"57014","details":null,"hint":null,"message":"canceling statement due to statement timeout"}

Are there recommendations for managing statement timeouts and schema configurations in such a large multi-tenant architecture?

[wolfgangwalther]

I have tried changing up the timeouts with SET statement_timeout = '5min'; with no luck

How did you change that?

[wolfgangwalther]

PGRST_DB_ANON_ROLE: postgres

So you are impersonating 100k users, but use the SUPERUSER postgres as the anonymous role?

I really hope this is just your reproducer and not actually happening :D

[steve-chavez]

I've also had a case with multi-tenant dynamic schemas and saw terribly slow schema cache loads (user reported it loaded after 2 hours). This dynamic schemas had a pattern like client-<uuid>. The user was open to changing the multi-tenancy design to single schema with RLS though, so I didn't pursue a solution.

@akshaysasidrn Do your dynamic schemas have a naming pattern?

I'm wondering if we should just add configuration for exclusion of these dynamic schemas, like:

db-excluded-schemas-pattern = 'client-%'

[wolfgangwalther]

Would that help? The way I understand it it's the opposite here: There are 100k exposed schemas, so they all need to be loaded?

[akshaysasidrn]

@steve-chavez Yes, I do have a naming pattern like workspace_%. This is set through a pre_config() function.

CREATE OR REPLACE FUNCTION postgrest.pre_config()
RETURNS void AS $$
 SELECT
   set_config('pgrst.db_aggregates_enabled', 'true', false);
 SELECT
   set_config('pgrst.db_schemas', string_agg(nspname, ','), true)
 FROM pg_namespace
 WHERE nspname LIKE 'workspace_%';
$$ LANGUAGE sql;

In my case, I don't think I can exclude the schemas pattern as I let the users create tables and do CRUD on respective schemas.
Is a single schema with RLS the only way to go with PostgREST there? I will explore that approach then. I wanted to do a separation of tenants within individual schemas for better isolation and security.

[steve-chavez]

There are 100k exposed schemas, so they all need to be loaded?

Right, that wouldn't help.

Is a single schema with RLS the only way to go with PostgREST there? I will explore that approach then.

Yes, that's the only way for now.

Also we're now logging the schema cache load time #3779.