Schema-based multi-tenancy is a popular pattern in PostgreSQL SaaS applications. Each tenant gets its own schema — tenant_1, tenant_2, and so on — with an identical table structure but fully isolated data. The application layer queries the right schema at runtime, and the database enforces the separation cleanly.

The headache arrives when you need to query across tenants: a one-off data audit, a cross-tenant report, a support investigation. The table exists in every schema, but there is no built-in SELECT * FROM ALL SCHEMAS syntax. You have to build that query yourself.

The procedural approach

The first instinct is usually a loop. Iterate over every tenant schema, query the table in each one, and collect the results somewhere. In PL/pgSQL:

-- Step 1: Create a staging table using the first tenant's column structure
DO $$
DECLARE
    sch text;
BEGIN
    FOR sch IN
        SELECT nspname
          FROM pg_namespace
         WHERE nspname LIKE 'tenant\_%' ESCAPE '\'
         ORDER BY nspname
         LIMIT 1
    LOOP
        EXECUTE format(
            'CREATE TEMP TABLE all_records AS
             SELECT %L::text AS tenant, * FROM %I.orders WHERE false',
            sch, sch
        );
    END LOOP;

    -- Step 2: Populate from every tenant schema
    FOR sch IN
        SELECT nspname
          FROM pg_namespace
         WHERE nspname LIKE 'tenant\_%' ESCAPE '\'
         ORDER BY nspname
    LOOP
        EXECUTE format(
            'INSERT INTO all_records
             SELECT %L::text AS tenant, * FROM %I.orders',
            sch, sch
        );
    END LOOP;
END $$;

SELECT * FROM all_records ORDER BY tenant;
DROP TABLE all_records;

This works. The first loop borrows a schema's column structure to create the temp table; the second loop populates it. But there is an awkward quality to it: two loops, two passes over pg_namespace, and a DO block that can't return rows directly, so a temp table is required as a staging area.

The set-based approach

PostgreSQL's string_agg function lets you collapse the entire loop into a single expression. Instead of visiting schemas one at a time, you assemble the full UNION ALL query string in one pass, then execute it once:

DO $$
DECLARE
    sql text;
BEGIN
    SELECT string_agg(
        format(
            'SELECT %L::text AS tenant, * FROM %I.orders',
            nspname, nspname
        ),
        ' UNION ALL '
        ORDER BY nspname
    )
    INTO sql
    FROM pg_namespace
    WHERE nspname LIKE 'tenant\_%' ESCAPE '\';

    EXECUTE 'CREATE TEMP TABLE all_records AS ' || sql;
END $$;

SELECT * FROM all_records ORDER BY tenant;
DROP TABLE all_records;

No loop. string_agg scans pg_namespace once and produces a string like:

SELECT 'tenant_1'::text AS tenant, * FROM tenant_1.orders
UNION ALL
SELECT 'tenant_2'::text AS tenant, * FROM tenant_2.orders
UNION ALL
SELECT 'tenant_3'::text AS tenant, * FROM tenant_3.orders

A single EXECUTE then runs that string wrapped in a CREATE TEMP TABLE AS, which also infers the column structure automatically — no need for a separate setup step.

Breaking It Down

Element	What it does
pg_namespace	PostgreSQL's system catalog table for schemas. Filter by nspname to target tenant schemas.
format('%I', …)	Safely quotes an identifier (schema name, table name). Prevents SQL injection in dynamic queries.
format('%L', …)	Safely quotes a string literal. Used here to emit the tenant name as a constant in each sub-query.
string_agg(…, ' UNION ALL ')	Joins all sub-queries into one SQL string. The ORDER BY clause ensures a deterministic join order.
CREATE TEMP TABLE AS …	Creates a temporary table whose columns are inferred from the query result. Scoped to the session and dropped automatically when the connection closes.

On format() and injection safety — Always use %I for identifiers and %L for literals when building dynamic SQL with format(). They handle quoting and escaping correctly, even for edge cases like schema names containing reserved words or special characters.

When to reach for this

This pattern is well suited to ad-hoc, operational queries:

• Cross-tenant reporting and aggregations
• Data consistency audits across all schemas
• Support investigations that span multiple tenants
• One-off backfill or data migration checks
• Debugging anomalies that may appear in only some tenants

For production cross-tenant queries that run on a schedule, a dedicated reporting schema or a materialized view strategy is usually a better fit. Dynamic SQL assembled at query time adds latency and bypasses the planner's ability to optimize across tenants as a whole.

The underlying idea

The loop version is imperative: it tells the database how to visit each schema. The string_agg version is declarative: it describes the result it wants and lets a single EXECUTE handle execution. That shift is modest here, but it reflects a broader principle in SQL — set-based operations tend to be shorter, easier to read, and easier to extend than their procedural equivalents.

Adding a filter is illustrative. In the loop version you modify the format string inside the loop body and track whether you've touched it everywhere. In the string_agg version you change the format string in one place:

SELECT string_agg(
    format(
        'SELECT %L::text AS tenant, * FROM %I.orders WHERE status = ''pending''',
        nspname, nspname
    ),
    ' UNION ALL '
    ORDER BY nspname
)
INTO sql
FROM pg_namespace
WHERE nspname LIKE 'tenant\_%' ESCAPE '\';

One change, one place. The loop version requires the same change inside the loop body, with the same effect — but the set-based version makes the scope of the change obvious at a glance.