2017-05-15

PostgreSQL 9.6.2 vs CockroachDB 1.0 vs ScyllaDB 1.6.4

New kids on the block, multi-master database that recently released 1.0, here's some microbenchmark result:

N = 999

test1: postgresql
INSERT: 3.442695947s (3.45 ms/op)
UPDATE: 3.912135754s (3.92 ms/op)
SELECT: 3.408927374s (0.52 ms/op)
CPU: 2.62s      Real: 11.07s    RAM: 57 864 KB

test2: postgresql jsonb
INSERT: 3.270218052s (3.27 ms/op)
UPDATE: 3.796453051s (3.80 ms/op)
SELECT: 3.209289448s (0.49 ms/op)
CPU: 2.33s      Real: 10.57s    RAM: 58 680 KB

test3: cockroachdb
INSERT: 7.495245970s (7.50 ms/op)
UPDATE: 8.249719113s (8.26 ms/op)
SELECT: 16m8.372273781s (148.34 ms/op)
CPU: 2.50s      Real: 986.17s   RAM: 58 340 KB

test4: scylladb
INSERT: 150.117719ms (0.15 ms/op)
UPDATE: 147.339553ms (0.15 ms/op)
SELECT: 5.422713068s (0.83 ms/op)
CPU: 4.06s      Real: 7.76s     RAM: 76 764 KB

N = 9999

test2: postgresql jsonb
INSERT: 36.012436525s (3.60 ms/op)
UPDATE: 35.902222429s (3.59 ms/op)
SELECT: 44.119970723s (0.68 ms/op)
CPU: 32.30s     Real: 116.34s   RAM: 58 632 KB

test4: scylladb
INSERT: 1.518285796s (0.15 ms/op)
UPDATE: 1.542542984s (0.15 ms/op)
SELECT: 2m16.29325852s (2.09 ms/op)
CPU: 41.55s     Real: 141.34s   RAM: 76 712 KB

This is shocking for me that CockroachDB 2x-19x slower than PostgreSQL, so I file a bug report and one for scylla (slow query on larger datasets).
This benchmark performed on 64-bit ArchLinux, i7-4720HQ, 16GB RAM, 256GB SSD Samsung. You can get the source here. Note that ScyllaDB requires XFS, but I use EXT4 filesystem.

PostgreSQL
+ battletested for 20 years
+ schema-free (via JSONB, it has indexes :3)
+ triggers, joins, language extensions (eg. pl/v8, pl/go, pl/ruby, etc)
- no multimaster replication, except if you use PostgresXL or Postgres-X2

CockroachDB
+ survive Aphyr's Jepsen
+ autoscaling, autohealing, autobalancing
+ only 1 binary file (Go power ^^)
- seriously slow on every part of this benchmark
- no BLOB! (as per 2017-05-15)

ScyllaDB
+ Cassandra rewritten in C++
+ autoscaling, autohealing, autobalancing
+ blazing fast for insert and update benchmark (not sure if it's persisted to disk though)
- no secondary index and serial/auto-increment (as per 2017-05-15)
- only support Ubuntu, Debian, RHEL (a bit challenging to compile on another OS because it's depends on old thrift and boost library)
- communication by default without authentication, this is bad if you don't have any private network (eg. host it on a public cloud), you must enable internode-ecnryption and put a firewall to allow only certain host access exposed ports.