7

Consider the example: Two tables foo and bar, each with a jsonb column.

  • For foo, there are a million rows where the value of the jsonb is [{"a":123}].

  • For bar, there are a million rows where the value of the jsonb is [{"very_long_key_not_premature_optimization_at_all":123}]

The json key in bar is 46 characters longer than that in foo. Would the size of bar be 46 million bytes greater than foo?

9

Yes key length matters

The bigger the key, the more space to store it. JSONB doesn't do anything special to keys.

Test Cast

Sample Data

# CREATE TABLE foo AS SELECT '{"f":true}'::jsonb FROM generate_series(1,1e6);
SELECT 1000000

# CREATE TABLE bar AS SELECT '{"very_long_key_not_premature_optimization_at_all":true}'::jsonb FROM generate_series(1,1e6);
SELECT 1000000

Table sizes

Now look at the tables

# \dt+ foo;
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description 
--------+------+-------+----------+-------+-------------
 public | foo  | table | ecarroll | 42 MB | 
(1 row)

test=# \dt+ bar;
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description 
--------+------+-------+----------+-------+-------------
 public | bar  | table | ecarroll | 89 MB | 
(1 row)

Source Code

You can see it in the source here

str = TextDatumGetCString(in_datums[i * 2 + 1]);
len = strlen(str);

v.type = jbvString;

v.val.string.len = len;

ZSON Extension

You may consider checking out ZSON extension which provides JSONB cross-row dictionary compression

ZSON is a PostgreSQL extension for transparent JSONB compression. Compression is based on a shared dictionary of strings most frequently used in specific JSONB documents (not only keys, but also values, array elements, etc).

In some cases ZSON can save half of your disk space and give you about 10% more TPS. Memory is saved as well. See docs/benchmark.md. Everything depends on your data and workload though. Don't believe any benchmarks, re-check everything on your data, configuration, hardware, workload and PostgreSQL version.

  • 1
    In light of this, does there exist any practice of minifying jsonb key names (similar to javascript minifiers)? – dvtan Jan 22 '17 at 0:40
  • 1
    Nah, they're toasted anyway so they're not inline in the table. Who cares about the space? I wouldn't suggest you index on the key. Indexing on the value makes sense sometime, but again key length shouldn't matter. The index would normally link the value to the location on disk. I don't make stupid long key names, but I don't also abbreviate them to save a few MB on disk. I created a million rows and saved 27 MB on key length. That's not worth my time to worry about. – Evan Carroll Jan 22 '17 at 0:41
  • 1
    I agree disk space is cheap, but wouldn't it also be wasting memory? And just to clarify: are all jsonb columns toasted (i.e. separately stored on disk), even if they're not >8kb? – dvtan Jan 22 '17 at 0:47
  • the value itself only has to be 2kb, afaik. the page has to be less than 8kb. (~2 block reads on a modern hd). – Evan Carroll Jan 22 '17 at 1:08
  • 1
    @DavidTan also updated with mention of ZSON. – Evan Carroll Jan 22 '17 at 1:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.