Solving Postgres' Count Problem

The piece explains how ParadeDB made PostgreSQL full-text COUNT queries faster than its earlier results, reporting 5,353 queries per second versus TIN’s 2,646 on a Wikipedia benchmark - about twice the throughput. The first optimization partitions immutable index segments by PostgreSQL row location, allowing entire segments to skip visibility checks when their table pages are marked all-visible; this cut visibility-related page accesses from 1,382 to 30. A reversed document-location map then narrows checks to documents on pages that may have changed, reducing buffer accesses from more than 7,500 to under 400 after 1,000 row updates. Finally, dense posting lists for common terms use bitmaps rather than compressed posting arrays, cutting p99 latency in half. Bitmaps help when many documents match, while compressed arrays remain better for rare terms.

The broader argument is that search performance depends on query shape, not just BM25 ranking or unfiltered counts. In Hacker News tests, ParadeDB handled filtered counts 10 times faster than TIN and filtered top-10 relevance searches 11.6 times faster; searches ordered by recency were 21.7 times faster. Across the tested filters and sort orders, the reported advantage reached 66 times. TIN is optimized for BM25 top-k retrieval and counts, while ParadeDB targets a wider range of search workloads inside PostgreSQL. The results emphasize that benchmarks should reflect the filters and ordering an application actually uses, rather than treating one search query as representative of all performance.