![Cover image for Postgres vs. MySQL](https://media2.dev.to/dynamic/image/width=1000,height=420,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Farticles%2F2zt85407p7onae7ktdv0.png)
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...
For further actions, you may consider blocking this person and/or reporting abuse
postgres >> everything :D
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.
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.
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
Interesting — feels like an AI optimization layer could be great for this.
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.
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.
Suggestion: it would be also interesting if you can compare time for
UPDATE
. :)Yeah that's a great idea, I'll try to run some new benchmarks whenever I get some down time.
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 needVACUUM
SQL command in PostgreSQL, which does some cleanup and "defragmentation" like our good oldspeedisk
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 usingUPDATE
.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!
Compare Postgres with MariaDB
maria has better performance than mysql
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.
Would've preferred to see Postgres Vs MariaDB tbh. That's had JSON support for ages. Surely MySQL has too?
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.
Congratulation!
Thank you!
Why not comparing PostgreSQL with MariaDB which is the open source fork of MySQL?
Was just comparing the two most popular DB choices, I want to eventually dig deeper into other alternatives as well!
Thanks for the topic
Thanks for reading :)