I Built a Recommendation Engine in Pure Postgres. The Embeddings Lost to ORDER BY count(*).

The brief sounds trivial: add “people also bought” to a store. Every tutorial gives you the same answer — embed your products, drop the vectors in pgvector, return nearest neighbors. Done.

So I built exactly that, on a real catalog. It produced beautiful, sensible-looking recommendations. Then I measured it against a baseline so dumb it’s almost insulting — recommend whatever’s most popular — and the embeddings lost. Not by a little. Then I replaced them with a handful of lines of SQL — a self-join that just counts — and beat the vectors by 3.4×.

This post is the honest version of “build a recommendation engine on Postgres.” Everything is in one database, every number is measured on real data, and the most useful parts are the places where the obvious approach falls on its face. I’ll keep the jargon explained as it comes up, because the punchline matters whether or not you write SQL: before you buy specialized AI infrastructure for recommendations, the boring thing — counting what your customers actually do — is usually the thing to beat. Here’s what actually works, and where the fancy approach does earn its keep.

The setup: one Postgres, real data

I used the Amazon Reviews 2023 dataset (McAuley Lab, UCSD), Video Games category, 5-core:

• 25,611 products with real titles, descriptions, brands, prices, and image URLs
• 814,533 interactions (real user reviews as a purchase signal)
• McAuley’s canonical leave-last-out split, so I can evaluate next-item prediction without fooling myself

Everything lives in one Postgres 17 container with two extensions — pgvector for embeddings and pg_textsearch for BM25 keyword ranking. No second datastore, no microservice.

Embeddings come from nomic-embed-text-v1.5 running locally on a single RTX 4090 (via llama.cpp). Embedding all 25,611 products took 179 seconds — about 143 docs/sec. The HNSW index built in 5.6 seconds and weighs 100 MB. None of this needs a cluster.

CREATE EXTENSION vector;
CREATE EXTENSION pg_textsearch;

CREATE TABLE products (
  id         text PRIMARY KEY,        -- Amazon ASIN
  title      text,
  brand      text,
  category   text,
  price      numeric,
  content    text,                    -- title + brand + category + description
  embedding  vector(768)              -- nomic-embed-text
);

The naive approach: nearest neighbors in embedding space

The standard trick is embeddings. An embedding turns each product’s text into a long list of numbers that captures what the product is about — arranged so that similar products end up with similar numbers. “Find similar products” then becomes “find the closest points in space.” pgvector is the extension that stores those numbers inside Postgres, and an HNSW index is what makes “find the closest points” fast — without it, every lookup would have to compare against all 25,611 products one by one. With it, “people also bought” is a single query:

SELECT p.id, p.title, 1 - (p.embedding <=> q.embedding) AS score
FROM products p, (SELECT embedding FROM products WHERE id = $1) q
WHERE p.id <> $1
ORDER BY p.embedding <=> q.embedding
LIMIT 10;

When you project all 25,611 product embeddings down to 2D, you can see why this feels right — the catalog organizes itself into clean semantic neighborhoods. Games in one continent, controllers in another, headsets, consoles, storage, each with their own territory. This is the map pgvector searches over:

Query The Legend of Zelda: Breath of the Wild and the nearest neighbors are flawless: Breath of the Wild’s other editions, Link’s Awakening, Twilight Princess, A Link to the Past. Ten Zelda games. The model clearly understands the product.

That’s exactly the problem.

The measurement that ruined it

So I measured it properly. For 4,000 customers I hid their last purchase, showed each method everything they’d bought before it, and checked whether the thing they actually bought next showed up in the top 10 recommendations. That’s the score called HitRate@10 — literally “how often we got it in our top 10.” (The other columns — nDCG, MRR — are finer-grained versions of the same question: did the right answer land near the top, not just somewhere in the list.) Every method here is pure SQL in Postgres:

Look at the first two rows. Content embeddings (0.013) lost to “just recommend popular stuff” (0.021). The fancy 768-dimensional semantic search underperformed a GROUP BY ... ORDER BY count(*).

(If those absolute numbers look tiny, they’re normal for next-item prediction over a 25k-item catalog — random guessing scores about 0.0004. The ranking between methods is the point, not the absolute value.)

Why content similarity is the wrong similarity

The Zelda result already told us, we just didn’t listen. Watch what happens when you put the two answers on the same map — the content neighbors versus what people actually bought next (from real co-purchase data):

On the left, the ten content neighbors are stacked on top of the query — ten near-identical Zelda titles in one tiny region. On the right, the items people genuinely bought after Breath of the Wild:

a tempered-glass screen protector, a Switch Pro Controller, Mario Kart 8, Super Mario Odyssey, Splatoon 2, an eShop gift card…

Accessories and completely different games, scattered across the whole catalog. Nobody who just bought Breath of the Wild needs ten more Zelda games. They need a controller, a screen protector, and their next few games for the console they just committed to.

Content embeddings encode what a product is. Recommendations are about what a customer does next. Those are different spaces, and text similarity can’t bridge the gap. This is the single most important thing to internalize before you reach for a vector database to do recommendations.

What actually works: counting what your customers do

First, the obvious question: where does a recommendation even come from? Not from the products — from your customers’ behavior. This approach has a name — collaborative filtering — but it’s a fancy label for a simple idea: recommend things based on what other shoppers did, not on what the product is.

And the raw material is something every store already has: the order history. In this experiment it’s one table — one row every time a customer bought (here, reviewed) a product:

CREATE TABLE interactions (   -- your orders / purchase history
  user_id     text,           -- which customer
  product_id  text,           -- which product they bought
  ts          bigint          -- when
);

“People who bought X also bought Y” is then just a counting question: across everyone’s order history, how often do X and Y turn up in the same customer’s purchases? In SQL you answer it by joining the table to itself on the customer (a “self-join”), and saving the result as a second little table of “these two products were bought together N times”:

CREATE TABLE cooc AS          -- "bought-together" counts
SELECT a.product_id AS src, b.product_id AS dst, count(*)::int AS n
FROM interactions a
JOIN interactions b USING (user_id)   -- same customer, two different products
WHERE a.product_id <> b.product_id
GROUP BY 1, 2
HAVING count(*) >= 2;         -- ignore one-off coincidences

That’s the entire model — no AI, no GPU, just counting. On this dataset it produces 749,108 “bought-together” pairs, stored right next to the catalog. (In a real store you’d add to interactions as orders come in, and rebuild this table on a schedule — nightly is plenty.) Making a recommendation is then a quick lookup: take the things a shopper already bought, add up the “bought-together” counts for every candidate product, and return the highest:

SELECT dst
FROM cooc
WHERE src = ANY($1)              -- the shopper's past purchases
  AND NOT (dst = ANY($1))        -- don't recommend what they already own
GROUP BY dst
ORDER BY sum(n) DESC             -- most-bought-together first
LIMIT 10;

No embeddings. No GPU. No model. And it scored 0.044 HitRate@10 — 2.1× the popularity baseline and 3.4× the content vectors. (Reminder: HitRate@10 just means “how often the thing they actually bought next showed up in our top 10 guesses.”) The dumbest-sounding method, expressed as ordinary SQL counting, was by far the best recommender I had.

And the output is exactly what you’d want on a product page — here it is straight from the counts table, for three real products in the catalog:

Notice that Breath of the Wild’s top “also bought” items are a screen protector, Mario Kart, and a Switch Pro Controller — accessories and different games, not the ten Zelda clones the embeddings were so proud of. The counting approach learned the customer’s actual next move for free.

The hybrid trap

Here’s the result I didn’t expect. The textbook move is to fuse signals — take the two ranked lists (collaborative filtering and content vectors) and blend them into one, hoping to get the best of both. The standard recipe for that blend is called Reciprocal Rank Fusion. So I did it:

The hybrid was worse than collaborative filtering alone. Blending a strong signal with a weak one doesn’t average their quality — it lets the weak signal pull good recommendations down the ranking. Fusion is not free. If one of your signals is genuinely bad for the task, the principled thing is to drop it, not average it in. “We combined multiple signals” is not automatically a better system.

Where vectors actually earn their place

So are the embeddings useless? No — I was just pointing them at the wrong job.

Collaborative filtering has one fatal weakness: it can only recommend items it has seen bought together with something. On this catalog, the “bought-together” table covers 19,101 of 25,611 products — just 74.6%. The other 25% have no behavioral signal at all: new arrivals, niche long-tail products, anything nobody has bought alongside something else yet. This is the classic cold-start problem — a product so new there’s no behavior to learn from — and for a quarter of the catalog, collaborative filtering returns nothing.

That’s exactly where content vectors are the only tool that works. They need no purchase history — just the product text — so they can place a brand-new item next to its semantic neighbors from day one. And the same embedding column powers genuine semantic search: a shopper typing “co-op game for my niece who likes dinosaurs” gets a sensible answer that keyword search and collaborative filtering both whiff on.

So the real architecture isn’t “use pgvector for recommendations.” It’s a layered policy, and Postgres runs all of it:

• Collaborative filtering for warm items with co-purchase history (the workhorse)
• Content vectors for cold-start and semantic queries (the safety net CF can’t provide)
• Popularity as the floor when you know nothing about the user

The thing a separate vector database can’t do

There’s one more reason to keep this in Postgres, and it’s the one that matters most in production. Recommendations aren’t useful if they point at things you can’t sell. Because the catalog, the vectors, and the live inventory are all in the same database, a recommendation can filter to in-stock items at the current price in a single transactional query:

SELECT p.id, p.title, i.price, 1 - (p.embedding <=> q.embedding) AS score
FROM products p
JOIN inventory i ON i.product_id = p.id AND i.in_stock   -- live join
, (SELECT embedding FROM products WHERE id = $1) q
WHERE p.id <> $1
ORDER BY p.embedding <=> q.embedding
LIMIT 10;

Bolt a dedicated vector database onto the side and you can’t do this without shipping inventory state into it and keeping two systems consistent. Your recommender starts confidently suggesting sold-out products. The integration tax is real, and you pay it forever.

”But surely a real vector database is faster?”

The usual objection is performance: pgvector is a cute toy, but for serious vector search you need a purpose-built engine. So I ran both on the same 25,611 vectors, same queries, and measured two things: recall (did it return the truly closest items, checked against an exhaustive brute-force search) and latency (how long a lookup takes). Latency is shown as p50 and p95 — half of all queries finish faster than the p50 number, and 95% finish faster than the p95:

Qdrant’s recall is a hair higher; pgvector’s latency is actually lower. I want to be fair about why: the pgvector query is in-process SQL over a local socket, while Qdrant goes over HTTP — some of that gap is transport, not the index. The honest read isn’t “pgvector is faster than Qdrant.” It’s that both are sub-5ms with essentially perfect recall, and the difference is noise next to the cost of running, syncing, and reasoning about a second datastore you didn’t need.

Does it scale? 25k to a million rows

The other objection is scale — fine for 25k products, but what about a real catalog? So I replicated the real embeddings with jitter up to one million vectors (synthetic copies, purely to stress the index — quality is only ever measured on the real catalog) and re-ran the latency test:

The headline: item-to-item query latency barely moved — p50 went from 0.9 ms at 25k rows to 1.1 ms at a million. That’s the whole point of HNSW: query time grows with the log of the catalog, not the size of it. The costs that do scale are build time and disk — the 1M HNSW index took ~9 minutes to build and 3.9 GB on disk — both one-time, both cheap.

And the recall/latency tradeoff is a single knob, hnsw.ef_search (left chart): dial it to 40 for 99.4% recall at 0.6 ms, or 400 for 99.95% at 2.3 ms. You tune it per query without touching the index. This is the part people miss when they assume they need a dedicated system — the database already gives you the dials.

The same idea runs Netflix, Spotify, and your feed

Nothing here is specific to selling video games. Strip a recommender down and it’s always the same two ingredients: a log of what people did, and a way to rank what’s likely next. Swap the nouns and you get much of the rest of the internet:

• Netflix and YouTube — “because you watched…” The logged action is a view instead of a purchase; “bought together” becomes “watched by the same people.”
• Spotify — Discover Weekly and “fans also like” are co-occurrence over listening history. And the cold-start trick is the same: a brand-new song nobody’s played yet gets surfaced by its audio/text features (the equivalent of our content vectors) until it has enough plays to stand on behavior.
• Social and news feeds — “people who engaged with this also engaged with that” — the same self-join, over likes and clicks.
• B2B and SaaS — “teams like yours also enabled this integration,” “customers who ordered this part also bought that one.”

The vocabulary changes — views, plays, clicks, orders — but the engine is the counting-plus-ranking you just saw, and for most companies it fits comfortably in the database they already run.

When you do need a dedicated system

I’m not claiming Postgres scales to everything. If you’re serving billions of vectors, need sub-10ms ANN at very high QPS, or want sophisticated learned ranking models, a purpose-built recommendation stack and a dedicated vector store start to earn their complexity. But that’s a problem you grow into and can measure. Most stores reaching for pgvector and a vector database are nowhere near it — and would be better served by a collaborative-filtering self-join and an HNSW index in the database they already run.

Takeaways

• A recommendation engine is retrieval + ranking, and Postgres can be the whole thing — content vectors, collaborative filtering, popularity, and a live-inventory join, in plain SQL.
• Content similarity is not behavioral similarity. Embeddings recommend ten more Zelda games; customers want a controller. Measure before you ship the obvious thing.
• Collaborative filtering, as a SQL self-join, beat content vectors 3.4× — and needed no model at all.
• Naive hybrid made it worse. Don’t average a strong signal with a weak one.
• Vectors earn their place on the 25% of the catalog with no purchase history — cold-start and semantic search, which collaborative filtering cannot serve.

The dataset is public and every number here came out of a runnable experiment, not a vibe. The general lesson is older than embeddings: reach for the measurement before the infrastructure.

Keep reading: Postgres for search and AI

Same thesis, different problems — all measured, all on one database:

• BM25 Search in PostgreSQL — the pg_textsearch ranking I used here, benchmarked against native ts_rank.
• Building a Support Copilot with RAG on Postgres — hybrid retrieval (pgvector + BM25 + RLS) without a separate vector database.
• Geospatial RAG on Postgres — fusing PostGIS proximity and pgvector similarity in a single ranked query.

I build and measure systems like this — recommendation, search, and RAG on Postgres — for companies who’d rather ship one well-understood database than operate four. If that’s the kind of problem you’re staring at, let’s talk.