performances vs PgSQL

[Fale]

I've written the following code snippet:

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/lib/pq"
)

const (
	iterations = 500
)

type conn struct {
	db   *sql.DB
	name string
}

func main() {
	p := connection("host=127.0.0.1 port=5432 user=fale password=fale sslmode=disable", "PgSQL")
	defer p.db.Close()
	c := connection("host=127.0.0.1 port=26257 user=root sslmode=disable", "CockroachDB")
	defer c.db.Close()

	// Schemas and DB
	p.schema()
	c.database()
	// Table
	p.table()
	c.table()
	// Insert
	p.inserts()
	c.inserts()
	// Select
	p.selects()
	c.selects()
	// Deletes
	p.deletes()
	c.deletes()
}

func connection(dbinfo string, name string) conn {
	var err error
	c := conn{name: name}
	c.db, err = sql.Open("postgres", dbinfo)
	if err != nil {
		log.Fatalln(err)
	}
	return c
}

func (c *conn) schema() {
	log.Printf("# Ensure schema is present - %s", c.name)
	_, err := c.db.Exec("CREATE SCHEMA IF NOT EXISTS test")
	if err != nil {
		log.Fatalln(err)
	}
}

func (c *conn) database() {
	log.Printf("# Ensure database is present - %s", c.name)
	_, err := c.db.Exec("CREATE DATABASE IF NOT EXISTS test")
	if err != nil {
		log.Fatalln(err)
	}
}

func (c *conn) table() {
	log.Printf("# Ensure table is present and empty - %s", c.name)
	_, err := c.db.Exec("CREATE TABLE IF NOT EXISTS test.accounts (id INT PRIMARY KEY, balance DECIMAL);")
	if err != nil {
		log.Fatalln(err)
	}
	_, err = c.db.Exec("TRUNCATE test.accounts;")
	if err != nil {
		log.Fatalln(err)
	}
}

func (c *conn) inserts() {
	exec(c, "inserts", "INSERT INTO test.accounts VALUES(%[1]d,%[1]d);")
}

func (c *conn) selects() {
	exec(c, "selects", "SELECT * FROM test.accounts WHERE id = %d;")
}

func (c *conn) deletes() {
	exec(c, "deletes", "DELETE FROM test.accounts WHERE id = %d;")
}

func exec(c *conn, name string, query string) {
	start := time.Now()

	var i int64
	for i = 0; i < iterations; i++ {
		stmt := fmt.Sprintf(query, i)
		_, err := c.db.Exec(stmt)
		if err != nil {
			panic(err)
		}
	}
	elapsed := time.Since(start)
	log.Printf("%d %s in %s took %s, with an average of %dns/op", i, name, c.name, elapsed, int64(elapsed/time.Nanosecond)/i)
}

I installed PgSQL from my distro (Fedora 27).

$ psql --version
psql (PostgreSQL) 9.6.6

I installed cockroach following https://www.cockroachlabs.com/docs/stable/start-a-local-cluster.html (v1.1.5) and I tried to perform some benchmarks.

2018/02/25 09:25:40 # Ensure schema is present - PgSQL
2018/02/25 09:25:40 # Ensure database is present - CockroachDB
2018/02/25 09:25:40 # Ensure table is present and empty - PgSQL
2018/02/25 09:25:40 # Ensure table is present and empty - CockroachDB
2018/02/25 09:25:42 1000 inserts in PgSQL took 1.287776052s, with an average of 1287776ns/op
2018/02/25 09:25:48 1000 inserts in CockroachDB took 5.860344296s, with an average of 5860344ns/op
2018/02/25 09:25:48 1000 selects in PgSQL took 81.824155ms, with an average of 81824ns/op
2018/02/25 09:25:49 1000 selects in CockroachDB took 933.020106ms, with an average of 933020ns/op
2018/02/25 09:25:50 1000 deletes in PgSQL took 1.325356857s, with an average of 1325356ns/op
2018/02/25 09:25:56 1000 deletes in CockroachDB took 5.810851181s, with an average of 5810851ns/op
fale@x250:~/go/src/github.com/fale/pg-benchmark $ go build && ./pg-benchmark 
2018/02/25 09:30:08 # Ensure schema is present - PgSQL
2018/02/25 09:30:08 # Ensure database is present - CockroachDB
2018/02/25 09:30:08 # Ensure table is present and empty - PgSQL
2018/02/25 09:30:08 # Ensure table is present and empty - CockroachDB
2018/02/25 09:30:09 1000 inserts in PgSQL took 1.31913866s, with an average of 1319138ns/op
2018/02/25 09:30:15 1000 inserts in CockroachDB took 5.720786054s, with an average of 5720786ns/op
2018/02/25 09:30:15 1000 selects in PgSQL took 78.829973ms, with an average of 78829ns/op
2018/02/25 09:30:16 1000 selects in CockroachDB took 878.112876ms, with an average of 878112ns/op
2018/02/25 09:30:18 1000 delete in PgSQL took 2.113067103s, with an average of 2113067ns/op
2018/02/25 09:30:23 1000 delete in CockroachDB took 5.10793473s, with an average of 5107934ns/op
fale@x250:~/go/src/github.com/fale/pg-benchmark $ go build && ./pg-benchmark 
2018/02/25 09:36:20 # Ensure schema is present - PgSQL
2018/02/25 09:36:20 # Ensure database is present - CockroachDB
2018/02/25 09:36:20 # Ensure table is present and empty - PgSQL
2018/02/25 09:36:20 # Ensure table is present and empty - CockroachDB
2018/02/25 09:36:21 500 inserts in PgSQL took 645.701328ms, with an average of 1291402ns/op
2018/02/25 09:36:24 500 inserts in CockroachDB took 3.277119748s, with an average of 6554239ns/op
2018/02/25 09:36:25 500 selects in PgSQL took 42.114179ms, with an average of 84228ns/op
2018/02/25 09:36:25 500 selects in CockroachDB took 422.683737ms, with an average of 845367ns/op
2018/02/25 09:36:26 500 deletes in PgSQL took 650.923468ms, with an average of 1301846ns/op
2018/02/25 09:36:28 500 deletes in CockroachDB took 2.545088971s, with an average of 5090177ns/op

I then tried to change the cockroach execution by adding --cache=25% --max-sql-memory=25% to every instance but the results are better but still very far from pgsql ones:

fale@x250:~/go/src/github.com/fale/pg-benchmark $ go build && ./pg-benchmark 
2018/02/25 09:47:22 # Ensure schema is present - PgSQL
2018/02/25 09:47:22 # Ensure database is present - CockroachDB
2018/02/25 09:47:22 # Ensure table is present and empty - PgSQL
2018/02/25 09:47:22 # Ensure table is present and empty - CockroachDB
2018/02/25 09:47:50 500 inserts in PgSQL took 750.454098ms, with an average of 1500908ns/op
2018/02/25 09:47:53 500 inserts in CockroachDB took 2.250529374s, with an average of 4501058ns/op
2018/02/25 09:47:53 500 selects in PgSQL took 61.181155ms, with an average of 122362ns/op
2018/02/25 09:47:53 500 selects in CockroachDB took 190.542076ms, with an average of 381084ns/op
2018/02/25 09:47:54 500 deletes in PgSQL took 686.743754ms, with an average of 1373487ns/op
2018/02/25 09:47:57 500 deletes in CockroachDB took 3.095964455s, with an average of 6191928ns/op
fale@x250:~/go/src/github.com/fale/pg-benchmark $ go build && ./pg-benchmark 
2018/02/25 09:48:16 # Ensure schema is present - PgSQL
2018/02/25 09:48:16 # Ensure database is present - CockroachDB
2018/02/25 09:48:16 # Ensure table is present and empty - PgSQL
2018/02/25 09:48:16 # Ensure table is present and empty - CockroachDB
2018/02/25 09:48:17 500 inserts in PgSQL took 650.64161ms, with an average of 1301283ns/op
2018/02/25 09:48:20 500 inserts in CockroachDB took 3.058602056s, with an average of 6117204ns/op
2018/02/25 09:48:20 500 selects in PgSQL took 42.282658ms, with an average of 84565ns/op
2018/02/25 09:48:21 500 selects in CockroachDB took 175.840808ms, with an average of 351681ns/op
2018/02/25 09:48:21 500 deletes in PgSQL took 662.712318ms, with an average of 1325424ns/op
2018/02/25 09:48:23 500 deletes in CockroachDB took 2.2847881s, with an average of 4569576ns/op
fale@x250:~/go/src/github.com/fale/pg-benchmark $ go build && ./pg-benchmark 
2018/02/25 09:48:39 # Ensure schema is present - PgSQL
2018/02/25 09:48:39 # Ensure database is present - CockroachDB
2018/02/25 09:48:39 # Ensure table is present and empty - PgSQL
2018/02/25 09:48:39 # Ensure table is present and empty - CockroachDB
2018/02/25 09:48:40 500 inserts in PgSQL took 662.140623ms, with an average of 1324281ns/op
2018/02/25 09:48:43 500 inserts in CockroachDB took 3.056347724s, with an average of 6112695ns/op
2018/02/25 09:48:43 500 selects in PgSQL took 48.732729ms, with an average of 97465ns/op
2018/02/25 09:48:43 500 selects in CockroachDB took 185.466359ms, with an average of 370932ns/op
2018/02/25 09:48:44 500 deletes in PgSQL took 655.312292ms, with an average of 1310624ns/op
2018/02/25 09:48:46 500 deletes in CockroachDB took 2.282639324s, with an average of 4565278ns/op

Surely my test is not a perfect match with a real workload (I write first, read second, delete third without mixed ops, for instance), also I would expect some kind of performance differences (since cockroach is distributed/multinode) mainly on inserts/deletes, but I was not expecting such performance gap. I wonder if I did something that makes the test "wrong" or if this performance gap is known and is probably the same I could expect on a real workload.

[knz]

Hi @Fale!

Thanks for your interest in CockroachDB.
With version 1.1 your numbers are not completely surprising as that release was not focused on performance. Several of the limitations you observe are being lifted in the upcoming 2.0 release and the one after that (2.1).

Meanwhile, you are doing two 'something that make the test "wrong"':

• running a performance test with 3 cockroach nodes running on the same computer is nonsensical. You're essentially dividing the performance of your computer by 3, plus adding overhead of process scheduling between the instances.
• comparing a single node postgres cluster with a 3-node cockroach cluster. to make a fair comparison you need to use either a 3-node postgres cluster or a single node cockroach

Finally, note the following:

• you are running all the queries over a single client connection. This is not the mode of operation that cockroachdb is optimized for. Instead we recommend benchmark tests that issue queries over many client connections simultaneously. (ideally, simulatenously over multiple nodes in a cluster, spread over multiple servers. Again to be fair, relative to the point above, to be fair you'd compare that against the same with postgres)
• your insert statements use identifiers for the primary key that are very close to each other. This is bound to cause some moderate contention on the range holding the table's data. Again CockroachDB is not well optimized for this case. But it's also not a very realistic workload -- in a real-world app we'd recommend the primary keys to be more randomly distributed (e.g. using our SERIAL type), which would yield better write distribution and less contention.

[Fale]

Thanks :).

I've done more benchmarks, using version 2.0-alpha.20180212:

The first one still with the three nodes on the same machine:

fale@x250:~/go/src/github.com/fale/pg-benchmark $ ./pg-benchmark 
2018/02/25 18:59:42 # Ensure schema is present - PgSQL
2018/02/25 18:59:42 # Ensure database is present - CockroachDB
2018/02/25 18:59:42 # Ensure table is present and empty - PgSQL
2018/02/25 18:59:42 # Ensure table is present and empty - CockroachDB
2018/02/25 18:59:43 500 inserts in PgSQL took 665.159742ms, with an average of 1330319ns/op
2018/02/25 18:59:45 500 inserts in CockroachDB took 2.444064827s, with an average of 4888129ns/op
2018/02/25 18:59:45 500 selects in PgSQL took 65.529611ms, with an average of 131059ns/op
2018/02/25 18:59:45 500 selects in CockroachDB took 219.302259ms, with an average of 438604ns/op
2018/02/25 18:59:46 500 deletes in PgSQL took 651.94164ms, with an average of 1303883ns/op
2018/02/25 18:59:48 500 deletes in CockroachDB took 2.407759236s, with an average of 4815518ns/op
fale@x250:~/go/src/github.com/fale/pg-benchmark $ ./pg-benchmark 
2018/02/25 19:00:24 # Ensure schema is present - PgSQL
2018/02/25 19:00:24 # Ensure database is present - CockroachDB
2018/02/25 19:00:24 # Ensure table is present and empty - PgSQL
2018/02/25 19:00:24 # Ensure table is present and empty - CockroachDB
2018/02/25 19:00:24 500 inserts in PgSQL took 676.834742ms, with an average of 1353669ns/op
2018/02/25 19:00:27 500 inserts in CockroachDB took 2.516174991s, with an average of 5032349ns/op
2018/02/25 19:00:27 500 selects in PgSQL took 48.130063ms, with an average of 96260ns/op
2018/02/25 19:00:27 500 selects in CockroachDB took 227.892005ms, with an average of 455784ns/op
2018/02/25 19:00:28 500 deletes in PgSQL took 661.885943ms, with an average of 1323771ns/op
2018/02/25 19:00:30 500 deletes in CockroachDB took 2.494310242s, with an average of 4988620ns/op

Speed is very similar to version 1.1.5, at least in those tests.

Then I moved to a single node cluster and I got:

@x250:~/go/src/github.com/fale/pg-benchmark $ ./pg-benchmark 
2018/02/25 19:09:06 # Ensure schema is present - PgSQL
2018/02/25 19:09:06 # Ensure database is present - CockroachDB
2018/02/25 19:09:06 # Ensure table is present and empty - PgSQL
2018/02/25 19:09:06 # Ensure table is present and empty - CockroachDB
2018/02/25 19:09:06 500 inserts in PgSQL took 646.523211ms, with an average of 1293046ns/op
2018/02/25 19:09:08 500 inserts in CockroachDB took 1.547001224s, with an average of 3094002ns/op
2018/02/25 19:09:08 500 selects in PgSQL took 51.706685ms, with an average of 103413ns/op
2018/02/25 19:09:08 500 selects in CockroachDB took 151.971039ms, with an average of 303942ns/op
2018/02/25 19:09:09 500 deletes in PgSQL took 668.726909ms, with an average of 1337453ns/op
2018/02/25 19:09:10 500 deletes in CockroachDB took 1.646481992s, with an average of 3292963ns/op
fale@x250:~/go/src/github.com/fale/pg-benchmark $ ./pg-benchmark 
2018/02/25 19:15:07 # Ensure schema is present - PgSQL
2018/02/25 19:15:07 # Ensure database is present - CockroachDB
2018/02/25 19:15:07 # Ensure table is present and empty - PgSQL
2018/02/25 19:15:07 # Ensure table is present and empty - CockroachDB
2018/02/25 19:15:08 500 inserts in PgSQL took 652.285039ms, with an average of 1304570ns/op
2018/02/25 19:15:10 500 inserts in CockroachDB took 1.585498223s, with an average of 3170996ns/op
2018/02/25 19:15:10 500 selects in PgSQL took 53.059632ms, with an average of 106119ns/op
2018/02/25 19:15:10 500 selects in CockroachDB took 135.993407ms, with an average of 271986ns/op
2018/02/25 19:15:10 500 deletes in PgSQL took 675.890494ms, with an average of 1351780ns/op
2018/02/25 19:15:12 500 deletes in CockroachDB took 1.776926316s, with an average of 3553852ns/op

Performances are better, but stil 2.5~3x PgSQL.
I'll try in the next few days to expand the tests to accommodate your comments :)

[rjnn]

Hi @Fale, one thing you might want to try is parallelizing your requests over multiple client connections. One thing that we do know is that we have worse latencies that PG, but are able to keep up in throughput. In this setup, the higher latencies are causing a throughput decrease as well.

[Wulfklaue]

@Fale

Those results are not surprising...

Your comparing a database ( PostgreSQL) that has had 21 years of development and is highly optimized for single system performance. If you go back 8 years in time, PostgreSQL lost hard to even MySql in every benchmark. They did a great job at fixing postgresql its performance issues.

Compare this to CockroachDB that is barely 3 year old. And has a totally different design philosophy.

CockroachDB is power is not speed. Its the ability to get a replicating, sharding database up and running with minimal effort. CockroachDB has lots of nifty features. Its the same reason why a lot of people started using PostgreSQL in the past. Because PostgreSQL was able to do things, that Mysql did not do ( or did not follow the standards ).

I doubt that CockroachDB will ever get the same performance in single system performance compared to PostgreSQL / Mysql. The real gain is again the ability to easily scale with more databases horizontally and still have a secure system for your data.

Try clustering on PostgreSQL vs CockroachDB and your results will be much more interesting. That is after you spend a few hours setting up clustering on PostgreSQL. Watch what happens when a PostgreSQL master or a slave drops. Or try having Master - Master on PostgreSQL. 👎

Think of it like this: What is more valuable. The money you spend on a extra server or the money you spend on a database engineer. The extra server is maybe 100 a 200$ per month, where as the database engineer is going to be way more.

If your planning on running a single server website, then stick with MySQL / PostgreSQL / ... as they are heavily optimized the last 20 years for that task. If your goal is to expand your hosting horizontally, then MySQL / PostgreSQL / ... are really not the best choice.

[tim-o]

@Fale, thanks for bringing this to our attention, and thanks to @wuflklaue for the context and recommendation! @Fale, there are some recommendations above that could be used to improve the test case:

• Testing nodes across multiple machines rather than the same machine.
• Changing the inserts to avoid transaction contention on the test.accounts table (https://www.cockroachlabs.com/docs/stable/performance-best-practices-overview.html#understanding-and-avoiding-transaction-contention)

If you have access to AWS, you could also follow our example of deploying a test cluster (https://www.cockroachlabs.com/docs/stable/deploy-a-test-cluster.html). This is closer to a true deployment of CRDB, with multiple nodes running on multiple machines.

Wulfklaue's summary of the relative benefits of CRDB vs. Postgres is on the money. That said, we're continuously looking to improve performance on specific queries, so if you have some real life examples where CRDB is not performant, feel free to let us know. In the meantime, I'll mark this as "closed - will not fix".

[lav45]

PostgreSQL 16.1 (Debian 16.1-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
CockroachDB CCL v23.1.13 @ 2023/12/01 21:38:31 (go1.19.13)

run test 1000 iterations

postgres insert: 2.593997s
cockroach insert: 6.457681s
postgres select: 0.150319s
cockroach select: 0.51686s
postgres delete: 2.674206s
cockroach delete: 6.345429s

[Perusae]

run test 1000 iterations

Unfortunately this will not change. No matter how many year pass. We did tests with Cockroachdb, YugubyteDB and ran into the same and way worse performance issues. As in 10x for basic searches hitting b-tree's.

As a user, your running 3x the server hardware, to get 1/3 the performance. Just setting up a basic Replicate on something like PostgreSQL, gives you the security and still 3x read performance (scaling is the same on node per node reading). Yes, its more work but it also does not read your data with telemetric (the Free cockroachdb enterprise model), nor comes to your door in the future asking payments. Fyi: The reported by others is a asking price is $150/vcpu (if you cross a 10m threshold, subject to change at any time!).

Seeing queries that take ms's on PostgreSQL, taking minutes on both distributed databases, as their query planners are not as mature. And even the simply basis of being a distributed DB can result in a simple LIKE '%work%' turn into a seq search, that just kills performance.

Both distributed DBs are way easier to setup, have a good build in interface for monitoring the nodes. But are just wasteful in resource out of the door.

Our conclusion was that its not just 3x vs 1x, or 3x vs 2x (replicate), but actually a 5x vs 1x situation because of the much higher resource demands. Raft protocol is not a free operations, and the more ranges/tables you have, the worse the communication overhead becomes.

Missing or differences in Postgresql compatibility are als issues. Less on YugubyteDB but they have other issues.

There is also the issue of need to design your entire application data structure around the DBs. Just to still see queries that are easily 10x slower then their older brother, on data that hits a single node.

My advice is for people who are wanting to run these systems: Are you in need for massive scaling? As in writing millions of records per second? Sure, go can go for a distributed DBs. But even a basic PostgreSQL can easily ingests 100k/s records per server, and push out millions or reads. A bit of smart app design, and you scale to more postgreSQL nodes that are cheaper and more performant.

So if your going to spend time developing your app around a distributed DB, you can do the same around postgreSQL and get the other benefits. PostgreSQL (and mariaDB) also scales horizontally, just differently. If you go on scale, you need both database architectures specific optimizations / designed anyway.

I can not help but feel that both Cockroachdb and YugubyteDB have a flaw in their design. But that is a different topic.