🚀 Optimizing Text Search in PostgreSQL (Supabase) – Looking for Suggestions
Hey everyone,
We’re working on a proof of concept where we need to search through millions of messages efficiently. Our main goal is to enable searching based on the text content itself (not semantic meaning).
We first tried using vector embeddings with Supabase’s native support, but the results weren’t ideal — embeddings returned unexpected matches like “goodbye” or “greetings” when searching for “hello.”
So we switched to Postgres pg_trgm for similarity search. It works much better for our use case, especially when combined with a date filter to narrow down results. For example, finding messages similar to "hello" within the last 30 days.
The current approach looks like this:
select id, content, similarity(content, 'hello') as score
from messages
where content % 'hello'
and created_at > now() - interval '30 days'
order by score desc
limit 50;
This gives us good accuracy, but we’re now facing performance concerns as the dataset grows (tens of millions of rows, potentially more).
Question: How do larger companies or teams usually scale this kind of workload on Postgres? Should we:
Keep tuning pg_trgm with indexes and parameters?
Consider Postgres full-text search (tsvector) as an alternative?
Or move part of the workload to a specialized search engine (like Elasticsearch, Meilisearch, etc.) while still relying on Supabase/Postgres as the source of truth?
Any advice, benchmarks, or shared experiences would be super valuable!
Thanks
We’re working on a proof of concept where we need to search through millions of messages efficiently. Our main goal is to enable searching based on the text content itself (not semantic meaning).
We first tried using vector embeddings with Supabase’s native support, but the results weren’t ideal — embeddings returned unexpected matches like “goodbye” or “greetings” when searching for “hello.”
So we switched to Postgres pg_trgm for similarity search. It works much better for our use case, especially when combined with a date filter to narrow down results. For example, finding messages similar to "hello" within the last 30 days.
The current approach looks like this:
select id, content, similarity(content, 'hello') as score
from messages
where content % 'hello'
and created_at > now() - interval '30 days'
order by score desc
limit 50;
This gives us good accuracy, but we’re now facing performance concerns as the dataset grows (tens of millions of rows, potentially more).
Question: How do larger companies or teams usually scale this kind of workload on Postgres? Should we:Keep tuning pg_trgm with indexes and parameters?
Consider Postgres full-text search (tsvector) as an alternative?
Or move part of the workload to a specialized search engine (like Elasticsearch, Meilisearch, etc.) while still relying on Supabase/Postgres as the source of truth?
Any advice, benchmarks, or shared experiences would be super valuable!
Thanks
