PostgreSQL – JSON Field Size Limit Before or After Compression

jsonpostgresql

The size limit of a json field is 1GB (source: this StackOverflow answer):

json is the same as a text datatype but with JSON validation. The text datatype's maximum size is 1GB.

I ran some experiments with inserting json of varying sizes into a table in Postgres.
Taking pg_sizeof of rows here seems to indicate that Postgres does compression of the json data.

My experiment:

create table json_size (n integer, j json);

For n with values of 1, 10, 100, 1000, 10 000, 100 000, 1 000 000.

with q as (select generate_series(1, n)::bigint as x)               
insert into json_size (n, j)
select 1, coalesce(json_agg(to_json(q.*)), '[]'::json) from q;

select n, pg_column_size(j) as size from json_size;

Results

n       size
1       10
10      92
100     996
1000    3224
10000   33488
100000  335115
1000000 3351365

Is the size limit of 1GB before or after this compression is done?

Best Answer

The limit of 1GB on varlena data types like json is before compression.

Note that you will need a lot of RAM to read and write such values. It is usually a problem and bad design to store such large values in a database.

Related Solutions

PostgreSQL to Redshift – Diagnosing Migration Error Through psycopg2

This is because you are querying a postgresql replica that doesn't allow queries to run for very long. Your only solution is to ask the database administrator to increase the query limit on that replica, or hit production instead.

PostgreSQL – How to Extract Values from JSON Array Field

You didn't provide any code, so I will create a sample table with one row and some queries to extract the information you need.

CREATE TABLE test (myfield jsonb);

Load data:

INSERT INTO test (myfield) VALUES ('
[
  {
    "text": "JIMIN",
    "indices": [
      16,
      22
    ]
  },
  {
    "text": "PUMA",
    "indices": [
      23,
      28
    ]
  }
]
'::jsonb);

To extract a specific item, let's say the text field of the second array item:

SELECT myfield#>>'{1,text}' AS text FROM test;

This will output:

  text 
 ------
  PUMA
 (1 row)

In order to extract all the text fields inside the array, a possible solution is:

SELECT (jsonb_array_elements(myfield)::jsonb)->'text' AS text FROM test;

The output is:

  text   
---------
 "JIMIN"
 "PUMA"
(2 rows)

Obviously this works with many records: each array item will be ported to one row for each array. This query is compatible with PostgreSQL 9.5. Look at the official docs to create a more efficient or versatile query ( https://www.postgresql.org/docs/9.5/static/functions-json.html ).

Best Answer

Related Solutions

PostgreSQL to Redshift – Diagnosing Migration Error Through psycopg2

PostgreSQL – How to Extract Values from JSON Array Field

Related Question