► What should I test next? ► AWS is expensive - Infra Support Fund: buymeacoffee.com/antonputra ► Benchmarks: th-cam.com/play/PLiMWaCMwGJXmcDLvMQeORJ-j_jayKaLVn.html&si=p-UOaVM_6_SFx52H
@Kingromstar Same. I'm a relative newbie to the tech world. But I've known postgresql is perhaps the best generic DBs out there. At some point, you'd want to move to self-hosted ScyllaDB, especially if you need sub-millisecond ops at scale. But, if you're building something at that scale, you'd know the details (if and when). For most starter projects, postgresql is the best choice. What I've also learnt is that it's equally important to vacate your cloud provider timely, otherwise you'll be paying through your nose to do so later.
@@shubhamkukrety6355ScyllaDB should NOT be your go-to at scale. Nothing wrong with it per se, but once you get to the scale where something like Postgres hits its limits-which is a bigger scale than 99.9% of projects ever reach-you should not be blindly reaching for any one off-the-shelf solution. At high scale you should be choosing the niche solution that most closely fits your requirements, and those requirements will NOT be what you expected at the start. Be flexible and bend to where the numbers lead you.
MariaDB v PostgreSQL would be a great comparison I think, not many opting to run MySQL over MariaDB these days, and I imagine it should be a bit of a closer race.
It would be more correct to compare MariaDB vs open source Citus Data(PostgreSQL + extension) or its commercial version Azure Cosmos DB for PostgreSQL.
It is tricky, because in reality you should utilize all db features (extensions in pg, OTLP tables in Azure SQL and whatnot). And realistic benchmark is challenging to make :)
@@IanWilkinson-SgtWilko how? The imperative part is almost like Oberon, Modula or Ada. wordy? yes, but comparatively easy to read. the SQL part looks like normal, standard SQL. not some completely arbitrary other thing. eg: Oracle uses “ for identifiers, not strings, like actual SQL, only ‘ is for strings and || is for concatenation. mssql is like a hipster trying to make it all different just because… if you limit yourself to SQL then targeting Oracle, postgres, Firebird and even sqlite to an extent will be fairly easy. mssql is just different for no reason other than the hipster dialect they bought back then (sybase)
Testing SQLite against any of those would be very interesting. I know that SQLite is a different "animal", and it does not scale horizontally, but I found in most of my use cases that it provides more than enough performance (actually much more), and it is incredibly easy to operate and maintain (less moving pieces). In addition, there are already solutions for availability and replication which are simple. So, I am using most of the time SQLite in production.
@@boot-strapper Yes, it is becoming a very attractive proposition. However, given that for "typical" applications (e.g. web with a db backend) the performance for writes with SQLite is limited by the disk (no network hop between the application an the DB), I do not normally require horizontal scaling for performance reasons. Below 100 tx/sec (writes), SQLite blows away the "traditional" DBs, and if I hardly need more than that. Availability is another thing, but it is also relatively simple to achieve with SQLite these days.
@@jesusruiz4073 oh yeah I agree. I wrote a mock data service that generated mock data. It was able to generate 3million records and insert them into SQLite in
Am I the only one who didn't see MySQL config? What is the table engine for MySQL? Not sure if this is still the case nowadays, but MySQL used to come with really tiny default parameters for INNODB engine. And the first thing you do with MySQL is to set those params like innodb_buffer_pool_size, etc. (there are a bunch of them) I. e. nobody uses MySQL out of the box. You have to set those params to reflect your load. The easiest way were running tuning-primer or MySQLTuner script after running the workload. You had to do this several times.
@@michalszalapski The query cache not only wouldn't help here, but it got removed in MySQL 8 because it was terrible. It's been disabled by default since MySQL 5.6 anyway, for that same reason. It would not be a thing in MySQL 9.
@@neothermic1 It would help here and ye, thats why Im not using mysql since 5.6. Never though ppl still use mysql... Mariadb query_cache works very well and is mandatory for high load clusters(unless u have no clue how to maintain it).
@@michalszalapski From mariadb's docs: "It does not scale well in environments with high throughput on multi-core machines, so it is disabled by default." - It would then raise the question as to if it can help here; these are about the same reasons it was disabled and removed from mysql itself. But I think we're in agreement that without seeing the config of the mysql server, it's being tested at a huge disadvantage as the defaults are awful.
The test seems a bit too one sided, the main architectural difference in MySQL vs Postgres is MySQL write operations (write, update, delete) are amortized so data is always "balanced", while Postgres postpones it until VACUUM and it is prone to degradation, when vacuum starts under load. It would be nice to show how these databases will compare under mixed workload, when records are inserted, updated and deleted at the same time, and data that is being changed should be in the index to test tuple bloating. Also can you publish schemas for databases?
That can be seen in the IO/s in the graph on right side, second from the bottom. MySQL is doing a hard fsync to disk or something along the lines. PGsql doing 1/3 of the disk write, and you can't cant cheat on disk writes. They either happen or not. So PGsql is running with a less durable data model in this test
@@opensourcedev22 Not true. Postgres writes to disk on each commit. It uses a copy on write model that lets it write asynchronously to disk before committing, but the commit step at the end of the transaction is synchronous and atomic. The default isolation level in postgres is much stronger than the default in mysql, since Postgres read committed is Monotonic Atomic views + causal consistency, while mysql repeatable read does not actually fulfill the ISO sql read committed standard in all situations while still being able to deadlock
@@BosonCollider No need to copy paste chatGPT into here brother, just look at the 3:40 mark. He's running 290 Q/s, and pgsql is doing 309 IO/s... And MySQL is at 1.4k I admit that personally, I am unsure what low level pgsql is doing in this specific teat, but disk IO can't be cheated unless it's not being used. So, are you honestly willing to admit pgsql does 300 Q/s and with ONLY 309 IO/s ? I think a database configuration is needed here with all settings posted to be sure
Already commented about some technical stuff, now about the design: for testing SQL databases I would like to see a slightly more complex set of operations, UPDATE and DELETE are missing, having more JOINs would be more realistic, where clauses that don't use an index or different types, sorting and aggregation would also help to highlight different strength and weaknesses. Maybe something like a simplified online shop with shopping cards (adding/removing items, changing amounts), orders and reports like the top 100 customers or articles in a given time frame. And also very important: transactions. In other words some kind of CRUD app that does everything in SQL. A lot of work and preparation though.
thank you for the feedback. i'm working on improving this test based on all the feedback i got. the suggestion about an online shop is very interesting.
2 Years ago i was tested on bare metal mysql vs postgresql using 0.9 TB file with real events. Nodejs was used, both databases used default configs. There was ~90% of selects, rest was IUD. With low level of concurrency mysql was winner, with > 30 workers postgre on a top. I played with database structure and other things, and found, than there is no clear winner or loser. Summary: both mysql and postgre have own advantages/and disadvantages, and database selection is depends on many factors.
Did you also consider fine tuning the database configs, since the default configurations are not optimized at all. Think about innodb_buffer_pool_size for Mysql. And shared_buffers in PostgreSQL.
@@moverecursus1337 This is fundamental difference between database product, and database project. In case of a product, developer might assume, that database will run on a dedicated server to make the best use of expensive per core licenses. In case of a project, such assumption cannot are not justified, database might run on a dedicated server, or it might be colocated with web application. In the latter case there must be a way to limit database memory usage, so application server won't starve.
I suppose it is calculated from VPS free space, like current_disk_usage - predefined_disk_usage at start of tests, so maybe rotating some large logs caused this or postgres autovacuum of some previous data? although postgres autovacuum does not free disk space by default, right?
I guess he takes a baseline and calculates size by subtracting base from current value. Around 7:35 we can see the graph goes low and then goes up. It means the baseline may be wrong. But the minimum value was around -110 MB but end value was 4.9 GB which makes it 5 GB - postgres vs 18.7 GB - mysql
Flaw of the benchmark environment set up. The graph jumps to negative few hundred megabytes for postgres, so clearly it wasn't doing `df` on the $PGDATA directory, but used a constant value subtracted from the current free space. Thus it could have been anything on the server affecting the results, or postgres log rotation, or dirty postgres data folder having some leftover data in pg_wal/ that got removed naturally. As others said, autovacuum doesn't reclaim free space.
In my experience with MySQL, charsets have a huge impact in database size and performance if not correctly specified. i.e utf8mb4 vs latin1 will show a big difference in database size, also in performance if the client is using different charsets than the server, it slow down every operation involving text because it needs a "translation".
Nice benchmark! A couple of things: 1 - I believe that Postgres uses a process per connection. Are you making sure to measure the combined CPU and memory usage of the 'main' process along with all of the connection processes? The reason that this is relevant is that MySql is thread based, so the main process should show the overall CPU/Memory whereas with Postgres you might only be looking at the DB process which is kind of cheating since the connection processes also use CPU/Memory. 2 - It would be cool to see a Java benchmark using the latest Java as well as the latest JDBC drivers for each. Be sure to use a connection pool like Hikari if you do.
@@hypnoticlizard9693 Why do you think like that? As far as I know, SQL Server is more advanced. I watched CMU database lessons in TH-cam, the professor was saying that. However, I'd use PostgreSQL if I had a choice as well, cause no license cost.
Thank goodness you blurred the names on those graphs not to spoil anything! I cannot possibly figure out which is which otherwise. Their names are too similar, and neither has associated colors.
How the MySQL is configured? How big the buffer pool size and redo log size you used for your test? These two are the most important config for high perf innodb instance. The default values are very low for production usage. When running mysql instance, the buffer pool should be accounted for as much of RAM as possible. From 60% to 80% of the RAM (bigger RAM the percentage should be bigger).
This! I see no mention of the configuration that's been done to either database server. There's a few more too, such as what was innodb_write_io_threads set to? Did you set innodb_io_capacity and innodb_io_capacity_max to roughly match what your storage medium is? What *is* your storage medium? (HDD? 2.5" SSD? M2 NMVE? Enterprise U2 drive? these differ drastically!) The defaults are terrible, but the defaults are run-everywhere defaults. You ALWAYS want to configure a mysql config to where you're deploying the server to.
i didn’t use dedicated disks for data storage and used the main os disk. when i installed mysql and postgres, the differences were 6 and 4 gigs, so i just subtracted that value from the graph before the test. i'm working on an improved version of this benchmark based on the feedback i received.
For the read iops graph for postgres being stuck at 0: - check the query - maybe it is measuring write ops instead, especially if the second dashboard was created by copying + tweaking the first dashboard - super easy to forget to replace "write" with "read" - maybe postgres is at 0 because it managed to fit the entire dataset into the page cache? Depending on how memory is measured, it may not include the OS page cache, which contain the contents of recently-used files. If every read for postgres was a page cache hit, the reads would never go to the disk, and you'd get 0 IOPS. 1KB records * 64M would start to fit in memory on a large VM, especially if the DB compresses the data before writing it to disk (which, if pg compresses and MySQL doesnt, also explains the large difference in DB size and write IOPS!) The page cache also isnt cleared by restarting the DB - the cache is in the OS after all, and is just caching file contents, for every process. To clear this, you'd have to run a command to drop the page cache, or reboot the VM in between tests. Or - before the read test, try writing at least 8-10x the VM's RAM in records (calculate it by adding the size in bytes of each field on every table, then multiplying by the number of records - not by looking at disk stats!) to ensure that the cache cant be big enough to fit the entire data set, regardless of compression
Those are 2 completely different databases with different usecases. Try doing complicated joins in mongodb or any relational data stuff, you simply shouldn't do that.
@@qizott6442 Not really. The question is not 'how good mongodb can do relational stuff', it's 'can postgres handle JSON as good as mongodb'. PG is very flexible and it's interesting to see how it compares to mongo.
@@qizott6442 relational stuff is completely doable with mongo aggregates, shouldn't !== cannot, and a lot of projects that have picked mongo initially, because it's somehow easier, end up with complex "joins" in mongo. It's well known that mongo's performance is terrible at this. But it's interesting to see how postgres can beat mongo in mongo's game.
I've seen a benchmark from PG developers where they demonstrated that their db beats mongo on JSON operations. That was a response to a benchmark published by mongo where they run PG installed from Ubuntu with its default settings.
@@antonkuranov I guess that's why benchmark from 3rd party on this would be interesting. Truth being told I wouldn't be surprised if PG really did beat mongodb at it's own game ;P
I'm not DBA at all. But there were stand many times (not by me, of course), that MySQL optimized more for reads and for OLTP operations when Postgres for OLAP ones. In addition, MySQL is better for horizontal scaling; and Postgres for vertical scaling. It looks like the real difference between these DBs will be clear when we'll have a huge amount of data, loaded in DB (so we already apply some scaling, i.e. replicating, sharding, etc.), as well as mixed requests (i.e. SELECT/INSERT/UPDATE/DELETE at the same time). For such scenarios as in the test (store for analytics records) choosing either of these two bases looks the same and is equally wrong - something like MongoDB or Cassandra looks more preferable since it would scale for used operations much better.
A lot of those statements are parroted from benchmarks over a decade old. I haven't seen anything recent where MySQL can match Postgres. Horizontal scaling was the most recent thing that Postgres changed the story on, but read performance has been better in Postgres for a number of years now in my experience.
@@DanWolf-codeNerd i think any of us would love to use open source and fast Postgres over MySQL if Postgres beats MySQL in literally ANY aspect. We just need some numbers from exhaustive tests, that prove the claims. Do you have such numbers, or links to such tests? (Surely, a 10-minute test for one simple insert; then one simple select, as this one, is not enough.)
Mongo is a very different DB since you do not use SQL and as soon as you need to join different tables together MongoDB is quite a lot harder to use efficiently. I have been using it a lot and for the right use cases its blindingly fast but my opinion is that if you have anything but separate lists of objects and need to use only a single type of DB, go with an SQL. It not necessarily the fastest in many cases BUT it lacks any serious weak spots that can derail a project. If you need to do more adhoc queries over multiple different data sets, mongo can be very hard since you most likely need to load things into memory and do the work in your own code and except for small databases where everything fits in memory, that is very hard to get any performance in. On the rest I totally agree, MySql are both good database with slightly different strengths which even for quite large projects are going to be almost irrelevant. For innodb you really need to make sure of the settings.
@@AlexanderBorshak I don't, but I also don't need benchmarks. I deal with an enormous throughput of data in my job, with compute and database combined costing over $100k/month on AWS for just my team, but if we had to scale upwards, we would be able to continue to scale for a very long time. MySQL might be able to as well, but since I know Postgres has so much headroom, a theoretical difference doesn't mean much. If you were to work for a larger company than mine, you could probably go even further and write your own custom modules if you eventually ran out of runway for either database. Both are extremely mature. I have had experience with MySQL performance. I'm certain that was probably entirely or nearly entirely about poor design in that case, not about the database.
I love seeing benchmarks between databases. We need more of them. Folks should keep in mind that schema design and use cases make a bigger difference than engine in most cases, just like data structures and algorithm choice makes a bigger difference than the programming language. There are also so many "shortcuts" that each engine implements for different scenarios. It's still good to see comparisons with apples-to-apples. It's a shame the proprietary db vendors don't allow performance comparisons like the open source DBs allow.
Thank u for this comparison, even though most of us probably already knew postgres was going to win. I'm not sure if u already did this but I'd love to see how Postgres compares to MongoDB, the two GOATs of the DB world. 😀
You didn't say anything about the tuning of the database servers. Tuning it well is primordial for performance ! You need to tune memory, and disk buffering among other things.Also you said nothing about the indexes that were used for the tests. Without those details, the comparison is pretty meaningless because default tunings usually aren't production tunings but development tunings. They are very conservative and limit the performance greatly. In general, you should ask for advice from the general public before performing your benchmarks, because sometimes, you make very naive assumptions that are simply incorrect and completely distort the results.
@@flamingspinach Well no...even untuned, the test is relevant ! It means that you if you can't or won't put the effort in "tuning" go for Postgres it gives you best likelihood for best performance with out-of-the-box config
@@dextrowim "It means that you if you can't or won't put the effort in "tuning" go for Postgres it gives you best likelihood for best performance with out-of-the-box config" No it does not - it only tells you that on that particular hardware and OS, with his specific DB setup and that particular workload Postgres is overall better. But there are hundreds of unaccounted variables at play here. If you use the default settings than ANY claims you make about performance are already wrong.
It could have, except MVCC wasn't really used if it was just 80M individual inserts on autocommit. Until tuples are updated or deleted, or fillfactor is configured for a table, MVCC has near zero overhead costs. (The longer answer is that ever tuple has around 16+8B of invisible metadata in table heap, another I think 26B for page header (one page by default = 8kB) and some more in the visibility map file.)
using a binary copy with pgx will allow you to insert jaw dropping numbers of rows per second. and it only re-evaluates indexes once when you say complete. it’s slightly slower cousin allows you to stream csv directly into or out of the psql cli client.
Database is very sensitive to removing records from the database. So additionally to insert and fetch will be good to deleting and updating operations.
I am missing some important details. Did you use the innodb storage engine and what was the setting for innodb flush at trx commit? And was a similar configuration used for postgres regarding transaction safety?
yes, i used innodb. as far as i understand, foreign keys are only supported with that storage. in this particular test, i mostly used defaults, and i have an updated video with full CRUD and configuration settings on git. please take a look. Round 2 - github.com/antonputra/tutorials/tree/main/lessons/216
Hey Anton, I have been watching your benchmarking series from the start and love them! I might have an idea back on the language performace... it would be interesting to see the power of fully-fledged JVM vs something like Go or Node? Last comparison was using Java compiled to binary without the JVM. The memory usage will be massive but I wonder if the JVM at peak performance (after warm up) can outperform other GC languages in terms on Number of Requests before failure and the Response Time. Something like Amazon Corretto or even OpenJDK would be interesting
I'm assuming mysql was using InnoDB engine? MyISAM is practically deprecated but it'd still be interesting to see this comparison using it and also vs Aria engine from mariadb
I think you should be careful with default configs. InnoDB by default has very conservative configs. Good for testing but not sure its good for production. @@AntonPutra
Love PostgreSQL. For best performance, it's recommended to configure PostgreSQL with "ALTER SYSTEM SET various parameters" - eg.shared_buffers, effective_cache_size, work_mem, wal_buffers, random_page_cost, max_worker_processes and a few more. If you don't care about ACID and want maximum write performance you could also disable WAL or create tables as UNLOGGED.
Small follow-up question: What about PostgreSQL vs MySQL vs MariaDB? Yes, MariaDB is used a lot now (including myself).. So it would be nice to include it in the test.
I was afraid to click on this video because I thought it might disagree with my cognitive bias in favor of Postgres. I'm pleased with the affirmation. Thank you!
It is a really good test! Both B+Tree database and a really big difference between I.O. IMO is more related with the storage engine strategy that postgres has implemented
i like the visualization you do, it's a great job. but i think the load scenario is too simple to judge the DBMS performance. pay attention to tpc-c testing and hammerdb.
Hi, Test should use full capabilities of the db, therefore postgres procedures and functions should be used to reflect the maturity levels of the used databases. To decrease the time of tests would recommend ram disk. Would be nice to see oracle xe vs postgres, with both using db objects.
I would be very interested in a non-bias comparison between FirebirdSQL and Postgress made in the same way as you did betweeN postgres and MySQL. I use FirebirdSQL on a daily bases and I didn't find any problems with it. I would like to know if changing the database in the future makes sense from some point of view. Thank you .
mysql uses repeatable read isolation level by default and postgres uses read committed.we might need to re evaluate this once again!The go sql client and infact postgres client would be using autocommit mode by default which is a transaction at the db level!So even for same query read or write mysql would need to do more work theoretically compared to postgres
This is not 100% clean benchmark as in Ubuntu packages these DBs has default settings optimized not for performance, but for low resource consumption. Playing with the configuration may significantly change the result.
MongoDB vs PostgreSQL. It would be really cool to see two different tests where first mongo tries to work with relational data by running joins, and then second model the data for PostgreSQL to be used as a document database with bson.
Nice work. It would be nice to know the versions used and a version that use the pgx driver. Also I wonder, how would they compare used with Vitess (yet another Go pearl) for MySQL and Citus for PG (and maybe TimeScaleDB and ParadeDb extensions as well). It is good the source code is in Go, I think it is allowed to get the source and use maybe even to modify it a bit (for pgx for example)?
Another cool video about the comparison. You are making wonderful progress; keep going, bro. I am looking for an SQL database to replace Postgres because it is so old now. Can you compare Postgres with Edgedb and some other SQL databases? It would be very helpful for me and, I think, for all your subscribers as well. Good luck, bro.
@AntonPutra could you upload the ddl for both mysql and postgres into the github repo? I did a similar benchmark on my own about 6 months ago, but focused on larger batches of operations and our results are wildly different.
The most important question. What was used in MySQL: InnoDB or MyIsam? If InnoDB, yeah, for me it's a garbage. Unfortunately it is the default in most situations. Use MyIsam and test again. MyIsam destroys InnoDB regarding speed. Row locking or journalling are much less important for me and I believe most of the others.
This is really great. I know it doesn't matter in terms of this test, but I was hoping you could share the hardware specs (or instance types) you had used. I know you mentioned 4cpu and ssds, but interested in the memory available as well.
After using both databases for decades, I will pick up MySQL every time over Postgres. Simplicity of MySQL is where the money is, e.g: to admin the databases, users permissions, import and export of data. Also querying MySQL from PHP or Java is easier than Postgres. Postgres doesn’t have a decent cluster solution like the Percona XtraDB 5.7.x. A few suggestions: what OS and hardware were you testing on? What versions of both engines? and did you tune-up both databases? If not, you are comparing Apples to Oranges. Also your data on both databases should be identical, no just the schema but the data.
Interresting, thanks. I'm interested in knowing how Postgres was configured with regards to random_page_cost and shared_buffers. And did you change defaults for values like checkpoint_completion_target file synchronization related parameters?
The problem with testing databases is that you really really have to understand their configurations. What if one is slower, because it's configured to flush data to disk more often, so it loses less data when the server goes down? You're basically testing default configurations, not actually databases. Another problem is how little data you've inserted. It probably fits into RAM. So again, not a realistic test. You have to insert gigabytes to make it meaningful.
@@AntonPutra I was thinking something like scaling up gradually from 10-1000+ connections, just to quantify the overhead of idle connections. It’s a useful metric imo because in serverless environments # of clients connected can scale up quickly
something funny is goin on with MySQL - the downward curve in iops while the inserts/second is increasing - implying inserts got easier as the inserts/second increased. The downward curve coincides roughly with capping out the connection count and the cpu spike at 21:50. Towards the end, the MySQL cpu also decreased, even though that was the highest QPS achieved.
Nice benchmark. What exactly happened with PG insert latency spike at the end 6:25? Note this is for a rather small record size, I wonder how they will scale with a large record size. I also wonder how they compare with more complicated join queries, compare their query plans, when they can no longer complete everything in memory.
I would want to see these two database handles few scenarios as here most of the things seems to be in favour of pg. 1. How both are handling updates (updating single column vs updating multiple columns) 2. How both are handling random queries done on both primary and secondary index. Random here to ensure that it mostly doesn't get cached in memory or if there a way to turn the buffer off in both. I feel like since mysql stores data in the leaf node of index tree, caching here will take a toll vs postgres which only stores indexed key to tuple reference allowing it to get more portion of tree cached in memory 3. How both are handling updates where we are updating column which belong to secondary index ps. i don't love any databases, i am a pragmatic. I really wanted to understand why read and writes are better in postgres than mysql as in terms of architecture i see at least read being better in mysql. Please share me any relevant articles for the same. I want to understand not only who but also how 🙏
another great comparison. thanks for the effort. btw, what happened to insert latency of postgres right at the end? it looked like there was a steep increase. cpu usage and disk write iops also looks degraded at the end
![[LIVE] : ONE ลุมพินี 88 | คู่เอก "ป้อมเพชร vs อัสลามจอน"](http://i.ytimg.com/vi/1ZqTVVbMgbo/mqdefault.jpg)
![BOWKYLION - วิงวอน (ex-change) [Official MV]](http://i.ytimg.com/vi/cLdnZWDaO-w/mqdefault.jpg)
► What should I test next?
► AWS is expensive - Infra Support Fund: buymeacoffee.com/antonputra
► Benchmarks: th-cam.com/play/PLiMWaCMwGJXmcDLvMQeORJ-j_jayKaLVn.html&si=p-UOaVM_6_SFx52H
surrealdb vs other databases
SQLite/libSQL vs Postgres, the in-process nature should make a difference for many use-cases.
Spring boot with virtual threads
timescale db vs clickhouse db
Javalin vs go
That's the most advanced open source database for you. I love postgresql.
Leave the "open source" away
@@twei__ why?
i'm working on an improved version of this benchmark based on the feedback i received
marry it
This is shocking. I never knew that Postgres was such a clear winner for most of the time.
I knew it, that's why I hate using tech stacks that are "bad"
@Kingromstar Same. I'm a relative newbie to the tech world. But I've known postgresql is perhaps the best generic DBs out there. At some point, you'd want to move to self-hosted ScyllaDB, especially if you need sub-millisecond ops at scale. But, if you're building something at that scale, you'd know the details (if and when). For most starter projects, postgresql is the best choice.
What I've also learnt is that it's equally important to vacate your cloud provider timely, otherwise you'll be paying through your nose to do so later.
Frankly speaking it is not shocking, knowing what kind of test coverage and query optimizations postgres has
@@shubhamkukrety6355ScyllaDB should NOT be your go-to at scale. Nothing wrong with it per se, but once you get to the scale where something like Postgres hits its limits-which is a bigger scale than 99.9% of projects ever reach-you should not be blindly reaching for any one off-the-shelf solution.
At high scale you should be choosing the niche solution that most closely fits your requirements, and those requirements will NOT be what you expected at the start. Be flexible and bend to where the numbers lead you.
@@mileselam641 True. This is the caveat of "if" I didn't delve much into. Pardon my ignorance.
MariaDB v PostgreSQL would be a great comparison I think, not many opting to run MySQL over MariaDB these days, and I imagine it should be a bit of a closer race.
It would be more correct to compare MariaDB vs open source Citus Data(PostgreSQL + extension) or its commercial version Azure Cosmos DB for PostgreSQL.
Is MariaDB that different from MySql though ?
I'm also curious to know.
I'm also curious to know.
@@jonnyso1 Not that much. Pretty much identical except licensing.
I think one that would interest me a lot is MS-SQL vs PostgreSQL. I personall have worked with both but I vastly prefer PostgreSQL
It is tricky, because in reality you should utilize all db features (extensions in pg, OTLP tables in Azure SQL and whatnot). And realistic benchmark is challenging to make :)
@@MikhailAksenovV and MSSQL EULA stating you cannot do so without M$'s written approval
mssql (or sybase) must be the only DBMS with a worse dialect than mysql. which is hard, the one of mysql is already really bad…
@@Robert-Goracle's pl-sql is worse than mssql's T-Sql.
@@IanWilkinson-SgtWilko how? The imperative part is almost like Oberon, Modula or Ada. wordy? yes, but comparatively easy to read.
the SQL part looks like normal, standard SQL.
not some completely arbitrary other thing. eg: Oracle uses “ for identifiers, not strings, like actual SQL, only ‘ is for strings and || is for concatenation.
mssql is like a hipster trying to make it all different just because…
if you limit yourself to SQL then targeting Oracle, postgres, Firebird and even sqlite to an extent will be fairly easy.
mssql is just different for no reason other than the hipster dialect they bought back then (sybase)
Testing SQLite against any of those would be very interesting. I know that SQLite is a different "animal", and it does not scale horizontally, but I found in most of my use cases that it provides more than enough performance (actually much more), and it is incredibly easy to operate and maintain (less moving pieces).
In addition, there are already solutions for availability and replication which are simple. So, I am using most of the time SQLite in production.
There are many tools that let you horizontally scale it now.
@@boot-strapper Yes, it is becoming a very attractive proposition.
However, given that for "typical" applications (e.g. web with a db backend) the performance for writes with SQLite is limited by the disk (no network hop between the application an the DB), I do not normally require horizontal scaling for performance reasons.
Below 100 tx/sec (writes), SQLite blows away the "traditional" DBs, and if I hardly need more than that.
Availability is another thing, but it is also relatively simple to achieve with SQLite these days.
I'm also curious.
Also it would be great to test different "flavours" of SQLite. For example SQLite with LMDB backend.
@@jesusruiz4073 oh yeah I agree. I wrote a mock data service that generated mock data. It was able to generate 3million records and insert them into SQLite in
Am I the only one who didn't see MySQL config? What is the table engine for MySQL?
Not sure if this is still the case nowadays, but MySQL used to come with really tiny default parameters for INNODB engine. And the first thing you do with MySQL is to set those params like innodb_buffer_pool_size, etc. (there are a bunch of them) I. e. nobody uses MySQL out of the box. You have to set those params to reflect your load.
The easiest way were running tuning-primer or MySQLTuner script after running the workload. You had to do this several times.
I was looking for this. Well spotted
Exacly. Also if he use right engine mariadb instead of mysql. And enable query_cache it would crush postgress.
@@michalszalapski The query cache not only wouldn't help here, but it got removed in MySQL 8 because it was terrible. It's been disabled by default since MySQL 5.6 anyway, for that same reason. It would not be a thing in MySQL 9.
@@neothermic1 It would help here and ye, thats why Im not using mysql since 5.6. Never though ppl still use mysql... Mariadb query_cache works very well and is mandatory for high load clusters(unless u have no clue how to maintain it).
@@michalszalapski From mariadb's docs: "It does not scale well in environments with high throughput on multi-core machines, so it is disabled by default." - It would then raise the question as to if it can help here; these are about the same reasons it was disabled and removed from mysql itself.
But I think we're in agreement that without seeing the config of the mysql server, it's being tested at a huge disadvantage as the defaults are awful.
Pls also tests MySQL vs MariaDB.
The Results should be pretty simular but it would be interesting to see
and maybe percona
Agree!!
up! this is something that bothered me when i was still using WordPress
will do!
The test seems a bit too one sided, the main architectural difference in MySQL vs Postgres is MySQL write operations (write, update, delete) are amortized so data is always "balanced", while Postgres postpones it until VACUUM and it is prone to degradation, when vacuum starts under load. It would be nice to show how these databases will compare under mixed workload, when records are inserted, updated and deleted at the same time, and data that is being changed should be in the index to test tuple bloating.
Also can you publish schemas for databases?
That can be seen in the IO/s in the graph on right side, second from the bottom. MySQL is doing a hard fsync to disk or something along the lines. PGsql doing 1/3 of the disk write, and you can't cant cheat on disk writes. They either happen or not. So PGsql is running with a less durable data model in this test
Don't forget hot updates, are 10 times faster than regular in pg, MySQL time is over
@@opensourcedev22 Not true. Postgres writes to disk on each commit. It uses a copy on write model that lets it write asynchronously to disk before committing, but the commit step at the end of the transaction is synchronous and atomic.
The default isolation level in postgres is much stronger than the default in mysql, since Postgres read committed is Monotonic Atomic views + causal consistency, while mysql repeatable read does not actually fulfill the ISO sql read committed standard in all situations while still being able to deadlock
@@BosonCollider No need to copy paste chatGPT into here brother, just look at the 3:40 mark. He's running 290 Q/s, and pgsql is doing 309 IO/s... And MySQL is at 1.4k
I admit that personally, I am unsure what low level pgsql is doing in this specific teat, but disk IO can't be cheated unless it's not being used. So, are you honestly willing to admit pgsql does 300 Q/s and with ONLY 309 IO/s ?
I think a database configuration is needed here with all settings posted to be sure
@@opensourcedev22 There is no way Postgres is not fsyncing after every write. They may be doing vectored IO, batches or whatever.
Already commented about some technical stuff, now about the design: for testing SQL databases I would like to see a slightly more complex set of operations, UPDATE and DELETE are missing, having more JOINs would be more realistic, where clauses that don't use an index or different types, sorting and aggregation would also help to highlight different strength and weaknesses.
Maybe something like a simplified online shop with shopping cards (adding/removing items, changing amounts), orders and reports like the top 100 customers or articles in a given time frame. And also very important: transactions. In other words some kind of CRUD app that does everything in SQL. A lot of work and preparation though.
like the idea, would be nice if it can be implemented
there are plenty of standard workloads for these tests like TPC-C
thank you for the feedback. i'm working on improving this test based on all the feedback i got. the suggestion about an online shop is very interesting.
true, the test must reflect real world cases, so the best test design would be based on an application and reflect its transactions
2 Years ago i was tested on bare metal mysql vs postgresql using 0.9 TB file with real events. Nodejs was used, both databases used default configs. There was ~90% of selects, rest was IUD. With low level of concurrency mysql was winner, with > 30 workers postgre on a top. I played with database structure and other things, and found, than there is no clear winner or loser. Summary: both mysql and postgre have own advantages/and disadvantages, and database selection is depends on many factors.
i'm working on an improved version of this benchmark based on the feedback i received
love your benchmarks, queep the good work!
thank you!
These videos are always so clean. Thanks for sharing this, it's really valuable information for scaling projects.
thank you!
Thank you for the colors. This is very clear to see the graph.
thanks!
Did you also consider fine tuning the database configs, since the default configurations are not optimized at all. Think about innodb_buffer_pool_size for Mysql. And shared_buffers in PostgreSQL.
"i'm now 😊 it seems that using default settings for database testing was not the best idea.
If you need fine tuning the database right after installing it, it's probably not the right and best database to choose
@@moverecursus1337 This is fundamental difference between database product, and database project.
In case of a product, developer might assume, that database will run on a dedicated server to make the best use of expensive per core licenses.
In case of a project, such assumption cannot are not justified, database might run on a dedicated server, or it might be colocated with web application. In the latter case there must be a way to limit database memory usage, so application server won't starve.
I liked this comparison between databases! I like the idea of seeing more databases on these videos.
thanks, will do! i need to improve this one based on the feedback i received
Why postgres at 4:05 had negative value in disk usage?
Must be freeing up space
@@jricardoprog No, that's not possible. It's clearly labels as "Database size", so it's not possible to say, that space was freed up...
I suppose it is calculated from VPS free space, like current_disk_usage - predefined_disk_usage at start of tests, so maybe rotating some large logs caused this or postgres autovacuum of some previous data? although postgres autovacuum does not free disk space by default, right?
I guess he takes a baseline and calculates size by subtracting base from current value. Around 7:35 we can see the graph goes low and then goes up. It means the baseline may be wrong. But the minimum value was around -110 MB but end value was 4.9 GB which makes it 5 GB - postgres vs 18.7 GB - mysql
Flaw of the benchmark environment set up. The graph jumps to negative few hundred megabytes for postgres, so clearly it wasn't doing `df` on the $PGDATA directory, but used a constant value subtracted from the current free space. Thus it could have been anything on the server affecting the results, or postgres log rotation, or dirty postgres data folder having some leftover data in pg_wal/ that got removed naturally. As others said, autovacuum doesn't reclaim free space.
In my experience with MySQL, charsets have a huge impact in database size and performance if not correctly specified. i.e utf8mb4 vs latin1 will show a big difference in database size, also in performance if the client is using different charsets than the server, it slow down every operation involving text because it needs a "translation".
So just check if database / table / row charset and collation is the same as the go client.
That's because UTF8 uses 2 bytes/character vs 1 byte/character for Latin1.
thanks for the feedback
I knew that PostgreSQL was booming, now I understand why. Thanks for this work.
Nice benchmark! A couple of things:
1 - I believe that Postgres uses a process per connection. Are you making sure to measure the combined CPU and memory usage of the 'main' process along with all of the connection processes? The reason that this is relevant is that MySql is thread based, so the main process should show the overall CPU/Memory whereas with Postgres you might only be looking at the DB process which is kind of cheating since the connection processes also use CPU/Memory.
2 - It would be cool to see a Java benchmark using the latest Java as well as the latest JDBC drivers for each. Be sure to use a connection pool like Hikari if you do.
Please check postgres vs sql server.
lol
Oracle will be cool as well
I want this as well. Forced to use sqlserver at work and wanna hate my life even more by seeing the comparison lol
I'd love to see this benchmark too. I've had people tell me MsSQL is faster than PostgreSQL but I have my doubts.
@@hypnoticlizard9693 Why do you think like that? As far as I know, SQL Server is more advanced. I watched CMU database lessons in TH-cam, the professor was saying that. However, I'd use PostgreSQL if I had a choice as well, cause no license cost.
Thank goodness you blurred the names on those graphs not to spoil anything! I cannot possibly figure out which is which otherwise. Their names are too similar, and neither has associated colors.
😊
chill, I was already sold on Postgres lol
i'm working on an improved version of this benchmark based on the feedback i received
How the MySQL is configured?
How big the buffer pool size and redo log size you used for your test?
These two are the most important config for high perf innodb instance.
The default values are very low for production usage.
When running mysql instance, the buffer pool should be accounted for as much of RAM as possible. From 60% to 80% of the RAM (bigger RAM the percentage should be bigger).
This! I see no mention of the configuration that's been done to either database server. There's a few more too, such as what was innodb_write_io_threads set to? Did you set innodb_io_capacity and innodb_io_capacity_max to roughly match what your storage medium is? What *is* your storage medium? (HDD? 2.5" SSD? M2 NMVE? Enterprise U2 drive? these differ drastically!) The defaults are terrible, but the defaults are run-everywhere defaults. You ALWAYS want to configure a mysql config to where you're deploying the server to.
mysql sucks, it has always sucked. stop using it or deal with it
my mistake was not paying much attention to the configuration settings. i'm collecting feedback right now and will update this benchmark.
thank you, makes me feel better with my postgres preference
😊
Why did PostgreSQL disk usage go negative for a while?
i didn’t use dedicated disks for data storage and used the main os disk. when i installed mysql and postgres, the differences were 6 and 4 gigs, so i just subtracted that value from the graph before the test. i'm working on an improved version of this benchmark based on the feedback i received.
What type of tabes did you create in MySql? MyISAM or InnoDB?
What tuning did you apply for this workload? Is this on completely untuned default settings on everything for both databases?
You are awesome!!! Please make a between PostgreSQL and Microsoft SQL Server!! I wasn't expected that.
I'd love to see that, however its unlikely, microsofts EULA states benchmarking results can't be disclosed without their approval
@@williamclark4143 True, but "Miachelsoft Sea Kwell DB "is a unknown database just leaving beta **wink wink**
thank you! will do!
Wow, I now love PostgresSQL. Definatly switching over from Mysql and MariaDB to PostgreSQL in my next project. Thanks.
glad it was useful 😊
For the read iops graph for postgres being stuck at 0:
- check the query - maybe it is measuring write ops instead, especially if the second dashboard was created by copying + tweaking the first dashboard - super easy to forget to replace "write" with "read"
- maybe postgres is at 0 because it managed to fit the entire dataset into the page cache? Depending on how memory is measured, it may not include the OS page cache, which contain the contents of recently-used files. If every read for postgres was a page cache hit, the reads would never go to the disk, and you'd get 0 IOPS. 1KB records * 64M would start to fit in memory on a large VM, especially if the DB compresses the data before writing it to disk (which, if pg compresses and MySQL doesnt, also explains the large difference in DB size and write IOPS!)
The page cache also isnt cleared by restarting the DB - the cache is in the OS after all, and is just caching file contents, for every process. To clear this, you'd have to run a command to drop the page cache, or reboot the VM in between tests.
Or - before the read test, try writing at least 8-10x the VM's RAM in records (calculate it by adding the size in bytes of each field on every table, then multiplying by the number of records - not by looking at disk stats!) to ensure that the cache cant be big enough to fit the entire data set, regardless of compression
thank you for your feedback. i'm working on an improved version of this benchmark that will include all the feedback i've received so far
How about postgres jsonb vs mongodb?)
Those are 2 completely different databases with different usecases. Try doing complicated joins in mongodb or any relational data stuff, you simply shouldn't do that.
@@qizott6442 Not really. The question is not 'how good mongodb can do relational stuff', it's 'can postgres handle JSON as good as mongodb'. PG is very flexible and it's interesting to see how it compares to mongo.
@@qizott6442 relational stuff is completely doable with mongo aggregates, shouldn't !== cannot, and a lot of projects that have picked mongo initially, because it's somehow easier, end up with complex "joins" in mongo. It's well known that mongo's performance is terrible at this. But it's interesting to see how postgres can beat mongo in mongo's game.
I've seen a benchmark from PG developers where they demonstrated that their db beats mongo on JSON operations. That was a response to a benchmark published by mongo where they run PG installed from Ubuntu with its default settings.
@@antonkuranov I guess that's why benchmark from 3rd party on this would be interesting. Truth being told I wouldn't be surprised if PG really did beat mongodb at it's own game ;P
Really love your benchmark series.
thank you!
Do you have a video on how you setup everything from scratch, and how you collect metrics
i'll make a tutorial soon
I'm not DBA at all. But there were stand many times (not by me, of course), that MySQL optimized more for reads and for OLTP operations when Postgres for OLAP ones. In addition, MySQL is better for horizontal scaling; and Postgres for vertical scaling. It looks like the real difference between these DBs will be clear when we'll have a huge amount of data, loaded in DB (so we already apply some scaling, i.e. replicating, sharding, etc.), as well as mixed requests (i.e. SELECT/INSERT/UPDATE/DELETE at the same time). For such scenarios as in the test (store for analytics records) choosing either of these two bases looks the same and is equally wrong - something like MongoDB or Cassandra looks more preferable since it would scale for used operations much better.
A lot of those statements are parroted from benchmarks over a decade old. I haven't seen anything recent where MySQL can match Postgres. Horizontal scaling was the most recent thing that Postgres changed the story on, but read performance has been better in Postgres for a number of years now in my experience.
@@DanWolf-codeNerd i think any of us would love to use open source and fast Postgres over MySQL if Postgres beats MySQL in literally ANY aspect. We just need some numbers from exhaustive tests, that prove the claims. Do you have such numbers, or links to such tests? (Surely, a 10-minute test for one simple insert; then one simple select, as this one, is not enough.)
Mongo is a very different DB since you do not use SQL and as soon as you need to join different tables together MongoDB is quite a lot harder to use efficiently.
I have been using it a lot and for the right use cases its blindingly fast but my opinion is that if you have anything but separate lists of objects and need to use only a single type of DB, go with an SQL.
It not necessarily the fastest in many cases BUT it lacks any serious weak spots that can derail a project.
If you need to do more adhoc queries over multiple different data sets, mongo can be very hard since you most likely need to load things into memory and do the work in your own code and except for small databases where everything fits in memory, that is very hard to get any performance in.
On the rest I totally agree, MySql are both good database with slightly different strengths which even for quite large projects are going to be almost irrelevant.
For innodb you really need to make sure of the settings.
@@AlexanderBorshak I don't, but I also don't need benchmarks. I deal with an enormous throughput of data in my job, with compute and database combined costing over $100k/month on AWS for just my team, but if we had to scale upwards, we would be able to continue to scale for a very long time. MySQL might be able to as well, but since I know Postgres has so much headroom, a theoretical difference doesn't mean much. If you were to work for a larger company than mine, you could probably go even further and write your own custom modules if you eventually ran out of runway for either database. Both are extremely mature. I have had experience with MySQL performance. I'm certain that was probably entirely or nearly entirely about poor design in that case, not about the database.
thanks for the feedback. i'm working on improving this test
I love seeing benchmarks between databases. We need more of them. Folks should keep in mind that schema design and use cases make a bigger difference than engine in most cases, just like data structures and algorithm choice makes a bigger difference than the programming language. There are also so many "shortcuts" that each engine implements for different scenarios.
It's still good to see comparisons with apples-to-apples. It's a shame the proprietary db vendors don't allow performance comparisons like the open source DBs allow.
thanks! i'll do more of them soon
Thank u for this comparison, even though most of us probably already knew postgres was going to win. I'm not sure if u already did this but I'd love to see how Postgres compares to MongoDB, the two GOATs of the DB world. 😀
thanks, will do mongo soon
Very revealing benchmark, love your videos!
Would love to see a benchmark on nodejs ORMs
noted
You didn't say anything about the tuning of the database servers. Tuning it well is primordial for performance ! You need to tune memory, and disk buffering among other things.Also you said nothing about the indexes that were used for the tests. Without those details, the comparison is pretty meaningless because default tunings usually aren't production tunings but development tunings. They are very conservative and limit the performance greatly.
In general, you should ask for advice from the general public before performing your benchmarks, because sometimes, you make very naive assumptions that are simply incorrect and completely distort the results.
@marcsfeh OP's point was that he should ask for advice before releasing a video with potentially mis-analyzed data in it.
@@flamingspinach Well no...even untuned, the test is relevant ! It means that you if you can't or won't put the effort in "tuning" go for Postgres it gives you best likelihood for best performance with out-of-the-box config
@@dextrowim "It means that you if you can't or won't put the effort in "tuning" go for Postgres it gives you best likelihood for best performance with out-of-the-box config"
No it does not - it only tells you that on that particular hardware and OS, with his specific DB setup and that particular workload Postgres is overall better.
But there are hundreds of unaccounted variables at play here. If you use the default settings than ANY claims you make about performance are already wrong.
my mistake was not paying much attention to the configuration settings. i'm collecting feedback right now and will update this benchmark.
Thanks for doing this!
I would have guessed that Postgres would take more space due to MVCC. Weird.
It could have, except MVCC wasn't really used if it was just 80M individual inserts on autocommit. Until tuples are updated or deleted, or fillfactor is configured for a table, MVCC has near zero overhead costs.
(The longer answer is that ever tuple has around 16+8B of invisible metadata in table heap, another I think 26B for page header (one page by default = 8kB) and some more in the visibility map file.)
working on an improved version of this benchmark, including all the feedback i've received so far.
What versions of the software were you testing?
The results are very interesting.
prometheus + grafana
using a binary copy with pgx will allow you to insert jaw dropping numbers of rows per second.
and it only re-evaluates indexes once when you say complete.
it’s slightly slower cousin allows you to stream csv directly into or out of the psql cli client.
thanks for the feedback!
Database is very sensitive to removing records from the database. So additionally to insert and fetch will be good to deleting and updating operations.
yes i'll do full crud test next time
I am missing some important details. Did you use the innodb storage engine and what was the setting for innodb flush at trx commit? And was a similar configuration used for postgres regarding transaction safety?
yes, i used innodb. as far as i understand, foreign keys are only supported with that storage. in this particular test, i mostly used defaults, and i have an updated video with full CRUD and configuration settings on git. please take a look. Round 2 - github.com/antonputra/tutorials/tree/main/lessons/216
Hey Anton, I have been watching your benchmarking series from the start and love them!
I might have an idea back on the language performace... it would be interesting to see the power of fully-fledged JVM vs something like Go or Node? Last comparison was using Java compiled to binary without the JVM.
The memory usage will be massive but I wonder if the JVM at peak performance (after warm up) can outperform other GC languages in terms on Number of Requests before failure and the Response Time.
Something like Amazon Corretto or even OpenJDK would be interesting
ok, thanks for the feedback. i'll come back to java soon
I'm assuming mysql was using InnoDB engine? MyISAM is practically deprecated but it'd still be interesting to see this comparison using it and also vs Aria engine from mariadb
i used default settings
I think you should be careful with default configs.
InnoDB by default has very conservative configs. Good for testing but not sure its good for production.
@@AntonPutra
Love PostgreSQL.
For best performance, it's recommended to configure PostgreSQL with "ALTER SYSTEM SET various parameters" - eg.shared_buffers, effective_cache_size, work_mem, wal_buffers, random_page_cost, max_worker_processes and a few more.
If you don't care about ACID and want maximum write performance you could also disable WAL or create tables as UNLOGGED.
thanks, i'm working on an improved version of this benchmark based on the feedback i received
I never thought there's that huge difference between PostgreSQL and MySQL
i’m working on an improved version of this benchmark based on the feedback i received
Small follow-up question: What about PostgreSQL vs MySQL vs MariaDB? Yes, MariaDB is used a lot now (including myself).. So it would be nice to include it in the test.
yes, i'll do MariaDB, but first, i need to improve this benchmark
Gran trabajo! Gracias por todos los aportes!
thanks!
Love your tests. Thanks for all effort put in ❤
PS: SQLite vs any database
noted!
Did you do any price compairison? As PostgreSQL typically is more expensive from my hosting site
well, efficiency translates to cost. the more efficient your database or service, the fewer resources you need to run it
Nice benchmarks and nice video. For me the performance aspect isn’t the absolute most important one. Ergonomics matter more. Keep it coming though 🙏🏻
thank you!
Thank you for sharing these insights really helpful.
Appreciate your efforts in making this vedio.
Why Disk Reads IOps was O for postgres SQL?
thanks, i'm working on an improved version of this benchmark based on the feedback i received
I guess I'm addicted to this channel now...
😊
Nice test. I think might be tested on the join and aggregation commands.😊
thanks, working on an improved version
I was afraid to click on this video because I thought it might disagree with my cognitive bias in favor of Postgres. I'm pleased with the affirmation. Thank you!
It is a really good test! Both B+Tree database and a really big difference between I.O. IMO is more related with the storage engine strategy that postgres has implemented
i like the visualization you do, it's a great job. but i think the load scenario is too simple to judge the DBMS performance. pay attention to tpc-c testing and hammerdb.
i'm collecting feedback right now and will update this benchmark to include CRUD operations and some configuration changes.
Hi, Test should use full capabilities of the db, therefore postgres procedures and functions should be used to reflect the maturity levels of the used databases.
To decrease the time of tests would recommend ram disk.
Would be nice to see oracle xe vs postgres, with both using db objects.
thanks for the feedback
I would be very interested in a non-bias comparison between FirebirdSQL and Postgress made in the same way as you did betweeN postgres and MySQL. I use FirebirdSQL on a daily bases and I didn't find any problems with it. I would like to know if changing the database in the future makes sense from some point of view. Thank you .
ok just added to my list
mysql uses repeatable read isolation level by default and postgres uses read committed.we might need to re evaluate this once again!The go sql client and infact postgres client would be using autocommit mode by default which is a transaction at the db level!So even for same query read or write mysql would need to do more work theoretically compared to postgres
So postgres allows more concurrency out of the box compared to mysql
@AntonPutra you might need to re evaluate this using same isolation level for both
thanks for the feedback!
Can you record a video describing how do you configure monitoring setting agents configuring graphs?
yes will do in the future
This is not 100% clean benchmark as in Ubuntu packages these DBs has default settings optimized not for performance, but for low resource consumption. Playing with the configuration may significantly change the result.
my mistake was not paying much attention to the configuration settings. i'm collecting feedback right now and will update this benchmark
MongoDB vs PostgreSQL. It would be really cool to see two different tests where first mongo tries to work with relational data by running joins, and then second model the data for PostgreSQL to be used as a document database with bson.
thanks noted!
Nice work.
It would be nice to know the versions used and a version that use the pgx driver. Also I wonder, how would they compare used with Vitess (yet another Go pearl) for MySQL and Citus for PG (and maybe TimeScaleDB and ParadeDb extensions as well).
It is good the source code is in Go, I think it is allowed to get the source and use maybe even to modify it a bit (for pgx for example)?
Awesome helpful comparison
thank you!
Thank you for these tests really great!! can you consider benchmarking cassandra with any nosql database please?
yes, i'll do cassandra in the near future!
Another cool video about the comparison. You are making wonderful progress; keep going, bro. I am looking for an SQL database to replace Postgres because it is so old now. Can you compare Postgres with Edgedb and some other SQL databases? It would be very helpful for me and, I think, for all your subscribers as well. Good luck, bro.
thank you! yes, i'll do more database testing in the future
Really beautiful testing ❤
I'd appreciate a lot if you can do exact testing among just PostgreSQL's versions, 15, 16 and 17
thank you! ok, it may be interesting
@AntonPutra could you upload the ddl for both mysql and postgres into the github repo? I did a similar benchmark on my own about 6 months ago, but focused on larger batches of operations and our results are wildly different.
yes will do for the next improved test
Add tests for reading by secondary index. Postgres and MySQL do it in a different way.
And updates. It will trigger Postgres vacuum.
thank you for the feedback. i'll add a full CRUD test in the improved version of this benchmark
The most important question. What was used in MySQL: InnoDB or MyIsam? If InnoDB, yeah, for me it's a garbage. Unfortunately it is the default in most situations. Use MyIsam and test again. MyIsam destroys InnoDB regarding speed. Row locking or journalling are much less important for me and I believe most of the others.
This is really great. I know it doesn't matter in terms of this test, but I was hoping you could share the hardware specs (or instance types) you had used.
I know you mentioned 4cpu and ssds, but interested in the memory available as well.
Are there other differences, which might be important ?
Like transactional robustness in case of powerloss, and other robustness attributes
After using both databases for decades, I will pick up MySQL every time over Postgres. Simplicity of MySQL is where the money is, e.g: to admin the databases, users permissions, import and export of data. Also querying MySQL from PHP or Java is easier than Postgres. Postgres doesn’t have a decent cluster solution like the Percona XtraDB 5.7.x. A few suggestions: what OS and hardware were you testing on? What versions of both engines? and did you tune-up both databases? If not, you are comparing Apples to Oranges. Also your data on both databases should be identical, no just the schema but the data.
thanks for the feedback!
Would be better if you could add events on the graph like added abc load etc, on the time axis. This could help understand capacity of the system
ok noted
MariaDB vs Postgres would be fair comparison. MySql is old and not maintained as well as MariaDB.
He and his sponsor already know that
Interresting, thanks. I'm interested in knowing how Postgres was configured with regards to random_page_cost and shared_buffers. And did you change defaults for values like checkpoint_completion_target file synchronization related parameters?
my mistake was not paying much attention to the configuration settings. i'm collecting feedback right now and will update this benchmark.
This is a good comparison! How about postgres vs Oracle DB? Can you do similar testing for them.
will do in the future on oracle cloud
This Aurora Posgres/MySQL (AWS Aurora V3) or MySQL/Posgres directly?
ok noted!
The problem with testing databases is that you really really have to understand their configurations. What if one is slower, because it's configured to flush data to disk more often, so it loses less data when the server goes down?
You're basically testing default configurations, not actually databases.
Another problem is how little data you've inserted. It probably fits into RAM. So again, not a realistic test. You have to insert gigabytes to make it meaningful.
thanks, i'm working on an improved version of this benchmark based on the feedback i received
I’d like to see how they compare when it comes to # of connections
not sure if i get the question. they have the same 100-connection default, which can be adjusted based on the hardware you use
@@AntonPutra I was thinking something like scaling up gradually from 10-1000+ connections, just to quantify the overhead of idle connections. It’s a useful metric imo because in serverless environments # of clients connected can scale up quickly
Be good to know if this is Mariadb, regular oracle mysql or percona MySQL. All makes a difference. Great job on these videos !
thank you!
something funny is goin on with MySQL - the downward curve in iops while the inserts/second is increasing - implying inserts got easier as the inserts/second increased. The downward curve coincides roughly with capping out the connection count and the cpu spike at 21:50. Towards the end, the MySQL cpu also decreased, even though that was the highest QPS achieved.
i'm working on an improved version of this benchmark based on the feedback i received
Why VARCHAR(256)? The standard for small indexable string is 255 or less if possible
ok, i'll adjust for the next bench
Wow!!
Thanks for this comparison.
i’m working on an improved version of this benchmark based on the feedback i received
@@AntonPutra wow, I would be watching too.
Nice benchmark. What exactly happened with PG insert latency spike at the end 6:25?
Note this is for a rather small record size, I wonder how they will scale with a large record size.
I also wonder how they compare with more complicated join queries, compare their query plans, when they can no longer complete everything in memory.
I want to know about PHP versions performance. Thank for your videos!
thanks, will do php soon
How about a comparison between MySQL vs. MariaDB?
noted!
I would want to see these two database handles few scenarios as here most of the things seems to be in favour of pg.
1. How both are handling updates (updating single column vs updating multiple columns)
2. How both are handling random queries done on both primary and secondary index. Random here to ensure that it mostly doesn't get cached in memory or if there a way to turn the buffer off in both. I feel like since mysql stores data in the leaf node of index tree, caching here will take a toll vs postgres which only stores indexed key to tuple reference allowing it to get more portion of tree cached in memory
3. How both are handling updates where we are updating column which belong to secondary index
ps. i don't love any databases, i am a pragmatic. I really wanted to understand why read and writes are better in postgres than mysql as in terms of architecture i see at least read being better in mysql. Please share me any relevant articles for the same. I want to understand not only who but also how 🙏
I love this channel. Great work my friend 👌
thank you!
What would be Oracle vs postgreSql ?
i may do it in the future running in oracle cloud
another great comparison. thanks for the effort.
btw, what happened to insert latency of postgres right at the end? it looked like there was a steep increase. cpu usage and disk write iops also looks degraded at the end
hard to say, but i'm working on an improved version of this benchmark based on the feedback i received
what software are you using for the graphs and bench marking.
prometheus + grafana
Wow. Fantastic. Can you please do SQLExpress (latest version?) Vs postgress?
noted!
How impact has Redis to this scenario? Or in general, what difference makes Redis at all?
Also, it might be worthwhile considering a replicated setup.
i may do it in the future, it's just will be expensive
Can you compare ElysiaJS vs Fastify vs HonoJs speed test?
will do soon
Serious question: could you try Posgres Vs Sqlite? I would be very keen to watch that
yes, but first i need to improve this one