What Is PostgreSQL with pgvector and Why SpeedMVPs Uses It
pgvector is an open-source PostgreSQL extension that adds a vector data type and approximate nearest-neighbour search operators to standard Postgres. Installing it on a PostgreSQL database turns your existing relational database into a vector store, allowing you to store embeddings in a column of type vector(1536) and query them with the <=> (cosine distance), <#> (negative inner product), or <-> (L2 distance) operators. The case for pgvector over a dedicated vector store is strongest when three conditions are met: you are already running PostgreSQL for your application data, your vector dataset is under five million records, and your query latency requirements are satisfied by sub-200ms retrieval. Under these conditions, pgvector eliminates the operational complexity of managing a separate database service, the network round-trip cost of querying an external vector store, and the cost of a third-party managed service at low to medium scale. SpeedMVPs defaults to pgvector for AI MVPs for a pragmatic reason: the standard stack for a Next.js AI SaaS product includes a PostgreSQL database (via Supabase or Neon) for user data, subscriptions, and application state. Adding pgvector to that existing database means the AI layer is one ALTER TABLE and CREATE INDEX statement away from having vector search. The single-database architecture simplifies GDPR compliance documentation (one database to secure, back up, and audit), reduces infrastructure cost, and gives the team one fewer service to configure in every environment. For products that grow beyond pgvector's comfortable operating range, the migration to Pinecone or Weaviate is well-understood and SpeedMVPs plans for it from the start.
Setting Up PostgreSQL with pgvector in a Production AI Project
pgvector setup on a managed PostgreSQL provider (Supabase, Neon, or AWS RDS) is straightforward. First, enable the extension. On Supabase, run CREATE EXTENSION IF NOT EXISTS vector in the SQL editor. On Neon, pgvector is available by default on all instances. On RDS, enable the pgvector extension via the AWS RDS console parameter group or via SQL after the extension is available in your RDS engine version. Second, add vector columns to your schema. For a chunks table storing document chunks for RAG, add a column: ALTER TABLE chunks ADD COLUMN embedding vector(1536). The dimension count must match your embedding model: 1536 for text-embedding-3-small, 3072 for text-embedding-3-large, 768 for most open-source sentence-transformers models. Third, create an index. Without an index, vector search performs an exact nearest-neighbour scan (accurate but slow for large tables). The HNSW index (available in pgvector 0.5+) is the recommended production index type: CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64). The m and ef_construction parameters control the index quality and build time - higher values give better recall at the cost of longer index builds and more memory. Fourth, set the ef_search parameter at query time for recall tuning: SET hnsw.ef_search = 100. Higher ef_search improves recall at the cost of query latency. The default is 40; setting it to 100 to 200 recovers most of the recall loss from HNSW approximation for typical RAG workloads. Fifth, write the similarity search query. A parameterised query retrieving the top 10 most similar chunks to a query embedding: SELECT id, content, 1 - (embedding <=> $1::vector) AS similarity FROM chunks WHERE document_id = $2 ORDER BY embedding <=> $1::vector LIMIT 10. The WHERE clause pre-filters by document context before the similarity calculation, reducing the search space. Sixth, ensure connection pooling is configured. pgvector queries open database connections like any other Postgres query. On Supabase, use the pooler connection string (port 6543 for transaction mode). On Neon, connection pooling is built in. Without pooling, an AI product under load will exhaust Postgres connection limits.
Key Features and Capabilities
The most important capability pgvector adds is exact or approximate nearest-neighbour search within a standard SQL query. This means vector search can be combined with any other SQL construct: JOIN with other tables, WHERE clauses on relational columns, GROUP BY and aggregate functions, and transactions. This composability is pgvector's primary advantage over external vector databases, which require separate API calls that cannot participate in database transactions. Hybrid search in pgvector combines vector similarity search with PostgreSQL's full-text search. A common pattern is to compute a final score as a weighted combination of the cosine similarity from pgvector and the ts_rank score from a tsvector full-text search, then ORDER BY that combined score. This gives results that balance semantic relevance with keyword precision without requiring a separate search service. Row Level Security integration is a significant advantage for multi-tenant AI products built on Supabase. With RLS policies enabled on the chunks table, a query from a user's session automatically scopes vector search to only that user's authorised documents, enforced at the database level rather than the application level. This eliminates an entire class of potential data exposure bugs and simplifies the application code significantly. IVFFlat and HNSW index options give different performance profiles. IVFFlat divides the vector space into lists and searches a configurable number of lists per query. HNSW builds a layered graph structure with better recall and query performance than IVFFlat but slower index builds and higher memory usage. SpeedMVPs uses HNSW for production workloads where query latency and recall quality are the priority. pgvector 0.7+ added quantization options (scalar and binary) that reduce memory usage for HNSW indexes at a small recall cost. For large vector datasets, enabling binary quantization can make pgvector viable in database instances where the HNSW index would otherwise exceed available RAM.
Real-World Workflow: PostgreSQL with pgvector in an AI MVP
An example from SpeedMVPs: an AI document assistant for a UK solicitors' firm. The product indexed client matter documents, letting solicitors search across case files and ask natural language questions about specific matters. The compliance requirement was simple: all data stays within the firm's existing infrastructure, which ran on AWS RDS PostgreSQL in eu-west-2. Adding pgvector to the existing RDS instance (upgraded to a version supporting the extension) was the work of an afternoon. A migrations file added the extension, created the chunks table with a vector(1536) column, and created an HNSW index. The ingestion pipeline (a Python script running on AWS Lambda) fetched documents from the firm's document management system, chunked them, generated embeddings via OpenAI text-embedding-3-small, and inserted to the chunks table. The query API used a parameterised SQL query combining a pgvector similarity search with a filter on matter_id (ensuring solicitors only retrieved their own client matters) and a date range filter (recent documents ranked higher). The top 8 chunks were retrieved per query, formatted into the RAG prompt, and sent to GPT-4o via Azure OpenAI UK South for the response generation. Because all vector data lived in the existing RDS database, the GDPR Article 30 record of processing activities required no new entries beyond updating the existing RDS database entry to include the embedding column and its purpose. No new data processor agreements were needed for the vector store. The firm's existing RDS backup and encryption configuration covered the vector data automatically. The complete feature was delivered and integrated into the firm's existing matter management web application within two weeks, without any new database vendors or services.
Cost and Pricing Considerations
pgvector adds no licensing cost to your PostgreSQL database. The cost impact is increased storage (for embedding columns) and potentially requiring a larger instance to hold the HNSW index in RAM. Storage impact: 1536 float32 values per vector consume 6 KB per row. One million document chunks add approximately 6 GB to your database storage. On Supabase, storage costs USD 0.021 per GB per month, so one million vectors adds approximately USD 0.13 per month in storage - negligible. Memory impact: HNSW indexes are loaded into RAM for fast query performance. The index size depends on the number of vectors and the m parameter of the index. A rough estimate for an HNSW index with m=16 on one million 1536-dimension vectors is 6 to 10 GB of RAM. This drives the database instance size requirement and is the main cost factor for large vector datasets. For Supabase, the Pro plan (USD 25/month) includes 8 GB RAM on a shared instance - sufficient for indexes up to around 500,000 vectors with comfortable headroom. The Team plan (USD 599/month) or dedicated compute add-ons are required for larger datasets. For Neon, the compute cost scales with the instance size configured for the primary compute unit. Compared to Pinecone at the same scale (one million vectors on Serverless costs approximately USD 7 per month in query and storage units), pgvector on Supabase Pro is comparable in cost if you are already paying for the database. For pure vector store use without an existing Postgres workload, Pinecone Serverless is typically cheaper at small to medium scale.
Alternatives to PostgreSQL with pgvector
Pinecone is the most common alternative for teams who want a fully managed, purpose-built vector database. It offers better query performance at very large scale (tens of millions of vectors), simpler multi-tenancy via namespaces, and no infrastructure to manage beyond the API key. The tradeoff is a separate service to integrate and maintain, and a third-party vendor processing your vector data. Weaviate offers hybrid search (BM25 plus vector) as a native capability, which requires more complex SQL patterns to replicate in pgvector. For products where keyword-semantic hybrid search is central to the product experience, Weaviate's native hybrid search API is cleaner than implementing it manually in Postgres. Neon with pgvector is a strong alternative to Supabase with pgvector for teams prioritising serverless PostgreSQL with database branching. Neon's scale-to-zero cost model and branch-per-PR workflow suit AI MVP development particularly well. The pgvector functionality is identical between Neon and Supabase - the choice between them is about the broader database feature set and team preference. For teams that need vector search but want to avoid SQL entirely, Chroma or Qdrant provide Python-first APIs that may feel more natural for ML engineers coming from Python backgrounds. The tradeoff is the loss of SQL composability and RDBMS features like transactions and foreign key constraints.