Vector pgvector

pgvector is an open-source extension that adds vector search to PostgreSQL. Teams that already run Postgres can store embeddings next to their regular tables and query both with ordinary SQL. No second database to install, secure, and maintain.

Why Add Vectors to Postgres?

Most applications already keep users, products, and orders in a relational database. Adding a separate vector database creates two systems that must stay in sync. pgvector removes that gap by keeping everything in one place.

ONE DATABASE, TWO SKILLS

+---------------------------------------------+
| PostgreSQL                                  |
|                                             |
|  products table                             |
|   id | name | price | embedding (vector)    |
|                                             |
|  SQL filters   +   Vector similarity        |
+---------------------------------------------+

Setting Up

-- Turn on the extension
CREATE EXTENSION vector;

-- Create a table with a vector column of 3 dimensions
CREATE TABLE items (
  id        bigserial PRIMARY KEY,
  title     text,
  category  text,
  embedding vector(3)
);

The number inside vector(3) sets the dimension. Real embeddings use larger values such as 384 or 768.

Inserting Data

INSERT INTO items (title, category, embedding) VALUES
  ('Beach holiday guide', 'travel',  '[0.1, 0.9, 0.3]'),
  ('Pasta recipe basics', 'cooking', '[0.8, 0.1, 0.2]'),
  ('Budget flight tips',  'travel',  '[0.2, 0.8, 0.4]');

Searching by Similarity

pgvector provides distance operators. You sort rows by distance and keep the closest few.

SELECT title
FROM items
ORDER BY embedding <=> '[0.1, 0.8, 0.4]'
LIMIT 2;

Smaller distance means a closer match, so ORDER BY ascending returns the best results first.

Combining Vectors With SQL

The real strength of pgvector appears when you mix similarity with normal SQL conditions.

SELECT title, category
FROM items
WHERE category = 'travel'
ORDER BY embedding <=> '[0.1, 0.8, 0.4]'
LIMIT 5;

You can join tables, group results, and apply permissions in the same query. A vector-only database would need extra application code for these steps.

Adding an Index

Without an index, Postgres scans every row, which behaves like a flat index. Create an approximate index for larger tables.

-- HNSW index for cosine distance
CREATE INDEX ON items
USING hnsw (embedding vector_cosine_ops);

-- IVFFlat alternative
CREATE INDEX ON items
USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

Strengths

• One system for relational data and vectors.
• Full SQL power, including joins and transactions.
• Familiar backup, security, and monitoring tools.
• Wide support across managed Postgres services.

Limits

• Very large collections may strain a single Postgres server.
• Specialized vector databases offer more tuning options for extreme scale.
• Index builds on big tables consume memory and time.

When pgvector Fits

1. Your team already uses PostgreSQL.
2. Your collection holds thousands to a few million vectors.
3. Your searches need strong filtering on relational data.
4. You prefer fewer moving parts in your architecture.

pgvector gives you a practical path into vector search without learning a new database. Begin with a simple table, test recall and speed, and add an index when query time grows.