PostgreSQL pgVector Setup Guide: Step-by-Step Installation for AI Applications

# Update package list
sudo apt update

# Install PostgreSQL and development packages
sudo apt install postgresql postgresql-contrib postgresql-server-dev-all

# Start and enable PostgreSQL service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Check PostgreSQL status
sudo systemctl status postgresql

# Install required build tools
sudo apt install -y build-essential git cmake

# Verify installation
gcc --version
make --version
git --version

# Clone pgVector repository
git clone --branch v0.5.1 https://github.com/pgvector/pgvector.git
cd pgvector

# Compile the extension
make clean
make

# Install pgVector (requires sudo)
sudo make install

# Verify installation files
ls -la /usr/lib/postgresql/*/lib/vector.so
ls -la /usr/share/postgresql/*/extension/vector*

# Restart PostgreSQL to load the new extension
sudo systemctl restart postgresql

# Check logs for any errors
sudo tail -f /var/log/postgresql/postgresql-*.log

# Switch to postgres user and connect to PostgreSQL
sudo -u postgres psql

# Create a new database for vector operations
CREATE DATABASE vectordb;

# Connect to the new database
\c vectordb

# Enable pgVector extension
CREATE EXTENSION vector;

# Verify installation
SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';

# Test basic functionality
SELECT '[1,2,3]'::vector <-> '[1,2,4]'::vector AS distance;

# Pull pgVector-enabled PostgreSQL image
docker pull pgvector/pgvector:pg16

# Run PostgreSQL with pgVector
docker run --name postgres-vector \
  -e POSTGRES_DB=vectordb \
  -e POSTGRES_USER=vector_user \
  -e POSTGRES_PASSWORD=vector_password \
  -p 5432:5432 \
  -v postgres_data:/var/lib/postgresql/data \
  -d pgvector/pgvector:pg16

# Connect to the database
docker exec -it postgres-vector psql -U vector_user -d vectordb

# Enable pgVector extension
CREATE EXTENSION vector;

FROM postgres:15

# Install build dependencies
RUN apt-get update && apt-get install -y \
    build-essential \
    git \
    postgresql-server-dev-15 \
    && rm -rf /var/lib/apt/lists/*

# Clone and build pgVector
RUN git clone --branch v0.5.1 https://github.com/pgvector/pgvector.git /tmp/pgvector \
    && cd /tmp/pgvector \
    && make clean \
    && make \
    && make install \
    && rm -rf /tmp/pgvector

# Cleanup build dependencies
RUN apt-get remove -y build-essential git postgresql-server-dev-15 \
    && apt-get autoremove -y \
    && apt-get clean

version: '3.8'

services:
  postgres-vector:
    build: .
    container_name: postgres-vector
    environment:
      POSTGRES_DB: vectordb
      POSTGRES_USER: vector_user
      POSTGRES_PASSWORD: vector_password
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    command: >
      postgres 
      -c shared_preload_libraries=vector
      -c max_connections=200
      -c shared_buffers=256MB
      -c effective_cache_size=1GB

volumes:
  postgres_data:

-- Initialize pgVector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create sample table
CREATE TABLE IF NOT EXISTS documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(384),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Create index for fast similarity search
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

# Build and start services
docker-compose up -d

# Check logs
docker-compose logs postgres-vector

# Connect to database
docker-compose exec postgres-vector psql -U vector_user -d vectordb

# Install Homebrew if not already installed
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# Install PostgreSQL
brew install postgresql@15

# Start PostgreSQL service
brew services start postgresql@15

# Add PostgreSQL to PATH
echo 'export PATH="/opt/homebrew/opt/postgresql@15/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

# Install development tools
xcode-select --install

# Clone pgVector
git clone --branch v0.5.1 https://github.com/pgvector/pgvector.git
cd pgvector

# Set PostgreSQL path
export PG_CONFIG=/opt/homebrew/opt/postgresql@15/bin/pg_config

# Compile and install
make clean
make
make install

# Restart PostgreSQL
brew services restart postgresql@15

# Create database
createdb vectordb

# Connect and enable extension
psql vectordb

# Enable pgVector
CREATE EXTENSION vector;

# Test installation
SELECT '[1,2,3]'::vector <-> '[1,2,4]'::vector AS distance;

# Open Command Prompt as Administrator
# Clone pgVector
git clone https://github.com/pgvector/pgvector.git
cd pgvector

# Set environment variables
set PGROOT=C:\Program Files\PostgreSQL\15
set PATH=%PGROOT%\bin;%PATH%

# Compile (requires Visual Studio tools)
nmake /f Makefile.win

# Install
nmake /f Makefile.win install

# Install WSL if not already installed
wsl --install

# Follow Ubuntu installation steps within WSL
# Access from Windows using localhost:5432

# Launch Ubuntu EC2 instance
# Follow Ubuntu installation steps
# Configure security groups for port 5432

# Install PostgreSQL and pgVector
sudo apt update
sudo apt install postgresql postgresql-contrib postgresql-server-dev-15
# ... follow Ubuntu installation steps

# Configure for remote access
sudo nano /etc/postgresql/15/main/postgresql.conf
# Set: listen_addresses = '*'

sudo nano /etc/postgresql/15/main/pg_hba.conf
# Add: host all all 0.0.0.0/0 md5

sudo systemctl restart postgresql

# Create Ubuntu droplet
# SSH into droplet
ssh root@your-droplet-ip

# Install PostgreSQL with pgVector
# Follow Ubuntu installation steps
# Configure firewall
ufw allow 5432/tcp

# Create Compute Engine instance with Ubuntu
# SSH using gcloud CLI
gcloud compute ssh your-instance-name

# Install PostgreSQL and pgVector
# Follow Ubuntu installation steps

# Configure firewall rules
gcloud compute firewall-rules create allow-postgresql \
  --allow tcp:5432 \
  --source-ranges 0.0.0.0/0

# docker-compose.yml for cloud deployment
version: '3.8'

services:
  postgres-vector:
    image: pgvector/pgvector:pg16
    container_name: postgres-vector
    environment:
      POSTGRES_DB: vectordb
      POSTGRES_USER: ${DB_USER}
      POSTGRES_PASSWORD: ${DB_PASSWORD}
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
    restart: unless-stopped
    command: >
      postgres 
      -c max_connections=200
      -c shared_buffers=256MB
      -c effective_cache_size=1GB
      -c work_mem=16MB

volumes:
  postgres_data:

# Memory settings
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 16MB
maintenance_work_mem = 256MB

# Performance settings
random_page_cost = 1.1
effective_io_concurrency = 200

# Parallel processing
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_worker_processes = 8

# Connection settings
max_connections = 200

-- Connect to your database
\c vectordb

-- Create extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Create sample table with proper indexing
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    embedding vector(384),  -- Adjust dimension as needed
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Create indexes for fast searches
CREATE INDEX documents_embedding_idx 
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

CREATE INDEX documents_metadata_idx 
ON documents USING gin (metadata);

CREATE INDEX documents_created_at_idx 
ON documents (created_at);

-- Create trigger for updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_documents_updated_at 
    BEFORE UPDATE ON documents 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Create application user
CREATE USER app_user WITH ENCRYPTED PASSWORD 'secure_password';

-- Grant necessary permissions
GRANT CONNECT ON DATABASE vectordb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO app_user;
GRANT USAGE ON SEQUENCE documents_id_seq TO app_user;

-- Create read-only user for analytics
CREATE USER readonly_user WITH ENCRYPTED PASSWORD 'readonly_password';
GRANT CONNECT ON DATABASE vectordb TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON documents TO readonly_user;

-- Test vector operations
SELECT 
    '[1,2,3]'::vector <-> '[1,2,4]'::vector AS l2_distance,
    '[1,2,3]'::vector <#> '[1,2,4]'::vector AS negative_inner_product,
    '[1,2,3]'::vector <=> '[1,2,4]'::vector AS cosine_distance;

-- Test vector functions
SELECT 
    vector_dims('[1,2,3,4]'::vector) AS dimensions,
    vector_norm('[3,4]'::vector) AS norm;

-- Insert test data
INSERT INTO documents (title, content, embedding) VALUES 
('Test Doc 1', 'This is a test document', '[0.1, 0.2, 0.3]'),
('Test Doc 2', 'Another test document', '[0.2, 0.1, 0.4]'),
('Test Doc 3', 'Third test document', '[0.3, 0.4, 0.1]');

-- Test similarity search
SELECT 
    title,
    embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
ORDER BY embedding <-> '[0.1, 0.2, 0.3]'::vector
LIMIT 3;

-- Create larger test dataset
INSERT INTO documents (title, content, embedding)
SELECT 
    'Document ' || i,
    'Content for document ' || i,
    (SELECT array_agg(random()) FROM generate_series(1, 384))::vector
FROM generate_series(1, 10000) i;

-- Test query performance
EXPLAIN (ANALYZE, BUFFERS)
SELECT title, embedding <-> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
ORDER BY embedding <-> '[0.1, 0.2, 0.3]'::vector
LIMIT 10;

-- Check index usage
SELECT 
    schemaname, 
    tablename, 
    indexname, 
    idx_scan, 
    idx_tup_read
FROM pg_stat_user_indexes 
WHERE tablename = 'documents';

# Python test script
import psycopg2
import numpy as np

# Database connection
conn = psycopg2.connect(
    host='localhost',
    database='vectordb',
    user='app_user',
    password='secure_password'
)

cur = conn.cursor()

# Test vector insertion
embedding = np.random.rand(384).tolist()
cur.execute("""
    INSERT INTO documents (title, content, embedding) 
    VALUES (%s, %s, %s)
""", ('Python Test', 'Document from Python', embedding))

# Test similarity search
query_vector = np.random.rand(384).tolist()
cur.execute("""
    SELECT title, embedding <-> %s::vector AS distance
    FROM documents
    ORDER BY embedding <-> %s::vector
    LIMIT 5
""", (query_vector, query_vector))

results = cur.fetchall()
for title, distance in results:
    print(f"{title}: {distance:.4f}")

conn.commit()
cur.close()
conn.close()

-- Check if extension files are installed
SELECT * FROM pg_available_extensions WHERE name = 'vector';

-- If not available, reinstall pgVector
-- sudo make install (Linux/macOS)

# Ensure you have proper permissions
sudo chown -R $USER:$USER pgvector/
cd pgvector
make clean
make
sudo make install

# Ubuntu/Debian
sudo apt install postgresql-server-dev-15

# macOS
export PG_CONFIG=/opt/homebrew/opt/postgresql@15/bin/pg_config

# Add to PATH permanently
echo 'export PATH="/opt/homebrew/opt/postgresql@15/bin:$PATH"' >> ~/.zshrc

# Check PostgreSQL status
sudo systemctl status postgresql

# Check configuration
sudo nano /etc/postgresql/15/main/postgresql.conf
sudo nano /etc/postgresql/15/main/pg_hba.conf

# Restart service
sudo systemctl restart postgresql

-- Check if indexes exist
\d+ documents

-- Create appropriate index
CREATE INDEX CONCURRENTLY documents_embedding_hnsw_idx 
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Update statistics
ANALYZE documents;

-- Check current settings
SHOW shared_buffers;
SHOW work_mem;

-- Adjust in postgresql.conf
-- shared_buffers = 256MB
-- work_mem = 16MB

# Check container logs
docker logs postgres-vector

# Verify environment variables
docker exec postgres-vector env | grep POSTGRES

# Test connection
docker exec -it postgres-vector psql -U vector_user -d vectordb

# Check volume mounting
docker volume ls
docker volume inspect postgres_data

# Ensure proper permissions
docker exec -it postgres-vector ls -la /var/lib/postgresql/data

-- Check pgVector version
SELECT extversion FROM pg_extension WHERE extname = 'vector';

-- Check available vector operators
SELECT oprname, oprleft::regtype, oprright::regtype 
FROM pg_operator 
WHERE oprname IN ('<->', '<#>', '<=>');

-- Verify vector functions
\df *vector*

-- Check index methods
SELECT amname FROM pg_am WHERE amname IN ('hnsw', 'ivfflat');