Enable AI-Ready Vector Search with PGVector - PolarDB

PGVector is a PostgreSQL extension written in C for storing, indexing, and searching machine learning embeddings directly in PolarDB for PostgreSQL (Compatible with Oracle). It supports vectors up to 16,000 dimensions and two indexing methods—HNSW and IVFFlat—so you can balance recall rate against query throughput.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for PostgreSQL (Compatible with Oracle) cluster running one of the following engine versions:
- Version 2.0, revision 2.0.14.7.9.0 or later
- Version 1.0, revision 2.0.11.9.35.0 or later

To check your revision version, run SHOW polardb_version; in the PolarDB console or connect to your cluster and run the statement directly. If your version does not meet the requirement, update the revision version in the PolarDB console.

How it works

PGVector implements two indexing algorithms for approximate nearest neighbor (ANN) searches.

IVFFlat (Inverted File with Flat Compression) is a simplified Inverted File System with Asymmetric Distance Computation (IVFADC) algorithm that partitions the vector space into clusters using k-means, then builds an inverted index over those clusters:

A clustering algorithm (k-means) groups all vectors into clusters, each with a centroid.
At query time, IVFFlat identifies the *n* centroids nearest to the query vector.
It scans and ranks all vectors in those *n* clusters and returns the *k* nearest results.

IVFFlat suits workloads that need high precision and can tolerate query latencies up to 100 ms. Compared with other algorithms, IVFFlat offers a high recall rate, high precision, a simple algorithm with few parameters, and low storage usage.

HNSW (Hierarchical Navigable Small World) builds a multi-layer graph where each node links to nearby nodes and to a few distant nodes. The layered structure lets queries start broad and narrow down quickly. HNSW delivers higher recall rate and better query performance than IVFFlat at the cost of slower index build time and higher memory usage.

Limitations

Cross-node parallel execution supports sorting high-dimensional vectors using an ORDER BY clause.
Cross-node parallel execution does not support index-based queries.

Get started

Enable the extension

CREATE EXTENSION vector;

Note

If you see ERROR: must be superuser, contact Alibaba Cloud support for assistance.

Create a table with a vector column

CREATE TABLE t (val vector(3));

Insert data

INSERT INTO t (val) VALUES ('[0,0,0]'), ('[1,2,3]'), ('[1,1,1]'), (NULL);

Create an index

Use HNSW as the default choice. It does not require pre-existing data and updates automatically as new rows are inserted.

-- HNSW index (recommended)
CREATE INDEX ON t USING hnsw (val vector_l2_ops);

If you need faster index build time or have memory constraints, use IVFFlat instead. Build the IVFFlat index after loading your data, as the algorithm samples existing rows to partition the vector space.

-- IVFFlat index
CREATE INDEX ON t USING ivfflat (val vector_ip_ops) WITH (lists = 1);

Run a similarity search

SELECT * FROM t ORDER BY val <#> '[3,3,3]';

Expected output:

   val
---------
 [1,2,3]
 [1,1,1]
 [0,0,0]
(3 rows)

Verify that the index is used

Run EXPLAIN ANALYZE on your query to confirm the planner uses the index rather than a sequential scan:

EXPLAIN ANALYZE SELECT * FROM t ORDER BY val <#> '[3,3,3]' LIMIT 3;

If the plan shows Seq Scan, the planner fell back to a sequential scan—typically because the table is too small to justify an index, or because no index exists for the chosen operator class.

Distance operators and operator classes

Every vector index requires an operator class that matches the distance metric you want to use. The following table maps each distance operator to its operator class.

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

For example, to build an HNSW index with cosine distance:

CREATE INDEX ON vecs USING hnsw (embedding vector_cosine_ops);

Choose an index and tune parameters

HNSW

In versions earlier than 0.5.0, the PGVector extension uses the IVFFlat indexing method, which is known for its fast building speed. The new HNSW indexing method provides a better recall rate and improved query performance, but requires slower index-building speed and higher memory usage.

HNSW is the default choice for most workloads. Add it immediately after creating the table—unlike IVFFlat, HNSW does not require pre-loaded data and remains optimal as rows are inserted.

Use HNSW when:

You want the highest possible recall rate.
Your workload has ongoing inserts.
Query latency is more important than index build time.

Index build parameters

Parameter	Range	Default	Effect
`m`	2–100	16	Number of bidirectional links per node. Higher values increase recall rate but extend build time and memory use.
`ef_construction`	4–100	64	Candidates checked when inserting a node. Must be at least `2 × m`. Higher values improve recall rate at the cost of longer build time.

Start with the defaults. If your dataset does not reach the target recall rate, increase ef_construction first, then adjust m.

CREATE TABLE vecs (id int PRIMARY KEY, embedding vector(1536));
CREATE INDEX ON vecs USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 64);

Search parameter

To increase the recall rate, increase the value of the hnsw.ef_search parameter. For example, you can set the value to 100. A larger value specifies a higher recall rate.

SET hnsw.ef_search = 100;

IVFFlat

Use IVFFlat when:

Index build speed is a priority.
Memory is constrained.
Your data is mostly static and you can rebuild the index when data changes significantly.

Important

Build the IVFFlat index after loading your data. An index built on an empty table produces poor recall rate because there is no data to sample for cluster partitioning.

Index build parameter

Parameter	Description
`lists`	Number of cluster centers for all vectors in the PGVector sampling table.

CREATE INDEX ON vecs USING ivfflat (embedding) WITH (lists = 100);

For the full parameter reference for both HNSW and IVFFlat indexes, see the pgvector README.

References

pgvector README — full parameter reference for both HNSW and IVFFlat indexes
Supported extensions — extension versions supported by each engine version