Docs

Database extensions

How to turn extensions such as pgvector (embedding search) and PostGIS (maps) on and off, which ones you can turn on, and how they behave on a shared database.

Database extensions (pgvector, PostGIS …)

list_extensions { ref } returns the extensions you can turn on and their state (available, enabled, version). Turn one on with enable_extension { ref, name } and off with disable_extension { ref, name, confirm: name }. Only owners and admins can turn them on and off.

What you can turn on: vector (pgvector), postgis, citext, hstore, uuid-ossp, pgcrypto, ltree, fuzzystrmatch, btree_gin, btree_gist, intarray, tablefunc. pg_trgm and unaccent are on from the start and cannot be turned off. Extensions not on the list are refused.

Extensions are installed in the extensions schema, and that schema is on every search path (the SQL editor, direct connections, the data API), so you use them without a schema prefix.

-- after enable_extension { ref, name: "vector" }
create table docs (id bigserial primary key, body text, embedding vector(1536));
create index if not exists docs_embedding_hnsw on docs using hnsw (embedding vector_cosine_ops);
select id, body from docs order by embedding <=> '[...]' limit 5;

-- after enable_extension { ref, name: "postgis" }
create table places (id bigserial primary key, name text, at geography(point, 4326));
select name from places where st_dwithin(at, st_makepoint(126.978, 37.566)::geography, 1000);

To find the nearest rows through the data API, write a function and call it with POST /rest/v1/rpc/<function>.

On a shared database, an extension belongs to the whole database. If another project has turned on the same extension, turning it off leaves it installed (note) and only removes this project's record. While columns, indexes or functions that use the extension remain, turning it off is refused. Remove those first.

Over HTTP it is GET /extensions, PUT /extensions/<name> (on) and DELETE /extensions/<name> (off).