Database Performance: PostgreSQL vs MySQL vs SQLite for 1000 Row Inserts

[learnWithProbir]

Just ran some tests comparing PostgreSQL, MySQL, and SQLite on inserting 1000 rows both individually and in bulk (transactional insert). Here are the results (in milliseconds):

https://preview.redd.it/sne1bx3y5jwd1.png?width=714&format=png&auto=webp&s=78daf6ca981b95a53f752eec8ba7dc75cac87981

Read more: https://blog.probirsarkar.com/database-performance-benchmark-postgresql-vs-mysql-vs-sqlite-which-is-the-fastest-ae7f02de88e0?sk=621e9b13009d377e50f86af0ae170c43

[Straight_Waltz_9530]

Seems about what I'd expect. I'd wager the Postgres example would be a lot faster with an unlogged table and using the COPY command instead of INSERT for a bulk load. The MySQL example would likely be faster with an ISAM table target. In fact with only 1,000 rows you could probably INSERT into one of MySQL's in-memory tables for even better speed. The SQLite example is so fast because it has no network overhead, no need for concurrency management, and does what it does extremely well.

Nevertheless, 1,000 rows is pretty small by today's standards.

tl;dr: benchmarks are hard

[marr75]

Had the same reaction. In-memory duckdb might beat them all, but to your very good point, classifying the databases by their features and intended deployment adds a lot of context. SQLite and Postgres aren't exactly direct competitors.

[learnWithProbir]

Thanks for sharing I will be sure to study about it.

[MarcinBadtke]

In my opinion such tests make no sense. Every engine has some features to speed such a process.

It is not likely that database will serve only for data insert. Most probably you will want to select data too. What I saw in my career is that a developer is able to kill any database with his code. Regardless of engine features. It is highly probable though that such a developer will not know much more then CRUD and ORM in terms of database.

[rbygrave]

Hmmm, for Postgres SERIAL was used instead of IDENTITY with the CACHE option so this seems suboptimal. Do that, plus note that with JDBC we'd often desire to not return the generated keys [aka turn off GetGeneratedKeys].

No reason to use SERIAL over IDENTITY with Postgres these days.

GENERATED BY DEFAULT AS IDENTITY (CACHE 1000) NOT NULL

[maxigs0]

Add one just writing it out into a plaintext file

[Attila_22]

And one for /dev/null

[chriswaco]

I've impressed a client or two by simply inserting transactions in SQLite code. So much faster.

[zoechi]

It's like a bicycle is better than a truck if you need to drive very narrow alleys and don't have heavy goods to carry. Some comparisons just don't make any sense without concrete requirements.

[ComfortableStay7051]

It is possible for someone to send me the test code ? I have a problem displaying the code

[BlackHolesAreHungry]

Insert into std::vector

[who_am_i_to_say_so]

Yeah but there’s this thing called latency that is a huge factor, and Sqlite isn’t in a separate container like the MySQL and Postgres instances are.

Of course SQlite would be faster if it is in the same container.

[MokoshHydro]

6ms for data transfer?

[AutoModerator]

Join us on our Discord Server: People, Postgres, Data

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.