4

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

  • What is a typeless graph storage/incremental event processing engine? And how is it relevant to the question? – Erwin Brandstetter Mar 8 '17 at 2:54
  • I have edited the question -- I thought it would be relevant to scope the question. The gateway is sending streams of events to the database, some are multi-event transactions but most are self contained. switching the search path will happen very often. – Hassan Syed Mar 8 '17 at 3:39
  • 1
    Your Postgres version is essential to the question. (It almost always is.) – Erwin Brandstetter Mar 8 '17 at 4:30
  • Postgres 9.6.2 -- fresh project. – Hassan Syed Mar 8 '17 at 9:19
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.