What Is pgvector Used For?

---

pgvector is an open-source PostgreSQL extension, not a separate database, that adds vector search to a table you already run, one of the retrieval layers Atlan’s Context Layer for AI sits upstream of, checking what got embedded before a similarity search ever runs against it. The vector database segment of the DBMS market is forecast to grow at a 75.3% CAGR, according to Gartner^[7], and Fortune Business Insights puts the global market at $2.58 billion in 2025, rising to $17.91 billion by 2034.^[8] A meaningful share of that growth runs straight through pgvector, precisely because adopting it needs no new infrastructure.

pgvector answers “what’s closest to this query vector” extremely well, and it answers that question inside the same database a team already trusts operationally. Source certification, sensitivity classification, and lineage get decided earlier, in the pipeline that writes the row and builds the embedding. A vector column stores and searches; whether that row was approved for an AI agent to read is settled upstream.

---

What is pgvector?

pgvector is an open-source PostgreSQL extension, maintained on GitHub, that lets a Postgres table store and search embeddings without a separate database. Enable it with one statement, CREATE EXTENSION vector;, and any existing table can gain a vector column the same way it would gain a text or integer one, no fork, no new engine to operate.^[1] pgvector uses the PostgreSQL License and has 23,061 GitHub stars and 1,326 forks as of September 2026, a scale of adoption well ahead of most single-purpose Postgres extensions. Its LICENSE file carries the PostgreSQL Global Development Group and UC Regents copyright alongside the PostgreSQL permission grant, which is why GitHub’s licence detector returns NOASSERTION rather than MIT.^[9] It supports Postgres 13 and later.^[1]

pgvector was built for the moment a team needed embeddings sitting next to the relational data it already trusted, without standing up new infrastructure to get there, the same underlying need that produced dedicated engines like Pinecone, solved from the opposite direction: extend the system you have instead of adding a system next to it. Anyone starting from zero on what a vector database actually is should read that page first; this one assumes the category and goes deep on the Postgres-specific mechanics.

Adoption followed the path of least resistance. AWS added pgvector support to Amazon RDS for PostgreSQL on May 3, 2023, for PostgreSQL 15.2 and later, across every AWS region including GovCloud, explicitly framed around efficient similarity search for machine learning embeddings.^[3] Supabase, Google Cloud SQL, Azure Database for PostgreSQL, and Neon followed the same pattern: enabling pgvector is a single CREATE EXTENSION vector; call, not a migration project.^[6]

An extension inherits its host database’s operational maturity automatically. It inherits none of that database’s content trustworthiness. Whether the row next to the new vector column was ever approved for an AI agent to read is a separate question, one this page returns to in depth below.

---

How does pgvector work inside Postgres?

pgvector adds a new column type and a new class of index to Postgres, and both slot into ordinary SQL rather than a separate query language.

Data types and distance operators

pgvector’s core type is vector, which stores up to 16,000 dimensions per value; halfvec stores the same data at half precision for smaller indexes, and sparsevec and bit cover sparse and binary embeddings respectively.^[1] Six distance operators ship with the extension: <-> for L2 (Euclidean) distance, <#> for negative inner product, <=> for cosine distance, <+> for L1 (taxicab) distance, and <~> / <%> for Hamming and Jaccard distance on binary vectors.^[1] Every one of them is an ordinary SQL operator, usable directly in ORDER BY and WHERE clauses the same way < or = would be.

A typical query embeds the input with a model built on a transformer architecture, then runs something close to:

SELECT id, content FROM documents
ORDER BY embedding <=> '[0.01, -0.02, ...]'
LIMIT 5;

Because the vector lives in the same row as everything else, that query can add any ordinary WHERE clause on another column, a department, a date range, a status flag, joined in a single call rather than merged in application code after two separate network round trips.

Indexing options

Two index types ship with pgvector, HNSW and IVFFlat, each with its own dimension ceilings. HNSW caps at 2,000 dimensions for vector, 4,000 for halfvec, 64,000 for bit, and 1,000 non-zero elements for sparsevec. IVFFlat matches the first three and does not cover sparsevec.^[1]

---

HNSW vs. IVFFlat: does pgvector do exact or approximate search?

Among vector search systems, pgvector is unusual: it defaults to exact search and only becomes approximate once a team adds an index, a real architectural choice, not a limitation to work around.

Without any index, pgvector performs a sequential scan: it compares the query vector against every row and returns the true nearest neighbors. pgvector’s README states it directly: “By default, pgvector performs exact nearest neighbor search, which provides perfect recall.” The cost scales with table size.^[1] For a few thousand rows, or a background job with no latency requirement, that’s often the simplest correct choice.

HNSW (Hierarchical Navigable Small World) builds a multi-layer graph connecting each vector to its close neighbors at several scales, giving pgvector its best recall-versus-speed tradeoff at the cost of a slower build and a larger memory footprint.^[1] Weaviate, Qdrant and Milvus all index with HNSW; Pinecone does not publish its index algorithm. The same tradeoff shows up wherever the algorithm runs. IVFFlat clusters vectors into Voronoi-style lists and searches only the nearest few, building faster and taking less memory, a better fit for indexes that get rebuilt often as data changes.

pgvector 0.8.0 fixed a real problem shared by both index types: “overfiltering,” where a WHERE clause combined with an index scan returned too few rows because the index stopped scanning before it found enough matches that also passed the filter. pgvector’s README puts a number on the problem: with HNSW and the default hnsw.ef_search of 40, a condition matching 10% of rows leaves only about 4 rows matching on average. Iterative index scans, configurable via hnsw.iterative_scan and ivfflat.iterative_scan, keep scanning until the filter is satisfied instead of silently under-returning.^[2]

Both index types decide how a vector is found, not whether it should have been built. That is a retrieval-quality mechanic rather than a data-quality one, a distinction the next section makes concrete.

---

What is pgvector used for in AI and RAG pipelines?

Two use cases account for most pgvector deployments in production, and both share the same motive: don’t add a new system if the one you have can be extended.

RAG on data already in Postgres. A team with product documentation, support tickets, or an internal knowledge base already stored in Postgres embeds that content in place and retrieves it with <=> instead of standing up a parallel vector store and keeping two systems in sync. See what retrieval-augmented generation is and what RAG solves for the pattern this serves, and retrieval orchestration for routing across Postgres alongside other sources.

Semantic search bolted onto an existing app. An application that already queries Postgres for everything else adds a vector column and a similarity ORDER BY clause to queries it’s already running, rather than a second network hop to an external index for every search. Semantic search versus keyword search covers the distinction directly; semantic search implementation covers the practical build, including how a knowledge base for AI agents gets structured around it.

A secondary pattern, hybrid retrieval, combines <=> similarity with Postgres’s own full-text search (tsvector) to approximate hybrid RAG. pgvector’s README is explicit that this is the intended route: its hybrid search section is one line pointing outward, “use together with Postgres full-text search for hybrid search,” with example code and a pointer to Reciprocal Rank Fusion or cross-encoders.^[1] Engines built around hybrid search from the start do the fusion for you.

None of this explains why retrieval quality still breaks in production. Chunking strategy, embedding model choice, reranking, and stale source data drive RAG accuracy problems just as much as which index sits underneath, and the wider RAG architecture around it.

---

How far does pgvector scale before you need a dedicated vector database?

There’s no fixed number for how far pgvector scales. The honest answer is a range that depends on dimension count, available RAM, and which index is doing the work.

The wall shows up as RAM and build time well before it shows up as a hard failure. Index build memory and query latency both rise with collection size, and pgvector publishes no fixed limits: maintenance_work_mem is the knob for build time, and the size of the finished index in RAM is what governs query latency. Treat any single number you read, including one in this paragraph, as a planning estimate rather than a hard spec, and measure on your own dimension count and hardware.

The complication worth knowing: pgvectorscale, a separate open-source extension from Timescale built on top of pgvector, adds a disk-backed StreamingDiskANN index and statistical binary quantization.^[5] Timescale’s own benchmark, run on 50 million 768-dimension Cohere embeddings at 99% recall, self-hosted on AWS EC2, reports 28x lower p95 latency and 16x higher query throughput than Pinecone’s storage-optimized index. Two caveats that change what it means: it is a vendor benchmarking a rival, not independently audited, and the Pinecone side is the legacy s1 storage-optimized pod, an index type new customers have not been able to create since August 18, 2025.^[4]

---

pgvector vs. Pinecone vs. Weaviate: which one fits your stack

The right choice comes down to one question a team can usually answer in a sentence: are you avoiding a new system, or do you need one built for a job pgvector was never designed to do?

A team already on Postgres, with an index that fits comfortably in the RAM it can afford, gets more from pgvector, paired with pgvectorscale for headroom, than from anything else, because nothing new joins the stack: no second backup job, no second on-call rotation to staff. Past that point, the calculus flips. Pinecone gives up the “nothing new” story for a serverless engine with no cluster to size or run, built for exactly the scale where pgvector starts asking for more RAM than most teams want to hand it. And a team that needs hybrid search or graph-like relationships alongside vectors, not approximated with tsvector and manual configuration but built in from the start, is choosing a different architecture entirely, not a faster version of the same one.

None of these three wins outright. Each was built for a different constraint, and the honest move is naming which constraint is actually yours before picking a winner. Readers weighing all the options at once should read the full eight-way comparison; anyone starting from zero on the category should read what a vector database is first. This page went narrower on purpose: pgvector specifically, its mechanics above, its limits below.

---

What a governed context layer adds upstream of pgvector

That gap is easy to miss with pgvector specifically, because adding a column doesn’t feel like adding a new system that needs its own review. A vector column inherits Postgres’s backups, ACID guarantees, and access control, not whether the row next to it is still accurate or was ever approved for AI use, the same data quality problem that shows up everywhere else in an LLM pipeline.

Atlan’s Enterprise Data Graph carries source certification and lineage upstream of whatever similarity-search layer a team runs, pgvector included. Atlan’s MCP Server exposes that context to agents at runtime over the Model Context Protocol. Whether an agent uses MCP versus a plain API, querying a Postgres table through a connected data catalog means it can check whether the row it just read was ever supposed to be AI-visible. A semantic layer closes a related gap: the distance between “close in embedding space” and “means what the business thinks it means,” a gap that has nothing to do with why LLMs need retrieval in the first place and everything to do with whether the retrieved answer was correct.

None of this is a knock on pgvector, a genuinely good fit for teams that don’t want new infrastructure. The context layer is a different, upstream layer, and context engineering is the discipline of building it deliberately, see how to implement an enterprise context layer end to end, rather than discovering its absence after an agent has already acted on a row nobody re-certified.

---

FAQs about pgvector

1. What is pgvector used for?

pgvector is used for retrieval-augmented generation on data already stored in Postgres and for semantic search bolted onto an existing app, without standing up a separate vector database. Because it’s an extension, a team keeps the same backups, access control, and SQL joins it already runs.

2. Is pgvector free and open source?

Yes. pgvector uses the PostgreSQL License and is free to self-host on any Postgres 13 or later. Its LICENSE file carries the PostgreSQL Global Development Group and UC Regents copyright and the PostgreSQL permission grant, which is why GitHub reports the repository as NOASSERTION rather than MIT. Every major managed Postgres provider, including AWS RDS, Supabase, Google Cloud SQL, Azure Database for PostgreSQL, and Neon, bundles it at no extra license cost; the database itself still carries its usual bill.

3. How many vectors can pgvector handle?

pgvector publishes no scale ceiling. Index build memory and query latency both rise with collection size, and RAM for the index is the practical limit, so the answer depends on dimension count, index type, and hardware. pgvectorscale, a separate Timescale extension built on pgvector, adds a disk-backed StreamingDiskANN index that extends the range. Past that, a dedicated vector database usually makes more operational sense.

4. What is the difference between HNSW and IVFFlat in pgvector?

HNSW is graph-based, giving pgvector its best recall-versus-speed tradeoff at the cost of a slower index build and more memory. IVFFlat is cluster-based, faster to build and smaller, which suits indexes that get rebuilt often as the underlying data changes.

5. Does pgvector do exact or approximate search?

Both. Without an index, pgvector runs an exact sequential scan with 100% recall, at a cost that scales with table size. Adding an HNSW or IVFFlat index switches to approximate nearest-neighbor search, trading a small amount of recall for significantly faster queries.

6. Can pgvector replace a dedicated vector database like Pinecone or Weaviate?

For teams already running Postgres at moderate scale, often yes. What flips the answer is scale that outgrows the RAM available for the index, or a need for native hybrid search and zero-ops scaling. pgvector’s README points outward on hybrid search, recommending Postgres full-text search alongside it, while Pinecone and Weaviate both document it natively.

7. What is pgvectorscale?

pgvectorscale is a separate open-source Postgres extension from Timescale, built on top of pgvector. It adds a disk-backed StreamingDiskANN index and statistical binary quantization to push past pgvector’s default scale ceiling, without leaving Postgres.

---

Sources

1. pgvector repository and README, GitHub
2. pgvector 0.8.0 Released, PostgreSQL.org
3. Amazon RDS for PostgreSQL supports pgvector for storing embeddings, AWS What’s New
4. Pgvector Is Now as Fast as Pinecone at 75% Less Cost, Tiger Data
5. pgvectorscale repository, GitHub
6. AI & Vectors, Supabase Docs
7. Forecast: Database Management Systems, Worldwide, Gartner
8. Vector Database Market Size, Share & Industry Analysis, Fortune Business Insights
9. pgvector LICENSE, GitHub