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

Gennadii Turutin; Mikita Puzevich

Authors

Gennadii Turutin City University of New York https://orcid.org/0009-0007-5236-8033
Mikita Puzevich City University of New York

Keywords:

JSONB, Indexing, PostgreSQL, document storage, Database optimization, Relational database

Abstract

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 paper, the comparative performance of regular typed-column indexes and JSONB-based indexes is evaluated across ten queries typical of a production application.

Generated metrics are used to visualize latency of indexes on JSONB expressions and typed-column indexes and for a one-sided statistical test.

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.

Author Biographies

Gennadii Turutin, City University of New York

Gennadii Turutin is Vice President of Distributed Ledger Technology at iCapital Network. He holds an M.S. in Statistics from the City University of New York (Baruch College) and has professional experience spanning sustainability, AI engineering, distributed ledger systems, and financial data infrastructure. His previous roles include Senior Data Engineer at TIFIN and Senior Software Engineer at Prescriptive Data (Nantum AI). His research interests include databases, AI, and the intersection of blockchain and finance.
Mikita Puzevich, City University of New York

Mikita Puzevich is a Financial Analytics Associate at Reach Financial, a lending FinTech company based in New York City. He holds an M.S. in Statistics from the City University of New York (Baruch College). His professional experience encompasses financial modeling, data analytics, and capital markets. At Reach Financial, he develops Python- and SQL-based tools for loan portfolio analysis, builds data pipelines, and designs Tableau dashboards. Earlier, he worked as a capital markets analyst, assisting in the issuance of several securitization deals and supported performance monitoring of securitizations and credit warehouses.

References

[1] S. Nakamoto, "Bitcoin: A Peer-to-Peer Electronic Cash System", https://bitcoin.org/bitcoin.pdf

[2] Miorandi, Sicari, De Pellegrini & Chlamtac, “Internet of things: Vision, applications and research challenges”, Computer Networks, 2012 — broad taxonomy and research agenda.

[3] Patrick Lewis, Ethan Perez, Aleksandra Piktus, Fabio Petroni, Vladimir Karpukhin, Naman Goyal, Heinrich Küttler, Mike Lewis, Wen-tau Yih, Tim Rocktäschel, Sebastian Riedel, Douwe Kiela, “Retrieval-Augmented Generation for Knowledge-Intensive NLP Tasks”, https://arxiv.org/abs/2005.11401, 2020

[4] PostgreSQL documentation, https://www.postgresql.org/docs/current/datatype-json.html

[5] Philip McMahon, Maria-Livia Chiorean, Susie Coleman, Akash Askoolum, “Bye bye Mongo, Hello Postgres”, The Guardian Engineering blog, https://theguardian.engineering/blog/info-2018-nov-30-bye-bye-mongo-hello-postgres

[6] G. Turutin “JSONB-indexing-vs-rel.-indexing” in Github, https://github.com/GennadiiTurutin/JSONB-indexing-vs-rel.-indexing

[7] PostgreSQL 17, https://www.postgresql.org/about/news/postgresql-17-released-2936/

[8] Bradley Efron, R. J. Tibshirani, "An Introduction to Bootstrap," https://www.taylorfrancis.com/books/mono/10.1201/9780429246593/introduction-bootstrap-bradley-efron-tibshirani, 1993