Vector Database: pgvector Setup Step by Step

What Is a Vector Database?

A vector database is a storage system designed specifically to handle data in the form of numerical vectors. In the AI world, every piece of text, image, or audio gets converted into a vector — an array of numbers representing the semantic meaning of the content. Traditional databases search by exact match. Vector databases search by semantic similarity.

pgvector is an open-source extension for PostgreSQL that adds vector storage and similarity search capabilities directly inside PostgreSQL. This means you do not need a separate database — you use the PostgreSQL you already know with full vector capabilities built in.

Why pgvector Specifically?

Feature	pgvector	Pinecone	Weaviate	Milvus
Open Source	Yes	No	Yes	Yes
Runs Inside PostgreSQL	Yes	No	No	No
Additional Cost	Zero	Monthly subscription	Separate resources	Separate resources
data protection and localization	Full	External cloud	Depends on hosting	Depends on hosting
Standard SQL	Yes	API only	GraphQL	API only
Maturity	3+ years	4+ years	3+ years	4+ years

The most important advantage: your data stays on your Saudi server. You do not send vectors representing your sensitive data to an external service.

Server Requirements

To run pgvector efficiently you need:

• CPU: 2 vCPU minimum (4 vCPU for production)
• RAM: 4 GB minimum — vectors are memory-intensive
• Storage: SSD mandatory — indexes require fast read speeds
• OS: Ubuntu 22.04 or 24.04

A Cloud Server from CLOUD (cloud.net.sa) on the Growth plan (2 vCPU, 4 GB RAM) is sufficient to start, with the option to scale resources later based on your data volume.

Step-by-Step Installation

1. Install PostgreSQL 16

sudo apt update
sudo apt install -y postgresql-16 postgresql-server-dev-16

2. Build pgvector from Source

sudo apt install -y build-essential git
cd /tmp
git clone --branch v0.7.4 https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

3. Enable the Extension

sudo -u postgres psql
CREATE EXTENSION vector;
\dx

You should see vector listed in the installed extensions.

4. Create a Table with a Vector Column

CREATE TABLE documents (
 id SERIAL PRIMARY KEY,
 content TEXT NOT NULL,
 embedding vector(1536),
 metadata JSONB,
 created_at TIMESTAMP DEFAULT NOW
);

The number 1536 is the vector dimension — it depends on the embedding model used. OpenAI text-embedding-3-small produces 1536 dimensions.

Creating Indexes

Indexes are what make search fast. Without an index, pgvector scans every row — extremely slow with millions of records.

IVFFlat Index (Faster to Build)

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

HNSW Index (More Accurate Results)

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Index Type	Build Speed	Search Accuracy	Memory Usage
IVFFlat	Fast	95-98%	Low
HNSW	Slow	99%+	High

Recommendation: Start with HNSW if your data is under 5 million records. Switch to IVFFlat if you need to rebuild indexes frequently.

Similarity Search Queries

Nearest Neighbor Search

SELECT id, content,
 embedding <=> '[0.1, 0.2, ...]'::vector AS distance
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

The <=> operator calculates cosine distance. The smaller the value, the greater the similarity.

Search with Filtering

SELECT id, content
FROM documents
WHERE metadata->>'category' = 'legal'
ORDER BY embedding <=> $1
LIMIT 5;

This combines semantic search with traditional filtering — a capability not easily available in standalone vector databases.

Practical Application: RAG System

A RAG (Retrieval-Augmented Generation) system uses pgvector to enhance AI model responses with local data:

1. Split documents into small chunks (500-1000 characters)
2. Generate a vector for each chunk using an embedding model
3. Store vectors in pgvector
4. On query: convert the question to a vector and search for nearest results
5. Send results along with the question to the language model

This system lets you build a private ChatGPT trained on your company data — and the data stays on your server in Saudi Arabia.

Performance Tuning

• shared_buffers: Set to 25% of available memory
• effective_cache_size: 75% of memory
• maintenance_work_mem: 1-2 GB during index building
• work_mem: 256 MB minimum for vector queries

# postgresql.conf
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 1GB
work_mem = 256MB

Python Integration

import psycopg2
from pgvector.psycopg2 import register_vector

conn = psycopg2.connect("host=localhost dbname=mydb")
register_vector(conn)

cur = conn.cursor
cur.execute(
 "SELECT content FROM documents ORDER BY embedding <=> %s LIMIT 5",
 (embedding,)
)
results = cur.fetchall

Next Steps

pgvector transforms PostgreSQL from a traditional database into a full semantic search engine. With a Cloud Server from CLOUD (cloud.net.sa) in Riyadh, you get sub-very low latency and complete data independence over your data. Start with the Growth plan and scale resources based on your data volume — no complex contracts or hidden fees.

---

Get Started

Ready to run AI on your own server? Explore AI hosting solutions or configure your server with the specs you need.