pgvector - Stop Paying Pinecone's Ridiculous Per-Query Fees

PostgreSQL Architecture Overview: PostgreSQL uses a process-per-connection model with shared memory pools, background writer processes, and the WAL (Write-Ahead Log) system. Extensions like pgvector integrate directly into this architecture, storing vector data in regular PostgreSQL tables with specialized indexing.

pgvector is a PostgreSQL extension that lets you store and search vector embeddings without migrating to some expensive vector database service. Andrew Kane started it around 2021, I think, and it's become the go-to solution for engineers who don't want to explain to their boss why the AI features cost $5000/month in Pinecone fees.

Look, we all know the AI hype is real. Every product manager wants "semantic search" and "RAG applications" yesterday. Instead of spinning up another service that'll cost you an arm and a leg, pgvector lets you add vector search to your existing PostgreSQL database. You know, the one that's been rock-solid for the past decade.

Why Everyone's Moving to pgvector

The main reasons engineers pick pgvector over managed vector databases:

• It stores vectors up to 16,000 dimensions (which covers 99% of embedding models you'll actually use)
• HNSW and IVFFlat indexes for fast similarity search (HNSW is usually better unless you're broke and can't afford the RAM)
• Works with regular SQL queries so you can combine vector search with WHERE clauses without writing some proprietary query language
• ACID transactions because your data integrity actually matters in production
• Uses PostgreSQL tooling you already know instead of learning another database admin interface

Version 0.8.x Finally Fixed the Shit That Mattered

pgvector 0.8.x finally fixed the shit from earlier versions (I think 0.8.0 dropped in like November 2024, maybe?):

• Way faster queries than 0.7.x (which was painfully slow with any WHERE clauses - like watching paint dry)
• Actually returns complete results for filtered searches (0.7.x would return like 3 results out of 50 and call it a day)
• Iterative index scans so combining vector search with filters doesn't return garbage anymore
• Less terrible query planning because PostgreSQL's cost estimation was completely fucked with vectors before

Translation: it actually works in production now instead of just demos.

Where You Can Actually Use It

pgvector is available pretty much everywhere PostgreSQL runs:

Cloud Providers (the easy button):

• Amazon RDS - works but you'll wait forever for new versions
• Google Cloud SQL - decent support, reasonable pricing
• Azure Database for PostgreSQL - supports Flexible Server configs

Managed PostgreSQL Services (often better than cloud giants):

• Supabase - enable with one click, great for prototyping
• Neon - serverless PostgreSQL that doesn't suck
• Timescale Cloud - if you need time-series data with vectors
• Crunchy Bridge - reliable managed PostgreSQL

Installation Methods:

• Docker (recommended for dev work)
• Package managers (Homebrew, APT, Yum, conda-forge)
• Compile from source if you hate yourself

Programming Languages:
Client libraries for like 20+ languages including Python, JavaScript, Go, Rust, and Java. Python library is solid with proper NumPy integration. JavaScript works fine but watch out for type issues. Go library is decent if you enjoy writing verbose SQL everywhere. Rust has all the features but enjoy your 10+ minute compile times. Java library works fine if you're stuck in Spring Boot hell - integrates with JDBC and Hibernate without much drama.

Bottom line: if you're already running PostgreSQL and need vector search, pgvector is probably your best bet. Unless you're Google or have unlimited budget, in which case knock yourself out with the fancy stuff.

But before you jump in, you need to understand what you're actually getting into - the capabilities, the limitations, and the production gotchas that'll bite you if you're not careful.

pgvector adds vector search to PostgreSQL, but there are production gotchas that'll bite you if you're not careful.

Vector Storage Types (And Their Memory Traps)

pgvector gives you four data types, each with different ways to eat your RAM:

• vector: Regular floats, 16k dimensions max. Takes 4 * dimensions + 8 bytes per vector. A million 1536-dimensional vectors? That's 6GB just for the vectors.
• halfvec: Half-precision floats, 2 * dimensions + 8 bytes. Saves memory but your similarity scores might look weird due to precision loss.
• bit: Binary vectors up to 64k dimensions. Great if you're into quantization, useless otherwise.
• sparsevec: Sparse vectors, 8 * non-zero elements + 16 bytes. Works well for NLP embeddings with lots of zeros.

Distance Functions That Actually Matter

Vector Distance Functions: The choice between L2 (Euclidean), cosine, and inner product distance significantly impacts search accuracy. L2 works well for most embeddings, cosine is ideal for normalized vectors (most modern AI models), and inner product handles non-normalized data but returns negative values due to PostgreSQL's ascending sort requirement.

Six distance operators, but you'll probably only use three:

• <-> (L2/Euclidean): The default everyone uses
• <=> (Cosine): For normalized embeddings, which most modern models output
• <#> (Inner product): Returns negative values because PostgreSQL only sorts ascending (yes, really)
• <+>, <~>, <%>: Manhattan, Hamming, and Jaccard for specialized cases

Pro tip: If your similarity search returns garbage results, check if your vectors are normalized. Cosine distance assumes normalized vectors, and you'll hate your life if they're not.

The Two Index Types (And Why You'll Probably Pick Wrong)

HNSW - Fast Queries, Slow Builds, RAM Hog

HNSW indexes create multilayer graphs that query fast but:

• Take forever to build: Hours for millions of vectors, and it might crash your server if you don't have enough memory
• Memory hungry: The whole graph needs to fit in memory or performance dies
• Parameters you WILL need to tune: m=16, ef_construction=64 are decent defaults, but increase `maintenance_work_mem` to 4GB+ or the build will fail

-- This will probably timeout on your first try
SET maintenance_work_mem = '4GB';  -- Hope you have the RAM
CREATE INDEX CONCURRENTLY items_embedding_idx ON items
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

IVFFlat - Faster Builds, Worse Performance

IVFFlat partitions your vector space:

• Builds faster than HNSW but queries slower
• Uses less memory during index building
• Needs tuning: Set lists to rows/1000 for small datasets, sqrt(rows) for large ones
• Query parameter: ivfflat.probes controls how many partitions to search (higher = more accurate, slower)

Version 0.8.x Fixed the Filtering Disaster

Before 0.8.0, combining vector search with WHERE clauses was a nightmare. Queries would return 3 results when you asked for 20 because filtering happened after the vector index scan.

0.8.0+ added iterative scanning that actually works:

-- This would return incomplete results in 0.7.x
-- Now it actually works properly
SET hnsw.iterative_scan = 'relaxed_order';

SELECT title, embedding <=> '[your_query_vector]' as distance
FROM documents
WHERE category = 'technical' AND published_at > '2024-01-01'
ORDER BY distance
LIMIT 10;

The relaxed_order setting lets PostgreSQL scan more vectors to find enough filtered results. Without it, you're back to the bad old days of incomplete result sets.

Production Memory Gotchas

HNSW Index Building Memory Explosion: HNSW indexes are memory hogs during builds - I've seen them eat like 10x-12x more RAM than the final index, maybe more. I learned this the hard way when building an index on ~2M vectors crashed our 16GB server at like 2 AM - maybe 2:30? Was definitely middle of the night. The ~800MB final index somehow ate like 12GB while building - maybe more, the server basically died. Budget way more RAM than you think you need or your server will crash.

Query Memory Usage: Set work_mem appropriately for vector queries. Too low and you get disk spills, too high and you run out of memory with concurrent queries. Sweet spot is usually 256MB-512MB per connection for typical embedding sizes.

Memory Usage Reality Check:

• A million 1536-dimensional vectors eats roughly 6GB just for the raw data
• HNSW indexes add another chunk on top - maybe like 20-30%, could be way more
• During index building, expect it to balloon to 8-12x the final size or your server dies
• Example: ~800MB final index somehow consumed like 12GB during build, then settled back to maybe ~1GB total (data + index)
• Concurrent queries each need their own work_mem chunk - I usually set like 256-512MB per connection but YMMV

Other Features You Might Actually Use

• Subvector searching: Index part of your vectors if dimensions are too high
• Vector arithmetic: Add/subtract vectors directly in SQL
• Binary quantization: Convert to binary for memory savings (with accuracy loss)

Real Production Use Cases

Common Vector Search Applications: RAG systems store document chunks alongside metadata for context-aware retrieval. Semantic search replaces keyword matching with meaning-based queries. Recommendation engines combine user behavior vectors with content embeddings. Deduplication systems identify similar content across large datasets using vector similarity thresholds.

pgvector works well for:

• RAG applications: Store document embeddings alongside metadata
• Semantic search: Better than Elasticsearch for many use cases
• Recommendation systems: User/item embeddings with SQL joins for complex filtering
• Content deduplication: Find similar content at scale

Just remember: pgvector is PostgreSQL first, vector database second. If you need Pinecone-level performance, you'll need to tune the shit out of it or consider dedicated vector databases.

The big question is whether pgvector can actually compete with dedicated vector databases or if you're just setting yourself up for disappointment.

Getting pgvector running is straightforward until you hit the gotchas they don't mention in the docs.

Cloud Providers (The Easy-ish Button)

Cloud Provider Comparison: AWS RDS/Aurora offers stability but slow updates. Google Cloud SQL provides good performance with reasonable pricing. Azure has decent pgvector support but poor documentation. Managed services like Supabase and Neon often offer faster feature adoption and better developer experience than big cloud providers.

Amazon RDS/Aurora

• Amazon RDS for PostgreSQL has pgvector 0.8.0 on PostgreSQL 13+, but you'll wait 6 months for version updates
• Amazon Aurora PostgreSQL claims 9x performance improvements, which is marketing speak for "better than the broken 0.7.x versions"

Google Cloud

• Cloud SQL for PostgreSQL supports pgvector, decent performance, reasonable pricing
• AlloyDB is Google's fancy PostgreSQL with better analytical performance (if you can afford it)

Azure

• Azure Database for PostgreSQL works across Flexible Server configs, but their documentation sucks

Managed PostgreSQL (Often Better Than Big Cloud)

• Supabase: One-click enable, great for prototyping, gets expensive fast
• Neon: Serverless PostgreSQL that doesn't completely suck
• Timescale Cloud: Good if you need time-series + vectors
• Crunchy Bridge: Reliable managed PostgreSQL from people who actually know PostgreSQL

Local Development Setup

Docker (Least Painful Option)

## Pull the official image (they update it regularly, unlike cloud providers)
docker pull pgvector/pgvector:pg17

## Run it with enough memory or you'll regret it later
docker run --name pgvector-dev \
  -e POSTGRES_PASSWORD=password \
  -p 5432:5432 \
  -m 4g \
  -d pgvector/pgvector:pg17

## Enable the extension
psql -h localhost -U postgres -c \"CREATE EXTENSION vector;\"

Homebrew (macOS)

## This usually works but might break on macOS updates
brew install pgvector
psql -d postgres -c \"CREATE EXTENSION vector;\"

Linux Package Managers

Ubuntu/Debian:

## Add the PostgreSQL APT repository if you haven't already
sudo apt install postgresql-15-pgvector
sudo systemctl restart postgresql
## Enable with: CREATE EXTENSION vector;

Red Hat/CentOS: sudo yum install pgvector_15
FreeBSD: pkg install postgresql15-pgvector

Compile From Source (If You Hate Yourself)

git clone --branch v0.8.1 https://github.com/pgvector/pgvector.git
cd pgvector
make  # Pray you have all the dev dependencies
sudo make install  # Hope it doesn't break your existing PostgreSQL

Check the GitHub releases for the latest version - they update it fairly regularly.

Your First Vector Query (That Won't Immediately Fail)

-- Enable the extension (obviously)
CREATE EXTENSION vector;

-- Create a table - note the dimension count matters for performance
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    embedding vector(1536),  -- OpenAI ada-002 dimensions
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert some test data (replace with actual embeddings)
INSERT INTO documents (title, content, embedding)
VALUES
    ('Test Doc', 'Some content', '[0.1, 0.2, 0.3, ...]'),  -- 1536 dimensions needed
    ('Another Doc', 'More content', '[0.2, 0.1, 0.4, ...]');

-- Create the index AFTER inserting data (faster than building empty index)
-- This will take a while and use a lot of RAM
SET maintenance_work_mem = '4GB';  -- Adjust based on your server
CREATE INDEX CONCURRENTLY documents_embedding_idx ON documents
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Test a similarity search
SELECT title, embedding <=> '[0.15, 0.25, 0.35, ...]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 5;

Production Configuration (Or Your Index Builds Will Fail)

Memory Settings That Actually Work:

-- For index building - adjust based on your RAM
SET maintenance_work_mem = '8GB';  -- 4GB minimum, I usually go higher

-- For queries - tune based on concurrent connections
SET work_mem = '256MB';  -- Start here, maybe 512MB if you have the RAM

-- pgvector specific settings
SET hnsw.ef_search = 40;  -- Start low, bump up if recall sucks
SET hnsw.iterative_scan = 'relaxed_order';  -- Fixes the filtering disaster

Index Building Best Practices:

-- Build indexes CONCURRENTLY on live tables (takes longer but doesn't block)
CREATE INDEX CONCURRENTLY items_embedding_idx ON items
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Add regular B-tree indexes on columns you filter by
CREATE INDEX items_category_idx ON items (category);
CREATE INDEX items_created_at_idx ON items (created_at);

Client Libraries (Quality Varies)

The Python library is solid with proper NumPy integration:

pip install pgvector psycopg2-binary

Other languages have varying quality:

• JavaScript/Node.js: Decent, actively maintained, doesn't crash on type errors
• Go: Works fine for most use cases if you enjoy verbose SQL
• Rust: Good if you're into fighting the borrow checker for database queries
• Java: Exists, probably works, nobody's complained yet

Monitoring Your Vector Queries

PostgreSQL's built-in monitoring tools will save your ass:

-- Enable query stats if not already on
-- Add to postgresql.conf: shared_preload_libraries = 'pg_stat_statements'

-- See your slowest vector queries
SELECT
    query,
    calls,
    mean_time,
    max_time,
    total_time
FROM pg_stat_statements
WHERE query ILIKE '%<=>%' OR query ILIKE '%vector%'
ORDER BY mean_time DESC
LIMIT 10;

-- Check if your indexes are being used
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,  -- Should be > 0
    idx_tup_read
FROM pg_stat_user_indexes
WHERE indexname LIKE '%embedding%'
ORDER BY idx_scan DESC;

-- Monitor index build progress (for long-running builds)
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query
FROM pg_stat_activity
WHERE query LIKE '%CREATE INDEX%';

Production Gotchas to Avoid

1. Don't build indexes during peak traffic - HNSW index builds are CPU and memory hogs. Learned this when some genius (me) kicked off an index build at like 3PM and brought down our API for maybe 20 minutes because it ate every available CPU core. Boss was definitely not amused.

2. Monitor your maintenance_work_mem - too low and builds fail with cryptic "could not extend file" errors, too high and you OOM the entire server. Start with like 4GB for million-vector datasets, maybe more.

3. Test your queries with real data sizes - what works with 1k vectors might not work with 1M. A query that takes like 5ms with 10k vectors can take 5+ seconds with 1M vectors if your indexes aren't tuned properly.

4. Set up proper monitoring - vector query performance is fucking unpredictable. I've seen HNSW queries vary from like 50ms to 2+ seconds on identical data because the graph traversal hits different paths. Your monitoring dashboards will look like a heart attack in progress.

5. Have a rollback plan - vector indexes can't always be rebuilt quickly. Plan for like 2-4 hours to rebuild large HNSW indexes, maybe longer, and have a read replica ready if you need zero-downtime migrations.

6. Watch for PostgreSQL version gotchas - pgvector 0.7.x had severe performance issues with PostgreSQL 15.3 that weren't fixed until 0.8.0. Always test in staging with your exact PostgreSQL version first.

With pgvector properly installed and tuned, you can build decent vector search applications. Just remember it's PostgreSQL first, vector database second - tune accordingly or prepare for 3AM debugging sessions when your "fast" vector queries start taking 30 seconds each.