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.

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.

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.

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.

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!

this channel is a goldmine, deep knowledge being dispensed here, a rarity on youtube. thank you

finally!! been waiting for this. thank you so much, hussein

Many great comments added earlier, but another compliment for your expetise and enthusiasm about the subject. Thanks, excellent content!

It is always so wonderful to hear you, thank you, keep awesome .

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

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.

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.

Your content is very different and deep. I'm learning new things from you. I watch one video every day.

Great content. Enjoyed it thoroughly. The best part is that it pumps up my interest in database technologies. Keep it up!

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

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

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

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.

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.

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

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

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

It's been a while since a watched to a 47 mins video, without feeling board or skipping to certain points, wooow 👌

@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.

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

Subbed ❤️🔥

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 ?

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.

could you talk about the tension between new long-running-network-protocols and serverless

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".

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 article was posted in 2016 though, how relevant is it today? Postgres has come quite far since then

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.

The starting point of your videos and QGinHQ is so similar :))

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.

Can you do a video on FaunaDB strengths and weaknesses?

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)

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.

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.

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.

thank you!

UKposts 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?

any one knows why ecommerce like shopify, wordpress/woocommerce, magento, use mysql and not postgres?

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 😂

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

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.

thinking of going to postgres from mysql so i am all ears

AWESOME!

UBER made a smart decision switching to MySQL.

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)

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.

Tl dr?

Super video! I applauded for CA$2.00 👏

sending manual statement for replica is over engineering, creates another layer of complication and maintenance

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..

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.?

Does anybody know whether Uber is still using MySQL in 2021?

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

Would be nice if you could upload using creative commons license so I can watch this in my streams :)

Just the table corruption is sufficient to avoid usage!

"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.

Make a video on MongoDB

After Oracle absolutely ruining mysql in the past years, I guess today they are considering to move back...

I choose postgresql because postgis.

Because MySQL virtually does the same thing except has a smaller learning curve. Learning curves do matter

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...

Who moves from Postgres to MySQL??? Mmmmh

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.

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.

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.

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...

2:37 Mongo!? For Uber!? :) You made me to smile. :)

Fun fact: there is no unsigned integer at PostgreSQL Good luck when overflow is near.

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!

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.