lunes, 14 de enero de 2019

Postgres compression of jsonb

Postgres supports JSON data since loong time ago, but until jsonb (9.4) all solutions were somewhat limited in features.  Jsonb gives you most of what a document storage engine would give you, but...  what about performance? and space efficiency? Unfortunately I don't have real answers to those questions, but I've started doing some research, and for now, here's a nice takeaway:

First of all you should be aware of "\dt+", which gives us the size of a table and "\d+" which describes a table. The "+" suffix adds more info to the descriptions.

CREATE TABLE foo AS SELECT '{"f":true}'::jsonb FROM generate_series(1,1e6);
CREATE TABLE bar AS SELECT '{"f":true}'::text FROM generate_series(1,1e6);
\dt+ foo;
\dt+ bar;  

We see that the text field takes about half of the size of the jsonb. So if you don't need json features in a field (you're only archiving data that happens to be json), think twice when giving it the jsonb type.


Jsonb is TOAST-able, but will probably be toasted  (and compressed) when it's bigger than a page, so don't count on that if you're jsons are <4kb .="" p="">
References:


  • https://dba.stackexchange.com/questions/161864/do-long-names-for-jsonb-keys-use-more-storage
  • https://stackoverflow.com/questions/23120072/how-to-check-if-toast-is-working-on-a-particular-table-in-postgres
  • https://postgrespro.com/list/thread-id/1849114

So, don't think spacewise it comes for free. If you're concerned about space, there's a big impact on the size of the table.

No hay comentarios: