3 ms·
The chosen benchmark (a customer reviews) table is likely something that benefits tremendously from compressed columnar storage: 1) It has a small number of att
by etrain 11y ago
The chosen benchmark (a customer reviews) table is likely something that benefits tremendously from compressed columnar storage:
1) It has a small number of attributes which are almost always present in the records - that is, a fixed schema.
2) The most of the fields are numeric/date, or text with low cardinality (product_category, etc.) These things respond well to huffman codes and run-length encoding.
This is a use case where JSON shouldn't really ever be used, because the schema is pretty much fixed and highly regular. JSONB records essentially carry the schema definition with them per-record and in this case most of that information is duplicated - hence the blowup in its representation on disk.
While column stores are great for answering analytical queries that require scans over the whole table (like the single query example they show), they aren't as good at transactional queries (like serving webpages).
If I were citus, I'd have written the blog post using a dataset of highly irregular JSON blobs - e.g. log messages from lots of different systems or a big collection of web pages (serialized as json representations of the DOM). Maybe we'll see these "in the coming weeks."
- ozgune 11y ago(Ozgun from Citus Data) Sure, we'd be interested in running more numbers. If you have example data sets in mind, could you share them with us? For clarification, we picked this data set for several reasons. The data set was real, sizeable, publicly available, and it became highly referenced in PostgreSQL's JSON/JSONB development: http://www.pgcon.org/2014/schedule/attachments/328_9.4json.pdf http://www.pgcon.org/2014/schedule/attachments/328_9.4json.p... http://www.pgcon.org/2014/schedule/attachments/313_xml-hstore-json.pdf http://www.pgcon.org/2014/schedule/attachments/313_xml-hstor... http://blog.2ndquadrant.com/jsonb-type-performance-postgresql-9-4/ http://blog.2ndquadrant.com/jsonb-type-performance-postgresq... http://www.pgcon.org/2014/schedule/attachments/318_pgcon-2014-vodka.pdf http://www.pgcon.org/2014/schedule/attachments/318_pgcon-201...
- ddorian43 11y agoCan you explain HOW are you compressing the json data? Ex, is it just block-pgzip-compress? Or are you exploding each jsonb-field as a separate file like with normal columns ?