How we implemented vector search in Postgres Pro

Imagine you visit an online store to buy a pair of sneakers. You open the description of a model you like, and the site immediately suggests similar products—and they really are similar. How does this work?

The answer is both simple and complex at the same time: this is vector search—one of the most promising technologies, changing approaches to working with information.

From Words and Numbers to Vectors

Traditional databases handle searches by exact values very well: numbers, dates, strings. But what if you need to find not exact matches, but objects similar in meaning?

Imagine we have two words: “автомобиль” and “машина” (“car” and “automobile”). For a human, it’s obvious that these are synonyms. For a computer, though, they’re just different sets of symbols. Using vector representation, each such word—or even a whole text, image, product, movie, or user—can be represented as a set of numbers—a vector.

Each number in such a vector is a characteristic of the object in a multidimensional feature space. The closer two vectors are to each other, the more similar are the objects they represent.

Why is this needed?

To understand how important vector search is in real life, let’s look at a few concrete scenarios.

• Semantic search. Typical keyword search loses a lot of context. For example, a query like “how to fix a car” would miss an article titled “DIY automobile repair.” Vector search, though, understands meaning and finds relevant materials even if the words don’t match literally.

• Recommendation systems. Services like Kinopoisk, Zen, and VK successfully use vector search for content recommendations. They compare user interests and content characteristics and offer the most suitable recommendations.

• Computer vision and image recognition. For example, you upload a photo of a nice T-shirt to a shop app, and it immediately suggests similar models from the catalog. This also works with vector representation of images.

• Generative AI and RAG (Retrieval-Augmented Generation). Modern chatbots (for example, ChatGPT + RAG) use vector search to quickly find relevant information before generating an answer, making it more accurate and informative.

How does vector search work?

The process of nearest neighbor search in a huge array of data by vectors is a complicated task. The naive approach, which goes through and compares all vectors in sequence, is inefficient and demands massive computational resources.

This is exactly why specialized Approximate Nearest Neighbor (ANN) algorithms are used, in particular, HNSW (Hierarchical Navigable Small World). They let you find similar vectors quickly thanks to a hierarchical data structure and approximate calculations, sacrificing some accuracy but gaining greatly in speed.

How ANN works

Imagine you have millions or even billions of vectors. For example, a vector for each product in a huge marketplace or for every article on the internet. If, for every search query—say, the vector of a product a user is viewing—you needed to calculate the distance to every other vector in the database to find the absolute closest one, it would be incredibly slow. Such exact search (Exact Nearest Neighbor, ENN) simply doesn’t scale for real-world tasks. This is where Approximate Nearest Neighbors (ANN) algorithms come into play. Their main idea is to find very close neighbors, perhaps without 100% guaranteeing that the neighbor found is the absolute closest, but doing it orders of magnitude faster. For most practical tasks (recommendations, semantic search), a slight chance of missing the perfect match is more than compensated by the massive gain in speed and the ability to process huge amounts of data in real time.

How HNSW Works

One of the most popular and efficient ANN algorithms today is HNSW (Hierarchical Navigable Small World). Its trick is in building a special multi-level data structure similar to a graph. Picture several layers: at the very top layer, there are very few “nodes” (vectors), and the connections between them are “long,” allowing you to quickly jump between distant regions of vector space. With each lower level, there are more nodes and the connections between them are shorter, providing more detailed navigation in the local area. When a search query (a new vector) comes in, the search starts at the very top, sparse layer. The algorithm quickly finds the closest node at that level and then “descends” to the next layer, continuing the search in the neighborhood of the found node. This process repeats, allowing you to efficiently “home in” on the target, skipping huge portions of the space where it’s clear there are no close vectors. Thanks to this hierarchical structure and “small world navigation,” HNSW achieves an excellent balance between search speed and the accuracy of finding truly close neighbors. This very algorithm, adapted for working with disk and filters, is at the heart of pgpro_vector indexes.

Vector Search Inside Postgres Pro: pgpro_vector

Previously, implementing vector search required using separate specialized databases such as FAISS, Milvus, or Qdrant. That meant extra infrastructure costs, integration complexity, and maintenance overhead.

Now, Postgres Pro offers the pgpro_vector extension, which adds powerful vector search capabilities right inside the familiar PostgreSQL environment. This extension:

• implements the HNSW algorithm;

• lets you create special indexes for fast nearest neighbor searches;

• supports working with filters and multi-column conditions.

Simple Example of Using pgpro_vector:

Let’s look at a straightforward example of using pgpro_vector, inspired by working with word embeddings such as GloVe. Such examples can be very useful for understanding the capabilities of the extension.

Before creating a table and index, you need to install the required extensions. It's worth mentioning an architectural nuance of pgpro_vector: the ganntype data type is placed in a separate extension. This ensures your vector data in tables will remain safe even if you decide to change or remove extensions that implement specific search algorithms (for example, gannhnsw). This increases data storage reliability.

1. Installing extensions

-- First, install the extension with the basic data type
CREATE EXTENSION ganntype;
-- Then, install the main gann extension (required for index methods)
CREATE EXTENSION gann;
-- Next, extensions for specific HNSW index types
CREATE EXTENSION gannhnsw CASCADE;
CREATE EXTENSION hnswstream CASCADE;
CREATE EXTENSION mc_hnsw CASCADE;

2. Creating the table:

CREATE TABLE word_embeddings (

    id BIGSERIAL PRIMARY KEY,

    word TEXT UNIQUE,

    embedding ganntype(50) -- 50 dimensions for the GloVe example

);

3. Generating and importing vectors. Generate word vectors using GloVe (or any other model) and load them into the table. The documentation provides a Python script for importing from a vectors.txt file.

4. Creating an index. Choose an appropriate index type and metric. Cosine distance is often used for finding similar words. If you need filtering, hnsw_stream or mc_hnsw is suitable.

-- Example of an hnsw_stream index for cosine distance

CREATE INDEX idx_word_embeddings_hnsw_stream_cos ON word_embeddings

USING gann (embedding hnsw_stream_a) -- hnsw_stream_a for cosine

WITH (M = 16, ef_construction = 100); -- HNSW parameters

5. Search. Perform nearest neighbor search using SQL.

-- Find 10 words most similar to 'king'

SELECT word, embedding <=> (SELECT embedding FROM word_embeddings WHERE word = 'king') AS distance

FROM word_embeddings

ORDER BY embedding <=> (SELECT embedding FROM word_embeddings WHERE word = 'king')

LIMIT 10;

-- Example "king - man + woman = queen"

SELECT word, embedding <=> (

    (SELECT embedding FROM word_embeddings WHERE word = 'king')

    - (SELECT embedding FROM word_embeddings WHERE word = 'man')

    + (SELECT embedding FROM word_embeddings WHERE word = 'woman')

) AS distance

FROM word_embeddings

ORDER BY distance

LIMIT 10;

This classic example of vector arithmetic demonstrates how semantic relationships can be identified. In the search results, the word 'queen' will be among the closest vectors. If 'queen' doesn't come first, it’s often due to ambiguity in the training corpus (such as 'queen' as a monarch, a music band, or a chess piece). Nonetheless, its appearance at the top demonstrates vector analogy in action.

6. Don't forget to set the gann.hnsw_stream.efsearch parameter (or an equivalent one for other index types) to control search quality before executing queries:

SET gann.hnsw_stream.efsearch = 100; -- The higher, the more accurate but slower

Technical details: different scenarios and different indexes

pgpro_vector provides three index types, each optimized for different tasks:

• gannhnsw — maximum search speed with no filtering. Suitable for tasks like image recognition.

• hnsw_stream — allows use of WHERE conditions and returns any number of results. A good choice for product recommendations with category filtering.

• mc_hnsw (multi-column) — multi-column index, ideal for searching vector data with additional attributes. For example, finding similar users of a certain age and region.

This makes pgpro_vector a versatile tool for a wide range of tasks.

What to pay attention to

Like any technology, vector search has its nuances:

• ANN search is approximate. The higher the accuracy you want, the slower the search speed may be. This is a fine balance that is adjusted for each specific task.

• On large datasets, creating and using indexes requires significant amounts of RAM. If there is not enough memory, the indexes are split into parts (clusters), which can reduce the speed of queries.

• The higher the dimensionality of the vectors, the more resources are required to process them. For high-dimensional vectors (e.g., >1000), the binary quantization method is used, which reduces the size and speeds up the search.

Why vector search is the future

The main challenge for businesses today is to find relevant data quickly and efficiently. Vector search solves this problem by moving from simple character matching to understanding the meaning of the data.

The pgpro_vector extension makes this technology available to every Postgres Pro user right now. Now, there is no need for separate complex systems: the full power of vector search can be implemented right in the familiar and reliable DBMS.

By implementing vector search in your projects, you will be able to:

• increase the relevance of search results;

• improve the quality of recommendations;

• make the user experience more personal and enjoyable.