The piece argues that search across normalized relational tables should not require either a fragile denormalized copy in an external search engine or painfully slow queries in PostgreSQL. Its StackOverflow example spans users, posts, and comments, with tens of millions of rows, and ranks matches by combined relevance across all three tables. Vanilla PostgreSQL takes about 118 seconds on this query because it must join and score millions of combinations before finding the top 20. A hand-written UNION ALL rewrite can reduce that to 1.2 seconds for rare terms, but takes more code and can reach 14 seconds for common ones. Denormalizing for a search engine also adds synchronization complexity, duplicated storage, and costly updates.
ParadeDB’s approach stores join keys and projected columns in columnar search indexes, then routes eligible queries through a DataFusion-based executor instead of PostgreSQL’s row-at-a-time heap processing. The executor delays fetching full rows and checking visibility until joins have reduced the candidates. For top-K search, its Rank Join reads each table’s matches in descending score order and tracks bounds on unseen combinations, stopping once they cannot beat the current winners. The original query runs in roughly 100-600 milliseconds on one core, compared with PostgreSQL’s parallel plan using up to eight workers. The system also targets grouped facet queries using tuple tagging, index co-partitioning, and late materialization, aiming to make search joins fast without sacrificing normalized schemas or transactional consistency.
Summary generated by AI from the linked article. hn.today is not affiliated with Hacker News or Y Combinator.