![]() Today it is thus possible to store and process data in JSON format with high performance while enjoying the robustness and flexibility that PostgreSQL has habitually provided us with over the years. Using jsonb_path_ops operator class, PostgreSQL combines the elasticity of the JSON format at an amazing data access speed. With the introduction of the JSONB type and the GIN indexes ![]() Both MySQL and PostgreSQL support JavaScript Object Notation (JSON) to store and transport data, although PostgreSQL also supports JSONB, the binary version of JSON which eliminates duplication of keys and extraneous whitespace. This allows the above query to be run in just 0.167ms, with a performance increase of 650% compared to the original GIN index and 8% compared to the specific B-tree index initially used: all this without loss of generality with regards to the possible search operation. The database compiles the query and figures out the correct data points. The resulting index occupies 46MB – only 22% of the size of the original data – and, thanks to its smaller size, it is used more efficiently by PostgreSQL. The syntax for creating this type of “optimised” index is as follows:ĬREATE INDEX on reviews USING GIN (review jsonb_path_ops ) In this case it is possible to use a different GIN index which only supports the operator and is therefore considerably smaller. In fact, it is often the case that the only operation used in the applications is the search for a subset. The query takes approximately 1.100ms to be performed on the test machine, and the index that has been created is flexible and can be used for any search within the JSON data. The file, once unzipped, takes up 209MB and contains approximately 600k of records in JSON format with a structure similar to the following: ' GROUP BY 1 ORDER BY 2 The file customer_reviews_nested_ can be downloaded from the website of Citus Data. look at how to get better performance out of jsonb data types in PostgreSQL in. We will run our tests using the customer reviews data from Amazon for the year 1998 in JSON format. Storing Json data in postgreSQL gives us many advantage, but querying the. ![]() It is useful to perform a number of tests to verify its actual performance. The 9.4 version of PostgreSQL introduces the JSONB data type, a specialised representation of the JSON data, allowing PostgreSQL to be competitive in managing the “lingua franca” of the moment for the exchange of data via web services.
0 Comments
Leave a Reply. |