To store and search embeddings with pgvector, enable the extension in the database, create a column whose vector dimension matches your embedding model, insert vectors, and sort query results by the distance operator for your chosen metric. PostgreSQL performs exact nearest-neighbor search by default; add an HNSW or IVFFlat index when measured workloads justify approximate search and its tradeoffs.
1. Enable pgvector and create a vector column
pgvector is a PostgreSQL extension for storing vectors and searching them by distance. The project README describes installation from release 0.8.6 and support for PostgreSQL 13 and later; installation steps depend on your PostgreSQL environment. After the extension is installed and available to the database, enable it in each database where you plan to use it with CREATE EXTENSION vector;. Check the pgvector project README for instructions that match your deployed version.
Choose the column dimension to match the output dimension of the embedding model you use. The vector(3) below is only a small example, not a recommended production dimension.
CREATE EXTENSION vector;
CREATE TABLE items (
id bigserial PRIMARY KEY,
embedding vector(3)
);Use the model’s actual output size in place of 3. A column with a mismatched dimension is not a suitable place for those embeddings.
Free tools Windows power users keep installed
One-click scans. No signup required.
2. Insert embeddings and run a nearest-neighbor query
Vectors can be inserted as bracketed, comma-separated values. In production, applications typically store the embedding returned by their model rather than the illustrative values here.
INSERT INTO items (embedding)
VALUES ('[1,2,3]'), ('[4,5,6]');
SELECT *
FROM items
ORDER BY embedding <-> '[3,1,2]'
LIMIT 5;The query orders rows from the smallest distance upward and returns up to five. The result is a nearest-neighbor search; do not call a distance value “similarity” without specifying the metric and whether larger or smaller values indicate a closer match.
3. Pick a distance operator that matches your metric
pgvector provides operators for several distance or comparison metrics. Use the same metric when querying and when choosing an index operator class.
| Operator | Meaning | Query ordering |
|---|---|---|
<-> |
L2 (Euclidean) distance | Ascending order returns nearer vectors first. |
<=> |
Cosine distance | Ascending order returns nearer vectors first. |
<#> |
Negative inner product | Ascending order is intentional so an index scan can return the highest inner products first. |
<+> |
L1 (taxicab) distance | Ascending order returns nearer vectors first. |
For example, switch the query expression to embedding <=> '[3,1,2]' for cosine distance or embedding <#> '[3,1,2]' for negative inner product. The operators and their index support are documented in the pgvector README.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 114. Decide whether to add an approximate index
Without an approximate index, pgvector uses exact nearest-neighbor search, which provides perfect recall: it finds the true nearest results for the query and metric. Exact search can be the right choice when correctness certainty matters or the dataset and latency permit it. Approximate indexes can improve query speed, but may not return the exact nearest neighbors. The project describes HNSW as generally offering a better speed-recall tradeoff than IVFFlat, at higher index-build and memory cost; these are qualitative project comparisons, not a guarantee for any particular workload.
| Approach | Tradeoff described by pgvector | Practical use |
|---|---|---|
| Exact search | Perfect recall; no approximate index is required. | Start here to establish correct results and a baseline. |
| HNSW | Generally a better speed-recall tradeoff than IVFFlat, with slower builds and higher memory use. It has no training step and can be created before data is loaded. | Consider when query performance matters and the added build and memory cost is acceptable. |
| IVFFlat | Typically faster to build and lower in memory use than HNSW, but with lower query performance in the project’s comparison. It needs data for useful training. | Consider when build cost and memory are priorities, and tune lists and probes against your workload. |
For either approximate option, create an index with an operator class compatible with the metric in your query. For example, an L2 HNSW index is written as:
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);For cosine distance, use vector_cosine_ops; for inner product, use vector_ip_ops. Follow the current README for the full supported options and version-specific details.
5. Tune IVFFlat using measurements, not a fixed recipe
IVFFlat organizes vectors into lists and probes some of those lists during a search. The pgvector README offers initial heuristics: use approximately rows / 1000 lists for tables up to one million rows, and approximately sqrt(rows) lists above one million rows. A starting probe count is approximately sqrt(lists). These are starting points, not universal settings or performance guarantees; increasing probes can improve recall while costing query speed. Build the index only after the table contains data so it has useful training data.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Best Value
Compare candidate settings with exact results and representative queries. Measure recall, query latency, index build time, and resource use on your own data before settling on an index or its parameters.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.6. Account for filters and tenant isolation
A filtered nearest-neighbor query might restrict results to a category, status, or tenant. With approximate indexes, filtering is applied after the index scan, so a selective condition can leave too few candidates even when the overall search returns enough vectors.
The README illustrates the effect this way: if a filter matches 10% of rows and HNSW uses its default hnsw.ef_search of 40, an average of four matching rows would be expected. This is an illustrative expectation from the documentation, not a general benchmark or a promise about an individual query.
- Try exact search for highly selective filters. An ordinary index on the filter column can help locate a small matching subset before ranking it by distance.
- Use iterative scans when applicable. They can continue scanning when filtering leaves too few candidates; check the README for the supported settings in your pgvector version.
- Consider partial indexes for a few stable filter values. They can target a small number of frequently queried subsets.
- Consider partitioning for many filter values. For tenant isolation, pgvector recommends list partitioning or separate tables; a shared approximate index can let one tenant’s vectors affect another tenant’s recall and speed.
7. Validate the choice on your workload
There is no universal row count at which an approximate index becomes worthwhile. Begin with exact queries, then compare an approximate index using representative data, query vectors, filters, and concurrency. Check how often approximate results match the exact nearest neighbors, how query latency changes, and what index creation and memory cost. Revisit the comparison after changing embedding models, dimensions, filter patterns, or pgvector versions; implementation details and defaults can change, so consult the README for the version you deploy.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
