My database was too small for my search

Because I liked asks what you loved and why, then finds films, shows, books and games that match the why. Under the hood it's OpenAI embeddings in Postgres with pgvector, on Supabase's paid instance. (Part 1 is how it works, part 2 is how I measure it. This one stands on its own.)

For a week, real searches kept failing with "Search is having trouble right now." This is how I found out why, the three fixes that didn't work, and the one that did, at no cost and with no loss in quality.

🐢 The symptom

The catalog has 13,826 titles, each with a 1,536-dimension embedding. A search is one SQL function call that finds the nearest vectors, optionally filtered ("films only", "books only").

Same four searches, run back to back against production:

Search	Run 1	Run 2	Run 3	Run 4
Films only	3.7 s	2.2 s	1.4 s	5.6 s
Books only	1.2 s	1.1 s	4.9 s	1.5 s
All media	2.3 s	0.6 s	0.4 s	1.2 s

Not consistently slow. Erratic. And the anonymous role's statement timeout is about 8 seconds, so the bad runs became errors.

❌ Fix 1: rebuild the index

The HNSW index was 192 MB. The instance's shared_buffers (Postgres's own cache) is 224 MB. After I deleted a batch of experimental vectors, the index was carrying a lot of dead weight, so:

reindex index concurrently item_embeddings_vector_idx;

192 MB → 108 MB. Searches were still slow.

That's when I re-read my own search function and remembered something: filtered searches don't use the index at all. Months ago, a filtered HNSW search ("books only" when the nearest neighbours are all films) walked thousands of rejects and timed out, so filtered calls switched to an exact scan:

if match_medium is not null then
  perform set_config('enable_indexscan', 'off', true);
end if;

At 14k rows an exact scan is a few million multiply-adds, which is nothing. The CPU wasn't the problem. Reading the rows was.

❌ Fix 2: move the vectors out of TOAST

A vector(1536) is about 6 KB. Postgres moves any value over about 2 KB out of the row into a side table (TOAST), so every row of my exact scan was a separate lookup into that side table. And it was bloated:

Relation	Size
item_embeddings (the rows)	1.9 MB
its TOAST table	171 MB

171 MB for about 85 MB of live vectors, because deleted rows leave space behind that autovacuum marks reusable but never gives back.

A 6 KB vector still fits in an 8 KB page, so you can tell Postgres to keep it inline:

alter table item_embeddings alter column embedding set storage plain;
vacuum full item_embeddings;  -- rewrites the table so existing rows move

A Supabase gotcha: run those as two separate executions. The SQL editor wraps a multi-statement run in a transaction, and you get VACUUM cannot run inside a transaction block.

Result: rows 108 MB, TOAST 0 bytes. A clean sequential scan. Searches: still erratic. Films-only timed out twice in a row.

❌ Fix 3: keep it warm

If the data falls out of cache, read it back in on a schedule: pg_prewarm every five minutes from pg_cron.

This made things worse, or at best did nothing. Here's the measurement that explained everything:

Read	Time
Fetch 1 row	75–230 ms
Walk 9,000 vector rows, first time	3.6 s
Same walk, immediately after	0.14 s

The database is fast when the data is in memory, and the data doesn't stay in memory. Search needs 108 MB of rows plus a 108 MB index, about 216 MB, on a machine where Postgres, the auth service, the API layer and the OS all share a small amount of RAM. Pages get evicted between searches. Prewarming a working set that doesn't fit just re-reads it from a throttled disk every five minutes, competing with the searches it was meant to help. I turned it off.

The honest fix is a bigger machine. I wanted to try the free one first: a smaller working set.

🧪 Two ways to shrink a vector

Fewer dimensions. text-embedding-3-small is trained so the leading dimensions carry most of the meaning; OpenAI's dimensions parameter is literally truncation. And cosine distance ignores length, so you don't even need to re-normalise. Keep the first 768 at half precision: ~1.5 KB per vector, ~50 MB all in.

Lower precision. pgvector's halfvec stores each number in 2 bytes instead of 4. Same 1,536 dimensions: ~3 KB per vector, ~110 MB all in.

Neither needs a single new API call. Both are a cast in SQL:

subvector(embedding, 1, 768)::halfvec(768)   -- fewer dimensions
embedding::halfvec(1536)                     -- lower precision

I didn't want to guess which one loses quality, so I ran my eval set on both. It has 28 real-style searches, each with titles that should come back, scored by recall@12 against the same pipeline the site runs.

Variant	Size	Mean recall@12	vs full vectors
Full (vector(1536))	216 MB	38%
768 dims, half precision	~50 MB	35%	4 up, 5 down
1536 dims, half precision	~110 MB	38%	0 up, 0 down, 28 flat

The 768 version looked fine on average and wasn't fine when I read the cases. "A post-apocalyptic story game" lost both Last of Us games. "Political fantasy with dragons" went from 75% to 25%, dropping House of the Dragon and The Witcher. Those aren't reshuffles; they're the answers.

Half precision changed nothing on all 28 cases. Same hits, same ranks.

🔧 Shipping it without a migration

I didn't want to touch the table the embedding scripts write to. So the half-precision copy is a separate table, kept in sync by a trigger:

create table item_vectors (
  item_id           uuid not null references items(id) on delete cascade,
  embedding_version text not null,
  embedding         halfvec(1536) not null,
  primary key (item_id, embedding_version)
);
-- 3 KB is still over the TOAST threshold: inline from the start
alter table item_vectors alter column embedding set storage plain;

create trigger item_embeddings_sync_vector
  after insert or update of embedding or delete on item_embeddings
  for each row execute function sync_item_vector();

The search function is a copy of the old one with the same signature, reading the new table and casting the query vector the same way:

declare
  q halfvec(1536) := query_embedding::halfvec(1536);

The app picks the function by name, with an environment variable override, so rolling back is one setting in Vercel, not a deploy:

export const MATCH_ITEMS_FN = process.env.MATCH_ITEMS_FN || 'match_items_short'

The offline job that precomputes "More like this" lists still uses the full vectors. It runs at night and can afford the bytes.

📉 Result

Search	Before (warm)	After, cold	After, warm
Films only	1.4–5.6 s, some timeouts	5.3 s	0.3–0.5 s
Books only	1.0–4.9 s	0.9 s	0.1–0.4 s
TV only	1.0–4.8 s	1.8 s	0.1–0.2 s

Cost: $0, plus a few cents of eval runs. The first search after a long quiet spell is still slow, which is what a small machine does. But it's under the timeout now, and the retry path covers the rest.

📎 While I was in there: Vercel's free tier

The same week, Vercel's dashboard said I'd gone past two Hobby-plan limits. The one that mattered was ISR writes: 218K of 200K. Every regeneration of a cached page counts, and I had:

• 14,000 title pages regenerating daily, and crawlers visiting all of them;
• a deploy after nearly every commit, each one throwing the cached pages away;
• a "where to watch" lookup cached per title per day, called for every pick in every search.

Fixes: title pages regenerate weekly (they only change when "More like this" is recomputed), the watch-provider cache went to three days, AI training crawlers are disallowed in robots.txt (search crawlers stay allowed: they send people), and I now deploy from a staging branch in batches.

🔍 And: the slow users weren't the ones I tested for

Lighthouse simulates a mid-range phone, and put the home page's LCP at 3.4 s. Real phones agreed: PostHog's Web Vitals said 3.4 s at p75. Real desktops said 10.3 s, mostly Chrome on Windows. I'd never have looked there. The sample is small (about 170 page loads), but a blank page for that long is a lost visitor, whatever the sample size.

Two things were in the way:

1. The landing page waited for a database read before sending anything. It's rendered per request, and it fetched the curated poster shelf (the same for everyone) before streaming a byte. On a slow-database day, the heading and the search box waited too. Now the shelf is cached for a day and streamed behind its own Suspense boundary.
2. Too much JavaScript before the page could settle. PostHog (about 100 KB) loaded with the page; now it loads when the browser goes idle, and events sent before that are queued, not dropped. supabase-js (about 60 KB) loaded on every title page just to ask "is anyone signed in?". Signed-out visitors have no session cookie, so now they skip the download entirely.

Initial JavaScript, uncompressed: title page 1,381 → 850 KB, home 1,623 → 1,314 KB. I'll report the real-user numbers when there's a week of them, not before.

🧭 What I'd tell myself a month ago

• Measure the read, not the query. "1 row in 100 ms, 9,000 rows in 3.6 s cold and 0.14 s warm" told me more than any EXPLAIN did.
• Prewarming only helps if the working set fits. If it doesn't, it's a cron job that thrashes the cache on a schedule.
• Shrinking embeddings is a quality decision, so treat it like one. Truncating dimensions looked harmless on the mean and dropped the right answer on two searches. Half precision didn't move a single case. I'd never have known without an eval set.
• Lab scores aren't your users. Lighthouse matched my phone visitors exactly and said nothing about the desktop ones, who were three times slower.

🙏 Try it

Free, no sign-up to search, no ads: becauseiliked.com

Try "a post-apocalyptic story game where the relationship matters more than the combat", the search the 768-dimension version got wrong. If you run pgvector on a small instance, I'd love to hear what your working set looks like, and whether halfvec held up on your eval.