A client asked us to add search to their product. Everyone's instinct was to reach for a dedicated search service immediately. We used Postgres's built-in full-text search instead, and it held up for two years and roughly 4 million documents before we finally needed to move.
The setup that got us surprisingly far
ALTER TABLE documents ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX documents_search_idx ON documents USING GIN (search_vector);A generated column plus a GIN index gets you ranked, typo-tolerant-ish, full-text search with zero additional infrastructure, no data synchronization to maintain, and search results that are always exactly as fresh as the row they came from.
Where it started to strain
Query latency crept past 200ms once the table passed roughly 4 million rows with our index and hardware.
Fuzzy matching for typos required bolting on
pg_trgm, which works but is noticeably less capable than a purpose-built engine.Faceted search — filtering by category and search term together — got expensive to express and to keep fast as filter combinations multiplied.
The signal we actually used to decide it was time
Not a specific row count or latency number, but the fact that we were writing increasingly elaborate SQL to work around limitations a dedicated search engine solves natively. That complexity, more than any single metric, was the tell.
If your search needs are simple — search a handful of text fields, rank by relevance, filter by one or two fields — Postgres will very likely take you further than you expect. Move to a dedicated engine when you notice yourself fighting the tool, not before.
