pgvector is an open-source Postgres extension that adds a vector column type, distance operators and two approximate-nearest-neighbour indexes (HNSW and IVFFlat), so you can store embeddings next to your normal tables and run similarity search in SQL. To use it: run CREATE EXTENSION vector, add a vector(n) column sized to your embedding model, create an HNSW index with the right operator class, and query with ORDER BY embedding <=> $1 LIMIT k. This guide walks through each step with SQL we ran ourselves, then covers filtering, hybrid search, and when a dedicated vector database is the better call. It is written by the WoWSQL team, but everything here is standard pgvector and works on any Postgres where the extension is installed.
Key facts
- Project: github.com/pgvector/pgvector. Latest release tag v0.8.7 when checked on 22 November 2026.
- Operators:
<->L2 distance,<#>negative inner product,<=>cosine distance,<+>L1 distance (plus Hamming and Jaccard for bit vectors).- Indexable dimensions:
vectorup to 2,000,halfvecup to 4,000,bitup to 64,000,sparsevecup to 1,000 non-zero elements (pgvector README).- Defaults: HNSW
m = 16,ef_construction = 64,hnsw.ef_search = 40; IVFFlativfflat.probes = 1.- Tested: every SQL statement below ran on PostgreSQL 17.12 with pgvector 0.8.0 on 22 November 2026 (with 3-dimension test vectors).
- On WoWSQL: enabling extensions needs a Pro ($25/month) or Scale ($99/month) plan, and the docs list
vectorunder "Optional (if installed on the server)". Check Database → Extensions in your project before you plan on it.
What pgvector does, in one paragraph
An embedding model turns text, images or other data into a list of numbers (a vector) where similar things end up close together. pgvector stores those vectors in a Postgres column and finds the nearest ones to a query vector. Because it lives inside Postgres, the vectors sit in the same rows as titles, owners, timestamps and permissions, so one SQL query can say "the five most similar documents that this user is allowed to see, newer than last month". That is the main reason to use it.
Step 1: Enable the extension
create extension if not exists vector;
-- check the installed version
select extversion from pg_extension where extname = 'vector';
If this fails with "extension is not available", the extension is not installed on the server. On managed platforms it is a dashboard toggle or a plan feature; on your own server, install the package (for example postgresql-17-pgvector on Debian/Ubuntu) or build from source as the README describes.
Step 2: Add a vector column
The number in vector(n) must match your embedding model's output size exactly. Supabase's guide, for example, uses 384 for the open-source gte-small model.
create table documents (
id bigint generated always as identity primary key,
owner_id uuid not null,
title text not null,
body text not null,
embedding vector(384) not null,
created_at timestamptz not null default now()
);
Two rules that save debugging time:
- Use one model per column. Distances between vectors from different models are meaningless.
- Embed queries with the same model and settings you used for the stored documents.
Step 3: Store embeddings from your app
Generate the embedding in your server code, then insert it as a vector literal. Here is a Node.js version with the pg driver; embed() stands in for your embedding provider's call.
import pg from 'pg'
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL })
const toVector = (arr) => `[${arr.join(',')}]`
const embedding = await embed('Refunds are issued within 7 days of a request')
await pool.query(
'insert into documents (owner_id, title, body, embedding) values ($1, $2, $3, $4::vector)',
[ownerId, 'Refund policy', 'Refunds are issued within 7 days of a request', toVector(embedding)],
)
Keep your embedding provider's API key on the server. Never call it from browser or mobile code.
Step 4: Search by similarity
select id, title, 1 - (embedding <=> $1::vector) as similarity
from documents
order by embedding <=> $1::vector
limit 5;
<=> returns cosine distance (0 means identical direction), so 1 - distance gives a similarity score that is easier to read. Always order by the distance expression itself, not the computed similarity, or Postgres can't use the index.
Which operator should you use? Use cosine (<=>) unless your model's documentation says otherwise. If your vectors are normalised to length 1, inner product (<#>) gives the same ranking and is slightly cheaper; remember it returns the negative inner product.
Step 5: Add an HNSW index
Without an index, pgvector does an exact scan: perfect recall, but it reads every row. Add an approximate index once the table grows:
create index documents_embedding_hnsw
on documents using hnsw (embedding vector_cosine_ops);
The operator class must match the operator you query with: vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>. A mismatch means the index is silently ignored.
Tuning knobs, with pgvector's defaults:
-- build-time: more connections and candidates = better recall, slower build
create index on documents using hnsw (embedding vector_cosine_ops)
with (m = 16, ef_construction = 64);
-- query-time: a bigger candidate list = better recall, slower queries
begin;
set local hnsw.ef_search = 100;
select id, title from documents order by embedding <=> $1::vector limit 10;
commit;
Large builds go faster when the graph fits in maintenance_work_mem; pgvector prints a notice when it doesn't. Don't set it so high that the server runs out of memory.
HNSW or IVFFlat?
| HNSW | IVFFlat | |
|---|---|---|
| Query speed vs recall | Better | Lower |
| Build time and memory | Slower, more memory | Faster, less memory |
| Can be built on an empty table | Yes (no training step) | No: create it after the table has data |
| Main knobs | m, ef_construction, hnsw.ef_search |
lists, ivfflat.probes |
| Starting point | Defaults | lists = rows / 1000 up to 1M rows, sqrt(rows) above; probes = sqrt(lists) |
Source: pgvector README. For most apps, start with HNSW.
-- IVFFlat, if you need faster builds or less memory
create index on documents using ivfflat (embedding vector_cosine_ops) with (lists = 100);
set ivfflat.probes = 10;
Step 6: Filter by user, tenant or category
Real apps almost always filter. The simple version:
create index on documents (owner_id);
select id, title
from documents
where owner_id = $2
order by embedding <=> $1::vector
limit 5;
The catch: with an approximate index, the filter is applied after the index scan. pgvector's README gives the example that if a condition matches 10% of rows, HNSW with the default ef_search of 40 returns only about 4 matching rows on average. Since version 0.8.0 you can turn on iterative scans so the index keeps searching until it finds enough rows:
begin;
set local hnsw.iterative_scan = strict_order;
select id, title from documents
where owner_id = $2
order by embedding <=> $1::vector
limit 5;
commit;
Other options from the README: a partial index per common filter value, or partitioning by the filter column when there are many values. For multi-tenant apps, sharing one approximate index across tenants means one tenant's vectors can affect another's recall and speed.
Row Level Security works too
Because vectors are ordinary columns, Postgres Row Level Security applies to similarity search. On a Postgres backend as a service (Supabase, WoWSQL), a policy such as using (auth.uid() = owner_id) limits search results to the signed-in user's rows; on plain Postgres, compare against your own session variable. See What is row level security?.
Step 7: Call it through a REST API
If your frontend talks to Postgres through PostgREST (the API layer behind Supabase's and WoWSQL's /rest/v1), wrap the search in a SQL function, because the vector operators are not available as URL filters. Supabase's vector columns guide uses the same pattern.
create or replace function match_documents(query_embedding vector(384), match_count int default 5)
returns table (id bigint, title text, body text, similarity float)
language sql stable
as $$
select d.id, d.title, d.body, 1 - (d.embedding <=> query_embedding) as similarity
from documents d
order by d.embedding <=> query_embedding
limit match_count;
$$;
PostgREST exposes functions at POST /rpc/<function_name> (PostgREST docs), with arguments as JSON. The function runs with the caller's permissions by default, so RLS policies still apply.
Step 8: Hybrid search (vectors plus keywords)
Embeddings are good at meaning and bad at exact terms like product codes or names. Combining vector search with Postgres full-text search and merging the ranks (reciprocal rank fusion) often gives better results than either alone:
alter table documents
add column fts tsvector generated always as (to_tsvector('english', title || ' ' || body)) stored;
create index on documents using gin (fts);
with semantic as (
select id, row_number() over (order by embedding <=> $1::vector) as rank
from documents
order by embedding <=> $1::vector
limit 20
), keyword as (
select id, row_number() over (order by ts_rank_cd(fts, q) desc) as rank
from documents, websearch_to_tsquery('english', $2) q
where fts @@ q
order by ts_rank_cd(fts, q) desc
limit 20
)
select d.id, d.title,
coalesce(1.0 / (60 + s.rank), 0) + coalesce(1.0 / (60 + k.rank), 0) as score
from semantic s
full outer join keyword k on s.id = k.id
join documents d on d.id = coalesce(s.id, k.id)
order by score desc
limit 5;
$1 is the query embedding and $2 the raw query text. The constant 60 is the usual RRF damping value; tune it on your own data.
Saving space: half-precision vectors
halfvec stores each dimension in 2 bytes instead of 4 and can be indexed up to 4,000 dimensions. You can index a half-precision copy without changing the column:
create index on documents using hnsw ((embedding::halfvec(384)) halfvec_cosine_ops);
select id from documents
order by embedding::halfvec(384) <=> $1::halfvec(384)
limit 5;
pgvector or a dedicated vector database?
pgvector is the better default when:
- your vectors belong to rows you already have (documents, products, users), and you need joins, filters and permissions in the same query;
- you want one database to back up, secure and pay for;
- your data fits comfortably on one Postgres server.
A dedicated vector database is worth a look when:
- vector search is the core workload and the collection is large enough that index builds and memory dominate your Postgres server;
- you need features pgvector doesn't target, such as built-in sharding of one index across many machines;
- your team would rather run search as a separate service with its own scaling.
We are not quoting row-count thresholds on purpose: they depend on dimensions, recall targets, filters and hardware. Load a realistic sample, measure recall and latency at your target ef_search, and decide from numbers.
Using pgvector on managed Postgres
| Platform | pgvector status (checked 22 November 2026) | Source |
|---|---|---|
| Supabase | Documented: enable vector under Database → Extensions |
Supabase vector columns |
| Neon | Listed in Neon's comparison guide for vector search | Neon vs Supabase guide |
| Your own server | Install the package or build from source | pgvector README |
| WoWSQL | Extensions need Pro or Scale; vector is listed as "optional (if installed on the server)". Not confirmed for every project: check Database → Extensions in your dashboard |
WoWSQL advanced features |
To be plain about WoWSQL: we are not claiming pgvector is available on every WoWSQL project. The docs list it as optional, depending on the server. Open your project's extensions page (or GET /api/v1/extensions/available from the docs) and see whether vector appears before you build on it. Extensions that WoWSQL lists as included on paid plans are pgcrypto, uuid-ossp and pg_stat_statements, with a second tier that includes pg_trgm, citext, hstore and ltree. The pg_trgm extension is useful for fuzzy keyword matching even without vectors.
For the wider AI-app picture (auth, MCP, storage), see Best backend for AI apps, Backend for AI agents: Postgres with MCP and Postgres MCP server options.
Related guides
- Supabase vs Neon (2026): backend platform or database?
- Do I need a backend for my app?
- Indian SaaS selling to the US: pick your DB region
- Backend for UK apps: using a Frankfurt region
FAQ
How do I use pgvector for vector search?
Enable it with create extension vector, add a vector(n) column matching your embedding model's size, insert embeddings from your server code, create an HNSW index with vector_cosine_ops, and query with order by embedding <=> $1 limit 5. Use the same model for documents and queries.
What is the difference between HNSW and IVFFlat in pgvector?
HNSW gives better speed for the same recall 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 has lower query performance, and it should be created after the table has data. Start with HNSW for most apps.
How many dimensions can pgvector index?
Up to 2,000 dimensions for vector, 4,000 for halfvec, 64,000 for bit and 1,000 non-zero elements for sparsevec, per the pgvector README checked on 22 November 2026. For larger embeddings, index a halfvec cast of the column.
Should I use pgvector or Pinecone?
Use pgvector when your vectors belong to rows you already store in Postgres and you need filters, joins and permissions in one query. Consider a dedicated vector database when vector search is your main workload at a scale where index memory and build time dominate a Postgres server. Test both on your own data.
Why does my filtered pgvector query return fewer rows than the limit?
With an approximate index, filters apply after the index scan, so a selective where clause can leave fewer rows than limit. Turn on iterative scans (set hnsw.iterative_scan = strict_order, pgvector 0.8.0 and later), raise hnsw.ef_search, or add a B-tree or partial index for the filter.
Does WoWSQL support pgvector?
Not on every project as a guarantee. WoWSQL's docs say enabling extensions needs Pro or Scale and list vector as "optional (if installed on the server)". Check Database → Extensions in your WoWSQL dashboard to see whether it can be enabled for your project.