Performance with cockroachDB compare to postgresql on single select *

Hi,

Today I am using a postgre database in production.
I wanted to test cockroachDB to see if I can use it instead as it offers replicas.

Everything that follows is done in local on my computer.

I am using the image of cockroach “cockroachdb/cockroach:v21.2.4” in local with 3 nodes folowing this sample getting started https://www.cockroachlabs.com/docs/v21.2/start-a-local-cluster-in-docker-mac.html

I created a database (size = 480 MB) containing one table called ‘process_definition’ with 11k rows.

When I run these commands :

psql -h localhost -p 26257 --u user bird_admin
psql (13.5 (Ubuntu 13.5-1.pgdg18.04+1), server 13.0.0)
Type "help" for help.

bird_admin=> EXPLAIN ANALYZE SELECT * FROM process_definition;

I get this result :

planning time: 589µs
execution time: 2.4s
distribution: full
vectorized: true
rows read from KV: 11,316 (0 B)
cumulative time spent in KV: 2.4s
maximum memory usage: 40 MiB
network usage: 0 B (0 messages)

• scan
nodes: n1
actual row count: 11,316
KV time: 2.4s
KV contention time: 0µs
KV rows read: 11,316
KV bytes read: 0 B
estimated row count: 11,316 (100% of the table; stats collected 23 seconds ago)
table: process_definition@process_definition_pkey

If I have the same database using postgre this time.
I run these commands :

psql -h localhost -p 5432 --u user bird_admin
psql (13.5 (Ubuntu 13.5-1.pgdg18.04+1), server 12.9 (Debian 12.9-1.pgdg110+1))
Type "help" for help.

bird_admin=# EXPLAIN ANALYZE SELECT * FROM process_definition;

The results are :


Seq Scan on process_definition (cost=0.00…559.16 rows=11316 width=788) (actual time=0.015…3.710 rows=11316 loops=1)
Planning Time: 0.074 ms
Execution Time: 5.319 ms
The difference of execution time is huge.

I think I am missing something here.
I have also tried to run a single node following this guide cockroach start-single-node | CockroachDB Docs

I get a better result but still too long compare to postgre :

planning time: 772µs
execution time: 1.7s
distribution: full
vectorized: true
rows read from KV: 11,316 (0 B)
cumulative time spent in KV: 1.7s
maximum memory usage: 40 MiB
network usage: 0 B (0 messages)

• scan
nodes: n1
actual row count: 11,316
KV time: 1.7s
KV contention time: 0µs
KV rows read: 11,316
KV bytes read: 0 B
estimated row count: 11,316 (100% of the table; stats collected 35 minutes ago)
table: process_definition@process_definition_pkey
spans: FULL SCAN

I am pretty sure I am missing something here.

If you can provide me some help, it will be great !

Thank you very much !

Regards Loic Seguin

Are you wondering why CockroachDB is slower than Postgres in this example? It’s likely because they are fundamentally different architectures that were built for different use cases, see more here. CRDB is a distributed database that is built to scale horizontally (i.e. run on many machines), while postgres was originally built to run on one machine. So, it makes sense that Postgres is a bit faster on your single machine for a simple query! I also wonder what isolation level you’re running on your postgres instance, and how your local machine setup plays into these results.

First of all, thank you so much for your fast answer MichaelB !

Before posting this post, I have tried this on my dev environment on openshift.

To be sure of what I was doing I removed all network, connectivity, latency problems trying in my local environment.

This is why I am showing you only the results in my local but I do have the same problems on openshift.

To answer your question, I have installed postgresql and cockroach using the default configuration so for postgre the isolation level by default should be used (so Read Committed) and for cockroach SERIALIZABLE is the only one supported isolation level.

So I have tried to set the isolation level to SERIALIZABLE on postgre to check if I am loosing performance. But the results are the same.

It is kind of related to this post Slow local (and remote) CockroachDB vs PostgreSQL. In that post, they also changed the isolation level on postgre, it did not change anything.

I will try to send you my database but I need to create some fake datas.

Thank you very much for your concern !

Regards

Loic