lakebase_vector

The lakebase_vector extension adds approximate nearest-neighbor (ANN) vector search to Lakebase via the lakebase_ann index type. It is a drop-in companion to pgvector: the same vector types, distance operators, and query syntax work without modification.

Install

First, enable Lakebase Search in your project settings. Then install the extension:

CREATE EXTENSION IF NOT EXISTS lakebase_vector CASCADE;

The CASCADE keyword automatically installs pgvector as a dependency.

Upgrade the extension and indexes

A new Lakebase Search release can add features, fixes, and performance improvements. Although Lakebase Search is released as part of Lakebase updates, it does not upgrade everything automatically. In lakebase_vector, two things upgrade separately and carry version numbers that are unrelated to each other:

• The extension version is the version of the SQL objects that CREATE EXTENSION lakebase_vector creates, including its data types, functions, operators, and the lakebase_ann index access method. This version is reported by SELECT installed_version FROM pg_available_extensions WHERE name = 'lakebase_vector'. ALTER EXTENSION lakebase_vector UPDATE updates this version.
• The index storage format is the on-disk layout of a lakebase_ann index. The extension may introduce updated index storage formats in an update, unlocking more features and delivering better performance. All newly created indexes automatically use the latest storage format, while existing indexes can be upgraded to the new format using REINDEX INDEX CONCURRENTLY after a newer storage format is available.

Upgrading is not urgent. The extension is compatible with SQL objects and index storage formats from older versions, but staying current keeps you on the supported, best-performing path and avoids a larger migration later, so upgrade when convenient rather than deferring indefinitely.

Note

The latest available extension version is reported by SELECT default_version FROM pg_available_extensions WHERE name = 'lakebase_vector'.

The latest storage format version is _2. The following query finds all indexes that use an older storage format. You can then rebuild them to the latest storage format with REINDEX INDEX or REINDEX INDEX CONCURRENTLY:

SELECT oid::regclass AS index, lakebase_ann_index_info(oid::regclass)::json ->> 'version' AS storage_format_version
FROM pg_class
WHERE relam = (SELECT oid FROM pg_am WHERE amname = 'lakebase_ann') AND relkind = 'i';

Note

REINDEX INDEX CONCURRENTLY allows reads and writes to continue, but it takes longer.

Quick start

-- Create a table with a vector column
CREATE TABLE items (id BIGSERIAL PRIMARY KEY, embedding VECTOR(3));

-- Insert sample data
INSERT INTO items (embedding)
SELECT ARRAY[random(), random(), random()]::real[]
FROM generate_series(1, 1000);

-- Create a lakebase_ann index
CREATE INDEX items_embedding_idx ON items
  USING lakebase_ann (embedding vector_l2_ops);

-- Query using standard pgvector distance operators
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

Populate from synced tables

If you're loading embeddings from Unity Catalog rather than inserting them directly, synced tables can map a lakehouse embedding column straight to a Postgres vector column during sync, instead of the default JSONB mapping. See Custom type mapping for Lakebase Search.

Configure the index

Set build_mode at index creation to control the accuracy/speed tradeoff:

• standard (default): balances recall and index build time. Use for most workloads.
• quality: improves recall but takes longer to build.

CREATE INDEX ON items USING lakebase_ann (embedding vector_l2_ops)
WITH (build_mode = 'quality');

The fast build mode remains supported for backward compatibility.

By default, lakebase_ann chooses lists based on the statistics of the table and the configuration of the index. Set lists to control the partition layout explicitly:

CREATE INDEX ON items USING lakebase_ann (embedding vector_l2_ops)
WITH (lists = '1000');

Index build time

Larger shared_buffers can significantly reduce index build time. Lakebase enables this optimization only on larger fixed-size computes. Check the current value before optimizing an index build:

SHOW shared_buffers;

If shared_buffers is 1 GB or less, consider temporarily resizing to a larger fixed-size compute before starting the index build.

You can also speed up index creation by increasing the number of parallel workers.

The max_parallel_maintenance_workers configuration parameter sets the maximum number of parallel workers that can be started by a single utility command such as CREATE INDEX.

The max_parallel_workers configuration parameter sets the maximum number of workers that the compute can support for parallel operations. Values of max_parallel_maintenance_workers above this limit have no effect.

The max_worker_processes configuration parameter sets the maximum number of background processes that the compute can support. Lakebase manages this setting based on compute size. Values of max_parallel_workers above this limit have no effect.

SHOW max_worker_processes;
-- Set both values to the desired parallelism minus one.
SET max_parallel_workers = 15;
SET max_parallel_maintenance_workers = 15;

Build indexes concurrently

CREATE INDEX CONCURRENTLY and REINDEX INDEX CONCURRENTLY allow reads and writes to continue while an index is built or rebuilt:

CREATE INDEX CONCURRENTLY items_embedding_idx_concurrent ON items
  USING lakebase_ann (embedding vector_l2_ops);

REINDEX INDEX CONCURRENTLY items_embedding_idx_concurrent;

Tune search accuracy

Before tuning, call lakebase_ann_index_info(index_name) to get the index's lists, default_probes, and default_epsilon values.

Use lakebase_ann.probes at query time to control how many IVF partitions are searched. Higher values improve recall at the cost of query speed. The default is 'auto'. Test different values to meet your recall target.

The shape of probes must match the shape of lists. Call lakebase_ann_index_info to find your lists array, then set one value for a one-level index or two comma-separated values for a two-level index:

lists from index info	probes to set
[] (empty)	''
[222]	'22'
[3333, 33333]	'33, 333'

Note

On a small dataset, lakebase_ann uses exact (flat) search instead of IVF partitioning, and lakebase_ann_index_info returns empty lists and default_probes. In this case, leave probes set to ''. When lists is not empty, a probes value whose shape does not match lists causes an error.

-- Check your index's lists array first
SELECT lakebase_ann_index_info('items_embedding_idx');

-- Then set probes to match the shape of lists.
-- One-level index (single-value lists): set one value.
SET lakebase_ann.probes TO '10';

-- Two-level index: set two ascending comma-separated values, for example '10, 20'.
-- Flat index (empty lists): leave probes set to ''.

SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 10;

lakebase_ann.epsilon controls how many candidates are reranked using full-precision distances. Higher values rerank more candidates and take longer. The default value of 'auto' works well for most workloads. During flat search on a small dataset, epsilon still controls full-precision reranking.

Prefilter

By default, Postgres applies non-vector filter conditions after the ANN index returns candidate rows. Enable lakebase_ann.prefilter to evaluate those conditions before full-precision distance reranking:

SET lakebase_ann.prefilter TO on;

SELECT * FROM items
WHERE id % 100 = 0
ORDER BY embedding <-> '[3,1,2]'
LIMIT 10;

Prefiltering works best when the filter is cheap to evaluate and removes most rows. Leave it off for filters that match many rows or require expensive calculations, since evaluating the filter inside the index can add overhead.

Prewarm an index

Use lakebase_ann_prewarm after a compute starts to load the frequently accessed parts of an index into memory. The scope argument accepts the following values:

• search (default): Prewarms the full hot portion used for search.
• routing: Prewarms only the routing structures. This option is faster and provides a better cost-performance tradeoff for large indexes.

-- Prewarm the full search scope
SELECT lakebase_ann_prewarm('items_embedding_idx');

-- Prewarm only routing structures
SELECT lakebase_ann_prewarm('items_embedding_idx', scope => 'routing');

Operator classes

Distance metric	Operator class	Query operator
L2 (Euclidean)	vector_l2_ops	<->
Negative inner product	vector_ip_ops	<#>
Cosine similarity	vector_cosine_ops	<=>

Choose the operator class that matches how your embeddings were trained, and use the same metric for the index and the query:

• vector_cosine_ops (<=>) is cosine similarity. Use it for most text embeddings. This is the most common choice.
• vector_l2_ops (<->) is Euclidean (L2) distance. Use it when absolute spatial distance matters and vectors are not normalized.
• vector_ip_ops (<#>) is negative inner product. Use it when vectors are pre-normalized to unit length. For unit vectors, inner product equals cosine similarity and is typically faster.

Index options reference

Option	Type	Default	Description
build_mode	string	'standard'	Controls the accuracy/speed tradeoff. Use 'quality' for better recall at the cost of a longer index build. 'fast' remains supported for backward compatibility.
lists	string	'auto'	Sets the IVF partition layout. With 'auto', the extension chooses a value based on the statistics of the table and the configuration of the index. Set a single integer such as '1000' for a one-level index, or two ascending comma-separated integers such as '100, 1000' for a two-level index.

GUC reference

Parameter	Type	Default	Description
lakebase_ann.probes	string	'auto'	Number of IVF partitions to scan at each level. Higher values improve recall at the cost of query speed. The shape must match the lists array from lakebase_ann_index_info.
lakebase_ann.epsilon	string	'auto'	Controls how many candidates are reranked using full-precision distances. Higher values rerank more candidates and take longer.
lakebase_ann.prefilter	enum	off	Evaluates non-vector filters before full-precision distance reranking. Valid values are on and off. Best for cheap filters that remove most candidate rows.

Utility functions

Function	Returns	Description
lakebase_ann_prewarm(regclass, scope text DEFAULT 'search')	void	Loads frequently accessed index data into memory. Valid scope values are search and routing.
lakebase_ann_index_info(regclass)	text	Returns index metadata as JSON text, including version, lists, default_probes, and default_epsilon.

Next steps

• lakebase_text
• Lakebase Search