Selecting the right open source DB for your workload

So. Many. Databases.
The latest DB-Engines ranking lists 370 databases according to popularity. And the sheer number may lead some to go back to what they know, or rely on other developers’ recommendations. The more intrepid may even spend some time researching.

It takes some navigation to understand the landscape across all the database flavors. And understanding doesn’t necessarily lead to the right choice. Martin Kleppman literally maps this out in his book, Designing Data-Intensive Applications, O’Reilly. (Highly recommended read)

Ye olde capture-first
Early days of databases had fewer choices, there was Oracle, SQL Server, MySQL and PostgreSQL. You just had to choose between them, as they all had one thing in common, they were built for capture-first. Get the data.

Pick a database
Define your schema
Start loading and integrating
Tune normalization and queries
Add materialized view and query caching
Get ready to switch if this doesn’t work as intended

In this workflow, queries arrive too late to influence the database choice. Finding out later on that certain queries can’t be supported is problematic.

Tackling the paradox of choice
Through this process we quickly find out the chosen database and associated effort doesn’t meet the requirements. Here are a few options:

Stick with what you know
Bribe a trusted DBA or architect
Look at relevant TPC benchmarks
See what database experience is missing from your résumé
Consult a soothsayer, Magic 8 ball, dartboard

Mind-shift to query-first
Start with the end in mind. Choose your database by working backwards from your target read workloads. Think of this as TDD for database architecture.

I’m not saying you can ignore write performance (you can’t) or that write benchmarks don’t matter (they do). But don’t use write capabilities as the most important criteria for which database to use.

Read workloads
Getting to the data is where the value lives in a database. Applying a Zen koan:

“What is the value of a write that can’t be read?”

Writes are simply the cost of expected reads. From this view, re-consider the database options in terms of reads, as each database will have different tricks for managing and accessing the reads. Indexes are extra writes to accelerate reads, and replication are extra writes to ensure reads.

Think query-first. What kinds of reads can you expect and how to support them most efficiently and effectively?

Pick the right database for query-first

Define seed data that approximates a working system
Run read workloads for seed data on multiple DBs
Select the database with best workload fit (ops/sec)
Optimize for loading/maintaining data

If no single database platform is a match, use a distributed query engine like Trino, then replicate data through queues like Kafka or consider other tricks (share your tricks!)

Read Workload	Category	Description
Fetch value for single key	Key/Value	Returns unstructured value
Fetch values for related keys	Key/Value	Returns collection of values
Find single row with criteria	OLTP	Returns tuple (row of named columns) using column indexes
Find group of rows with criteria	OLTP	Returns collection of tuples using column indexes
Read rows within transaction	OLTP	Returns value based on transaction isolation level
Join subset of rows & related rows	OLAP	Returns collection of tuples joined across multiple tables
Join/summarize for few columns	OLAP	Returns count/histogram on a limited set of columns
Find/join/summarize for all columns	DSS	Returns data transformation computed against all available columns

Cut to the chase
For our own use, here’s how we evaluated the options for our read workloads:

READ WORKLOAD	CACHE Redis, Memcached	LSM **** Cassandra, HBase, RocksDB, LevelDB	BTREE **** MySQL, Postgresql, SQLite, SQL Server	COLUMNAR **** Druid, Iceberg, Parquet, Orc	M/R **** Hadoop, Resurface
Fetch value for single key	🥳	😀	🤔
Fetch values for related keys	😀	🥳	😀
Find single row with criteria	😀	😀	🥳
Find group of rows with criteria		😀	🥳
Read row within transaction		🤔	🥳
Join subset of rows & related rows			🥳	😀
Join/summarize for few columns			😀	🥳	😀
Find/join/summarize for all columns			😱	😱	🥳