{"id":6120,"date":"2026-10-26T07:00:00","date_gmt":"2026-10-26T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=6120"},"modified":"2026-10-23T21:51:55","modified_gmt":"2026-10-23T18:51:55","slug":"pos-kanoyme-chrisi-pgvector-stin-postgresql-ai-ve","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-kanoyme-chrisi-pgvector-stin-postgresql-ai-ve\/","title":{"rendered":"How do we use pgvector in PostgreSQL for AI and Vector Search?"},"content":{"rendered":"

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<\/strong>.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

<\/p>\n\n\n\n

What is pgvector?<\/h2>\n\n\n\n

The pgvector<\/strong> 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.<\/p>\n\n\n\n

Installing pgvector on PostgreSQL 14<\/h2>\n\n\n\n

To add it to our server, we build it directly from source code with a few easy commands.<\/p>\n\n\n\n

First, we install the necessary tools<\/p>\n\n\n\n

sudo yum install -y git make gcc postgresql14-devel<\/pre>\n\n\n\n
Then, we download the code, define where the pg_config<\/code> of version 14 and we do the installation:<\/p>\n\n\n\n
cd \/tmp\ngit clone https:\/\/github.com\/pgvector\/pgvector.git\ncd pgvector\nmake PG_CONFIG=\/usr\/pgsql-14\/bin\/pg_config\nsudo make PG_CONFIG=\/usr\/pgsql-14\/bin\/pg_config install<\/pre>\n\n\n\n
Finally, we connect to the database and activate the extension:<\/p>\n\n\n\n
CREATE EXTENSION vector;<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
After enabling the extention, we can create a table containing vector fields and try a search.<\/p>\n\n\n\n
We create a table with 3 dimensions and insert some records:<\/p>\n\n\n\n
CREATE TABLE items (\n    id SERIAL PRIMARY KEY,\n    name TEXT,\n    embedding VECTOR(3)\n);\n\nINSERT INTO items (name, embedding) VALUES \n('Item A', '[1, 2, 3]'),\n('Item B', '[1, 1, 1]'),\n('Item C', '[4, 5, 6]');<\/pre>\n\n\n\n
To find which object most resembles a particular vector based on Cosine distance, we run the following query:<\/p>\n\n\n\n
SELECT name, embedding <=> '[1, 1, 2]' AS distance\nFROM items\nORDER BY embedding <=> '[1, 1, 2]'\nLIMIT 2;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Which Index to choose: HNSW or IVFFlat?<\/h2>\n\n\n\n
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:<\/p>\n\n\n\n
IVFFlat Index<\/h4>\n\n\n\n
The algorithm IVF Flat<\/strong> 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.<\/p>\n\n\n\n
To create an IVFFlat index, we run:<\/p>\n\n\n\n
CREATE INDEX items_ivfflat_idx ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);\n<\/pre>\n\n\n\n
HNSW Index<\/h4>\n\n\n\n
The algorithm HNSW<\/strong> 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.<\/p>\n\n\n\n
To create an HNSW index, we run:<\/p>\n\n\n\n
CREATE INDEX items_hnsw_idx ON items USING hnsw (embedding vector_cosine_ops);<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n