anno 2013 "MIGRATING UBER FROM MYSQL TO POSTGRESQL"
https://www.yumpu.com/en/document/view/53683323/migrating-ub...
Why Uber Engineering Switched from Postgres to MySQL
191–200 of 306 posts
Re: Why Uber Engineering Switched from Postgres to MySQL
#192Facebook maintains it's own fork [0] of MySQL. A couple of interesting talks are also available: MySQL at Facebook, Current and Future [1] and Massively Distributed Backup at Facebook Scale [2]. [0] https://github.com/facebook/mysql-5.6 [1] https://www.youtube.com/watch?v=jqwegP9xwVE [2] https://www.youtube.com/watch?v=UBHcmP2TSvk
Technically they use it as a dumb key-value storage.
Re: Why Uber Engineering Switched from Postgres to MySQL
#193I 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…
Support for online schema changes has been getting a lot better in recent releases of MySQL. No idea if it is totally bulletproof right now though, we are still in the "stone ages" of doing failover based schema changes, not even pt-online-schema-change.
It seems that the replication arguments in the post are pretty weak (migrating to 9.4+ to allow heterogeneous versions would still have less downtime than migrating to MySQL), but clustered indexes seem like a huge win for their use case of updating only a subset of each row. Whether that is reason enough to totally ditch postgres, I don't know.
Re: Why Uber Engineering Switched from Postgres to MySQL
#194Earlier quoted context omitted.
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 multipl…
I used to be a huge proponent of ORMs everywhere, but I've come to realize that if you're writing your app in such a way that a developer needs to be able to do any arbitrary data fetch or transformation whenever they want, that's your real problem. The set of retrievals and transformations you want to support should be well-defined, and abstracted into a layer whose interface allows the level above it to only think…
I don't really agree; I think you either need to have a very thin layer between your DB facts and your domain, or else use the DB as a kind of persistence layer for a complex graph.
The latter only really works if you've got a primary implementation language and aren't integrating lots of applications / libraries written in different languages communicating with the same database. You need to go down the SOA / distributed RPC / FFI route to integrate different languages, and that has its own complexities.
Personally I prefer treating the DB as a canonical store of facts. Models with a lot of code are pretty suspect. Retrievals can be tuned to just the facts required, ma'am - you don't accidentally drag in the banana + gorilla + whole forest. Doesn't stop you building a higher-level service layer if that's what you need, either. You'll need that when you scale up anyway; chatty models won't work at that level either.
Re: Why Uber Engineering Switched from Postgres to MySQL
#195Earlier quoted context omitted.
it's very hard to abstract away from SQL. to be honest, many nosql have integrated concepts similar to grouping, having, joins in their API as well as they matured, because that's what data elaboration needs.
But still everyone do it. That must tell something, right? And I'm not talking about NoSql borrowing some relational concepts, but the opposite, and more directly, the API. I know that most folks (including the ones that downvote!) have no clue what I'm talking about, because almost nobody (recently) have experience in talking against a database without SQL. Is like if the only way to talk to a NoSql was using Json +…
Well, data transformations are much easier to read and write in SQL than e.g. Java, what with temporary collections built up in memory, random maps, lists, etc.
CTEs aren't required very often - you generally only need them for recursive CTEs, and that's iterative retrieval analogous to pointer-chasing. It's typically a sign of a data model that's poorly suited to relational storage, e.g. trees and graphs.
I have issues with the irregularity of SQL syntax - it deeply annoys me that we have both 'where' and 'having' for doing exactly the same thing, one pre-fold and one post-fold - and I don't like my lack of access to the potential the index has for related fetches (window functions are not pleasant to work with) - but mostly my problems come not from lack of access to the relational nature, but lack of power over poor implementation / query planner / etc. details.
Re: Why Uber Engineering Switched from Postgres to MySQL
#196Earlier 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.
Am there right now, it's not really a problem - you just have to plan your schema changes and use Percona OST.
Re: Why Uber Engineering Switched from Postgres to MySQL
#197Earlier quoted context omitted.
Tools such as the percona toolkit ( https://www.percona.com/software/mysql-tools/percona-toolkit ) provide the ability to perform safe and performant online alters for MySQL. Behind the scenes it actually creates a new table with the new schema and utilizes triggers to apply writes to the new table as the original data is being copied. Once it completes the table is renamed and the triggers are removed. Percona also…
online schema change is absolutely riddled with crippling bugs if you use foreign keys. Just a heads up, we've had to basically take everything it does and make an in-house version. The idea and execution are great, when it actually works.
Foreign keys are a major problem in MySQL if you do lots of writes, especially bulk writes. As is auto-increment (table-lock for load data infile, yay!).
We allocate our own IDs, and disable foreign keys for bulk loading.
Re: Why Uber Engineering Switched from Postgres to MySQL
#198I like this transparency, I know I never want to work at uber.
Re: Why Uber Engineering Switched from Postgres to MySQL
#199I 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…
I just want to add that newer versions of MySQL can add indexes online. In fact, it looks like all DDL can be done online, even though some if it (like adding a column) may require the table to be rebuilt, which can take time.
Note that the master is still available for read and write during this. Replicas will lag though.
Re: Why Uber Engineering Switched from Postgres to MySQL
#200One major advantage of MySQL's clustered indexes the article doesn't mention is that, although secondary key reads may be a little slower, primary key reads will be faster. The row data lives in the primary key index, so there is no need for referencing an additional database page (possibly causing random I/O).
This is especially relevant when doing range queries over the primary key. Imagine a table containing billions of chat messages, from which you want to retrieve a single conversation history. With a clustered primary key on (conversation id, message id), MySQL would need to process just a couple of database pages. Postgres, on the other hand, would need to reference a semi-random page for each of the messages.
Now imagine a 10k message chat conversation, a table too large to fit into RAM, and storage by means of spinning rust (yeah, yeah, I know what year it is :-)). The difference would be somewhere between 2 and 3 orders of magnitude.