Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB
Ronny Abraham schrieb am 15.05.2017 um 19:25: 4. Insert 10,000 rows to JSON, execution time (sec): 122.855001211 5. Insert 10,000 rows to JSONB, execution time (sec): 122.128999233 What’s interesting is that inserting to JSONB is slightly faster than inserting to JSON. A difference in 0.7 seconds from a single test run is not significant enough to warrant the statement "JSONB is slightly faster". That could very well have been caused by other things going on your system. Or maybe just some caching of metadata. Unless you repeat this at least 4 or 5 times, you can't tell if one is really faster then the other. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB
On Mon, May 15, 2017 at 12:02 PM, Ronny Abraham wrote: > 4. Insert 10,000 rows to JSON, execution time (sec): > 5. Insert 10,000 rows to JSONB, execution time (sec): > > What’s interesting is that inserting to JSONB is slightly faster than > inserting to JSON. With those times, only explanation is that you are bottlenecked by storage fsync time. If storage is fast, you should be able to do 10k inserts per second+ for either type unless the documents are large. For storing documents, you should only consider using the json type if the exact structure of the originating document is important for the most part. For querying and manipulation it's better and faster (mostly) and the API is more robust. The json type has other uses; mainly for serialization from non-json data but that's not a storage consideration. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB
Thank you, I updated my test to insert 10,000 rows where each row has a JSON or JSONB with 100 keys (I do not have a use case of 1000), here are the results: 7. Insert 10,000 rows to JSON (100 items in each row), execution time (sec): 119.411994457 8. Insert 10,000 rows to JSONB (100 items in each row), execution time (sec): 118.248999119 Thanks Ronny From: Dmitry Dolgov [mailto:9erthali...@gmail.com] Sent: Monday, May 15, 2017 3:35 PM To: Ronny Abraham Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB On 15 May 2017 at 19:25, Ronny Abraham mailto:rabra...@kns.com>> wrote: > > What’s interesting is that inserting to JSONB is slightly faster than > inserting to JSON. > > Maybe that’s because my JSON has a flat structure (no nesting), or maybe I am > doing something else wrong? I assume it's because your json documents (10 fields) are not big enough. If you'll try the same tests with something like 1000 keys for each document, there should be noticeable difference (at least in my case it was like that). This email is non-binding, is subject to contract, and neither Kulicke and Soffa Industries, Inc. nor its subsidiaries (each and collectively “K&S”) shall have any obligation to you to consummate the transactions herein or to enter into any agreement, other than in accordance with the terms and conditions of a definitive agreement if and when negotiated, finalized and executed between the parties. This email and all its contents are protected by International and United States copyright laws. Any reproduction or use of all or any part of this email without the express written consent of K&S is prohibited.
Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB
On 15 May 2017 at 19:25, Ronny Abraham wrote: > > What’s interesting is that inserting to JSONB is slightly faster than inserting to JSON. > > Maybe that’s because my JSON has a flat structure (no nesting), or maybe I am doing something else wrong? I assume it's because your json documents (10 fields) are not big enough. If you'll try the same tests with something like 1000 keys for each document, there should be noticeable difference (at least in my case it was like that).
Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB
Here are the attachments. From: Ronny Abraham Sent: Monday, May 15, 2017 1:03 PM To: 'pgsql-general@postgresql.org' Subject: Insert performance and disk usage in JSON vs JSONB Hello all, I am trying to decide whether to use JSON or JSONB to store my application data. From what I read so far about JSON vs JSONB: Performance - JSON is faster for inserts since it only odes JSON format verification, vs JSONB which also converts the jSON input to a binary JSONB with its meta-data. However, queries are much faster for JSONB especially when using indexes. Disk Usage - JSONB uses more space vs JSON, I assume this is due to its meta data it stores in the binary. But, I need to take my application use cases into consideration when making the decision of JSON vs JSONB: -- My application has a data producer which sends data to a consumer which in turn inserts the data to the DB. I wanted to make sure I don't run into a consumer-producer problem where my producer generates data at a rate the consumer cannot handle. Part of that is understanding insert times in JSON and JSONB. In my application insert time is more critical than read time, since I do not have many clients on the reading side, and time is not critical. I also wanted to experiment with disk usage. So I wrote 2 small test in python one for storage and one for insert performance. In my test db I created 2 tables, each has only one column named 'data', in each I store a JSON\B with 10 fields. The results of the first test (disk usage) are attached (excel sheet) - in it you can see a 26% overhead in JSONB over JSON. The second test (insert performance) results are as following (python script attached): 1. Inserting 1 row to JSON table, result: QUERY PLAN --- Insert on table_json10 (cost=0.00..0.01 rows=1 width=32) (actual time=0.048..0.048 rows=0 loops=1) -> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1) Planning time: 0.035 ms Execution time: 0.071 ms (4 rows) 2. Inserting 1 row to JSONB table, result: QUERY PLAN Insert on table_json10b (cost=0.00..0.01 rows=1 width=32) (actual time=0.052..0.052 rows=0 loops=1) -> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1) Planning time: 0.018 ms Execution time: 0.066 ms (4 rows) 3. Multi insert (10) to JSON table, result: QUERY PLAN -- Insert on table_json10 (cost=0.00..0.12 rows=10 width=32) (actual time=0.045..0.045 rows=0 loops=1) -> Values Scan on "*VALUES*" (cost=0.00..0.12 rows=10 width=32) (actual time=0.006..0.010 rows=10 loops=1) Planning time: 0.036 ms Execution time: 0.072 ms (4 rows) 4. Multi insert (10) to JSONB table, result: QUERY PLAN -- Insert on table_json10b (cost=0.00..0.12 rows=10 width=32) (actual time=0.029..0.029 rows=0 loops=1) -> Values Scan on "*VALUES*" (cost=0.00..0.12 rows=10 width=32) (actual time=0.002..0.005 rows=10 loops=1) Planning time: 0.021 ms Execution time: 0.043 ms (4 rows) 4. Insert 10,000 rows to JSON, execution time (sec): 122.855001211 5. Insert 10,000 rows to JSONB, execution time (sec): 122.128999233 # END TEST What's interesting is that inserting to JSONB is slightly faster than inserting to JSON. Maybe that's because my JSON has a flat structure (no nesting), or maybe I am doing something else wrong? I was just interested in some input regarding insert performance and disk usage in JSON vs JSONB. Thanks! Ronny This email is non-binding, is subject to contract, and neither Kulicke and Soffa Industries, Inc. nor its subsidiaries (each and collectively "K&S") shall have any obligation to you to consummate the transactions herein or to enter into any agreement, other than in accordance with the terms and conditions of a definitive agreement if and when negotiated, finalized and executed between the parties. This email and all its contents are protected by International and United States copyright laws. Any reproduction or use of all or any part of this email without the express written consent of K&S is prohibited. DbStorage_JsonVsJsonB.xlsx Description: DbStorage_JsonVsJsonB.xlsx testInsertPerformance.py Description: testIns
