Performance Characteristics Working With JSONB in CockroachDB
In this article, look at performance characteristics working with JSONB in CockroachDB.
Join the DZone community and get the full member experience.Join For Free
As I was working on my previous article on JSONB support in CockroachDB, I was interested in identifying performance characteristics for loading JSONB data. I will look at performance based on a single node instance, using the same schema and JSON as my last post. Let's dig right in.
Writes on a Table Without an Inverted Index or Computed Column
If we just take a basic table with a generated
id and a JSONB column called
payload. Running a 1000000 row load will finish in less than a minute.
These are the start and end times for my python script that creates threads and using
upsert, inserts 50 records at a time using multi-row upsert. I will use 57 seconds as a baseline.
Writes on a Table With an Inverted Index
This time, we're going to create a table schema along with an inverted index.
This time, it took almost 10 minutes to load the exact same data. We can conclude that inverted indexes are really slow on writes. We will evaluate feasibility of creating an index post-load later in this article.
Writes on a Table With Computed Columns atop JSONB
Using computed columns is significantly faster than using inverted index from the get go but still not as fast as barebones load. Given the flexibility, it may be a good middle ground working with JSONB.
Creating an Index Post-Load
Given the bulk of the data is already loaded and takes a little under one minute to load, compared to loading the table with the associated index from the beginning, we can reduce the time it takes to load and index the data using inverted index by first creating a table without an index and then creating an inverted index on it.
Run the load and then issue the following command to create an index
It took 40 seconds to populate the index
This is in line with the time it took us to load a table with computed column.
Hope this helps in your work with CockroachDB and JSONB.
Published at DZone with permission of Artem Ervits. See the original article here.
Opinions expressed by DZone contributors are their own.