search_path Drift and Object Resolution #

A query returns different row counts depending on which connection runs it. Both connections run identical SQL against the same database, and both are correct — they are reading different tables, because search_path differs and the unqualified name orders resolves differently in each.

Settings drift generally is covered in session parameter drift. search_path deserves its own treatment because, unlike other settings, it changes which objects a query refers to rather than how they are accessed.

The Condition #

An unqualified name is resolved by walking search_path in order and taking the first schema containing a matching object. The default is "$user", public: a schema named after the current user if one exists, then public.

That makes the following all true at once:

search_path also carries a well-known security consideration: a SECURITY DEFINER function that does not pin its own path can be made to call a different function than it intended by a caller who controls the path. Pinning it is standard practice for that reason alone.

How a name becomes an object Four layers. An explicitly schema-qualified name resolves directly with no search involved. Otherwise the first schema on search_path is checked, then the next, and so on. Implicit system schemas are always searched first for system objects. The first match wins, and nothing warns that another schema also had a match. qualified name no search at all first schema on the path first match wins later schemas on the path only if not found earlier system catalogs always searched first a second match in a later schema is silently ignored

Annotated Evidence #

The same statement, two sessions:

-- session A
SHOW search_path;          -- "$user", public
EXPLAIN SELECT count(*) FROM orders;
Aggregate  (cost=41204.10..41204.11 rows=1 width=8)
  ->  Seq Scan on orders  (cost=0.00..38412.08 rows=1116808 width=0)
-- session B, a tenant worker
SHOW search_path;          -- tenant_42, public
EXPLAIN SELECT count(*) FROM orders;
Aggregate  (cost=412.10..412.11 rows=1 width=8)
  ->  Seq Scan on orders  (cost=0.00..384.08 rows=11208 width=0)
-- a hundredfold smaller: this is tenant_42.orders, a different table

Which object was actually used:

EXPLAIN (VERBOSE) SELECT count(*) FROM orders;
Aggregate  (cost=412.10..412.11 rows=1 width=8)
  Output: count(*)
  ->  Seq Scan on tenant_42.orders  (cost=…)
-- VERBOSE qualifies every relation: the fastest way to see what resolved

And where the path is coming from:

SELECT setdatabase::regclass, setrole::regrole, setconfig FROM pg_db_role_setting;
 setdatabase | setrole  |           setconfig
-------------+----------+--------------------------------
 -           | worker   | {"search_path=tenant_42, public"}
-- a role default, invisible to anyone reading the application's code
Three ways to see the resolution Three items are annotated. EXPLAIN VERBOSE qualifies every relation name with its schema, showing exactly what resolved. SHOW search_path gives the order used. The pg_db_role_setting catalog shows role and database defaults that set the path outside application code. EXPLAIN (VERBOSE): Seq Scan on tenant_42.orders what actually resolved SHOW search_path → tenant_42, public the order used pg_db_role_setting holds search_path set outside the application cost estimates differing by orders of magnitude is the first clue

Step-by-Step Resolution #

  1. Qualify names in anything that must be unambiguous — migrations, SECURITY DEFINER functions, monitoring queries, anything in a shared library. public.orders cannot drift.

  2. Pin the path for functions that rely on it:

    ALTER FUNCTION app.process_order(bigint) SET search_path = app, public, pg_temp;

    Including pg_temp last (or omitting it deliberately) prevents a caller’s temporary objects shadowing real ones.

  3. Set the application’s path once, where it is visible — a role default or the connection string’s options — rather than with a SET inside request code, which leaks through pooled connections as described in application-side pools and server-side state.

  4. Audit what is already set: pg_db_role_setting for role and database defaults, the connection string, and any SET in application startup code.

  5. Use EXPLAIN (VERBOSE) whenever a query’s cost estimate looks wrong by an order of magnitude in one environment and not another. It qualifies every relation, which settles the question immediately.

  6. Keep extension schemas on the path deliberately. Installing extensions into a dedicated schema and adding it to the role default is more predictable than installing into public.

  7. Test with the application’s real role. A superuser session in psql frequently has a different path than the application, which is how this class of bug reaches production.

Where should the path be decided? Three cases. SQL that must always mean the same object should use schema-qualified names. A function whose behaviour must not depend on its caller should pin search_path in its own definition. An application that legitimately works within one schema should set the path as a role default, not per request. what must the name resolve to? always this object schema-qualify it no ambiguity fixed for this function ALTER FUNCTION … SET caller cannot change it a per-role schema role default set once, visible a bare SET in request code is the case that drifts

Before and After #

-- BEFORE: unqualified, path set per request
SET search_path = tenant_42, public;   -- leaks to later requests on this connection
SELECT count(*) FROM orders;           -- which orders? depends on who ran before

-- AFTER: role default plus qualification where it matters
ALTER ROLE worker_42 SET search_path = tenant_42, public;
SELECT count(*) FROM public.orders;    -- unambiguous when it must be

Why plans differ, not just results #

The plan difference is not incidental — it is the clearest symptom. Two tables with the same name in different schemas have different sizes, different statistics and different indexes, so the planner makes different choices for each. A query that uses an index scan against a tenant’s small table can use a sequential scan against the shared one, and the estimated row counts differ by whatever the size ratio is.

That gives a useful diagnostic: when the same SQL produces plans whose estimates differ by orders of magnitude across environments, resolution is a stronger hypothesis than statistics. Statistics drift changes estimates by a factor of two or ten; resolving to a different table changes them by whatever the tables differ by, which is usually much more.

It also explains a caching subtlety. A prepared statement binds its object references when it is prepared, so changing search_path afterwards does not re-resolve it — the cached plan keeps pointing at the objects it originally found. In a pooled application where the path is set per request, that means a connection can end up executing a cached plan against the previous tenant’s tables, which is a correctness problem rather than a performance one.

Schema-per-tenant and the plan cache #

Multi-tenant designs that give each tenant a schema use search_path as the tenant selector, which makes all of the above operational rather than theoretical.

The arrangement works well when each tenant has its own role and its own connection pool: the path is a role default, sessions never change it, and cached plans always refer to the right tenant’s tables. It works badly when one pool serves every tenant and the path is set per request, because that is exactly the configuration where a cached plan can outlive the path that produced it, and where a returned connection carries the previous tenant’s path to the next request.

If a shared pool is unavoidable, two mitigations help. Reset the path explicitly when checking a connection out, rather than relying on the previous user to have cleaned up. And avoid prepared statements for the tenant-resolved queries, or clear them alongside the path — DISCARD ALL does both, at the cost of the plan cache.

The alternative design — one table with a tenant column and row-level security — trades this problem for a different one: plans are shared, which is good, but selectivity on the tenant column becomes a statistics question, and a tenant holding most of the rows will mislead the planner for everyone else.

Common Pitfalls #

Setting search_path per request. It leaks through pooling and invalidates assumptions about cached plans. Diagnostic signal: results depending on request order. Fix: role defaults.

Unpinned SECURITY DEFINER functions. The caller controls resolution. Diagnostic signal: a definer function with no SET search_path. Fix: pin it, ending with pg_temp.

Testing as a superuser. The path differs from the application’s. Diagnostic signal: works in psql, fails in the app. Fix: test with the application role.

Assuming a cached plan re-resolves. It does not. Diagnostic signal: a prepared statement reading the wrong schema’s table. Fix: do not change the path on live connections.

Frequently Asked Questions #

How do I see which schema a query actually used? #

Run EXPLAIN (VERBOSE): it qualifies every relation with its schema, so the resolution is explicit in the plan. SHOW search_path gives the order that produced it.

Why does the same query give different row counts in different sessions? #

Because search_path differs and the unqualified names resolve to tables in different schemas. Both sessions are behaving correctly; they are reading different objects.

Where should search_path be set for an application? #

As a role default with ALTER ROLE, or in the connection string, so it applies to every session that role opens. Setting it per request leaks through connection pools and interacts badly with cached plans.

Up: Session Parameter Drift