The Data API and Postgres extensions

What it is

Two ways of getting more out of the same Postgres without adding a server:

• the Data API, a REST interface over the tables of a branch, generated from the schema and compatible with PostgREST;
• extensions, the Postgres mechanism for adding types, indexes and functions — vectors, geospatial, trigram search, query statistics — which Lakebase offers as a fixed list you install per database.

Why it exists

A small application, a webhook or an edge function often needs three queries against two tables. Standing up a service to hold a connection pool, a driver and a deployment for that is out of proportion. The Data API gives those callers HTTP and JSON against the same tables, with the same roles and grants deciding what they may see.

Extensions exist because Postgres’s answer to “we also need vectors” or “we also need geography” is a package installed in the database rather than another system to run. A recommendation service that keeps its embeddings next to the rows they belong to can filter and rank in one query, instead of asking a vector store for ids and then the database for rows.

How it works

The Data API

It is switched on from the project’s Data API page. Enabling it creates an authenticator role and a pgrst schema, and exposes the public schema. You then get CRUD, filtering, embedding of related rows and RPC calls over HTTP, with an OpenAPI description at /openapi.json.

Authentication uses Databricks OAuth bearer tokens rather than PostgREST’s own JWT configuration. The identity in the token has to have its own Postgres role, and that role must be granted to authenticator, which is how the request is executed as that role:

GRANT "alice@example.com" TO authenticator;

Two rules that catch people out: do not call the API as the project owner, and roles created through the “Add role” button in the UI cannot be granted to authenticator.

Because the API exposes whole tables to whoever holds a token, row-level security is the thing to set up first — the page has a button for enabling it. Settings also cover the maximum number of rows a response may return and CORS.

Not supported: application settings through GUCs, the db-pre-request hook, and propagating trace headers.

Extensions

An extension is installed per database, by a role that may create it:

SELECT * FROM pg_available_extensions ORDER BY name;   -- what this database offers
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

The list Lakebase ships covers about forty-six extensions, with versions per Postgres release:

Vectors

vector gives the vector type and pgvector’s index types, enough for similarity search next to the row data. A synced table can land an embedding column straight into it with type_overrides mapping the column to vector(n) or halfvec(n) (see Synced tables, from Unity Catalog into Postgres).

Lakebase Search, in Beta since June 2026, adds two extensions of its own: lakebase_vector, which brings the lakebase_ann index and is pgvector-compatible, and lakebase_text, which brings lakebase_bm25 for keyword ranking. They need Postgres 16 or later and beta access through your account team, and enabling them on a project cannot be undone.

CREATE EXTENSION IF NOT EXISTS lakebase_vector CASCADE;   -- pulls in pgvector
CREATE INDEX ON items USING lakebase_ann (embedding vector_l2_ops);

This is not a replacement for AI Search. Vector Search indexes Unity Catalog tables, is governed by Unity Catalog and serves retrieval pipelines over documents. pgvector in Lakebase serves an application that is already reading those rows by key and wants a nearest-neighbour clause in the same transaction.

Example

A product page that needs “similar products, in stock, from this catalogue”, in one round trip:

CREATE EXTENSION IF NOT EXISTS vector;

SELECT id, name
FROM products
WHERE in_stock AND catalogue_id = 42
ORDER BY embedding <-> :query_embedding
LIMIT 10;

The filter and the ranking are evaluated together, on rows the application already owns, with no second system to keep in step.

Common mistakes

• Opening the Data API without row-level security. Every token holder with a role sees every exposed row.
• Calling the API as the project owner, or trying to grant a UI-created role to authenticator. Neither works.
• Expecting PostgREST’s JWT settings. Authentication is Databricks OAuth.
• Installing an extension and finding it missing elsewhere. Extensions are per database, and a branch created afterwards inherits only what existed when it was created.
• Turning on Lakebase Search to try it. On a project, it is irreversible.
• Choosing pgvector for a document retrieval system. That is what Vector Search is for; pgvector is for vectors that belong to operational rows.