pgvector in production: indexes, sizing and running it at scale
Vector search inside the PostgreSQL you already run, in OSStend's cloud, your cloud account or your data center.
45 minutes with an engineer: we review your workload and sketch the design.
pgvector adds a vector column type and nearest-neighbour search to PostgreSQL. Search is exact by default, and two approximate index types, HNSW and IVFFlat, trade some recall for speed. It fits when your data already lives in Postgres and you want vectors under the same backups and access control.
- PostgreSQL
- MySQL
- MongoDB
- Redis
- Valkey
- OpenSearch
- Kafka
- RabbitMQ
- AWS
- Google Cloud
- Azure
- Kubernetes
- OpenShift
- Prometheus
- Grafana
- Datadog
- Dynatrace
- Splunk
What changes between a prototype and production
A pgvector prototype works on a few thousand rows. Production adds millions of embeddings, filters, concurrent writes, replicas and a recall target, and each of those changes how the index behaves.
- Memory grows with vectors and dimensions.
- Each stored vector takes 4 bytes per dimension plus 8 bytes, before the index. pgvector notes that indexes perform better when they fit in memory (pgvector FAQ).
- Recall changes quietly.
- An approximate index returns different results from exact search. Unless recall is measured against exact search, a setting change can lower answer quality with no error.
- Filters change result counts.
- With approximate indexes, filtering runs after the index scan, so a filtered query can return fewer rows than the LIMIT asked for.
- Maintenance gets slower.
- Building and vacuuming HNSW indexes takes longer as tables grow, and builds are faster when the graph fits in `maintenance_work_mem`.
- Embeddings go stale.
- When the source row changes, its vector must be recomputed, or the search returns the old meaning.
Where pgvector sits in the architecture
pgvector is a PostgreSQL extension, so vectors live in ordinary tables next to the rest of your data. Queries use normal SQL with a distance operator, and backups, replication and access control are Postgres's own.
| Component | Role | Notes from the docs |
|---|---|---|
| Embedding model | Turns text or images into vectors | Runs outside the database. All vectors in one column must have the same number of dimensions for an index. |
| Table with a `vector` column | Stores the embedding with the row it describes | `vector` stores up to 16,000 dimensions; `halfvec` uses half the space per dimension. |
| Index: HNSW or IVFFlat | Makes search approximate and fast | Optional. Without an index, search is exact. Add one index per distance function you query with. |
| Distance operator | Ranks rows by similarity | `<->` L2, `<#>` negative inner product, `<=>` cosine, `<+>` L1. |
| Filters and full-text search | Narrow results; combine keyword and vector scores | Use Postgres full-text search with Reciprocal Rank Fusion or a cross-encoder for hybrid search. |
| Replicas and backups | Read scale and recovery | pgvector uses the write-ahead log, so replication and point-in-time recovery work. |
| Refresh pipeline | Re-embeds rows when source data changes | Often change data capture into Kafka feeding an embedding job. See keeping embeddings fresh with Kafka and CDC. |
Source: pgvector README, checked 30 Oct 2026.
CREATE EXTENSION vector; CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(1536)); CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops); SELECT id FROM items ORDER BY embedding <=> '[...]' LIMIT 5;
HNSW or IVFFlat
HNSW gives a better speed-recall trade-off but builds slower and uses more memory. IVFFlat builds faster and uses less memory, but its query performance is lower for the same recall. HNSW is the usual starting point when memory allows.
| Property | HNSW | IVFFlat |
|---|---|---|
| Structure | Multilayer graph | Vectors divided into lists; a subset of lists is searched |
| Speed-recall trade-off | Better | Lower |
| Build time | Slower | Faster |
| Memory | Higher | Lower |
| Needs data before the index is created | No, there is no training step | Yes, create it after the table has some data |
| Main query setting | `hnsw.ef_search` (default 40); higher improves recall and costs speed | `ivfflat.probes` (default 1); higher improves recall and costs speed |
| Main build settings | `m`, `ef_construction` | `lists`; start at rows / 1000 up to 1M rows, then sqrt(rows) |
| Dimensions indexed | `vector` up to 2,000; `halfvec` up to 4,000; `bit` up to 64,000 | Same limits |
Source: pgvector README, indexing section, checked 30 Oct 2026.
Sizing and tuning
Memory need grows with vector count multiplied by dimensions, then again for the index. Recall against latency is set by the query-time knobs, and the choice of precision decides how much of it fits in RAM.
| Driver | What it changes | Lever in the docs |
|---|---|---|
| Vector count × dimensions | Table and index size, and how much must be cached | A `vector` takes 4 × dimensions + 8 bytes; a `halfvec` takes 2 × dimensions + 8. |
| Precision | Working set size | `halfvec` storage or indexing; binary quantization with re-ranking by the original vectors. |
| Recall target | Latency per query | Raise `hnsw.ef_search` or `ivfflat.probes` for recall; lower for speed. Check recall against exact search. |
| Filtering | Result counts and recall | Iterative index scans (from pgvector 0.8.0), partial indexes, or partitioning. |
| Index build | Build time and write blocking | `maintenance_work_mem`, `max_parallel_maintenance_workers`, and `CREATE INDEX CONCURRENTLY` in production. |
| Read load | Throughput | Postgres replicas; sharding with tools such as Citus for more. |
Source: pgvector README, performance and scaling, checked 30 Oct 2026.
Worked example, arithmetic only. One million vectors at 1,536 dimensions in the `vector` type take (4 × 1,536 + 8) × 1,000,000 = 6,152,000,000 bytes, about 5.7 GiB, before the index and Postgres row overhead. The same data in `halfvec` takes about 2.9 GiB. This is the documented storage formula, not a benchmark.
pgvectorscale and other extensions
pgvectorscale is a separate extension from Timescale that builds on pgvector. It adds a StreamingDiskANN index type, Statistical Binary Quantization and label-based filtered search, aimed at larger indexes.
Its README (pgvectorscale on GitHub, checked 30 Oct 2026) includes vendor-run benchmark claims. Treat any such figure as a starting question, not a result: index settings, hardware and recall targets all change the outcome, so test with your own vectors and queries. Whether an extension beyond pgvector is worth adding is a design question, answered against measured load.
Pitfalls documented by pgvector and Postgres
The pgvector docs list the failure modes that surprise teams most often. They are documented behaviour, not field reports.
- Fewer results than expected after adding an HNSW index.
- Results are limited by the candidate list (`hnsw.ef_search`, default 40), and dead tuples or filters can reduce them further. Iterative index scans help.
- An IVFFlat index created on too little data.
- If there are too few rows for the number of lists, recall suffers. Drop it and rebuild once the table is larger.
- Shared indexes across tenants.
- Vectors from one tenant can affect recall and speed for another. The docs suggest list partitioning or separate tables.
- A query that does not use the index.
- Check the plan with `EXPLAIN (ANALYZE, BUFFERS)`. Small tables and some filters make Postgres choose a scan.
- Slow vacuum on HNSW.
- The docs suggest reindexing first, then vacuuming.
- NULL vectors are not indexed.
- Zero vectors are also skipped for cosine distance.
What OSStend runs, where it runs and who it is not for
OSStend designs, moves and runs PostgreSQL in its own cloud, in your AWS, Azure or Google Cloud account, or co-managed in your data center. Extensions are part of the design that you sign off before anything is built.
Contact us if
- ✓Your embeddings sit beside relational data that already lives in PostgreSQL.
- ✓Vectors hold customer data and must stay in your cloud account or data center.
- ✓You want vector search, backups, replicas and on-call under one operator. See vector database for RAG and agent memory for the patterns built on it, and the Postgres MCP server guide for giving agents safe read access.
Not a fit if
- ✕A vendor-run vector API you want with no design step. OSStend runs a dedicated Qdrant for vector-only workloads; see Qdrant Cloud alternatives, pgvector vs Pinecone, pgvector vs Qdrant, or the Pinecone alternative page if you already run Pinecone.
- ✕A prototype with no production target yet.
- ✕A team that wants no database to operate or review at all.
Questions about pgvector as a Postgres vector database
pgvector makes PostgreSQL a Postgres vector database for most retrieval and memory workloads. These are the questions teams ask most before using it in production.
What is pgvector used for?
pgvector stores embeddings in PostgreSQL and finds the nearest ones to a query vector. Typical uses are retrieval for RAG, semantic search, recommendations and agent memory, where vectors sit beside the relational data they describe. The vector database and RAG guide explains how retrieval is put together end to end.
Should I use HNSW or IVFFlat?
HNSW has the better speed-recall trade-off but builds slower and uses more memory. IVFFlat builds faster and uses less memory. The pgvector docs recommend neither universally: choose by memory budget, build time and the recall the application needs, then measure.
How many dimensions can pgvector index?
A `vector` column can be indexed up to 2,000 dimensions. Half-precision (`halfvec`) indexing reaches 4,000 dimensions, and binary quantization reaches 64,000. The `vector` type itself stores up to 16,000 dimensions.
Is pgvector a PostgreSQL vector database?
Yes, for most workloads. pgvector turns PostgreSQL into a Postgres vector database that can store and search vectors, with ACID transactions, joins and point-in-time recovery. It scales vertically and through replicas, and sharding needs extra tooling such as Citus, which is the main difference from services built for scale-out vector search.
Is pg vector the same as pgvector?
Yes. "pg vector" is a common way of writing pgvector, the open source extension that adds a vector type and nearest-neighbour search to PostgreSQL. It is installed in a database with `CREATE EXTENSION vector;`.
Can pgvector do hybrid search?
Yes, by combining vector search with Postgres full-text search. The pgvector docs point to Reciprocal Rank Fusion or a cross-encoder to merge the two result lists.
Related pages
- KafkaManaged Kafka in your cloud or data centerManaged Kafka with the broker count, partition plan, KRaft, Connect and schema registry designed for your estate, run 24/7 in your cloud or your data center.
- AI data · Fresh contextEmbeddings freshness with Kafka and Postgres CDCPostgres CDC with Debezium and Kafka Connect keeps embeddings current: re-embed on change, handle deletes, and stop agents answering from stale data.
- PostgreSQLManaged PostgreSQL service and Postgres hostingManaged PostgreSQL service, compared: how to choose between RDS, Aiven, Crunchy Bridge, Neon and a tailored cluster run 24/7 in your cloud or ours.
- AI data · RetrievalVector database for RAG on your own infrastructureA vector database for RAG on your own infrastructure: when PostgreSQL with pgvector is enough, when OpenSearch hybrid search fits, and how to size retrieval.
- AI data · MemoryAgent memory on PostgreSQL and Redis in productionAgent memory on PostgreSQL and Redis: long-term memory and checkpoints in Postgres with pgvector, short-term state in Redis or Valkey, and retention.
- AI data · Agent accessPostgres MCP server: safe agent accessPostgres MCP server guide: read-only roles, row-level security, query limits and audit, so an AI agent can use production data without broad access.
Talk to an engineer about pgvector
Bring the vector count, dimensions and recall target. An engineer will help you size it and decide where it should run.
- ✓A design sketch for your workload, not a plan menu
- ✓A zero-downtime cutover path for what you run today
- ✓A price for that design, after the review