Per‑organization schemas: can PostGraphile CRUD/mutations follow runtime search_path instead of fixed model schema?

[hchatel]

Summary

We attempted to move from a classic multi-tenant (organization_id + RLS) model to a “one schema per organization” model in order to reduce materialized view refresh times. After restructuring, we discovered that PostGraphile’s auto‑generated CRUD resolvers always target the statically introspected “model schema” instead of resolving tables/functions dynamically via search_path. This removes most of the benefit of using PostGraphile unless we re‑implement all mutations manually with dynamic SQL.

Our questions:

1. Is there a supported pattern for per-request schema routing (dynamic tenant schema) while keeping auto‑generated CRUD?
2. If not, is this something that could be (or is planned to be) supported?
3. Should we instead abandon per‑tenant physical schemas and solve the original performance problem differently?

Original Motivation

We have large materialized views (MVs) whose full REFRESH MATERIALIZED VIEW time grows rapidly (both data volume × refresh frequency).

Objective:

Isolate per organization to refresh smaller MV segments independently and avoid long refresh window.

Chosen approach

Split main application schema → N org schemas.

• common: helpers, shared metadata
• org_model: canonical schema introspected by PostGraphile
• One schema per org: org_foo, org_bar, ...

=> Use search_path with org_model, common at initialization (postgraphile) => Dynamically set search_path to org_X, common for each request (in pgSettings)

Issue

Postgraphile qualifies functions at initialization based on model schema. It seems to make auto-generated functions useless as they all point to model schema instead following search_path.

=> Is their a way to keep auto-generated functions and point to schema dynamically at runtime ?

Details

Function qualification problem

The issue with intended approach is that functions are qualified at initialization. Take this function:

create function common.my_function()
returns ... as $
begin
  select * from my_table;
end;

Even though we are calling this function with search_path set to org_X,common, it will select in org_model.my_table instead of org_X.my_table.

This problem can be solved with more code by setting schema dynamically

create function common.my_function()
returns ... as $
declare
  v_org_schema text;
begin
  v_org_schema := current_setting('app.org_schema', true);
  EXECUTE format('select * from %I.my_table', v_org_schema);
end;

=> The real problem lies with auto-generated CRUD functions: they are all qualified on org_model. Our only option seems to be to omit every auto-generated functions and add them "by hand" using dynamic schema injection, which removes 90% of postgraphile power.

[benjie]

Try setting preset.gather.pgIdentifiers:

const preset = {
  // ...
  gather: {
    pgIdentifiers: "unqualified",
  },
};

[hchatel]

Hi Benjie, thank you for the quick replly !

I'm currently on PostGraphile V4. From what I understand, this option is only available in V5, correct ?

And I don't see any documentation for gather.pgIdentifiers in the gather section of V5 config doc, could you briefly clarify what it actually changes ? Does it only affect naming during schema gathering, or does it also influence whether CRUD SQL stays fully qualified? (My goal would be to have table/function resolution defer to search_path, but I assume qualification remains.)

Thanks again for your help!

[benjie]

Hi @hchatel; it is indeed a V5 feature, V4 doesn't have this capability. V5 splits the schema build into two phases: gather and schema; gather goes and figures out all the database resources and codecs and what not, and then schema takes those results and uses them to build the schema. So, because gather stores the results as unqualified, ("users" rather than "public"."users") when schema builds the schema from it, it doesn't even know that "public". was even a thing - it can only select * from "users". Thus resolution defers to search_path.

And yes, the docs are the main thing that needs completion before V5 can be released as stable; the software is essentially "done" now. Help or sponsorship here would be greatly appreciated!

[hchatel]

Great, thanks a lot for the clarification! I’ll plan a migration to v5 soon to test this behavior with unqualified identifiers. I’ll report back once I’ve tried it 🤞 Thanks again!