Search

Top 60 Oracle Blogs

Recent comments

Ingest format performance with UUID using CockroachDB

Recently, I have been working with customers that have been concerned about the performance of various UUID formats. Other products have various performance characteristics for inserting, generating and presenting UUID data.

For this blog, I ran a quick series of tests using jmeter insert data along with some simple SQL tests to generate UUID values. Hopefully, this will be helpful to better your understanding of UUID with CockroachDB.

UUID formats

Cockroach DB has four different ways data can be formatted for use with the UUID data type.

String format
'63616665-6630-3064-6465-616462656564'

Curly Brace format
'{63616665-6630-3064-6465-616462656564}'

Urn format:
'urn:uuid:63616665-6630-3064-6465-616462656564'

Byte format
b'kafef00ddeadbeed

Test Configuration

I created a test database as well as four separate tables to house the UUID values as follows:

create database uuid;

use uuid;

create table vstr ( token uuid );
create table vcurl ( token uuid );
create table vurn ( token uuid );
create table vbytes ( token uuid );

Once these tables were created, the following SQL statements can be run to insert values in the various formats.

-- Four ways to insert values
--
insert into vstr values ('63616665-6630-3064-6465-616462656564');
insert into vcurl values ('{63616665-6630-3064-6465-616462656564}');
insert into vurn values('urn:uuid:63616665-6630-3064-6465-616462656564');
insert into vbytes values (b'kafef00ddeadbeed');

Regardless of the input format, it is stored in CockroachDB using just one UUID format.

Insert Test Results

A simple Jmeter script to use the JDBC connector was created to insert into the four different tables. This test was achieved by running the [UUID_tests.jmx]() file for two minutes. A write-up and the scripts are available on my github repository.

At the completion of these tests, the following values were inserted into the four tables:

select (select count(*) as vstr_cnt from vstr),
       (select count(*) as vcurl_cnt from vcurl),
       (select count(*) as vurn_cnt from vurn),
       (select count(*) as vbytes_cnt from vbytes)
;

  vstr_cnt | vcurl_cnt | vurn_cnt | vbytes_cnt
-----------+-----------+----------+-------------
     12075 |     12009 |    11985 |      12021

The "response time" graph of all various insert methods, is shown below as well:

UUID generation functions

Cockroach can generate UUIDs via 3 different functions:

  • experimental_uuid_v4()
  • gen_random_uuid()
  • uuid_v4()

I ran a quick test to generate 1,000,000 UUID values with each function:

root@:26257/uuid> select count(*) from (select experimental_uuid_v4() from generate_series(1,1000000));
   count
-----------
  1000000
(1 row)

Time: 103.726ms

root@:26257/uuid> select count(*) from (select uuid_v4() from generate_series(1,1000000));
   count
-----------
  1000000
(1 row)

Time: 104.963ms

root@:26257/uuid> select count(*) from (select gen_random_uuid() from generate_series(1,1000000));
   count
-----------
  1000000
(1 row)

Time: 103.432ms

Conclusion

CockroachDB uses the UUID datatype user to store UUID values internally. While there are multiple different input formats and generation functions, there appears to be no discernible difference in performance using the various methods.