Postgres full-text search is enough

Every publishing stack eventually grows a search dependency. For a blog, that dependency is usually unjustified: Postgres has shipped ranked full-text search since 2008, and a single GIN index will answer queries faster than a network round trip to a separate service.

The schema

The trick is keeping the vector in sync without touching application code:

alter table posts add column search_vec tsvector;

create index posts_search_idx on posts using gin(search_vec);

create or replace function posts_search_vec() returns trigger as $$
begin
  new.search_vec :=
    setweight(to_tsvector('english', coalesce(new.title,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(new.excerpt,'')), 'B') ||
    setweight(to_tsvector('english', coalesce(new.content_md,'')), 'C');
  return new;
end $$ language plpgsql;

create trigger posts_search_vec_trg
before insert or update of title, excerpt, content_md on posts
for each row execute function posts_search_vec();

Weights matter. A title hit should outrank a body hit by enough that searching for a phrase you used once in passing does not bury the post that is actually about it.

The query

websearch_to_tsquery accepts what readers expect to type — quoted phrases, or, -exclusions — and it never errors on stray punctuation:

select id, title
from posts
where status = 'published'
  and search_vec @@ websearch_to_tsquery('english', $1)
order by ts_rank(search_vec, websearch_to_tsquery('english', $1)) desc,
         published_at desc
limit 20;

Add an ilike '%term%' fallback for partial words — full-text search matches lexemes, not prefixes, so “cach” will not find “caching” on its own.

Where it stops working

Three honest limits:

• Typo tolerance. Postgres does not fuzzy-match. Use pg_trgm with similarity() if you need it, at the cost of a second index.
• Faceting and highlighting. Ranked snippets (ts_headline) work, but faceted counts across millions of rows get awkward.
• Global scale. One index per database, and index builds lock writes unless you use CREATE INDEX CONCURRENTLY.

For a blog with 10,000 posts, none of these bite. For a product catalogue with 4 million SKUs and per-facet counts, they do — and by then you have earned the complexity.

The point is not that Postgres beats a search engine forever. It is that the default answer for “search on my blog” should be the database you already have, and you should need evidence before changing it.