Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why Uber Engineering Switched from Postgres to MySQL

eng.uber.com

Post reply on HN
Print view View original

151–160 of 306 posts

Re: Why Uber Engineering Switched from Postgres to MySQL

#151

Post by [deleted] »

madhusudhan000 wrote: post #103
ledjon wrote: post #51

I would argue that most of these Postgres "flaws" are actually advantages over MySQL when you look at them holistically rather than the very specific Uber use-case. Postgres's MVCC is superior (can rollback DDL, can add indexes online, can have open read transactions for a VERY long time without impacting other parts of the system) Postgres supports many types of indexes, not just b-tree. One thing it doesn't have is…

Well, for one thing, if they say schemaless, then you are using the wrong tool for the job. Sure postgres has JSONB (comparing to mongo db), Key value store such as HStore, but they do well if they fit on one machine. The moment you hit that scale you have to realize that there are tools specifically built for this. There is the phoenix project https://phoenix.apache.org/ that salesforce is using for scaling. Definit…

[deleted]

Re: Why Uber Engineering Switched from Postgres to MySQL

#152

Post by nawitus »

spotman wrote: post #120
jedberg wrote: post #116

> MySQL supports multiple different replication modes: > Statement-based replication replicates logical SQL statements (e.g., it would literally replicate literal statements such as: UPDATE users SET birth_year=770 WHERE id = 4) Postgres has that too (using a 3rd party tool, but it's an officially supported tool). We were using it on reddit 10 years ago. It caused a lot of problems. I wouldn't call that an advantage…

Try enforcing this on teams that use ORMs like hibernate with 500 developers. Super, duper, common issue, you will find this at every large shop at some point in its life time, usually around the time of hiring people and expanding extremely fast, and taking on some tech debt. All functions of extreme scale, hyper growth, and yeah, not following the absolute best practices all the time, but tech debt is like any debt…

That's what I've often wondered about ORMs. First they try to abstract away all the database internals so that the developer hardly even knows what's happening. However, ultimately the abstraction always ends up having some limitations. In the end the internals need to be exposed, but since your code base is still mostly based around the ORM it can end up being a mess.

Re: Why Uber Engineering Switched from Postgres to MySQL

#153

Post by darklajid »

manigandham wrote: post #129

They should use SQL Server (which has great replication abilities, although horizontal scale out is still difficult) or MemSQL (which is distributed, scalable, and can do everything they need). Or use Cassandra which is a perfect fit (or ScyllaDB which is a better version of it). This all sounds like an aversion to just paying for or using better products when the problem is easily solved.

SQL Server needs a couple hits with a cluebat to even satisfy the basic three requirements for a database system they listed (I'm talking about the MVCC line of course).

Honestly, if a commercial database provides what you require and you have the budget? Sure, why not. But I'd always try to avoid that myself, because the serious players (MS, Oracle, is anyone still using DB2?) are really, really expensive.

Re: Why Uber Engineering Switched from Postgres to MySQL

#154

Post by fasteo »

_Codemonkeyism wrote: post #117
busterarm wrote: post #87

Earlier quoted context omitted.

Can't agree with this post enough. I find their whole writeup to be terribly myopic. When they started their service, Postgres was almost certainly the right choice for what they were building and their MySQL setup was not. Now Postgres is less effective for them. These kind of tech switches are _inevitable_ if you're making the right choices for your organization. This strikes me as very similar to the article where…

"1B+ rows in it with mysql?" Been there, really no fun.

Percona to the rescue [1]. Works flawlessly, at least in our case.

[1] https://www.percona.com/doc/percona-toolkit/2.1/pt-online-sc...

Re: Why Uber Engineering Switched from Postgres to MySQL

#155

Post by nawitus »

tormeh wrote: post #148
jedberg wrote: post #132

Earlier quoted context omitted.

Open db connection get data for receipt generate receipt send email write success to database close connection To a junior programmer this would probably look reasonable, and to be fair, it takes some experience and getting burned, or good training, to know it is not.

So the correct version is, I guess: Open db connection 1 get data for receipt Close db connection 1 generate receipt send email open db connection 2 write success to database close db connection 2 I guess you could speed this up a lot by doing it in bulk instead of opening and closing db connections twice for every email. Anyway, the version you wrote sounds reasonable for everything but really big operations to me,…

You don't need to close the database connection to not have an open transaction, right?

Re: Why Uber Engineering Switched from Postgres to MySQL

#156

Post by jedberg »

tormeh wrote: post #148
jedberg wrote: post #132

Earlier quoted context omitted.

Open db connection get data for receipt generate receipt send email write success to database close connection To a junior programmer this would probably look reasonable, and to be fair, it takes some experience and getting burned, or good training, to know it is not.

So the correct version is, I guess: Open db connection 1 get data for receipt Close db connection 1 generate receipt send email open db connection 2 write success to database close db connection 2 I guess you could speed this up a lot by doing it in bulk instead of opening and closing db connections twice for every email. Anyway, the version you wrote sounds reasonable for everything but really big operations to me,…

You can still do it in one transaction, sort of. Replace "send email" with "queue email" and then either eliminate the step that writes the success to the database (pushing that off to the queue processor, who will open or already have open a database connection) or instead write to the DB that you queued the item.

The advantage you gain is that the queue is a nice buffer if something gets held up with email sending, and also, the queue processor can work in bulk, say sending 100 emails, and then opening a connection to the DB and writing them all in one statement.

Re: Why Uber Engineering Switched from Postgres to MySQL

#157

Post by mml »

jedberg wrote: post #116

> MySQL supports multiple different replication modes: > Statement-based replication replicates logical SQL statements (e.g., it would literally replicate literal statements such as: UPDATE users SET birth_year=770 WHERE id = 4) Postgres has that too (using a 3rd party tool, but it's an officially supported tool). We were using it on reddit 10 years ago. It caused a lot of problems. I wouldn't call that an advantage…

Experience with Mysql replication (even simple master/slave) leads me to believe Uber is going to have some rather nasty surprises at some point.

Re: Why Uber Engineering Switched from Postgres to MySQL

#158

Post by djsumdog »

spotman wrote: post #120
jedberg wrote: post #116

> MySQL supports multiple different replication modes: > Statement-based replication replicates logical SQL statements (e.g., it would literally replicate literal statements such as: UPDATE users SET birth_year=770 WHERE id = 4) Postgres has that too (using a 3rd party tool, but it's an officially supported tool). We were using it on reddit 10 years ago. It caused a lot of problems. I wouldn't call that an advantage…

Try enforcing this on teams that use ORMs like hibernate with 500 developers. Super, duper, common issue, you will find this at every large shop at some point in its life time, usually around the time of hiring people and expanding extremely fast, and taking on some tech debt. All functions of extreme scale, hyper growth, and yeah, not following the absolute best practices all the time, but tech debt is like any debt…

ORMs have mostly been just painful at ever shop I've been at. I've used ActiveRecord, Squirl, Hibernate, django.db .. they're all various level of suck.

The one huge advantage of an ORM is the ability to support multiple databases, but that only really works if you can do everything using the ORM. The moment you have a function too complex that you need to write some SQL, now you need some case statements and multiple integration tests for all the database your product needs to support.

They remove some boiler plate while adding others. In one of my own projects, I just created several files (pgsql.commands, mysql.commands, etc.), a basic set of classes around them and a base set of commands that will work for all the DBs I wanted to support (so the command files had an inheritance model, albeit only one layer).

With all that being said, most ORMs I've used do have explicit transaction support. I know Squirl had a `transaction {}` block you could wrap commands around. Transactions shouldn't be an excuse. They should be off by default and explicit added around blocks of things that need to be atomic.

> Try enforcing this on teams that use ORMs like hibernate with 500 developers.

I realize this is a hyperbole (I hope) because you really shouldn't have 500 developers all on the same monolithic project (unless you're developing like...the Linux kernel). Getting your team to at least try to implement best practices does take some effort, but with things like weekly demos and code reviewed commits, it's do-able.

Re: Why Uber Engineering Switched from Postgres to MySQL

#159

Post by jedberg »

nawitus wrote: post #155
tormeh wrote: post #148

Earlier quoted context omitted.

So the correct version is, I guess: Open db connection 1 get data for receipt Close db connection 1 generate receipt send email open db connection 2 write success to database close db connection 2 I guess you could speed this up a lot by doing it in bulk instead of opening and closing db connections twice for every email. Anyway, the version you wrote sounds reasonable for everything but really big operations to me,…

You don't need to close the database connection to not have an open transaction, right?

No you don't. But depending on the ORM, some of them batch writes unless you explicitly tell them not to, but will flush when you close the connection. So some programmers will just close the connection because they don't understand the difference or because they've learned that closing the connection guarantees that the data is written.

Re: Why Uber Engineering Switched from Postgres to MySQL

#160

Post by manigandham »

darklajid wrote: post #153
manigandham wrote: post #129

They should use SQL Server (which has great replication abilities, although horizontal scale out is still difficult) or MemSQL (which is distributed, scalable, and can do everything they need). Or use Cassandra which is a perfect fit (or ScyllaDB which is a better version of it). This all sounds like an aversion to just paying for or using better products when the problem is easily solved.

SQL Server needs a couple hits with a cluebat to even satisfy the basic three requirements for a database system they listed (I'm talking about the MVCC line of course). Honestly, if a commercial database provides what you require and you have the budget? Sure, why not. But I'd always try to avoid that myself, because the serious players (MS, Oracle, is anyone still using DB2?) are really, really expensive.

> basic three requirements

SQL Server works just fine and has lots of concurrency control to do whatever they need. And the way they're using the database doesn't seem to really make this an issue outside of their replication.

> are really, really expensive

This whole janky setup they have sounds even worse. None of the commercial relational databases are really that expensive considering what they offer, and we're talking about Uber here. We're a small startup that pays for both.

Money for (a better) working product with support is the right call, not build it yourself. This is just poor tech decision (outside of using a RDBMS in the first place).

Post reply on HN