# pgvector

pgvector adds a vector type and similarity search to PostgreSQL, with HNSW and IVFFlat indexes. Learn when keeping vectors in Postgres is the right choice.

## Overview

pgvector is an open-source extension for [PostgreSQL](/tools/postgresql) that lets you store vectors in a table column and search them by similarity with ordinary SQL. Once installed, a table can hold a product's name, price and stock level next to its embedding, and a single query can filter, join and rank by vector distance.

The appeal is simplicity. Many teams already run Postgres. pgvector lets them add semantic search or retrieval for [RAG](/tools/naive-rag) without a second database, a second backup plan or a sync job. Vectors get the same transactions, permissions, replication and point-in-time recovery as the rest of the data.

An everyday analogy: instead of renting a separate warehouse for one new kind of stock, you add a new set of shelves to the shop you already own. The staff, the locks and the alarm system stay the same.

## What it is

pgvector adds data types, distance operators and index methods to Postgres.

| Part | What it provides | Notes |
|---|---|---|
| `vector` type | Single-precision vectors | Stores up to 16,000 dimensions; indexable up to 2,000 |
| `halfvec` type | Half-precision vectors | Half the storage; indexable up to 4,000 dimensions |
| `bit` type | Binary vectors | Indexable up to 64,000 dimensions |
| `sparsevec` type | Sparse vectors | Up to 16,000 non-zero elements; indexable up to 1,000 |
| Distance operators | `<->` L2, `<#>` negative inner product, `<=>` cosine, `<+>` L1, `<~>` Hamming, `<%>` Jaccard | Used in `ORDER BY` |
| HNSW index | Graph-based approximate search | Best speed–recall balance, slower to build |
| IVFFlat index | Cluster-based approximate search | Faster to build, needs data before creation |
| Iterative scans | Keep scanning an index until enough filtered rows are found | Helps queries that combine filters and vector order |

Without an index, pgvector performs exact nearest-neighbour search by scanning every row. That gives perfect recall and is often fine for tens of thousands of rows. Approximate indexes are added when latency starts to matter.

## Where it came from

pgvector was created by Andrew Kane and is released under the PostgreSQL licence. Its first release, version 0.1.0, came out in April 2021 with exact search and the IVFFlat index.

Interest grew quickly once large language models made embeddings common. Key releases:

- 0.5.0 (August 2023): added the HNSW index type.
- 0.7.0 (April 2024): added `halfvec` and `sparsevec`, and indexing for `bit`.
- 0.8.0 (October 2024): added iterative index scans for filtered queries.
- 0.8.x patch releases followed with fixes; 0.8.7 was published in October 2026.

Major managed Postgres services, including [Amazon RDS](/tools/amazon-rds) and Aurora, offer the extension, which is a large part of its reach.

## How it works

### 1. Enable the extension and add a column

You run `CREATE EXTENSION vector;` once per database, then declare a column such as `embedding vector(1536)`. The dimension is fixed per column, and every inserted vector must match it.

### 2. Insert embeddings

Your application computes [embeddings](/tools/embeddings) with a model and inserts them like any other value. They can be written in the same transaction as the row they describe, so the text and its vector never drift apart.

### 3. Query by distance

A similarity query orders rows by a distance operator and takes the top k. Normal `WHERE` clauses and joins work alongside it.

```sql
SELECT id, title
FROM documents
WHERE tenant_id = 42
ORDER BY embedding <=> '[0.12, -0.03, ...]'
LIMIT 5;
```

### 4. Add an approximate index

An HNSW index builds a layered neighbour graph. Its build parameters are `m` (links per node, default 16) and `ef_construction` (candidate list during build, default 64). At query time, `hnsw.ef_search` (default 40) controls how widely the graph is explored. See [HNSW](/tools/hnsw) for the algorithm.

An IVFFlat index splits vectors into `lists` clusters using k-means on the existing data, so it should be built after the table is loaded. At query time, `ivfflat.probes` (default 1) sets how many clusters are searched. The documentation suggests starting with rows divided by 1,000 lists up to a million rows, the square root of rows beyond that, and probes near the square root of lists.

Each index is created for one operator class, such as `vector_cosine_ops`. A query only uses the index if it uses the matching operator.

### 5. Handle filters

With an approximate index, Postgres first takes candidates from the index and then applies the `WHERE` clause. A strict filter may leave fewer than k rows. Iterative scans, turned on with `hnsw.iterative_scan` or `ivfflat.iterative_scan`, let the index keep returning candidates until enough rows pass. Partial indexes and partitioning are other options for very selective filters.

## Key concepts and terms

| Term | Meaning |
|---|---|
| Extension | A package that adds features to Postgres |
| Operator class | Tells an index which distance it serves |
| Exact search | Sequential scan over all rows; perfect recall |
| Approximate search | Index-based search that may miss some true neighbours |
| HNSW | Layered graph index; high recall and speed |
| IVFFlat | Inverted-file index with uncompressed vectors in each list |
| m | Maximum connections per node in an HNSW graph |
| ef_search | Size of the candidate list during HNSW search |
| lists | Number of clusters in an IVFFlat index |
| probes | Clusters scanned per IVFFlat query |
| Iterative scan | Continued index scanning until a filtered query has enough rows |

## A worked example

This is an illustrative scenario.

A help-desk application stores 2 million support articles across 300 customer tenants. Each article has a 1,024-dimension embedding.

1. The team adds `embedding vector(1024)` to the existing `articles` table. Raw vector storage is about 2,000,000 × 1,024 × 4 bytes, roughly 8 GB, before index overhead.
2. To save space they consider `halfvec(1024)`, which halves that to about 4 GB, and test that answer quality does not drop.
3. They build an HNSW index with `vector_cosine_ops` and default `m` and `ef_construction`, raising `maintenance_work_mem` so the graph builds in memory.
4. A typical query filters by `tenant_id` and asks for the top 5 articles. Small tenants return fewer than 5 rows, so they enable `hnsw.iterative_scan = relaxed_order`.
5. They compare results against exact search on 500 sampled queries, then raise `hnsw.ef_search` from 40 to 100 until recall meets their target.

The whole feature ships without a new database, and backups and access rules are unchanged.

## Strengths and limitations

**Strengths:**

- One database for relational data and vectors, with full SQL, joins and transactions.
- Inherits Postgres backups, replication, roles and row-level security.
- Supports exact and approximate search, several vector types and distances.
- Widely available on managed Postgres services.
- Permissive PostgreSQL licence.

**Limitations:**

- Index dimension limits (2,000 for `vector`) can block some large embeddings unless you use `halfvec` or reduce dimensions.
- HNSW builds are slow and memory-hungry on large tables.
- Filtered approximate queries need care to return enough rows.
- Scaling past a single primary requires sharding approaches outside pgvector itself.
- No built-in embedding generation or keyword ranking; you add those yourself.

## How it compares with alternatives

| Option | Type | Choose it when | Trade-off against pgvector |
|---|---|---|---|
| pgvector | Postgres extension | Vectors belong with relational data | Baseline |
| [Pinecone](/tools/pinecone) | Managed vector database | You want a hosted service tuned for vectors | Separate system to sync and pay for |
| [Qdrant](/tools/qdrant) | Vector search engine | Heavy payload filtering and quantization | Extra service, no SQL joins |
| [FAISS](/tools/faiss) | Library | Batch jobs or embedded search | Faster raw search, no database features |
| [Elasticsearch](/tools/elasticsearch) | Search engine | Keyword and vector search in one place | Strong text search, weaker transactions |

## Practical guidance and best practices

1. Start without an index; measure latency on real data before adding one.
2. Use the operator class that matches your distance, and use the same operator in queries.
3. Prefer HNSW for most workloads; consider IVFFlat when build time matters more than recall.
4. Build IVFFlat only after loading representative data.
5. Raise `maintenance_work_mem` before large index builds and build after bulk loads.
6. Measure recall against exact search on a sample before tuning `ef_search` or `probes`.
7. Enable iterative scans for filtered queries, or use partial indexes for common filters.
8. Combine vector ranking with Postgres full-text search for [hybrid search](/tools/hybrid-search).

## Common mistakes

- Creating an index with one operator class and querying with a different operator, so the index is never used.
- Building IVFFlat on an empty table, which produces useless clusters.
- Assuming filtered approximate queries always return k rows.
- Storing embeddings from different models in the same column.
- Forgetting to re-embed rows when the source text changes.

## Frequently asked questions

### Is pgvector fast enough for production?

For many applications, yes. With an HNSW index, queries over millions of rows can be fast on a well-sized server. At very large scale or very high query rates, a dedicated vector database may be simpler to operate. Measure with your own data and hardware.

### Should I use HNSW or IVFFlat?

HNSW usually gives a better speed–recall balance and can be built on an empty table, but it builds more slowly and uses more memory. IVFFlat builds faster and uses less memory but needs data before creation and tends to need more tuning.

### Which distance should I use?

Use the distance your embedding model was trained for, usually cosine. For normalised vectors, inner product gives the same ranking and can be slightly cheaper. Match the index operator class to that choice.

### Can I combine vector search with filters?

Yes, with normal `WHERE` clauses. With approximate indexes, very selective filters can return too few rows, so use iterative scans, partial indexes or partitioning.

### What if my embeddings have more than 2,000 dimensions?

You can store them in a `vector` column, but you cannot build an approximate index on them directly. Options include `halfvec`, which is indexable up to 4,000 dimensions, or a model that produces shorter embeddings.

### Does pgvector create embeddings?

No. It stores and searches vectors. Your application or another tool calls an embedding model.

## Glossary

- Embedding: a list of numbers representing the meaning of a piece of content.
- Cosine distance: one minus the cosine of the angle between two vectors.
- L2 distance: straight-line distance between two vectors.
- Recall: share of true nearest neighbours that a search returns.
- Partial index: an index built only over rows that meet a condition.
- Half precision: 16-bit floating-point numbers that halve storage.
- Sparse vector: a vector where most values are zero and only non-zero entries are stored.

## Summary

pgvector turns PostgreSQL into a capable vector store by adding vector types, distance operators and HNSW and IVFFlat indexes. Its main advantage is keeping embeddings beside the data they describe, under the same transactions, backups and permissions. It suits most retrieval and semantic search workloads up to large single-server scale. Choose the right operator class, measure recall against exact search, and plan for filtered queries with iterative scans or partial indexes.