Database Performance Test 1
Here we run a very simple performance test scenario against multiple databases and DDL configurations:
- aerospike
- cockroach
- cockroach_json
- ignite
- mongo
- postgres
- postgres_json
- redis
- yugabyte
- yugabyte_json
This wil give an out-of-the-box developer experience assessment of, for example, CockroachDB vs YugabyteDB performance, and Aerospike vs Redis vs Ignite.
Test description
The test performs the following database operations sequentially (in a single thread):
- initalise and clean the database table (aka "collection")
- insert 10,000 test records to populate the table (these are randomly generated ecommerce products with just a few fields)
- query 20,000 times for the title of a product ID (each query gets a random product ID)
- query 20,000 times for the description of a product ID (each query gets a random product ID)
Caveats
- this is a single-node test (some of these databases shine most when clustered)
- the queries used are deliberately simple so that very different database engines can be easily compared
- real world queries may be far more complex (but not necessarily possible in some of these database engines)
- as the databases are run in Docker it's possible there is a performance variance depending on the particular Docker image creation/optmisation
Methodology
Test machine: M1 Mac, MacOS 12.6.3, Docker 20.10.13 (build a224086).
All database services were run as Docker containers.
The following Docker images were used (these were the latest stable official images available in Docker Hub as of 2023-03-18):
aerospike:ce-6.2.0.3
apacheignite/ignite:2.14.0-jdk11
cockroachdb/cockroach:v22.2.6
mongo:6.0.4
postgres:14.7
redis:7.0.9-alpine
yugabytedb/yugabyte:2.16.2.0-b41
Multiple runs were used in each case to ensure results were reliable and consistent.
Only one database server was running at a time.
The test code is here https://github.com/exaspace/db-perf-tests (commit 2d17af43fbe88ef911d2f579199a18631f474712).
Results
Aerospike
AerospikeProductStoreScenario load_products 1540.1 per second (10000 in 6.5 seconds)
AerospikeProductStoreScenario query_titles_by_product_id 1566.7 per second (20000 in 12.8 seconds)
AerospikeProductStoreScenario query_descriptions_by_product_id 1572.5 per second (20000 in 12.7 seconds)
CockroachDB
PostgresProductStoreScenario load_products 13092.4 per second (10000 in 0.8 seconds)
PostgresProductStoreScenario query_titles_by_product_id 1272.3 per second (20000 in 15.7 seconds)
PostgresProductStoreScenario query_descriptions_by_product_id 1255.4 per second (20000 in 15.9 seconds)
CockroachDB JSON
NB Cockroach DB was slow for Json reads.
PostgresProductStoreScenarioJson load_products 5948.7 per second (10000 in 1.7 seconds)
PostgresProductStoreScenarioJson query_titles_by_product_id 128.5 per second (20000 in 155.7 seconds)
PostgresProductStoreScenarioJson query_descriptions_by_product_id 135.4 per second (20000 in 147.7 seconds)
Ignite
NB
- Ignite was configured for persistence (via its XML config file)
- Faster APIs (
putAll) for loading exist in Java but seem not available for Python
IgniteProductStoreScenarioSQL load_products 530.31 per second (10000 in 18.9 seconds)
IgniteProductStoreScenarioSQL query_titles_by_product_id 834.95 per second (20000 in 24.0 seconds)
IgniteProductStoreScenarioSQL query_descriptions_by_product_id 832.57 per second (20000 in 24.0 seconds)
MongoDB
MongoProductStoreScenario load_products 25006.9 per second (10000 in 0.4 seconds)
MongoProductStoreScenario query_titles_by_product_id 1016.4 per second (20000 in 19.7 seconds)
MongoProductStoreScenario query_descriptions_by_product_id 999.2 per second (20000 in 20.0 seconds)
Postgres
PostgresProductStoreScenario load_products 19209.6 per second (10000 in 0.5 seconds)
PostgresProductStoreScenario query_titles_by_product_id 1528.6 per second (20000 in 13.1 seconds)
PostgresProductStoreScenario query_descriptions_by_product_id 1528.7 per second (20000 in 13.1 seconds)
PostgresJSON
PostgresProductStoreScenarioJson load_products 17186.2 per second (10000 in 0.6 seconds)
PostgresProductStoreScenarioJson query_titles_by_product_id 600.9 per second (20000 in 33.3 seconds)
PostgresProductStoreScenarioJson query_descriptions_by_product_id 582.8 per second (20000 in 34.3 seconds
Redis
RedisProductStoreScenarioUsingHashes load_products 1382.5 per second (10000 in 7.2 seconds)
RedisProductStoreScenarioUsingHashes query_titles_by_product_id 1501.1 per second (20000 in 13.3 seconds)
RedisProductStoreScenarioUsingHashes query_descriptions_by_product_id 1481.0 per second (20000 in 13.5 seconds)
Yugabyte
PostgresProductStoreScenario load_products 2644.9 per second (10000 in 3.8 seconds)
PostgresProductStoreScenario query_titles_by_product_id 504.8 per second (20000 in 39.6 seconds)
PostgresProductStoreScenario query_descriptions_by_product_id 503.3 per second (20000 in 39.7 seconds)
Yugabyte JSON
NB Yugabyte was very slow for Json reads so I had to run 10x smaller test case.
PostgresProductStoreScenarioJson load_products 905.3 per second (1000 in 1.1 seconds)
PostgresProductStoreScenarioJson query_titles_by_product_id 47.7 per second (2000 in 41.9 seconds)
PostgresProductStoreScenarioJson query_descriptions_by_product_id 47.7 per second (2000 in 41.9 seconds)
Combined results in CSV format
database,load_products,query_title aerospike,1540,1566 cockroach,13092,1272 cockroach_json,5948,128 ignite,530,835 mongo,25006,1016 postgres,19209,1528 postgres_json,17186,600 redis,1382,1501 yugabyte,2644,504 yugabyte_json,905,47
Conclusions
Writes
The fastest inserts are achieved by those databases with easy and explicit bulk API operations:
- Mongo (using
collection.insert_many()) - Postgres (using
psycopg2.extras.execute_values())
Yugabyte is considerably slower than CockroachDB for bulk inserts.
Redis and Aerospike are similarly slow to load this data as they lack bulk load APIs and so an API call is made for each data record to be loaded.
Reads
The fastest databases for simply looking up a single record field by ID are Aerospike, Postgres and Redis - they are all roughly similar performance.
Postgres performance is particularly impressive given its larger number of features.
Mongo is noticeably slower than the fastest databases on this read test.
Using a JSON document model rather than structured table has a big negative impact on performance.
Postgres suffers relatively less than Cockroach and Yugabyte when using JSON models.
Overall Winner
Postgres