Opening Old Wounds - Why Uber Engineering Switched from Postgres to MySQL
ฝัง
- เผยแพร่เมื่อ 25 มิ.ย. 2024
- An article from 2016 caused a lot of discussions in the software engineering community. We bring it back and open old wounds and discuss it again. This is when uber moved from Postgres to MySQL.
0:00 Intro
3:00 Problems with Architecture of Postgres
4:00 Postgres on-Disk Format
9:45 Replication
13:19 Write Amplification
16:44 Replication Bandwidth
21:16 Data Corruption
24:00 Replica MVCC
31:30 Postgres Upgrades
33:00 MySQL on-Disk Format
37:00 MySQL Replication
40:00 Connection Handling
Resources
eng.uber.com/postgres-to-mysq...
news.ycombinator.com/item?id=...
🔥 Members Only Content
• Members-only videos
🎙️Listen to the Backend Engineering Podcast
husseinnasser.com/podcast
🏭 Backend Engineering Videos
• Backend Engineering (B...
💾 Database Engineering Videos
• Database Engineering
🏰 Load Balancing and Proxies Videos
• Proxies
🏛️ Software Archtiecture Videos
• Software Architecture
📩 Messaging Systems
• Message Queues & PubSu...
Become a Member
/ @hnasr
Support me on PayPal
bit.ly/33ENps4
Stay Awesome,
Hussein - วิทยาศาสตร์และเทคโนโลยี
Check out my udemy Introduction to Database Engineering course
database.husseinnasser.com
Learn the fundamentals of database systems to understand and build performant backend apps
This guy out there giving his 15 years of knowledge for free is a blessing for me. Can't thank you enough. No more afraid of BACKEND. It's all logical, no magic.
The points raised:
- Having indexes is great, but having too many indexes is a nightmare.
- Even Postgres supports Statement based replication and WHL replication.
- Threads (used by MySQL for transactions) and processes (used by Postgres for transactions) have similar performances now a day.
- For MVCC, Postgres AutoVaccum vs MySQL rollback segment - Both have their drawbacks, MySQL rollback segment - has the extra overhead of looking up.
- Both Postgres and MySQL can have bugs but are fixed immediately and is not a reason to switch.
- Upgrading Postgres is still a pain.
-Processes are far less scalable than threads.
-One might have to have many indexes, postgres' data layout and index modeling just don't go hand to hand with it. That's not a problem with the engineering of Uber, It's a postgres' one.
It's just a matter of architectural decision making. Primarily, making reads faster at the cost of write amplification
@@kap1840 processes are much safer, unlike the threads you can not manipulate a process and it's different in other databases. and keeping many db indexes is a wrong architecture design decision to make. And many things have changed since this article has been written.
Colocation = when you buy your own servers (for technical reasons) but don't want to build your own data center instead you rent space, connectivity/bandwidth in a 3rd party data center. Great content by the way, keep it up.
Thanks I didn't know that!
This is where one of your Pied Piper boxes will go
finally!! been waiting for this. thank you so much, hussein
This is by far one of the best engineering podcast/video/ I have ever encountered. Thank you so much for putting so many hours researching and then putting a podcast for us.
Glad you enjoyed it! thanks for your comment Sumit!
There is so much to binge-watch. I have joined your channel as a paid member now :)
this channel is a goldmine, deep knowledge being dispensed here, a rarity on youtube. thank you
I listen to your videos while rocking my 4 month old baby to sleep! Your videos have really reignited my passion for backend engineering. Thank you and keep it up!
Thanks and congrats for the little guy or gal!
and now your baby will slowly become backend engineer like you :)
It is always so wonderful to hear you, thank you, keep awesome .
Thanks for listening appreciate the support :D
Interesting to hear about your pick on this. My jaw dropped when i read their post on this some 2 years ago.
Great selection of content Hussein. Thank you so much
Glad you like them!
Many great comments added earlier, but another compliment for your expetise and enthusiasm about the subject. Thanks, excellent content!
Your content is very different and deep. I'm learning new things from you. I watch one video every day.
Enjoy and take your time, so much content 😊 do suggest feedback or new content, this is welcome
Don't know how I stumbled on this video but I remember this article! Great deep dive, just subscribed 👍
Read the article guys.. He just breeze through them. After reading the article I realised they had some serious issues with the database. They were at point where there is no other choice but to switch to another database.
Great content. Enjoyed it thoroughly. The best part is that it pumps up my interest in database technologies. Keep it up!
Thanks 😊
I just found this channel today... This is the first video I watched from you... And based on the comments, I think I just found a gem hidden in the youtube algo... This is awesome. I'm currently making a hobby project, and I want to use open-source as much as possible and I need at database for it. I know Postgres but I really want to go deeper before I use it (currently using SQL Server in our office)... By the way, thanks!
Subscribed. The best podcast I've ever heard
Don’t have time to sit and watch? Consider listening on my podcast on your favorite player
Check out my Backend Engineering Podcast and make sure to rate it
Apple podcasts
itunes.apple.com/us/podcast/igeometry-podcast/id1330350799?mt=2
Google podcasts
www.google.com/podcasts?feed=aHR0cHM6Ly9hbmNob3IuZm0vcy8xZWI2ZDE0L3BvZGNhc3QvcnNz
Spotify
open.spotify.com/show/55pPBm0l75K28dIqoHIQIc
Breaker
www.breaker.audio/igeometry-podcast
Castbox
castbox.fm/channel/id1162406
Google Play Music
playmusic.app.goo.gl/?ibi=com.google.PlayMusic&isi=691797987&ius=googleplaymusic&apn=com.google.android.music&link=play.google.com/music/m/Iqiym43r3psdpmo4m6attczny5u?t%3DIGeometry_Pod
Overcast
overcast.fm/itunes1330350799
Pocketcasts
pca.st/AUbZ
RadioPublic
play.radiopublic.com/igeometry-podcast-Wa2QLb
Great content bro. Being an infrastructure head for a local bank, and having an engineering company (Software and Hardware), and being a full stack engineer, I have seen this problem a lot of times. From this article I make the assumptions:
*. Having many indexes usually means they use the DB for the system but also for the reporting. (Maybe make a video discussing this topic?!)
*. They don't have proper communications across teams. Software engineers are disconnected from infrastructure engineers, and decision makers enforce buzz words on both teams leads to this problem. Schema-less on MySQL?! WTH?!
*. Using colocation means they wanted to start small with minimal cost, having setup the oldest hosting company back in Bahrain, I used to colocate servers being way cheaper than leasing them from data centres. Things changes quicker than they anticipated and instead of dealing with the problems at the beginning, they may have tried to workaround them hence the schema-less MySQL?!
PostgreSQL upgrades are not actually upgrades, they are migrations, and yes it is a nightmare, but it works. I generally go the lazy easy way by building a new instance and restoring the data.
Thanks Ali! I agree with many of your points, there is a disconnect between engineering and the requirements. Usually engineers implement things blindly without asking “why”
The analytics questions can be answered by moving the data into a read only place where it is optimized for OLAP such as column oriented store with lots of indexes.. kafka became a crucial part to enable this
Great explanation. 27:24 actually there are another choices for example read repair from cassandra ofcourse it has implifications like require consensus.
when they mention 10000 connections most likely means thats concurrent query , with maybe 500k of users. connection pool is probably already in place
It's been a while since a watched to a 47 mins video, without feeling board or skipping to certain points, wooow 👌
dedication 👏 thanks
* bored
@@erickonnig3021 get this man a medal 🥇
In my opinion, this is important because the application searches for cars in the radius every time it changes geolocation. They should have used the S2Geometry library
I've worked with companies that required indexes on any fields used for selecting, grouping, etc in any queries. For a couple reasons - one is that if you have to evaluate a field for which you don't have an index, the table records for all otherwise matched rows must be found and read from disk, unlike a nice btree index, that could be a lot of disk seeks, and another is that for cursors, changes are easier to detect in the index than in the table rows.
I don't actually know if those are valid points, but I've heard them several times.
Thanks for discussing this
Thanks for your reaction video / rebuttal attempt. I am in the process of selecting a db engine. I conclude PG may have larger drawbacks than were aparent before watching your reaction.
somestimes when the bug is difficult to fix , and took months pending, u cant accept it any longer as production. especially when it break your application
The article makes sense. Lot's of sense.
I can believe you about some oddities, but in general I feel they did right. I feel outdated myself.
Don't teach your customer its needs. They need lots of indexes, ok, "Is it theoretically possible? So make it happen".
in my team, we use bucardo to minimize downtime for upgrade by basically create the newer version cluster, and replicate the data from old master to the new master. When the data is synced (or almost synced?) then we switch the routing to the new master.
@Hussein Nasser is there a book or research articles that I can use to learn more about the internals of how databases like PostgreSQL work? The postgresql documentation doesn’t even go in-depth as you do so im trying to see what resources I can use to learn more of the in-depth information regarding databases and how transactions work.
Subbed ❤️🔥
The starting point of your videos and QGinHQ is so similar :))
Hi @hussein - Oracle as far as I am aware doesn't have this write amplification problem since updates to the rows don't result in new ctids ( rowid in Oracle case ) .so isn't it also kind of a design problem of postgres also and not just that uber is indexing all over the place ?
its possible to hold transaction during sending data to other microservices to finally update the final data to indicate its completed. but there are better ways are to have another jobs queue that can be updated when a data is done. but sometimes its difficult to split a block of logics into multi sub parts, of cox i do agree they will have todo that eventually to scale
thank you!
UBER made a smart decision switching to MySQL.
could you talk about the tension between new long-running-network-protocols and serverless
Postgres is much better than mysql for supplanting oracle database. But it still has some very noticible weaknesses such as lack of direct I/O (thus requiring double buffering) and need for vacuuming due to it's lack of undo.
About 15 years ago, I worked with a company that thought they "outgrew" MySql and moved to Oracle - along with a small army of Oracle consultants (straight out of college) for a hefty 7 figure pricetag including hardware. They fired the 2 MySql people they had, and over a year migrated everything over. Two horrible problem filled years later, they hired back the 2 MySql people at huge salary increases and migrated back to MySql in a few weeks and it solved all the problems Oracle brought. Funny thing is, they had no real reason to switch in the first place - they just believed that MySql is for early stage and then you move to a "real" database. To be fair, Oracle marketing was really great at the time. That was also before it was common knowledge that TH-cam was built on MySql. They also thought that "My" was like mine and yours and made jokes like "not my SQL", straight out of cartoons.
Can you do a video on FaunaDB strengths and weaknesses?
Did postgreSQL change anything since then? It would be interesting to know about it. The official documents unfortunately offer very little info around MVCC and other db internals.
Well at least the upgrade part seems to be more or less seamless nowawadays. Using logical replication i did do major version upgrades in production with a very minimal downtime.
thinking of going to postgres from mysql so i am all ears
Super video! I applauded for CA$2.00 👏
❤️🙏
8:08 Updating year (int32) in theory can happen in-place. Updating a varchar without increasing its length can happen in-place. Oversizing varchars can make this a rare event. All this is in theory.
AWESOME!
TH-cam just recommended this to me 😊. I don't know if it's just my experience but sometime I make complicated queries and I'm not a DBA so they are not optimize performance wise and it looks like Postgres somehow know how to interpret my query optimally and also select the best indexes for the job automatically and MySQL isn't that forgiving in this regard. Dose Postgres have some special mechanism for this or am I'm wrong?
Postgres does seem to have a pretty awesome query optimizer.
This article was posted in 2016 though, how relevant is it today? Postgres has come quite far since then
We're using PostgreSQL heavily ... and yes, it has come a lot way ... but even back then, this article is weird.
The article didn't make sense at the time either.
So does MySQL
Can't even connect postgres w some of VPS
Hey thankyou soo much for sharing this, As i'm a student its really very helpfull for me. I want to learn more. How can i become a member of your channel?
This was a nice old video find. You certainly know your databases, I hope that you better understand physical infrastructure now as well (SSD knowledge and co-location are kind of important to know as it gives you visibility of what your deployment pains are going to need to be resolved)
Multi version concurrency control is better in oracle. They had it since oracle 6, took many years to protect. Also no lock escalation is an important feature.
Do you need to query for first name? Yes in customer or driver support. Database designer would have tried to limit indexing, but if need to search user friendly fields to answer queries fast, can cheaper than buying extra cpu and cloud space.
Unfortunately here again in 2024 complaining almost the same on postgres. On the side of engineering your statement is true when dealing with pros that understands what they do and how things works under the hood. Seriously, I found postgres interesting but for tool of choice I would go MariaDb in most cases
I felt you were being to-much defensive toward Postgres.
Few point that you have exaggerated.
* Nowhere in the doc they have mentioned they used 700 indexes. using 5-7 indexes pretty common where you need searching on multiple column but don't want to push data into searching infrastructure due to limited searching use case. If one write becomes 5~7 writes its no big issue for small amount of write but for big amount of write this might be.
* You also didn't recognised the point of data corruption, they already mention upgrading to new version of Postgres is really pain. So if you hit bug that disrupt your services and also upgrading new version is pain, combining those, it might be good time to see other alternatives. Yes bug still exists in everywhere, but how Uber described it, it feels they had hard time defending the bug and had some serious consequences.
.... will add more after completing the video..
I can tell you that the bug which causes postgres replicas to follow the wrong timelines is still not fixed. I've had this break replication on Pg14 and Pg15. I have to assume that in the years since this blog was published, Postgres devs have read and dissected it, yet that bug persists even into modern postgres.
12:15 mysql support statement based and row based replication both of them are logical replication. None of them deal with disk offsets. One says update where year>1980 and the other records the primary keys of the 3 matching rows.
ORM - no longer having to worry about dbs and transactions and other pesky things. However, when you work for Uber, you HAVE to know what is happening or else ... (but whatever)
any one knows why ecommerce like shopify, wordpress/woocommerce, magento, use mysql and not postgres?
Certainly an interesting exercise in micromanagement. It's a different perspective for me running over 1000 databases of varying sizes and flavors(Maria, MySQL, and PG). It's usually the more general database knowledge that I get to use. I rarely get to spend this much time with one db/architecture. I see their perspective, but I feel like perhaps their time may be better spent elsewhere. I probably sound like a Java developer when I say . . . just throw hardware/bandwidth at the problem. I think you'll find very few going in the PG -> MySQL direction and many more doing the opposite. Appreciate you covering the cheaper memory per thread. It is in fact still true. One of the few reasons for going from PG -> MySQL is large numbers of connections.
Thanks Hussein for your great content ❤️ .. I still don't get it how comes "Schemaless" with "MySQL" .. I'm not even a beginner in this field, but this was our first class in the University introductory course in DBs 😂
@Divyang Shah Oh, thanks for clarification 😃
sending manual statement for replica is over engineering, creates another layer of complication and maintenance
Absolutely, and one of the main problem of statements replication is that it can bring data inconsistent when you uses functions within your statement, like datetime functions.
I am no DB Expert, but that Postgres Upgrade Topic was the most painfull for me too. I cant undstand how you can design things like this
"But my transaction times out!"
**proceeds to keep transaction alive until email is sent**
Serves you fucking right if u ask me!
Next time they should hang http requests, maybe we'll get some complains about load balancers aswell, would be a fun read on what not to do.
Just the table corruption is sufficient to avoid usage!
Does anybody know whether Uber is still using MySQL in 2021?
No, we have switched to good old file system store
@@cat-.- ahahaha
I don't understand why they didn't use a search database to get the benefit of quick queries and have a microservice that deletes data from the search db whenever that data is deleted from the db
Probably because they're saving all that data to spy on us or hand it to the government
Tl dr?
Because MySQL virtually does the same thing except has a smaller learning curve. Learning curves do matter
Make a video on MongoDB
I choose postgresql because postgis.
So ... there's a reason why databases like Cassandra (and ScyllaDB) has much larger write throughput than databases like PostgreSQL ... and sure .. you can probably use MySQL in a "schemaless" fashion and sacrifice some of the integrity .... by why would you instead of using an database actually designed for what you want.?
Would be nice if you could upload using creative commons license so I can watch this in my streams :)
Of course you can, I give you permission, and Ill switch that option (thats youtube default Didnt play with it at all)
So from this guy review the conclusion is: you had bad experience because you didn't do it right... But even doing it so in the wrong way, MySQL still superior to PostgreSQL.
Out the box postgres is easily the worst of the big name db's... with a lot of tuning to the task at hand it can easily be one of the best. And right there lies the problem with it.
After Oracle absolutely ruining mysql in the past years, I guess today they are considering to move back...
How did Oracle ruin mysql? In one of my projects I'm using now mysql 8.0.x (the Percona version) and I can see it's getting better and better. Sure, I can see it's not mature in its query planner, especially for complex queries and it still leaves me with an impression it's a good database for simple stuff but its development is moving forward.
Me and my friend, nearly every day we yield at each other about current tech, and everyone protecting his own thinking of view where tech is going, and I'm thinking that react, docker, kubernetes etc ... just to name few... just bring more tears than joy... I think everything we have and it's already out here are good and solid proof, but we just give a try with modern technologies does not help nothing at all... Yes when u start working with them looks fancy, interesting, more easier to learn, but on long run they are pretty bad... So today I've watched multiple videos from yours, and instead I learn something new, I've learned that FB messed things up, 9 years ago whats app shows who is boss with one server and erlang... Rabbitmq is done in erlang, but nowadays kids like to use kafka than rabbitmq... and kafka turn back to polling... overall i like to say, as generation we don't know where to head, the technologies are broken, and we just try to be modern and fancy with every single new tool out there, not thinking at all what consequences will be there tommorow...
Hi, that was really informative. Iam a student. Can you give me some pointers?
@@anirudhtd7193 Buy gaming PC and stream games :D
@@MrEnsiferum77 😂😂 Iam not into gaming. And iam not a girl. So I don't think I have much scope of making money there
I am curious how docker and k8 are bringing tears. You either have not used these tools or you completely missused them.
@@ringishpil U have erland. U have phoenix. Just for example. Fault tolerant systems, without docker and k8s. The syntax is nasty? I agree. so k8s syntax is nastly, but I have minus two tools i don't need to worry about. Btw k8s, does not help solving me microservice decoupling problem, DDD helps me.
Who moves from Postgres to MySQL??? Mmmmh
Smart people
@@blueplayer6197 🤣🤣🤣🤣🤣🤣🤣🤣🤣🤣
2:37 Mongo!? For Uber!? :) You made me to smile. :)
What's wrong with that? (I'm not deeply versed in NoSQL)
Some of this sounds like Uber has poor programmers and don't do code reviews. Something is wrong with their DevOps chain if they allow an email send to block a transaction.
Fun fact: there is no unsigned integer at PostgreSQL
Good luck when overflow is near.
bigint
@@hometutorsite Okay, overflow still far away, so unsigned bigint still not a problem for now. Let's say a company only need 0-255 for storing hex number. Should they spend 2 bytes (smallint) for storage but only half usable? That's why people still prefer MySQL over PostgreSQL
@@KangJangkrik If you're fighting over individual bytes, you're already looking in the wrong place. You're probably losing more space to a couple bytes of padding on rows anyway. And even if for some stupid reason you really need that storage savings, just convert it in the client application.
Yes unsigned problem, cast bigint
I just saw this video and was watching with curiosity. But...
How come you think the indexes they used are unnecesary?! I think you will discover the need to index more columns than you think if you work on an enterprise scale database. It's easier to speak here than to do the work in the field...
And a bug corrupting my database! Think of a hospital or a bank database for example. You can't have your tables corrupted. I would run away from this database asap and never turn back again.
Before making videos like this you need to change your point of view or get some real life experiences with serious databases first...
TL;DR useless uber "engineer" create crap architecture and then blames tool...
😁
This is what happens when you hire taxi drivers to manage databases.
Uber app sucks
Why is you name Barack Obama’s last name
It's S. Q. L. Not Sequel, that means something different!
wrong
@@iconoptixx No not wrong. Structured, Query Language. Sequel was already wrong 40 years ago. If peoples take over wrong behavior doesn't make it right 30 years later.
Uber App is worse. I know this I drive for Uber and the app is worse. Lyft app is way better. They have to fire the developer of Uber and hire a new one.