How do we use pgvector in PostgreSQL for AI and Vector Search?
Certifications:
- How do we use pgvector in PostgreSQL for AI and Vector Search? - 26 October 2026
- How to enable Native Steaming Replication in PostgreSQL - 19 October 2026
- How to Partition an Existing Table in PostgreSQL and How to Maintain It - 12 October 2026
Modern artificial intelligence applications and Large Language Models are based on data called vectors or embeddings. PostgreSQL, thanks to its flexibility, can be transformed into a full vector database with the help of pgvector.
In this article we will see in simple words what it is, how it is installed in PostgreSQL in a RHEL environment, how it works in practice, and which indexes are best for us to use.
What is pgvector?
The pgvector is an extension for PostgreSQL that allows us to store vectors and perform semantic searches. Instead of just searching for keywords, we can find texts, images, or products that are similar to each other, making it ideal for artificial intelligence systems.
Installing pgvector on PostgreSQL 14
To add it to our server, we build it directly from source code with a few easy commands.
First, we install the necessary tools
sudo yum install -y git make gcc postgresql14-devel
Then, we download the code, define where the pg_config of version 14 and we do the installation:
cd /tmp git clone https://github.com/pgvector/pgvector.git cd pgvector make PG_CONFIG=/usr/pgsql-14/bin/pg_config sudo make PG_CONFIG=/usr/pgsql-14/bin/pg_config install
Finally, we connect to the database and activate the extension:
CREATE EXTENSION vector;
Example
After enabling the extention, we can create a table containing vector fields and try a search.
We create a table with 3 dimensions and add some records:
CREATE TABLE items (
id SERIAL PRIMARY KEY,
name TEXT,
embedding VECTOR(3)
);
INSERT INTO items (name, embedding) VALUES
('Item A', '[1, 2, 3]'),
('Item B', '[1, 1, 1]'),
('Item C', '[4, 5, 6]');
To find which object most resembles a particular vector based on Cosine distance, we run the following query:
SELECT name, embedding <=> '[1, 1, 2]' AS distance FROM items ORDER BY embedding <=> '[1, 1, 2]' LIMIT 2;
Which Index to choose: HNSW or IVFFlat?
When our data becomes too large (thousands or millions), simple searching becomes slow. Then we create special indexes to go faster. Then we have two options:
IVFFlat Index
The algorithm IVF Flat It divides the vectors into clusters so that the server only searches in nearby clusters. It is quite fast, but it requires that there is already enough data in the table when we create it, otherwise the database will warn us that it has low accuracy.
To create an IVFFlat index, we run:
CREATE INDEX items_ivfflat_idx ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
HNSW Index
The algorithm HNSW It creates a network-like structure and offers extremely high speed and top search accuracy. The only downside is that it consumes more RAM, so the choice depends on the resources available on our server.
To create an HNSW index, we run:
CREATE INDEX items_hnsw_idx ON items USING hnsw (embedding vector_cosine_ops);
