pgvector: vector search in Postgres, from setup to HNSW indexes

pgvector is an open-source Postgres extension that adds a vector column type and similarity search to Postgres. You store embeddings from a model next to the rows they describe, then find the nearest ones with an ORDER BY on a distance operator. With an HNSW or IVFFlat index, that query stays fast at millions of rows. For most apps that need semantic search, retrieval for an LLM (RAG) or recommendations, it removes the need for a separate vector database.

This guide covers turning pgvector on, the SQL you need, how to pick an index, how much memory vectors take, and where it stops being the right tool. The examples use pgvector 0.8, the version on Ownkube’s managed Postgres.

TL;DR

• Turn it on with CREATE EXTENSION vector;. On a managed host you may need the host to do it for you, since creating extensions usually needs superuser.
• Store embeddings in a vector(n) column, where n matches your model’s output (1536 for OpenAI’s text-embedding-3-small).
• Query with <=> for cosine distance, <-> for Euclidean distance or <#> for negative inner product.
• Use an HNSW index for most apps. It is slower to build and uses more memory than IVFFlat, and it gives better recall at the same speed.
• A 1536-dimension vector takes about 6 KB, so a million of them is about 6 GB before the index. Plan the database’s memory around the index.

Is Postgres a vector database?

With pgvector, yes. A vector database stores embeddings and returns the ones closest to a query vector. pgvector adds both parts to Postgres: a column type to store them and index types that find nearest neighbours without scanning every row.

The practical difference from a dedicated vector database is that your embeddings live in the same database as the rest of your data. A query can filter on user_id, join to the documents table and rank by similarity in one statement, inside one transaction. There is no second system to keep in sync when a row changes or is deleted.

How do I install pgvector?

On your own Postgres, install the package for your Postgres major version (for example postgresql-18-pgvector from the PostgreSQL apt repository), then run this in each database that needs it:

CREATE EXTENSION IF NOT EXISTS vector;

On a managed Postgres, the package is usually already installed and the question is who can run CREATE EXTENSION. Supabase, Neon and Amazon RDS let your database user create it. On Ownkube, you turn pgvector on from the database’s Settings tab, under Extensions, and Ownkube runs CREATE EXTENSION vector for you as the superuser. Your own role never needs superuser rights.

Check that it worked:

SELECT extversion FROM pg_extension WHERE extname = 'vector';

How do I store and query embeddings?

Create a table with a vector column sized to your embedding model:

CREATE TABLE documents (
  id         bigserial PRIMARY KEY,
  user_id    bigint NOT NULL,
  content    text NOT NULL,
  embedding  vector(1536) NOT NULL
);

Insert rows with the embedding your model returned, written as an array literal:

INSERT INTO documents (user_id, content, embedding)
VALUES (42, 'How do I reset my password?', '[0.012, -0.044, ...]');

Most client libraries handle the conversion. In Python, pgvector-python registers the type for psycopg and SQLAlchemy. In Node, pgvector works with pg, Prisma (through raw queries), Drizzle and Kysely.

Then find the five rows closest to a query embedding:

SELECT id, content
FROM documents
WHERE user_id = 42
ORDER BY embedding <=> $1
LIMIT 5;

Which distance operator should I use?

OpenAI’s embeddings are normalized to length 1, so cosine distance and inner product rank results the same way. Inner product is slightly cheaper to compute. Whichever you choose, the index must be built with the matching operator class, or Postgres will not use it.

HNSW or IVFFlat: which index should I use?

Without an index, pgvector compares the query with every row. That is exact and fine up to tens of thousands of rows. Past that, add an approximate index.

HNSW is the better default. It can be built before you load data and keeps working as rows arrive:

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

The operator class must match your query: vector_cosine_ops for <=>, vector_l2_ops for <-> and vector_ip_ops for <#>. The build parameters m (default 16) and ef_construction (default 64) trade build time for recall. Raise hnsw.ef_search per session when you need better recall at query time:

SET hnsw.ef_search = 100;

IVFFlat suits large tables you load once and rebuild occasionally. Build it after loading the data, start with lists at about rows / 1000 for up to a million rows, and set ivfflat.probes to about the square root of lists.

Why does my filtered query return fewer rows than LIMIT?

An approximate index finds the nearest candidates first and applies your WHERE clause after. If the filter removes most of them, you get fewer rows than you asked for. pgvector 0.8 added iterative index scans, which keep scanning until enough rows pass the filter:

SET hnsw.iterative_scan = relaxed_order;

For a filter that always applies, such as a tenant or user id, a B-tree index on that column lets Postgres pick the cheaper plan. When a single value matches few rows, an exact scan of those rows is often faster than the vector index. For a handful of large tenants, a partial index per tenant or table partitioning also works.

How much memory does pgvector need?

Each vector takes 4 bytes per dimension, plus 8 bytes of overhead. The index adds roughly the same again for HNSW.

These cover the vectors alone. Queries stay fast while the HNSW index fits in memory, so size the database’s memory to the index, not just the disk.

There are two ways to shrink it.

Use halfvec

halfvec stores each dimension in 2 bytes instead of 4, which halves storage and index size with little loss of recall for most text embeddings.

The vector type stores up to 16,000 dimensions, and indexes cover up to 2,000. halfvec indexes go up to 4,000, which is how you index a 3072-dimension model.

Use fewer dimensions

OpenAI’s text-embedding-3 models accept a dimensions parameter, so you can request 512 or 768 dimensions instead of the full size.

When is pgvector the wrong choice?

pgvector fits most apps with up to tens of millions of vectors on one database. Look at a dedicated vector database when:

• You have hundreds of millions of vectors or more, and the index no longer fits in the memory of one machine.
• Vector search is the main workload and needs to scale on its own, separate from your transactional database.
• You need features built for search, such as hybrid ranking tuned out of the box or many collections with separate scaling.

For hybrid search inside Postgres, combine pgvector with the built-in full-text search (tsvector) or the pg_trgm extension and merge the two rankings in SQL.

How to use pgvector on Ownkube

Ownkube runs managed PostgreSQL 17 and 18 with pgvector and the other common extensions available per database.

1. Create a database from the dashboard and pick a box. DB 2 (0.5 vCPU, 2 GiB, $12 a month) keeps the table and HNSW index for about 100,000 vectors of 1536 dimensions in memory. DB 8 (2 vCPU, 8 GiB, $54) suits about half a million. Storage is $0.25 per GB a month.
2. Open the database’s Settings tab, turn on pgvector under Extensions, and deploy the change. Ownkube creates the extension for you, with no restart.
3. Link the database to your app so it gets the connection string as an environment variable, from the dashboard or with okctl deploy link.
4. Open psql against it to create the table and index:

okctl connect my-database

The same Extensions section turns on pg_trgm for fuzzy text search, pg_cron for scheduled re-embedding jobs and pgmq for a queue of rows waiting to be embedded, so the whole pipeline can live in one database. A worker that calls the embedding model runs next to it on the same bill.

FAQ

What is pgvector used for?

Semantic search, retrieval for LLM apps (RAG), recommendations, deduplication and clustering. It fits any job where you compare items by the meaning their embeddings capture rather than by exact text.

Is pgvector free?

Yes. pgvector is open source under the PostgreSQL License. Managed hosts that support it do not charge for the extension itself; you pay for the database’s compute and storage.

Does Supabase support pgvector?

Yes, on every plan. Neon, Amazon RDS for PostgreSQL and Ownkube support it too. See Supabase pricing explained for what the database behind it costs.

How many dimensions can pgvector index?

Up to 2,000 for the vector type and up to 4,000 for halfvec. The vector type can store up to 16,000 dimensions, without an index.

Is pgvector fast enough for production?

For most apps, yes. With an HNSW index in memory, nearest-neighbour queries over a million vectors usually return in milliseconds. Speed drops when the index no longer fits in memory, which is the main thing to size for.

Where Ownkube fits

If you want vector search next to your app’s data without running a second database, Ownkube gives you a managed Postgres with pgvector a toggle away, from $12 a month for a box that holds a useful index, with the app and its workers on the same bill. The add a Postgres database guide walks through setup. Deploy my first app.