Semantic search in pure SQL

Every other guide here writes application code. This one doesn’t write any.

If you already run Postgres, you can store vectors in a column, index them, filter them by whatever you filter everything else by, and join them to the rest of your data — in SQL, with one extension. For most teams that’s the whole answer, and the rest of this site’s advice about vector databases never becomes relevant.

Every query on this page was run

The output blocks below are real psql output from PostgreSQL 16.15 with pgvector 0.6.0. Nothing here is illustrative.

The similarity scores also match the C#, Java and Go guides exactly — same vectors, same maths, five different implementations agreeing.

Before you start

You need Postgres and the pgvector extension.

# Debian / Ubuntu, matching your server version
sudo apt install postgresql-16-pgvector

# macOS with Homebrew
brew install pgvector

Managed Postgres almost always has it already: Supabase, Neon, Amazon RDS and Aurora, Google Cloud SQL and AlloyDB, and Azure Database for PostgreSQL all ship pgvector. You may only need to enable it.

CREATE EXTENSION IF NOT EXISTS vector;

The schema

The vector is just a column. That sentence is the entire argument for this approach.

CREATE TABLE notes (
    id        bigserial PRIMARY KEY,
    body      text      NOT NULL,
    owner_id  bigint    NOT NULL,
    embedding vector(5) NOT NULL
);

vector(5) fixes the width at five dimensions. Real models produce far more — 1,536 for text-embedding-3-small, 384 for all-MiniLM-L6-v2 — and you must declare the right number, because vectors of different widths cannot be compared.

We’re using five here for the same reason the C# and Java guides do: hand-picked numbers across five made-up categories (where things are kept, tools & DIY, food, health, admin & money) keep the maths visible. A 1,536-number literal is unreadable in a SQL statement and teaches nothing.

INSERT INTO notes (body, owner_id, embedding) VALUES
  ('The spare set for the front door is in the kitchen drawer.', 1, '[0.95,0.20,0.05,0.00,0.10]'),
  ('The dog needs his flea treatment on the 3rd.',               1, '[0.05,0.00,0.10,0.70,0.35]'),
  ('Mum''s birthday is in April, she likes tulips.',             1, '[0.05,0.00,0.15,0.00,0.60]'),
  ('Renew the car insurance before it lapses.',                  2, '[0.00,0.05,0.00,0.00,0.95]'),
  ('The standing desk needs a 4mm hex key to assemble.',         1, '[0.15,0.95,0.00,0.05,0.00]'),
  ('Pizza dough: 500g flour, 300ml water, rest overnight.',      1, '[0.05,0.05,0.95,0.10,0.00]'),
  ('The wifi password is taped under the router.',               2, '[0.65,0.10,0.00,0.00,0.20]'),
  ('Physio said ice the knee for twenty minutes.',               1, '[0.00,0.00,0.00,0.95,0.05]');

A vector literal is a string: square brackets, comma-separated, no spaces required.

The search

SELECT round((1 - (embedding <=> '[0.90,0.30,0.00,0.00,0.05]'))::numeric, 3) AS similarity,
       body
FROM notes
ORDER BY embedding <=> '[0.90,0.30,0.00,0.00,0.05]'
LIMIT 3;

 similarity |                            body
------------+------------------------------------------------------------
      0.991 | The spare set for the front door is in the kitchen drawer.
      0.957 | The wifi password is taped under the router.
      0.459 | The standing desk needs a 4mm hex key to assemble.
(3 rows)

That’s semantic search. One ORDER BY.

The query vector represents “where did I put my keys?”. The winning note contains no form of the word “key” — and the note that does, the 4mm hex key, correctly lands third. A LIKE '%key%' would have returned exactly the wrong row.

`<=>` is a distance, not a similarity

This trips people up constantly. pgvector’s <=> returns cosine distance, which runs the opposite way to the similarity scores used everywhere else on this site: 0 means identical, 2 means opposite.

So ORDER BY embedding <=> query is ascending — nearest first — and to display a familiar similarity you convert with 1 - distance, which is what the SELECT above does.

Get this backwards and your search returns the least relevant rows, ranked confidently.

The three operators

pgvector gives you three, and picking the wrong one is a quiet way to get bad results.

SELECT '[1,0,0,0,0]'::vector <=> '[0,1,0,0,0]' AS cosine_distance,
       '[1,0,0,0,0]'::vector <->  '[0,1,0,0,0]' AS l2_distance,
       '[1,0,0,0,0]'::vector <#>  '[0,1,0,0,0]' AS neg_inner_product;

 cosine_distance |    l2_distance     | neg_inner_product
-----------------+--------------------+-------------------
               1 | 1.4142135623730951 |                -0
(1 row)

Those two vectors are at right angles, so cosine distance is exactly 1 — no similarity at all. L2 is √2, the straight-line gap. Inner product comes back negated, because Postgres indexes ascend and a larger dot product means more similar; pgvector flips the sign so “smaller is better” holds throughout.

Use <=> for text. It ignores vector length, which for embeddings mostly encodes how long the text was rather than what it meant. Full explanation.

Filtering, which is the actual point

This is where a vector column beats a separate vector database, and it’s one word of SQL:

SELECT round((1 - (embedding <=> '[0.90,0.30,0.00,0.00,0.05]'))::numeric, 3) AS similarity, body
FROM notes
WHERE owner_id = 2
ORDER BY embedding <=> '[0.90,0.30,0.00,0.00,0.05]'
LIMIT 3;

 similarity |                     body
------------+----------------------------------------------
      0.957 | The wifi password is taped under the router.
      0.069 | Renew the car insurance before it lapses.
(2 rows)

Two rows, because user 2 only owns two notes. No post-filtering, no over-fetching, no leaking another user’s content into your process memory. The planner applies the predicate; you didn’t have to think about it.

Doing this correctly in a standalone vector database is a real engineering problem and a large part of what those products charge for. Here it’s a WHERE clause you’d have written anyway.

Joins

SELECT o.name,
       round((1 - (n.embedding <=> '[0.90,0.30,0.00,0.00,0.05]'))::numeric, 3) AS similarity,
       n.body
FROM notes n
JOIN owners o ON o.id = n.owner_id
ORDER BY n.embedding <=> '[0.90,0.30,0.00,0.00,0.05]'
LIMIT 3;

 name  | similarity |                            body
-------+------------+------------------------------------------------------------
 Priya |      0.991 | The spare set for the front door is in the kitchen drawer.
 Sam   |      0.957 | The wifi password is taped under the router.
 Priya |      0.459 | The standing desk needs a 4mm hex key to assemble.
(3 rows)

Semantic ranking and relational data in one query, one round trip, one transaction. With a separate vector store this is two systems, an ID round trip, and a consistency problem you now own.

Deletion just works

DELETE FROM notes WHERE body LIKE 'The wifi%';
SELECT count(*) AS notes_remaining FROM notes;

 notes_remaining
-----------------
               7
(1 row)

The vector went with the row, because the vector is part of the row. No second system to keep in step, no orphaned embeddings still surfacing in search.

That sounds mundane until a right-to-erasure request arrives and you need to prove the vector is gone too.

Indexing

Everything above was a sequential scan — fine for eight rows, and fine well beyond that. When it stops being fine:

CREATE INDEX ON notes USING hnsw (embedding vector_cosine_ops);

1. Match the operator class to your operator. vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>. An index built for the wrong one is simply not used, and your query silently stays slow.

2. HNSW or IVFFlat. HNSW builds slower and uses more memory, but gives better recall and doesn’t need training data. IVFFlat builds fast but must be created after the table has representative rows in it, because it clusters what it sees.

3. It’s approximate. You may miss the true nearest neighbour occasionally. Tune with SET hnsw.ef_search = 100; — higher is more accurate and slower.

4. Check it’s used. EXPLAIN ANALYZE your query. If you see a sequential scan, something doesn’t match: usually the operator class, or a WHERE selective enough that the planner reasonably prefers a scan.

Above 2,000 dimensions, indexes need a workaround

pgvector’s indexes cap at 2,000 dimensions. text-embedding-3-small fits at 1,536; text-embedding-3-large at 3,072 does not.

Options: ask the API for fewer dimensions via its dimensions parameter (see choosing a model), or store the full vector as halfvec and index that. Reducing to 1,024 or fewer is usually the better trade — smaller, faster, indexable, and typically no worse on your data.

Getting real embeddings in

The vectors above were written by hand. In production something has to generate them, and Postgres won’t call an embedding API for you.

The usual shape:

-- 1. Insert the text with no vector yet
INSERT INTO notes (body, owner_id) VALUES ('…', 1);

-- 2. A worker finds what needs embedding
SELECT id, body FROM notes WHERE embedding IS NULL LIMIT 100;

-- 3. …calls the model, then writes the vectors back
UPDATE notes SET embedding = $2 WHERE id = $1;

Make embedding nullable, and let a background worker fill it in. It keeps writes fast, survives the API being down, and gives you a trivially resumable backfill when you change models — which you will, and which is a migration.

Some managed platforms will do the call for you: Supabase has edge functions with gte-small built in, and AlloyDB can invoke Vertex AI from SQL. Both are convenient and both tie your schema to one vendor.

When this stops being enough

Honestly, later than most people assume. pgvector comfortably handles millions of vectors on ordinary hardware.

Reach for something else when you need very high query concurrency against a large index, or distributed sharding, or filtered ANN at a scale where HNSW’s recall degrades. Then look at the database directory — and note that pgvectorscale and VectorChord extend Postgres a good deal further before you have to leave it.

Where to go from here

Databases that support vectorsSixty-nine of them, and why the one you already run is usually right.

Chunking and vector databasesHow to split documents before any of this helps.

Security and privacyFiltering by permission, and why deletion has to reach the vectors.

The same app in PythonIf you need application code after all, this is the shortest version.

References

• pgvector — the extension, its operators and index types
• pgvector — indexing — HNSW and IVFFlat tuning in detail
• PostgreSQL — CREATE INDEX — operator classes, and why the wrong one is ignored
• pgvectorscale — StreamingDiskANN for larger-than-memory indexes
• Supabase — AI and vectors — managed Postgres with embedding generation built in
• AlloyDB — AI — calling Vertex AI models directly from SQL
• HNSW paper — the algorithm behind the index you just created