The Supabase Extension Trap: Why CREATE EXTENSION Succeeds and Your Query Still Fails

On Supabase, CREATE EXTENSION pg_trgm can succeed while similarity() remains unreachable. Your migration reports success. Your schema looks correct. Then the first query that uses a trigram operator fails in production with function similarity(text, text) does not exist.

The cause is not a missing extension. It is a missing schema on the role’s search_path.

This bites hardest in hybrid search, because a trigram leg is usually one of several — so the failure surfaces as one broken retrieval path inside an otherwise working system, at query time, long after the migration that appeared to install it.

Why it happens

Most Postgres tutorials assume extensions land in public. Supabase does not do that. It installs extensions into a dedicated extensions schema, which is good hygiene — it keeps public clean and makes permissions easier to reason about.

The consequence is that extension functions resolve only if extensions is on the search_path of the role running the query. CREATE EXTENSION does not care: it succeeds, and pg_extension will happily confirm the extension is installed. Installation and reachability are different questions, and only the first one is checked at migrate time.

So you get this:

CREATE EXTENSION IF NOT EXISTS pg_trgm;   -- succeeds
SELECT extversion FROM pg_extension WHERE extname = 'pg_trgm';  -- returns a version

SELECT similarity('kitten', 'sitting');   -- ERROR: function does not exist

Everything that comes with the extension is affected, not just the obvious function: the % similarity operator, set_limit(), show_limit(). The same applies to unaccent() from the unaccent extension — which is worse in one specific way, because if a migration wraps unaccent() in an IMMUTABLE helper for use in a generated column, the function has to resolve at DDL time. That failure at least happens during the migration rather than in production.

The fix

One statement:

ALTER ROLE your_role SET search_path = public, extensions;

New sessions for that role pick it up. Verify with SHOW search_path on a fresh connection — not the one you just ran the ALTER in.

If you would rather not change the role, schema-qualify every call (extensions.similarity(...)), but that means every query, in every code path, forever, including inside any library you use. Fixing the search_path is almost always the right call.

Detecting it before you deploy

The general lesson is worth more than the specific fix: “the extension is installed” is not the same check as “the functions are reachable.” A preflight that only asks the first question passes on a database that will fail in production.

So test the behaviour, not the metadata:

SELECT similarity('kitten', 'sitting');   -- pg_trgm functions resolve
SELECT 'kitten' % 'kitteh';               -- the operator resolves too
SELECT set_limit(0.3);                    -- and the GUC helpers
SELECT unaccent('café');                  -- unaccent resolves

Context Engine ships this as a preflight test you point at a scratch database before trusting a provider:

CE_COMPAT_DATABASE_URL='postgresql+psycopg2://user:pass@host/db' \
    uv run pytest tests/test_managed_postgres_compat.py -v -s

Each capability is a separate test, so a partial failure tells you which one is missing rather than just “it didn’t work.”

The other managed-Postgres check worth running

While you are pointed at the provider, verify the pgvector version:

SELECT extversion FROM pg_extension WHERE extname = 'vector';

Below pgvector 0.8, filtered vector search silently loses recall. An access-control predicate over an HNSW index is a post-filter: the index walk is ordered by distance, rows failing the predicate are discarded as they are met, and the walk stops when its candidate budget is spent. A user who can see a small slice of the corpus can get zero results while matching documents exist, with no error and nothing logged. Iterative scan, introduced in 0.8, keeps the walk going until the limit is filled. Iterative scan does not remove the loss: with it on and the index forced, recall@10 for a caller who sees 1% was 0.652 with clustered permissions and 0.961 with random ones. That is the vector leg in our benchmark on 100,000 documents with synthetic permissions, with the library’s exact route switched off, measured against a corpus holding only that caller’s documents. The conditions are on the benchmarks page.

Both Neon and Supabase ship pgvector new enough for this, so the version check usually passes — but check rather than assume, because there is no application-level workaround if it fails.

One more trap in the same family: pgvector registers its GUCs lazily, when its shared library first loads. So probing support with current_setting('hnsw.iterative_scan', true) on a fresh connection returns NULL and makes the feature look absent. Read pg_extension.extversion instead, or force a vector operation first.

The pattern

All three of these — extension reachability, pgvector version, lazy GUC registration — share a shape. The check that is easy to write is not the check that tells you the truth. CREATE EXTENSION succeeding, a row in pg_extension, a current_setting call: each looks like verification and each can pass on a database that will fail under load, under an ACL, or at query time.

Test the behaviour you actually depend on, on the provider you are actually deploying to, before you need it to work.

A promptev workspace can keep its knowledge in its own PostgreSQL database. If you choose that, run these checks on your database first. The Context Engine guide covers the rest.