A short while ago, some colleagues and I were discussing the benefits and differences between database engines and how certain database engines could be more beneficial that others for a particular project.

Of course, the subject of speed made it’s way into the conversation.

This got me thinking. With modern hardware, for simplest of database queries, should speed really be a factor?

Of course, we all want things to be as fast as possible, but typically other factors drive the decision for a database choice, such as concurrent connections.

Neither the less, I decided to put Postgres, MySQL and SQLite up against one another, to see what the difference really is for the simplest of queries. To test this, I created a new schema in each database which consisted of a single table containing only 3 records and performed a simple count on the table.

Here are the results from the first test:

This surprised me. I thought SQLite would have been the fastest, but Postgres came out on top here, returning the count in less than half the time of SQLite and almost 4x faster than MySQL.

Now, in the real world, no one is creating a database only to store 3 records. So, lets try with a larger dataset. For this test, I chose to import the 2024 UK crime dataset provided by https://data.police.uk/data/, which contains more than 5 million records.

I will run 3 queries on each database. Another count, a select with limit and a filtered select.

select count(*) from crime_data;

select * from crime_data limit 10;

select crime_id from crime_data where crime_type = 'Vehicle crime';

It’s clear from the results that MySQL is far slower than both Postgres and SQLite, and this time, as I would have expected SQLite was the winner.

But what does this tell us, SQLite is better for simple queries?

Although it is easy to come to that conclusion, you shouldn’t only take these tests into account. SQLite is great for basic queries, small applications such as personal projects or for embedded applications.

Postgres is known to excel at performance and scalability. However, real-world performance varies. Query speed depends on a number of factors, such as indexing, workload and concurrency.

If you are currently deciding which database best suites your project, it’s best your tests reflect the actual use case.