DEV Community

Cover image for Postgres vs. MySQL

Postgres vs. MySQL

brandon on January 16, 2025

Hello I'm Brandon, I’m the CEO & Co-founder of Outerbase, where we are building a modern data platform. We work with thousands of developers ma...
Collapse
 
code42cate profile image
Jonas Scholz

postgres >> everything :D

Collapse
 
burcs profile image
brandon

Yeah it does seem like the everything DB these days, everyone has a pg extension. I do like a lot of the other databases as well especially SQLite, I also think DuckDB is something to watch out for.

Collapse
 
mrpercival profile image
Lawrence Cooke

This is just an observation I found recently, when it comes to the read speed, MySQL is great with row order querying (primary key searches, create date etc, anything where the data order matches the physical order). but when It comes to other types of querying, it falls behind.

SELECT * FROM table WHERE id BETWEEN 100000 AND 100100 (MySQL is faster)

SELECT * FROM table WHERE update_dt BETWEEN '2024-01-01' AND '2024-01-31' (Postgres is faster

When you get to aggregated functions, then Postgres was way faster.

While MySQL is consistently faster under those primary key type searches, it wasn't a really big difference, while areas like Aggs, then there was a big difference, it feels like a small tradeoff for a big gain with Postgres (Though I haven't used it much in production, it does feel like the right choice for most circumstances) .

MySQL isnt going away any time soon, it still performs well under a lot of circumstances. It probably just lacks the extendibility and feature set of Postgres.

Collapse
 
abrahamn profile image
Abraham

If you create an index on update_dt maybe even organize the table as a column store it will be as fast as primary ID ordering in mysql and related. I think that both need a level of investment to understand the intricacies and use them well

Collapse
 
burcs profile image
brandon

Interesting — feels like an AI optimization layer could be great for this.

Collapse
 
burcs profile image
brandon

This is a great insight, thank you for sharing :)

Agreed on it not going anywhere, it's still ranking a lot higher on sites like db-engines.com but it is dropping, wish there were more companies working on it like Planetscale.

Postgres seems to be where all the new DB startups are focusing, and extending its feature set.

Collapse
 
john_lauro_f6c2da31eff0a4 profile image
John Lauro

I would like to know what tuning was tried. Last I check (which admittedly was over 5 years ago) Postgres offered better default configuration, but MySQL could be tuned to give better performance. If going with default setup, I'm not surprised by the numbers, but they seem very low for a properly tuned MySQL configuration.

Collapse
 
teminian profile image
Robert Teminian

Suggestion: it would be also interesting if you can compare time for UPDATE. :)

Collapse
 
burcs profile image
brandon

Yeah that's a great idea, I'll try to run some new benchmarks whenever I get some down time.

Collapse
 
teminian profile image
Robert Teminian

I think that's also an important theme of test considering the difference in how each of them manage the record physically(I mean, in file system structure).

When doing UPDATE, PostgreSQL just marks previous records as "deleted" and append new record. The marked records are expected to be reused, but since this shakes the physical structure of the table(i.e. you have to read in the end but go back to front a bit to read more), in random access scenario it surely degrades the performance. That's why you need VACUUM SQL command in PostgreSQL, which does some cleanup and "defragmentation" like our good old speedisk from Norton Utilities in DOS era or Disk Defrag tools in Windows.
And when it comes to InnoDB engine in MySQL, it just overwrites the previous record and that's all, which makes MySQL faster than PostgreSQL(at least in theory) if the condition needs in frequent record update scenario. My personal test result in the past showed that in rapid INSERT scenario PostgreSQL was far better than InnoDB since it saved records in multiple files, meaning that even simultaneous writing was possible as long as you have enough bandwidth in your disk, but in update scenario, it was better to TRUNCATE and rewrite the whole record from the beginning was better than using UPDATE.

It was years ago and I heard that both have reinforced their engines for better performance so I'm not sure about what will happen if the test is done again. However, given that background knowledge, I'm sure that it'll give you some interesting results for you too.

Cheers for those who want to make his hands dirty to find the truth!

Collapse
 
drjavab profile image
Hossein Raad

Compare Postgres with MariaDB
maria has better performance than mysql

Collapse
 
burcs profile image
brandon

Yeah I'll do that one next, we just see a lot more usage of mysql than mariadb at Outerbase so it made more sense in terms of total traffic. I hear you though probably a more fair comparison with mariadb.

Collapse
 
pau1phi11ips profile image
Paul Phillips

Would've preferred to see Postgres Vs MariaDB tbh. That's had JSON support for ages. Surely MySQL has too?

Collapse
 
burcs profile image
brandon

Yeah I should do some benchmarking there too, good idea! MySQL definitely has JSON support it's just more limited and less efficient than Postgres' JSON indexing.

Collapse
 
andradesampaio profile image
Andrade Sampaio

Congratulation!

Collapse
 
burcs profile image
brandon

Thank you!

Collapse
 
maneamarius profile image
maneamarius

Why not comparing PostgreSQL with MariaDB which is the open source fork of MySQL?

Collapse
 
burcs profile image
brandon

Was just comparing the two most popular DB choices, I want to eventually dig deeper into other alternatives as well!

Collapse
 
kozato_keizo_4b664b53d836 profile image
Kozato Keizo

Thanks for the topic

Collapse
 
burcs profile image
brandon

Thanks for reading :)