pgvector brings approximate nearest-neighbor search into PostgreSQL

An index usually changes how PostgreSQL finds an answer without changing the answer itself. pgvector's first release explicitly warns that its approximate index can return different results after the index is added. That warning identifies the main design decision behind the extension. A nearest-neighbor query can trade some retrieval accuracy for speed while retaining a SQL interface.

The first-release changelog entry dates version 0.1.0 to April 20. The README in that release defines a vector column type, distance operators and an IVFFlat index method. It supports PostgreSQL 9.6 and later. The extension is compiled and installed into PostgreSQL, then enabled in each database that needs it.

I like PostgreSQL's extension model because substantial new behavior can use the database's existing interfaces. Here, a vector becomes a typed column and distance becomes an expression in a query. That makes the boundary between relational data and similarity search worth examining in detail.

The column type defines the input

The first release represents a vector as a sequence of floating-point elements. A declaration such as vector(3) fixes the column's dimension count. The documented upper limit is 1,024 dimensions. Every element must be finite, so NaN and positive or negative infinity are invalid inputs.

Those restrictions define what an application can store before indexing enters the discussion. A vector-producing component must agree with the column definition about dimensions. Its output must also meet the finite-value requirement. Selecting a distance operator cannot compensate for incompatible input representations.

The documented storage size is four bytes per dimension plus eight bytes. That gives the vector itself a size determined by its dimension count. It is not a statement about the total size of a row or an index. The distinction helps avoid treating the type's representation as a complete database-capacity estimate.

The example below follows the release's documented operations after installation. It uses an ordinary table with a single vector column, so there is no application-specific identifier or filtering rule to obscure the distance expression. The extension must already be installed on the server before the database can enable it.

CREATE EXTENSION vector;

CREATE TABLE samples (position vector(3));

INSERT INTO samples (position)
VALUES ('[1,2,3]'), ('[4,5,6]');

SELECT position
FROM samples
ORDER BY position <-> '[3,1,2]'
LIMIT 1;

The example names the table and column explicitly because the README's placeholders need non-reserved identifiers. It stores two vectors and orders them by distance to a query vector. The LIMIT asks for the closest result under that ordering. No approximate index has been created in this example.

The vector values are data supplied by the application. The extension provides storage and comparison operations. Nothing in this initial interface generates a representation from a document or establishes that two vectors encode the same kinds of measurements. That agreement remains part of the application's data contract.

Distance determines what nearest means

Version 0.1.0 supports Euclidean distance, inner product and cosine distance. The operators are <->, <#> and <=> respectively. Choosing between them changes the ordering expression and, for an indexed query, the operator class that supports it.

The inner-product operator has a deliberate sign convention. It returns the negative inner product because PostgreSQL supports ascending index scans on operators in this setting. Larger inner products therefore become smaller operator results. Reading the returned value as the ordinary inner product would reverse its sign.

That choice illustrates the work required to fit a new search operation into an existing query interface. SQL ordering and index capabilities place constraints on the representation of the score. The extension documents the convention so the application's interpretation can match the index ordering.

The release also provides named functions for cosine distance, inner product and Euclidean distance. Vector-dimension and vector-norm functions expose properties of the value. Element-wise addition and subtraction are available as operators. These operations give the type uses beyond an opaque value handed directly to an index.

A consumer that displays a distance value also needs to know which expression produced it. The negative-inner-product convention is a concrete example of why a generic score label can hide useful meaning. The query expression should make its metric explicit. Two systems can both ask for nearest neighbors while ordering the same stored values differently. A column declaration establishes the dimensions, but it does not select the distance function for every query. The operator in the ORDER BY clause expresses that decision.

Approximate indexing introduces a separate decision

The release describes IVFFlat as an approximate index. For Euclidean distance, the default operator class permits the definition below. The list count is explicit here to distinguish that index property from the query setting introduced next. Leaving out the option would use the same documented default.

CREATE INDEX samples_position_l2
ON samples USING ivfflat (position)
WITH (lists = 100);

The README recommends creating the index after the table contains data for optimal clustering. Index creation therefore depends on the available values in a way that matters to the resulting search structure. An empty-schema deployment and a populated-data index build have different implications for that recommendation.

The number of inverted lists defaults to 100. At query time, ivfflat.probes controls how many lists the search probes, with a default of one. The README states that increasing probes improves recall at the cost of speed. The release's index implementation registers that setting as user-settable.

These settings separate a construction choice from a search choice. The list count belongs to the index definition. The probe count can vary when querying. An application does not have to rebuild an index merely to request a different documented probe setting for a particular transaction.

The README permits SET LOCAL inside a transaction to set the probes for a single query. In this example, the transaction contains one search statement after changing the setting. This keeps the requested search effort next to the query that requires it.

BEGIN;
SET LOCAL ivfflat.probes = 10;

SELECT position
FROM samples
ORDER BY position <-> '[3,1,2]'
LIMIT 1;

COMMIT;

Ten probes is an illustrative choice within the example's hundred-list index, not a performance recommendation. The release provides no universal recall target for that value. Its documented relationship is directional, with more probes improving recall while adding search work.

The earlier query over two rows is useful for showing syntax. It does not establish that a hundred-list approximate index is useful for such a small dataset. The examples separate the SQL operations so the configuration's meaning remains visible without pretending to demonstrate an actual performance result.

Operator classes belong to the search design

An index for Euclidean distance does not automatically provide every supported distance ordering. The README instructs users to create an index for each distance function they want indexed. Inner product uses vector_ip_ops, and cosine distance uses vector_cosine_ops.

Cosine search names its operator class in the index definition. The query then orders by the cosine-distance operator. The following example retains the same column and query vector to make the metric change visible.

CREATE INDEX samples_position_cosine
ON samples USING ivfflat (position vector_cosine_ops);

SELECT position
FROM samples
ORDER BY position <=> '[3,1,2]'
LIMIT 1;

This is an alternative search design, with its own index definition and matching operator. It demonstrates why a schema review needs both the index and the queries that will use it. The presence of a vector column alone says little about which similarity operation an application intends.

The initial access-method implementation also states concrete limits. It does not support unique indexes or multicolumn indexes. It advertises ordering by an operator, while ordinary index ordering is disabled. These declarations fit the documented nearest-neighbor use and keep the index's capabilities narrower than a general-purpose replacement for PostgreSQL indexing.

I regard the explicit approximation warning as the most useful part of the first release's contract. A faster query that omits a relevant neighbor can be acceptable for some applications. The decision belongs with the application's interpretation of the result, because a successful SQL execution does not itself establish sufficient recall.

The extension keeps the tradeoff visible in SQL

The initial design exposes several decisions separately. A typed column determines the representation. The ordering operator determines the distance measure. An optional index introduces approximate retrieval, and the probe setting changes the search effort.

Those decisions are close enough together to review in ordinary SQL. A reader can identify which metric a query requests and which operator class an index supplies. The explicit transaction-local setting can show where a query requests more probing than the default.

The release's Euclidean-index regression example constructs an index with one list and inserts another vector after index creation. That example exercises the index alongside ordinary table operations. The public README supplies the application-level warning that remains essential when using the result, since adding an approximate index can change which neighbors a query returns.