Semantic Search on 44,000 Photos, Free-Tier Postgres

I build a photo and video platform for agencies (internal name: Skyline Atlas). Photos are enriched locally with a vision-language model, then mirrored to a Supabase Postgres that powers the web portal. Search is a fusion of full-text, a text-embedding lane (bge-m3, 1024-d) and a CLIP lane (768-d).

It worked beautifully at 15,000 photos. Then I imported my personal archive, about 28,000 more, and the search silently broke.

What it looks like

Here is the portal on a demo library of 120 stock photos. The query is embedded and ranked by meaning (alongside full-text), and the filter sidebar on the left shows the facet counts described further down.

Skyline Atlas portal searching for "lonely person in morning fog"

A vaguer, mood-based query still lands: "cozy warm light, slow evening" returns blankets, warm interiors and low sun.

Skyline Atlas portal searching for "cozy warm light, slow evening"

The failure that didn't look like one

Whole-library semantic search for admin and staff logins took 8–16 seconds. The authenticated role has an 8 s statement_timeout, so the query was killed. The portal handled that gracefully, which was the problem: it quietly showed only videos (a separate query that was still fast) and no error.

My hourly smoke test stayed green. It only ran FTS-anchored queries, as the postgres superuser. Two things were wrong with the test, and they're the same two things that bit me throughout:

• Role matters. RLS changes the query plan completely. A test as postgres tells you nothing about what a client login sees.
• Cold and warm are different databases. On a small instance, performance is "what fits in cache".

The smoke test now runs whole-library semantic search as admin with a 5 s budget.

The second problem: the 500 MB cap

I'm on Supabase's free plan, which caps the database at 500 MB. Mine was 1,018 MB. Roughly 800 MB of that was vectors plus two full-precision HNSW indexes (516 MB on their own). I was already over the cap, and any fix that added an index would make it worse.

So the work had two goals that pull against each other: make search faster and make the database less than half its size.

Database size before and after

What I changed

1. vector → halfvec

Embeddings were stored as 32-bit floats. Switching the columns to halfvec (16-bit) halves the vector storage, and for cosine ranking the precision loss is not noticeable.

2. Binary-quantized HNSW with exact re-rank

The full-precision HNSW graphs were the biggest single cost. pgvector can index a binary-quantized version of the vector (1 bit per dimension, compared with Hamming distance):

create index photos_clip_bq on photos
  using hnsw ((binary_quantize(clip_vec)::bit(768)) bit_hamming_ops);

Hamming distance only approximates cosine, so the index is used to fetch an oversampled candidate list (200–400), which is then re-ranked by exact distance on the stored halfvec:

select c.id, c.clip_vec <=> q          -- exact distance
from (
  select p.id, p.clip_vec
  from photos p
  where not p.deleted
  order by binary_quantize(p.clip_vec)::bit(768) <~> binary_quantize(q)
  limit p_n                            -- oversampled
) c;

The indexes went from about 516 MB to about 36 MB. They also stay in cache on a tiny instance, which turned out to matter more than the disk size. On my test queries the top-120 overlapped the old ranking 116–120 out of 120.

3. Don't let the planner be clever

Inside the big ranking CTE, Postgres preferred a hash semi-join plus a full sort, detoasting every visible vector (about 10 s at 15k rows). Moving the ANN lookup into a separate plpgsql function made it unable to do anything but walk the index.

The ranking function now picks a path by the size of the visible set:

How one search is answered

If the query is anchored by a keyword, or the visible set is 8,000 rows or fewer (every client gallery, every shoot filter), it ranks exactly, with full recall. Only large unanchored queries, in practice whole-library admin and staff search, go through the quantized index.

4. Index by who can see what

This was the least obvious one. With row-level security, HNSW returns rows the caller isn't allowed to see, and each one still costs a heap fetch before RLS discards it. My private archive made up most of the library and was invisible to most users, but it dominated the graph walk.

The fix was to partition the indexes by visibility: a denormalized is_private column kept in sync by triggers, one partial index for public rows, and per-client indexes for any client above 8,000 photos. RLS is still the security boundary. The partition only decides which graph gets walked.

Proving it at 500,000 photos

44,000 photos is not a scale test, so I built a lab: a local Postgres container shaped like the Supabase Micro instance (same pgvector, same schema, same RLS, same memory), filled with 500,000 synthetic photos cloned from ~5,000 real rows. A search-bench command runs each case for each role.

Latency before and after at 500k

On the real cloud database (44k photos) the same changes took admin semantic search from about 560 ms to 70 ms p50, and facets from 403 ms to 61 ms. The original 8–16 s case is now 130–400 ms.

A caveat on the lab numbers: every sample row exists about 100 times with near-identical vectors and the same caption, so top-10 overlap between versions is noisy (arbitrary order among clones). I judged quality against exact ground truth (true k-NN distance), not overlap alone. Recall was equal or better in every persona.

Things I only learned by doing it

These cost me hours, so here they are as a list:

• Drop the HNSW indexes before backfilling. With the indexes present, every non-HOT UPDATE inserts into the graph. My backfill was glacial until I dropped them, then rebuilt CONCURRENTLY.
• DROP COLUMN doesn't free space. You need VACUUM FULL, which rebuilds all indexes under an exclusive lock. 66 s without the vector indexes, 3 minutes with. Do it before building them.
• col = any(<44k-element array>) as an index-scan filter is not hashed. It cost 4.5 s. Hash-join against the base set instead.
• A cached generic plan can be much slower than a custom one. A PL/pgSQL facet function took 1.3 s because of a nested loop in the generic plan. set plan_cache_mode = force_custom_plan on the function fixed it. auto_explain isn't loadable on Supabase, so I reproduced it with PREPARE plus plan_cache_mode.
• Parallel index builds OOM on a Micro instance (dynamic shared memory). Build serially with maintenance_work_mem = 64MB.
• Mass UPDATEs bloat the heap (about +65 MB for one boolean backfill). Budget for a VACUUM FULL.
• Precompute what you can. Facet counts are now precomputed per client, with statement triggers marking them dirty and a 5-minute timer refreshing them. There's no pg_cron on the free tier, so a systemd timer does it.

What's still open

• The private archive's plain semantic search is still about 1 s warm, and can be slow cold. 33k rows don't fit in the instance's cache, so it's bound by heap reads while re-ranking candidates. Fewer candidates or a smaller oversample might fix it.
• I'm at roughly 436 MB of 500 MB after adding keyword search over metadata. That is room for about 7,000 more photos, so the free plan has maybe one more import in it. The honest answer is the $25/month Pro plan, and I'd rather make that decision on purpose than have an import make it for me.

The general lesson

On a small Postgres, you don't optimize queries so much as you optimize what has to be in memory. Smaller vectors, smaller indexes, and indexes that match who is allowed to see what all work for the same reason. Measure cold and warm, and measure as every role you have, because RLS makes them different databases.