with pgvector

Powered by GlobalSolutions

PostgreSQL with pgvector is a powerful open-source vector database solution that adds native vector similarity search capabilities to PostgreSQL. Using the official pgvector extension, you can efficiently store and query high-dimensional vector embeddings (from OpenAI, Hugging Face, Cohere, and other models) directly inside your PostgreSQL tables.

It combines the reliability, ACID compliance, rich SQL features, and mature ecosystem of PostgreSQL with fast vector similarity search — making it ideal for RAG applications, semantic search, recommendation systems, chatbots, and more.

---

Access to the PostgreSQL Vector Database

We have created a sample database named sample_vector_db and enabled the pgvector extension in it.

You can create the same setup yourself by running the following commands:

CREATE DATABASE sample_vector_db;

\c sample_vector_db

CREATE EXTENSION IF NOT EXISTS vector;

SELECT * FROM pg_extension WHERE extname = 'vector';

---

How to Connect Using psql

Launch psql and connect to the sample database:

psql -h localhost -p 5432 -U postgres -d sample_vector_db

---

Working with Vectors in PostgreSQL

1. Create the items table

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT,
    metadata JSONB,
    embedding VECTOR(1536)
);

2. Insert Sample Data

INSERT INTO items (name, description, metadata, embedding)
VALUES 
('Sample Item 1', 
 'This is a sample item about artificial intelligence.',
 '{"category": "ai", "tags": ["ml", "vector"]}',
 '[0.12, 0.45, 0.67, ...]'::vector);

3. Perform Similarity Search (Cosine Distance)

SELECT 
    id, name, description,
    embedding <=> '[0.12, 0.45, 0.67, ...]'::vector AS distance
FROM items
ORDER BY embedding <=> '[0.12, 0.45, 0.67, ...]'::vector
LIMIT 5;

---

Adding an Index for Better Performance

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

---

Support

For any questions or assistance with your PostgreSQL + pgvector setup, reach out to us at support@theglobalsolutions.net.