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
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?