When should I use pgvector instead of a dedicated vector database?

pgvector is the correct default for most RAG and agentic systems: it adds vector search to an existing Postgres instance with no extra service to run. Move to a dedicated vector DB when you have measured evidence of degradation: p99 latency spikes under selective metadata filters (selectivity ~10% or below), or sustained concurrent similarity queries above 1-5M vectors. Measure first, graduate on data, not on projected scale. Source: vecstore.app/blog/vector-database-performance-compared.

Which index should I use: HNSW or IVFFlat?

Start with HNSW. It has a better speed-recall tradeoff, can be built on an empty table (no training step), and degrades more gracefully under query load. IVFFlat is lighter on build time and memory, which matters for very large static corpora that are rarely reindexed. If you choose IVFFlat, build it after inserting representative data: building on an empty table produces meaningless centroids and terrible recall. Sizing IVFFlat lists (official guidance): rows/1000 for up to 1M rows, then sqrt(rows) beyond 1M, not sqrt(rows) at every scale. Source: dbi-services.com/blog/pgvector-a-guide-for-dba-part-2-indexes-update-march-2026.

Does Row Level Security work with vector similarity queries?

Yes. RLS policies apply to any SELECT on the table, including similarity searches using . The vector column is just a column type: Postgres filters out rows the current role is not authorized to see before returning results. On Supabase, auth.uid() is directly available in RLS policies. On self-hosted Postgres, the standard pattern is SET LOCAL app.tenant_id = '...' before the query and a USING (tenant_id = current_setting('app.tenant_id', TRUE)::UUID) policy. Source: supabase.com/docs/guides/ai/rag-with-permissions.

What does ef_search do and when should I tune it?

ef_search controls the size of the dynamic candidate list during an HNSW search. Higher ef_search = better recall at the cost of slower queries. The default is 40. Tune it upward (e.g. SET hnsw.ef_search = 100) when you observe recall degradation on your evaluation set. Tune it downward (e.g. 20) when you can accept lower recall and need maximum throughput. Set it per session or per transaction (SET LOCAL) to vary by use case without changing the index.

Can I use pgvector with Supabase for EU data residency?

Yes. Supabase offers EU regions (Frankfurt, Ireland) and provides managed Postgres with pgvector, RLS, Auth, and connection pooling in one service. For stricter sovereignty requirements (French public sector, HDS), self-hosting Postgres + pgvector on Scaleway fr-par or Hetzner DE gives full jurisdiction control with zero lock-in. pgvector uses the PostgreSQL License (permissive, BSD-style): no vendor dependency on the extension itself.

← Stack

pgvector + Supabase

Vector search inside Postgres: ACID, RLS, SQL joins, one service to run.

Overview

pgvector is an open-source Postgres extension (PostgreSQL License, the same permissive BSD-style license as Postgres itself) that adds a vector column type, distance operators, and two index types: HNSW and IVFFlat. Embeddings live in the same database as your application state. A write that inserts a document row and its embedding is a single transaction: it commits or rolls back as one unit. Source: github.com/pgvector/pgvector.

The structural advantage over a dedicated vector store is the join. A nearest-neighbor query in pgvector is a SQL SELECT: it can filter on tenant_id, document_type, or any relational column in the same WHERE clause, and join the vector results with an access_control table in the same query plan. Row Level Security (RLS) policies apply to vector searches automatically: the database enforces tenant isolation even if application code omits the filter. No network hop between the vector layer and the relational layer.

v0.5.0 (August 2023) introduced HNSW, which is now the index of choice for production. v0.7.0 (April 2024) added halfvec and binary quantization: because HNSW and IVFFlat cap the vector type at 2,000 dimensions, halfvec (half-precision) is what makes indexes on up to 4,000 dimensions possible. v0.8.0 (October 2024) added iterative index scans (hnsw.iterative_scan) that prevent overfiltering when WHERE clauses are highly selective. The current release is 0.8.5. Supabase bundles managed Postgres + pgvector + RLS + Auth in one service with EU regions, removing the DBA overhead for teams without an in-house database operator. Sources: postgresql.org news, supabase.com.

Architecture

The mental model is a standard Postgres table with an extra column of type vector(n). Distance queries use infix operators: <=> (cosine), <-> (L2 / Euclidean), and <#> (negative inner product), each paired with a matching index opclass (vector_cosine_ops, vector_l2_ops, vector_ip_ops). The HNSW index rewrites the execution plan to an approximate nearest-neighbor scan. All Postgres machinery (transactions, WAL, RLS, partitioning) applies to this column without modification.

pgvector as the default: one Postgres database for app state and vectors, with graduation signals that mark when to add a dedicated vector store

Key concepts

vector(n): The column type. Stores a fixed-length array of n float32 values. n must match the embedding model output dimension (e.g. 1536 for OpenAI text-embedding-3-small). HNSW and IVFFlat can only index a vector up to 2,000 dimensions; beyond that you index a halfvec (float16, indexable up to 4,000 dims). Also available: sparsevec (up to 1,000 nonzero dims), bit (binary).
HNSW: Hierarchical Navigable Small World. The production index type: better speed-recall tradeoff than IVFFlat, slower to build, higher memory. Parameters: m (connections per layer, default 16) and ef_construction (build quality, default 64). Query-time recall is tuned via SET hnsw.ef_search = N (default 40; higher = better recall, slower). Can be built on an empty table.
IVFFlat: Inverted File Flat. Lighter than HNSW: faster to build, less memory. Splits vectors into lists; at query time, searches a subset of lists. Requires representative data at creation (building on an empty table produces meaningless centroids). Key params: lists (number of partitions; official guidance is rows/1000 for up to 1M rows, then sqrt(rows) beyond 1M, not sqrt(rows) everywhere) and ivfflat.probes (lists to search at query time).
ef_search: HNSW query-time parameter. Controls the size of the dynamic candidate list during search: higher ef_search = better recall, slower query. Set per session (SET hnsw.ef_search = 100) or per transaction (SET LOCAL hnsw.ef_search = 100). Default is 40.
Iterative scan: hnsw.iterative_scan (added in v0.8.0): when a WHERE clause is highly selective, a single HNSW scan may return too few qualifying rows. Iterative scan re-enters the index multiple times until enough results are found. Mitigates overfiltering at the cost of more index traversal. Enable with SET hnsw.iterative_scan = relaxed_order.
Row Level Security (RLS): Postgres native access control at the row level. With pgvector, RLS policies apply to similarity queries exactly as they apply to any SELECT: the database filters out rows the current role is not authorized to see before returning results. Standard pattern: tenant_id column + USING (tenant_id = current_setting('app.tenant_id')::UUID). On Supabase, auth.uid() is directly available in policies.
Index build tuning: Building an HNSW index on a large corpus is CPU- and memory-bound, and it is the number-one operational pain point at scale. Raise maintenance_work_mem so the build stays in memory instead of spilling to disk, and raise max_parallel_maintenance_workers (default 2) to parallelize it: on multi-million-row tables this is the difference between minutes and hours. Parallel HNSW builds shipped in v0.5.0.

When to use

Good fit

Embedding writes must be atomic with relational writes: pgvector lives in Postgres, so inserting a document row and its vector embedding commits or rolls back together. No dual-write sync problem.
Multi-tenant RAG with per-tenant isolation: a single table with all tenants' embeddings and an RLS policy on tenant_id isolates each tenant at the database level. The DB enforces the filter even if application code omits it. Source: supabase.com/docs/guides/ai/rag-with-permissions.
Vector results need to JOIN relational data: nearest-neighbor results enriched with metadata, access control records, or user preferences in one SQL query. No round-trip to a second service.
Corpus under approximately 1-5M vectors at moderate concurrent load: HNSW delivers millisecond p50 at this scale without tuning memory or sharding. Source: vecstore.app benchmarks.
Teams already running Postgres who want zero extra ops surface: one connection string, one backup, one monitoring dashboard. Supabase (EU regions available) removes the DBA overhead entirely.

Anti-patterns

Highly selective metadata filters at scale: pgvector post-filters the HNSW candidate set after the approximate nearest-neighbor scan. When a WHERE clause matches only ~10% or less of the corpus, the ANN must overscan to find enough qualifying rows. Dedicated vector DBs like Qdrant filter inside the graph traversal, which is materially faster for selective queries. v0.8.0 iterative scan (hnsw.iterative_scan) mitigates the worst case but does not eliminate it. Source: tigerdata.com/blog/pgvector-vs-qdrant.
Very large corpora with concurrent load and a p99 SLA: above roughly 1-5M vectors under sustained concurrent similarity queries, dedicated vector DBs (which hold the entire index in memory, purpose-built) deliver more consistent p99. If you have measured p99 degradation on real traffic, that is a graduation signal. Source: vecstore.app/blog/vector-database-performance-compared.
Memory pressure on the Postgres host: the HNSW index is held in memory during queries. Do the arithmetic before sizing: 1.5M vectors at 1536 dims is already ~9.2 GB of raw float32 vectors (1.5M x 1536 x 4 bytes), and the HNSW index stores the graph links on top of that, roughly 1.5x, so budget ~13-14 GB. On an undersized instance, index eviction causes latency spikes. Size the Postgres host to keep the HNSW index in shared_buffers, use halfvec to roughly halve it, or treat memory pressure as a graduation signal.
IVFFlat built on an empty table: IVFFlat requires representative data at creation to compute meaningful centroids. Building on an empty table produces random centroids, which gives terrible recall. Always build IVFFlat after inserting a representative data sample. HNSW does not have this constraint.

Code examples

Set up pgvector: extension, table, HNSW index

-- Enable the extension (once per database). On self-hosted Postgres this
-- needs superuser or rds_superuser. On Supabase, enable it from the dashboard
-- (Database > Extensions) instead, since you do not have a superuser role.
CREATE EXTENSION IF NOT EXISTS vector;

-- Documents table: relational columns alongside the embedding column
CREATE TABLE documents (
  id         BIGSERIAL PRIMARY KEY,
  tenant_id  UUID        NOT NULL,
  content    TEXT        NOT NULL,
  embedding  vector(1536),          -- matches OpenAI text-embedding-3-small output
  created_at TIMESTAMPTZ DEFAULT now()
);

-- On a large corpus, speed up the build first: keep it in memory and
-- parallelize it (this is the #1 operational pain point at scale).
SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;   -- default is 2

-- HNSW index for cosine distance (production default)
-- m=16 and ef_construction=64 are solid starting defaults
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Tune recall at query time (session-level, default is 40)
-- Higher ef_search = better recall, slower query
SET hnsw.ef_search = 100;

HNSW can be built on an empty table (no training step needed). IVFFlat cannot: it requires representative data for meaningful centroids. Start with HNSW. The vector(1536) dimension must match your embedding model's output exactly.

Similarity query with tenant filter (SQL + Python)

-- Top-5 nearest neighbours filtered by tenant, ordered by cosine distance.
-- The SQL below uses positional placeholders ($1 = query embedding, $2 = tenant UUID);
-- the Python version uses psycopg named parameters (%(q)s, %(tenant)s).

-- SQL (prepared statement; $1 = embedding, $2 = tenant UUID):
SELECT id,
       content,
       1 - (embedding <=> $1) AS similarity
FROM   documents
WHERE  tenant_id = $2
ORDER  BY embedding <=> $1
LIMIT  5;

-- Python equivalent using psycopg v3 + pgvector-python:
-- pip install "psycopg[binary]>=3.1" "pgvector>=0.3"
import psycopg
from pgvector.psycopg import register_vector

with psycopg.connect("postgresql://user:pass@host/db") as conn:
    register_vector(conn)
    query_emb = get_embedding(user_question)   # your embedding call
    rows = conn.execute(
        """
        SELECT id, content, 1 - (embedding <=> %(q)s) AS similarity
        FROM   documents
        WHERE  tenant_id = %(tenant)s
        ORDER  BY embedding <=> %(q)s
        LIMIT  5
        """,
        {"q": query_emb, "tenant": tenant_id},
    ).fetchall()

The <=> operator is cosine distance, which ranges [0, 2] (0 = identical, 1 = orthogonal, 2 = opposite). So 1 - (embedding <=> $1) is cosine similarity in [-1, 1], not [0, 1]. In practice the unit-normalized embeddings most models emit land in [0, 1], but do not assume the bound. The tenant_id filter is a standard WHERE clause: the HNSW index scan and the relational filter happen in the same Postgres query plan.

Row Level Security for multi-tenant isolation

-- Step 1: add tenant_id column (already in schema above)
-- Step 2: enable RLS on the table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Step 3a: Supabase pattern. auth.uid() is the USER id, not the tenant id.
--   tenant_id = auth.uid() only holds if one user maps to exactly one tenant.
--   For real B2B multi-tenant, resolve the user's tenants via a membership table.
CREATE POLICY tenant_isolation ON documents
  AS PERMISSIVE FOR ALL
  TO authenticated
  USING (tenant_id IN (
    SELECT tenant_id FROM memberships WHERE user_id = auth.uid()
  ));

-- Step 3b: self-hosted pattern: policy reads from a session variable
--   The application sets the variable before each query.
CREATE POLICY tenant_isolation ON documents
  AS PERMISSIVE FOR ALL
  TO app_role
  USING (tenant_id = current_setting('app.tenant_id', TRUE)::UUID);

-- Application code (self-hosted): set the context before querying
BEGIN;
SET LOCAL app.tenant_id = '018e4567-e89b-12d3-a456-426614174000';
SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM   documents
ORDER  BY embedding <=> $1
LIMIT  5;
-- tenant_isolation policy enforces the filter automatically
COMMIT;

RLS policies apply to vector similarity queries automatically, the same as to any SELECT. The database enforces the tenant filter at the row level, independent of application code. On Supabase, auth.uid() (the authenticated user id) is available in policies so no SET LOCAL is needed, but resolve it to a tenant through a membership table rather than treating the user id as the tenant id.

Comparison

vs Qdrant

Filtered search performance, memory-optimized index, dedicated vector ops

pgvector post-filters the HNSW candidate set after the approximate nearest-neighbor scan. Qdrant filters inside the HNSW graph traversal. For a 5M-vector corpus with a 10% selectivity filter, Qdrant is materially faster. The graduation signal from pgvector to Qdrant is measured p99 degradation under selective filters or sustained concurrent load above 1-5M vectors. Start with pgvector; add Qdrant when you have real numbers showing the crossover. Source: tigerdata.com/blog/pgvector-vs-qdrant, vecstore.app/blog/vector-database-performance-compared.

vs Pinecone

Managed simplicity vs control and cost

Pinecone is a fully managed vector database: zero infrastructure to run, but you cannot tune HNSW parameters (their docs confirm: "Pinecone does not support the ability to tune your index to control the accuracy performance trade-off"). Its managed per-vector pricing climbs with corpus size and query volume. pgvector on self-hosted Postgres or Supabase keeps full HNSW tuning control and folds the vector store into a database you already run and pay for, so there is no second bill and no dual-write sync. Choose Pinecone for zero-ops prototyping at small scale; move to pgvector when cost predictability or index tuning matters.

vs Weaviate

Object-oriented schema, GraphQL API, multi-modal

Weaviate offers auto-schema inference, a GraphQL API, and multi-modal search (text + image) out of the box. Its module system handles vectorization, which removes the need to manage an embedding pipeline separately. The tradeoff: a new service to operate, a new query language to learn, and no relational joins. pgvector fits when your data is already in Postgres and you need SQL semantics. Weaviate fits when the primary interface is vector or multi-modal search and you want the module ecosystem to handle vectorization.

Resources

FAQ

When should I use pgvector instead of a dedicated vector database?: pgvector is the correct default for most RAG and agentic systems: it adds vector search to an existing Postgres instance with no extra service to run. Move to a dedicated vector DB when you have measured evidence of degradation: p99 latency spikes under selective metadata filters (selectivity ~10% or below), or sustained concurrent similarity queries above 1-5M vectors. Measure first, graduate on data, not on projected scale. Source: vecstore.app/blog/vector-database-performance-compared.
Which index should I use: HNSW or IVFFlat?: Start with HNSW. It has a better speed-recall tradeoff, can be built on an empty table (no training step), and degrades more gracefully under query load. IVFFlat is lighter on build time and memory, which matters for very large static corpora that are rarely reindexed. If you choose IVFFlat, build it after inserting representative data: building on an empty table produces meaningless centroids and terrible recall. Sizing IVFFlat lists (official guidance): rows/1000 for up to 1M rows, then sqrt(rows) beyond 1M, not sqrt(rows) at every scale. Source: dbi-services.com/blog/pgvector-a-guide-for-dba-part-2-indexes-update-march-2026.
Does Row Level Security work with vector similarity queries?: Yes. RLS policies apply to any SELECT on the table, including similarity searches using <=>. The vector column is just a column type: Postgres filters out rows the current role is not authorized to see before returning results. On Supabase, auth.uid() is directly available in RLS policies. On self-hosted Postgres, the standard pattern is SET LOCAL app.tenant_id = '...' before the query and a USING (tenant_id = current_setting('app.tenant_id', TRUE)::UUID) policy. Source: supabase.com/docs/guides/ai/rag-with-permissions.
What does ef_search do and when should I tune it?: ef_search controls the size of the dynamic candidate list during an HNSW search. Higher ef_search = better recall at the cost of slower queries. The default is 40. Tune it upward (e.g. SET hnsw.ef_search = 100) when you observe recall degradation on your evaluation set. Tune it downward (e.g. 20) when you can accept lower recall and need maximum throughput. Set it per session or per transaction (SET LOCAL) to vary by use case without changing the index.
Can I use pgvector with Supabase for EU data residency?: Yes. Supabase offers EU regions (Frankfurt, Ireland) and provides managed Postgres with pgvector, RLS, Auth, and connection pooling in one service. For stricter sovereignty requirements (French public sector, HDS), self-hosting Postgres + pgvector on Scaleway fr-par or Hetzner DE gives full jurisdiction control with zero lock-in. pgvector uses the PostgreSQL License (permissive, BSD-style): no vendor dependency on the extension itself.