# Postgres large JSON value query performance

DevFeed: [Postgres large JSON value query performance](<https://devfeed.tech/articles/postgres-large-json-value-query-performance-20755.md>)

Original publisher: [Read original article](<https://www.evanjones.ca/postgres-large-json-performance.html>)

Published: 2022-02-01T14:19:27Z

Content type: article

Language: en

Sources: [Evan Jones](<https://devfeed.tech/sources/evan-jones.md>)

Topics: [JSON](<https://devfeed.tech/topics/json.md>), [Benchmark](<https://devfeed.tech/topics/benchmark.md>), [Database](<https://devfeed.tech/topics/database.md>), [data](<https://devfeed.tech/topics/data.md>)

Tags: [benchmark](<https://devfeed.tech/tags/benchmark.md>), [database](<https://devfeed.tech/tags/database.md>), [json](<https://devfeed.tech/tags/json.md>), [performance](<https://devfeed.tech/tags/performance.md>), [postgres](<https://devfeed.tech/tags/postgres.md>)

## AI overview

This article presents benchmark results on query performance for large JSON, JSONB, and HSTORE values in Postgres. It reports a 2-10x slowdown once rows exceed about 2 KiB, discusses the effects of compression and external TOAST storage, and suggests JSONB, HSTORE, splitting large values across rows, and LZ4 compression as relevant considerations.

## Source excerpt

Postgres supports three types for "schemaless" data: JSON (added in 9.2), JSONB (added in 9.4), and HSTORE (added in 8.2 as an extension). Unfortunately, the performance of queries of all three gets substantially slower (2-10x) for values larger than about 2 kiB, due to how Postgres stores long variable-length data (TOAST). The same performance cliff applies to any variable-length types, like TEXT and BYTEA. This article contains some quick-and-dirty benchmark results to explore how Postgres's performance changes for the "schemaless" data types when they become large. My conclusion is that you should expect a 2-10x slower queries once a row gets larger than Postgres's 2 kiB limit. Most applications should use JSONB for schemaless data. It stores parsed JSON in a binary format, so queries are efficient. Accessing JSONB values is about 2x slower than accessing a BYTEA column. Queries on HSTORE values are slightly faster (~10-20%), so if performance is critical and string key/value pairs are sufficient, it is worth considering. Never use JSON because the performance is terrible. Compressed values makes queries take about 2x more time, and queries for values stored in external TOAST tables take about 5x more time. In cases where you need excellent query performance, you may want to consider trying to split large JSON values across multiple rows. If you are using Postgres 14 or later, you should use LZ4 compression. I didn't test it, but others have found it to use a bit more space but be signficantly faster (1, 2). This should reduce the performance penalty for compressed values. This is a general database problem and not a Postgres problem: MySQL and others have their own performance cliffs for large values. However, Postgres's row length limit is pretty low. I suspect the 8 kiB page may be the wrong default these days. A rough rule of thumb is that smaller pages are better for workloads that read and write small values, but larger pages are likely better for queries t