pgvector Index and Schema Generator

Pick your embedding dimensions, distance metric and index type, and this generator writes the pgvector SQL for you: CREATE EXTENSION, the table or ALTER TABLE with a vector, halfvec, bit or sparsevec column, an HNSW or IVFFlat index with the right operator class, and the nearest-neighbour query whose ORDER BY matches that index. It also suggests IVFFlat lists/probes from your row count and emits EXPLAIN and recall checks. Everything runs in your browser — no embeddings are uploaded.

Table (schema.table)Vector columnDimensionsColumn type

Distance metricIndexCONCURRENTLYEmit CREATE TABLE (uncheck for ALTER TABLE)

m (links per node)ef_constructionhnsw.ef_search

Filter column (optional)Partial index WHERETop K

Schema and index SQL

-- 1. Enable the extension (superuser or rds_superuser)
CREATE EXTENSION IF NOT EXISTS vector;

-- 2. Table with the embedding column
CREATE TABLE IF NOT EXISTS public.documents (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   text NOT NULL,
  content     text NOT NULL,
  metadata    jsonb NOT NULL DEFAULT '{}'::jsonb,
  embedding   vector(1536),
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- 3. Approximate nearest-neighbour index (HNSW)
CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_idx
  ON public.documents USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Faster build: give the session more memory and parallel workers
SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;

-- Helper index for the metadata filter used below
CREATE INDEX IF NOT EXISTS documents_tenant_id_idx ON public.documents (tenant_id);

Nearest-neighbour query

-- Nearest neighbours for a query embedding
SET hnsw.ef_search = 40; -- raise for better recall
SELECT id,
       content,
       embedding <=> $1 AS distance
       , 1 - (embedding <=> $1) AS cosine_similarity
FROM public.documents
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 10;

-- Bind $1 as a literal like '[0.12,-0.44,...]' with exactly 1536 numbers.
-- The ORDER BY expression must match the index operator (<=>) exactly,
-- otherwise the planner falls back to a sequential scan.

-- Distance threshold (filter out weak matches)
SELECT id, content FROM public.documents
WHERE embedding <=> $1 < 0.35
ORDER BY embedding <=> $1 LIMIT 10;

Verify: plan, size, recall

-- Is the ANN index actually used?
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM public.documents ORDER BY embedding <=> $1 LIMIT 10;
-- Look for "Index Scan using documents_embedding_hnsw_idx"; a Seq Scan + Sort means
-- the operator, operator class or LIMIT does not match the index.

-- Index size and build progress
SELECT pg_size_pretty(pg_relation_size('documents_embedding_hnsw_idx')) AS index_size;
SELECT phase, blocks_done, blocks_total, tuples_done, tuples_total
FROM pg_stat_progress_create_index;

-- Installed pgvector version (0.5+ for HNSW, 0.7+ for halfvec/sparsevec/L1)
SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';

-- How many rows still have no embedding?
SELECT count(*) FILTER (WHERE embedding IS NULL) AS missing, count(*) AS total FROM public.documents;

-- Recall check: compare ANN results with exact search
SET LOCAL enable_indexscan = off;
SELECT id FROM public.documents ORDER BY embedding <=> $1 LIMIT 10; -- ground truth

Do more than pgvector index and schema generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the table and column, the embedding dimensions (1536 for OpenAI text-embedding-3-small, 768 for many open models) and the column type.
Choose the distance metric your model was trained for — cosine for normalised embeddings, L2 for raw Euclidean, inner product for dot-product models — then pick HNSW or IVFFlat and tune m, ef_construction or lists.
Copy the schema script, the KNN query and the verification queries into psql or Chat2DB, then confirm with EXPLAIN that the index is used.

Frequently asked questions

Should I use an HNSW or an IVFFlat index in pgvector?

HNSW builds a graph and gives better recall at the same speed, works on an empty table, and is the default choice for most applications; the trade-off is a slower build and more memory. IVFFlat clusters vectors into lists and builds much faster with a smaller index, but it must be created after the data is loaded (an empty table produces meaningless centroids) and needs rebuilding when the data distribution shifts. Start with HNSW using m = 16 and ef_construction = 64, then raise hnsw.ef_search at query time until recall is acceptable.

Why does my pgvector query do a sequential scan instead of using the index?

An approximate index only answers ORDER BY queries that use the exact operator it was built for: an index created with vector_cosine_ops serves ORDER BY embedding <=> $1 and nothing else, so switching to <-> or wrapping the column in a function forces a scan. Other common causes are a missing LIMIT, a query that filters most rows away so the planner prefers a scan, a dimension mismatch between the column and the literal, or an index that was built on a different column type. Run EXPLAIN (ANALYZE, BUFFERS) and check for "Index Scan using ..._hnsw_idx".

How do I combine vector search with normal WHERE filters in PostgreSQL?

Postgres can apply a filter and an ANN index together, but the index returns a fixed number of candidates first, so a very selective filter can leave fewer rows than your LIMIT. Three patterns work well: add a B-tree index on the filter column and over-fetch (LIMIT 100, filter, keep 10); build a partial HNSW index with a WHERE clause per tenant or category; or pre-filter into a CTE when the subset is small enough for exact search. Chat2DB, a free AI-powered SQL client, is handy for comparing the plans of these variants side by side — download it at https://chat2db.ai/download or use https://app.chat2db.ai.