# pgvector: vector search in Postgres, from setup to HNSW indexes > What pgvector is, how to store embeddings and run similarity search in Postgres, HNSW or IVFFlat, vector memory sizing, and pgvector on managed Postgres. - **Published:** 2026-10-27 - **Author:** Abhishek Jaiswal - **Category:** How-To - **Tags:** pgvector, postgres, vector-database, embeddings, ai, managed-postgres - **Canonical URL:** https://ownkube.io/blog/pgvector - **Cover:** https://ownkube.io/blog/pgvector.png --- 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql SELECT id, content FROM documents WHERE user_id = 42 ORDER BY embedding <=> $1 LIMIT 5; ``` ## Which distance operator should I use? | Operator | Distance | Use it when | |---|---|---| | `<=>` | Cosine distance | The default for text embeddings | | `<->` | Euclidean (L2) distance | Your model's docs say to use L2 | | `<#>` | Negative inner product | Vectors are normalized and you want the fastest option | | `<+>` | L1 (taxicab) distance | Rarely, for some image and sparse models | | `<~>` and `<%>` | Hamming and Jaccard | Binary vectors (the `bit` type) | 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 | IVFFlat | |---|---|---| | How it works | A layered graph of neighbours | Rows grouped into lists around centroids | | Build time | Slower | Faster | | Memory | More | Less | | Recall at the same speed | Higher | Lower | | Build on an empty table | Yes | No, it needs data to pick centroids | | Main query setting | `hnsw.ef_search` (default 40) | `ivfflat.probes` (default 1) | HNSW is the better default. It can be built before you load data and keeps working as rows arrive: ```sql 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: ```sql 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: ```sql 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. | Dimensions | One vector | 100,000 vectors | 1,000,000 vectors | |---|---|---|---| | 384 | about 1.5 KB | about 150 MB | about 1.5 GB | | 768 | about 3 KB | about 300 MB | about 3 GB | | 1536 | about 6 KB | about 600 MB | about 6 GB | | 3072 | about 12 KB | about 1.2 GB | about 12 GB | 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: ```bash 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](/compare/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](/blog/add-managed-postgres-database) walks through setup. [Deploy my first app](https://app.ownkube.io/login). ## Search This site has a search endpoint: https://context7.com/api/v2/ask?siteKey=ask_c64dfe8ffaa8d3ad19576838&query=. It returns documentation that answers the question, with a source link for each part. No API key is needed. If nothing matches, it says so.