Tuning Postgres Full-Text Ranking on a 400k-Story Table

Last quarter our aggregation table hit 412k stories, 38GB on a single Postgres 15 instance. Search worked but ranked badly: a story from 2019 with the keyword in the title beat yesterday’s story that merely mentioned it once in the body. I spent two days on ranking and the fix wasn’t the one I expected.

The setup: one stories table, tsvector column generated from title and body with different weights, GIN index, and a query that used ts_rank. The first thing I measured was the score distribution, because I had no idea if the problem was the weights or the decay.

SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY ts_rank(fts, q)) AS p50,
  percentile_cont(0.9) WITHIN GROUP (ORDER BY ts_rank(fts, q)) AS p90,
  max(ts_rank(fts, q)) AS max
FROM stories, websearch_to_tsquery('english', 'rate limits') q
WHERE fts @@ q;

p50 was 0.0603, max 0.0991. The whole useful range was a narrow band, and title hits were dominating so hard that freshness was invisible. ts_rank doesn’t know about time; ts_rank_cd doesn’t either. So I stopped using a single rank and combined three normalized signals in the SELECT, keeping the @@ filter on the indexed column so the GIN index still does the heavy lifting:

SELECT id, title,
  (0.55 * ts_rank_cd(fts, q)
   + 0.30 * exp(-extract(epoch FROM now() - published_at) / 259200.0)
   + 0.15 * least(log(2 + comment_count) / 5.0, 1.0)) AS score
FROM stories, websearch_to_tsquery('english', $1) q
WHERE fts @@ q AND published_at > now() - interval '90 days'
ORDER BY score DESC
LIMIT 30;

The 259200 is three days in seconds; 90-day cutoff is a product decision, not a technical one, and it matters more than the weights. Sorting happens after the index scan, so this is only cheap because the candidate set is small. I checked with EXPLAIN: 2,400 candidates, 14ms. On a broad query like the it was 90k candidates and 1.8s, which is why the date filter is load-bearing.

Weights I tuned by hand against about 40 queries I actually type, not a benchmark set. Title weight A, body B in the tsvector config; comment count is a weak signal but it beats nothing, and I capped it because one 600-comment thread shouldn’t own the front page.

Where this breaks: stories with no comments get 0.15 * 0.43-ish, so brand-new submissions start slightly behind anything with discussion. That’s the tradeoff I chose, and I’m not sure it’s right for us. Also exp() on published_at defeats any attempt to precompute scores, so if the candidate set ever grows past ~50k per query this needs a materialized score refreshed on a schedule instead. I tried that first and it was worse: the refresh job and the ingestion job fought over the same rows, and I couldn’t explain a stale front page to myself at 2am.

One more gotcha: websearch_to_tsquery handles user input better than to_tsquery, but it silently drops stopwords, so a search for to be or not to be returns nothing and looks like a bug.

If you’ve done freshness decay in SQL, what half-life did you land on, and did you normalize comment counts against the story’s age or leave them raw?