hn.today

Do We Still Have to Denormalize? Have Your Schema and Search It Too

paradedb.com3 points0 comments
Screenshot of Do We Still Have to Denormalize? Have Your Schema and Search It Too

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.

Read on paradedb.com0 comments on Hacker News

Summary generated by AI from the linked article. hn.today is not affiliated with Hacker News or Y Combinator.

More in Programming

Aristotle Expands into Rust

Aristotle has expanded its formal verification platform to Rust, allowing developers to prove code meets specifications and contains no undefined behavior, including in unsafe blocks. It can also verify that Rust rewrites behave like their original C code, targeting critical software such as smart contracts and infrastructure. (harmonic.fun)

The daily digest

Today's best Hacker News stories, summarized and screenshotted, one email a day.