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_vectorcreates, including its data types, functions, operators, and thelakebase_annindex access method. This version is reported bySELECT installed_version FROM pg_available_extensions WHERE name = 'lakebase_vector'.ALTER EXTENSION lakebase_vector UPDATEupdates this version. - The index storage format is the on-disk layout of a
lakebase_annindex. 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 usingREINDEX INDEX CONCURRENTLYafter 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.
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';
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:
|
|
|---|---|
|
|
|
|
|
|
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) |
|
|
Negative inner product |
|
|
Cosine similarity |
|
|
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 |
|---|---|---|---|
| string |
| Controls the accuracy/speed tradeoff. Use |
| string |
| Sets the IVF partition layout. With |
GUC reference
Parameter | Type | Default | Description |
|---|---|---|---|
| string |
| Number of IVF partitions to scan at each level. Higher values improve recall at the cost of query speed. The shape must match the |
| string |
| Controls how many candidates are reranked using full-precision distances. Higher values rerank more candidates and take longer. |
| enum |
| Evaluates non-vector filters before full-precision distance reranking. Valid values are |
Utility functions
Function | Returns | Description |
|---|---|---|
| void | Loads frequently accessed index data into memory. Valid |
| text | Returns index metadata as JSON text, including |