What pgvector actually is

pgvector is an open source PostgreSQL extension that adds a native vector data type plus similarity search operators to your existing Postgres database. It is not a separate database, not a managed service, and not a competitor to Pinecone or Weaviate in the sense of being a standalone system. It is a Postgres extension, which means it runs inside the same process as your relational data and uses the same authentication, backup, and replication infrastructure you already have.

Technically, pgvector introduces a vector type that stores fixed-dimensional arrays of floats, typically 32-bit floats but with half-precision support in newer versions. It adds three distance operators, <-> for Euclidean distance, <=> for cosine distance, and <#> for negative inner product. It also adds two index types, IVFFlat and HNSW, which let you trade exact recall for query speed at scale.

The extension was originally developed by Supabase engineers and is now maintained as a community project under the PostgreSQL license. It works with any standard Postgres installation from version 11 onward in most setups, though the current version of pgvector typically targets the latest stable Postgres releases.

The practical implication is that if you already run Postgres, you can add semantic search to your application without spinning up new infrastructure, learning a new query language, or syncing data between systems. That single fact is the reason pgvector has become the default choice for a large number of teams building retrieval augmented generation systems on top of existing data.

Setup and authentication

pgvector installs the same way as any other Postgres extension. There is no separate authentication layer because the extension runs inside your database and inherits whatever auth model your Postgres cluster already uses, whether that is password, SCRAM, IAM, or certificate based.

For a local install on macOS using Homebrew, the typical flow is to install Postgres first, then add the pgvector formula. On Linux with apt, you install the postgresql-XX-pgvector package matching your server version. On Windows, the community provides prebuilt binaries. If you are running a managed service like Supabase, Neon, RDS, or AlloyDB, pgvector is usually available as a one click enable or a simple CREATE EXTENSION call.

The actual SQL to enable it once Postgres is running is:

CREATE EXTENSION IF NOT EXISTS vector;

That single statement registers the extension, creates the support functions, and makes the vector type available. There is no API key, no separate user, and no network call to a third party. Your existing connection string works unchanged.

If you are connecting from an application, you use the standard libpq connection string or any Postgres driver in your language of choice. The pgvector project itself does not ship a client SDK, which is a feature rather than a limitation, because every Postgres driver already speaks the protocol.

For production deployments, the only auth consideration is making sure the role your application uses has CREATE permission on the target schema and INSERT permission on the vector table. There is nothing pgvector specific beyond standard Postgres privilege management.

First working example

Let’s walk through a complete example from an empty database to a working similarity query. Assume you have Postgres running locally and have already run CREATE EXTENSION vector.

First, create a table to store documents alongside their embeddings. A common pattern is to keep the source text and metadata in the same row as the vector, which lets you filter and join in a single query.

CREATE TABLE documents ( id bigserial PRIMARY KEY, content text, source text, embedding vector(1536) );

The 1536 dimension matches OpenAI’s text-embedding-3-small and ada-002 models. If you use sentence-transformers all-MiniLM-L6-v2, the dimension is 384. The dimension must match what your embedding model produces exactly, or inserts will fail.

Next, generate embeddings and insert them. From Python with the openai library, a typical flow is to chunk your source text, call the embeddings endpoint, and insert the result. The SQL insert looks like:

INSERT INTO documents (content, source, embedding) VALUES ($1, $2, $3::vector);

Pass the embedding as a JSON array string and cast it to vector. Postgres parses it on insert.

Now query for the five most similar documents to a new piece of text:

SELECT content, source, embedding <=> $1::vector AS distance FROM documents ORDER BY distance LIMIT 5;

The <=> operator computes cosine distance, which is 1 minus cosine similarity. Lower is closer. If you prefer Euclidean, use <->. If you want inner product, use <#> and remember it returns the negative, so larger is closer.

Without an index, this query scans every row and computes the distance. That is fine for a few thousand documents but becomes slow quickly. The next section covers when and how to add an index.

Key settings that matter

The two index types in pgvector behave very differently and choosing the wrong one is the most common mistake.

IVFFlat builds a flat inverted file index by clustering your vectors into a number of lists using k-means at index build time. You set the number of lists with the lists parameter, typically to the square root of your row count. At query time, you set probes to control how many lists are scanned. More probes means higher recall and slower queries. The catch is that IVFFlat requires training data, so you must have a meaningful number of rows before the index is useful, and rebuilding it after large inserts is expensive.

HNSW (Hierarchical Navigable Small World) is the default choice for most workloads in the current version of pgvector. It builds a graph structure that does not require training and supports incremental inserts. The two key parameters are m, which controls the number of bidirectional links per node, and ef_construction, which controls the size of the dynamic candidate list during build. Higher values give better recall and slower builds. At query time, you set ef_search per query to balance speed and recall.

A typical HNSW index looks like:

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

The _ops suffix on the operator class matters. Use vector_cosine_ops for cosine distance, vector_l2_ops for Euclidean, and vector_ip_ops for inner product. Picking the wrong operator class silently returns wrong results.

Other settings worth knowing:

• Quantization. pgvector supports binary and half precision quantization in newer versions, which cuts memory roughly in half or by a factor of 32 respectively. The trade off is some recall loss.
• Pre-filtering. If you combine vector search with WHERE clauses, pgvector applies the filter before the index scan, which can hurt recall. For high precision hybrid search, consider a two stage approach.
• Maintenance. HNSW indexes do not need VACUUM REINDEX as often as IVFFlat, but you should still monitor index size and rebuild after large bulk loads.

Where it shines

pgvector is at its best when you already have Postgres in your stack and your dataset fits comfortably in a single server. For datasets in the range of 100,000 to a few million vectors, it delivers recall and latency comparable to dedicated vector databases without the operational overhead of running a second system.

It genuinely excels at hybrid search. Because the vector lives in the same row as your metadata, you can write a single query that filters by user, date range, category, or any indexed column and then ranks by vector similarity. This is awkward to do well in dedicated vector stores that lack a full SQL engine.

Other strong fits:

• ACID requirements. If your embeddings need to participate in transactions with other writes, pgvector gives you that for free.
• Small teams. One database, one backup strategy, one monitoring stack.
• Compliance. Data stays in your existing Postgres, which simplifies residency and audit requirements.
• Prototyping. You can ship a semantic search feature in an afternoon without procurement.

Where it fails

pgvector is not the right choice for every workload, and pretending otherwise wastes time.

The first limit is scale. Once you cross roughly 10 million vectors with high dimensionality, memory pressure on the Postgres buffer cache becomes severe, and you start paying for RAM you would not