March 9, 2026
Index wars: Postgres vs search stans
How we optimized Top K in Postgres
Speed boost sparks a comment brawl: “Use a search engine,” “Your index is wrong,” and one tiny plot hole
TLDR: ParadeDB’s post shows Postgres can return top results fast—until filters complicate things—pushing some to suggest search tech like Lucene. Comments erupted over whether the index trick even works, with purists nitpicking wording and pragmatists yelling “use a search engine,” underscoring how tricky fast filtering really is.
A blog from ParadeDB’s Ming Ying bragged about making “Top K” queries—asking for the top results—go from 15 seconds to a zippy 5ms with a sorted index, then admitted things get messy once you add filters. Cue the comment section turning into database court. One reader, davidelettieri, immediately called a continuity foul: the post mentions a “US” filter before it’s introduced. The vibe: “Great story, but your script supervisor fell asleep.”
Then the real fight broke out. jmgimeno challenged the claim that a two-column index (think: a sorted guide by severity, then time) magically jumps to the right spot when you ask for “severity less than 3.” Translation for non-nerds: some folks say the trick works, others say the math doesn’t check out. Meanwhile, tacone went full mic-drop: Postgres stores rows in a way that makes lots of filters slow—so just use a column-focused setup like Timescale or shove it into a data warehouse. And over in the peanut gallery, bob1029 waved a big foam finger for Lucene: the search tech that “feels like magic,” especially for top results. Even Vadim_samokhin tossed in a niche tool tip (btreed+gin indexes) with a shrug: it won’t fix ordering. Verdict? The crowd is split between “index smarter,” “use a search engine,” and “change the whole storage game.”
Key Points
- •B-tree indexes in PostgreSQL enable very fast Top K queries when the index matches the sort column, reducing latency from seconds to milliseconds.
- •B-tree structure allows efficient navigation to the largest (or smallest) key and sequentially reading K entries via linked leaves.
- •Adding filters not included in the index forces PostgreSQL to either scan many index entries and filter or to sort after filtering, increasing work substantially.
- •Composite B-tree indexes can fit specific query shapes (e.g., severity then timestamp) but do not generalize across varying filters or sort orders.
- •Supporting many combinations of filters and sorts leads to a combinatorial explosion of indexes; specialized systems like Lucene, Tantivy, or ParadeDB use different approaches.