What is the cost of changing the search path in postgres in a schema-per tenant model

I am working on a multi-tenant storage gateway.

Each tenant has his own schema -- the schema for each tenant is a meta-schema so each tenant schema is identical. The DB interactions only occur through a stored procedure API. The stored procedure layer is on the public schema. The stored procedures rely on the search_path being set to ;public.

The application layer prepares statements against the stored procedures in the public schema.

I have a stored procedure for switching the tenant that looks as follows:

CREATE OR REPLACE FUNCTION domains_switch(domain DOMAIN_NAME) RETURNS VOID AS $ BEGIN
  IF EXISTS (SELECT NULL FROM public.domains WHERE domain_name = domain) THEN
    EXECUTE format('set search_path=%s,public;', domain);
  ELSE
    RAISE 'domain "%" does not exist',domain USING ERRCODE = 'AX001';
  END IF;
  RETURN; END;
$ LANGUAGE plpgsql;

plpgsql stored procedures manage plans themselves which means I only need to prepare the api statements once per backend in the gateway. I suspect the stored procedure's internal cached plans are invalidated when I switch the schema.

I am planning to add a layer to the gateway's connection pool management to minimise search_path switches. Is my intuition correct? Is this the right thing to do?

I would say your intuition is correct. Changing the search_path causes Postgres to start from scratch with prepared statements. The manual on PREPARE:


Also, if the value of search_path changes from one use to the next,
the statement will be re-parsed using the new search_path. (This
latter behavior is new as of PostgreSQL 9.3.)

And SQL statements inside PL/pgSQL functions are treated like prepared statements.

Especially while working with connection poolers (same connection stays open), it will help performance if you keep switching the search_path to a minimum.

The extra cost is not too high for simple statements, but it may be a noticeable effect for more complex queries.

StackExchange Database Administrators Q#166471, answer score: 2