PostgreSQL JSONB-based vs. Typed-column Indexing: A Benchmark for Read Queries

A document storage pattern is used in a wide range of business applications, particularly in systems with strong requirements for immutability and auditability. Using the JSONB data type is a common approach to achieving document storage in PostgreSQL, which supports indexing for JSONB. However, building an efficient system for such structures remains a challenge in terms of read latency, mainly due to payload size, recheck cost and value extraction.In this dataset, the comparative performance of regular typed-column indexes and JSONB-based indexes is evaluated across ten queries typical of a production application.The findings show that JSONB-based indexes are not an optimal solution in terms of read performance, and overall typed-column indexes are expected to be at least 20% faster for tables with 1 million records or more. This benchmark is important to consider when designing an efficient data storage for documents.