10 ms·
Why Uber Engineering Switched from Postgres to MySQL (2016)
- chovybizzass 6y agobecause its simpler? i found postgres to be overly complicated compared to MySQL/Maria
- DaiPlusPlus 6y agoDefine: "complicated"
- sschueller 6y agoRecovering a split brain Galera cluster. Fun times...
- eznzt 6y agoJust creating a new user is annoying enough. Permissions are also much more complex. What the hell are schemas?
- oftenwrong 6y agoSchemas are similar to databases in mysql. They serve as a namespace. In mysql you can have a database `foo` and a table `foo.bar`. In postgres you can have a schema `foo` and a table `foo.bar`. In postgres you can have multiple databases in a cluster, and multiple schemas within each of those databases.
- eznzt 6y agoWhat you are basically saying is that yes, they are much more complex.
- desas 6y agoIt's another layer, you don't have to use it. If you pretend schemas don't exist you basically never know they do, unless you go looking for complexity in the postgres bowels.
- Tostino 6y agoTo be honest, it's better to think of MySQL db = Postgres schema, because I'm MySQL you can do cross db queries and there is no intermediary schema level, and in Postgres you can do cross schema queries, but not cross db queries.
- rleigh 6y agoThey are not complex, and they are entirely optional. They are just a namespace. You are free to never, ever use them. For a company the size of Uber, I don't think spending five minutes reading the documentation for createuser is a significant burden to deployment. PostgreSQL is very easy to deploy.
- chris_wot 6y agoNo, what you mistake for "complexity" seems to be your general unfamiliarity of what a schema is. In fact, when you understand what schemas are, then it actually makes a lot more sense.
- looperhacks 6y agoHow is creating a new user complicated? The normal CREATE USER is all I've ever needed to create a new user in postgres (assuming I don't have set up the pg_hba so that I need to allow every user separately)
- vinger 6y agoDoes postgres still require a separate user to access the db. I remember this being a limitation in 2008. That forced user creation always pushed me to mysql because I hate having separate users for each service because you still have to manage and account for these extra accounts.
- mgkimsal 6y agoMost tutorials/instructions I read have you use "createuser" command from the system shell. But... you have to be able to switch to a system 'postgres' user first, which ... perhaps you don't have privileges to do, or need sudo access or whatnot. If you can install postgres, connect to it directly with some sort of root identity, then immediately create users and databases (as is the case with pretty much every mysql walk-through I've ever seen), it's not a default. https://wiki.postgresql.org/wiki/First_steps https://wiki.postgresql.org/wiki/First_steps "The default authentication mode is set to 'ident' which means a given Linux user xxx can only connect as the postgres user xxx." This alone is a complicated/confusing thing, because it's mixing system accounts with the db server accounts/access - and none of that is obvious, and doesn't quite map to how other databases handle things. I've never had to have matching system account names for user access in MSSQL, for example.
- chousuke 6y agoWith MySQL, you'll still have to switch to root to connect by default? I honestly don't remember, since it's been ages since I set up MySQL manually. If MySQL actually allows administrative access out-of-the-box without any kind of special authorization, then that's a terribly insecure default. With PostgreSQL, you have to switch to the superuser to configure things further because that's the only sane default you can have on an unconfigured system. If you can run commands as the user PostgreSQL is running as, you are "safe" to trust, and PostgreSQL will let you in. UNIX ident authentication is also is extremely convenient for local applications, since you don't even have to have a password for the account, or make the PostgreSQL server network-accessible in any way. Oracle can do the same thing, and so can MySQL, apparently (with IDENTIFIED VIA unix_socket). MySQL user management has its own complexity in that you have to manage "user@address" identities, and the same user at different addresses or auth methods can have different permissions. How's that "simple"? With PostgreSQL, your users will at least map to the same user regardless of how they authenticate themselves.
- tinus_hn 6y agoIt would be great if there was some management GUI for these tasks so you don’t have to look up the syntax for these things that in many deployments you only do once.
- mvanbaak 6y agopgadmin ;-P
- tinus_hn 6y agoThis actually looks pretty reasonable, I am going to look into it. First I need to figure out how to open up the server for connections but still limit it, though.
- jhauris 6y agoLook at the pg_hba.conf file (probably something like /etc/postgresql/<version>/main/pg_hba.conf). https://www.postgresql.org/docs/current/auth-pg-hba-conf.html https://www.postgresql.org/docs/current/auth-pg-hba-conf.htm...
- tinus_hn 6y agoPerhaps it’s better to only listen to localhost and connect through a SSH proxy connection
- rleigh 6y agoOr DataGrip if you already have a JetBrains licence.
- rektide 6y agoboth kubernetes operators have some ok user management built in
- paulryanrogers 6y agoSchemas are SQL standard namespaces within a DB. You can join between them. Mysql allows joining across databases, so it doesn't implement schemas.
- lucian1900 6y agoWhat MySQL calls databases are actually schemas. They’re even aliases as such. MySQL doesn’t have multiple SQL databases, you’ve been using multiple schemas.
- isoprophlex 6y ago"What the hell is scoping? Why not put every variable in global scope? Much less complex"
- corty 6y agoPostgreSQL: "your date 2020-02-31 isn't a date, fix that" MySQL: "2020-02-31? Whatever man, I'll just enter something..."
- consp 6y agoConsidering the US uses a weird date format, I definitely prefer the former in combination with input sanitation forcing you to thing about your actions before assuming the database will fix it for you.
- corty 6y agoAgreed. As with strong typing in programming languages, I do prefer a database to be strict in rejecting invalid inputs. MySQL does two bad things here: It accepts an invalid input plus it interprets it creatively, producing something the user most probably didn't intend. In that respect, MySQL is almost as bad as Excel creatively "interpreting" dates. Another example of a database doing improper things would be Oracle mixing up the empty string with NULL. In Oracle, both are the same... MySQL has a few more of those gotchas, e.g. regarding broken charsets (UTF-8 isn't 'utf8', it is 'utf8mb4', 'utf8' is an alias for 'utf8mb3' which is a broken subset). I wouldn't use MySQL for any data that was important to get back consistently. However, since Uber seems to be using some schemaless "we don't care"-layer anyways, that point is moot for the original article.
- bbarnett 6y agohttps://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mo... Much of your MySQL complaint is not a MySQL issue, but a config issue. And yes, powerful config options are good, not bad.
- BitPirate 6y agoOne can still complain about mysqls dumb defaults. https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_rollback_on_timeout https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.ht... "Hey, let's just not act transactional on a timeout by default"
- ignoramous 6y agoPrevious discussions: 2016: https://news.ycombinator.com/item?id=12166585 https://news.ycombinator.com/item?id=12166585 2018: https://news.ycombinator.com/item?id=17280239 https://news.ycombinator.com/item?id=17280239 Community responses: - https://news.ycombinator.com/item?id=12216680 https://news.ycombinator.com/item?id=12216680 - https://news.ycombinator.com/item?id=12179222 https://news.ycombinator.com/item?id=12179222
- chris_wot 6y agoThe response was: https://news.ycombinator.com/item?id=14222721 https://news.ycombinator.com/item?id=14222721
- asah 6y agoEven though 99.9% of applications will never run into Uber's issues, it's been 4 years and 4 major versions later, and I'd love to review these complaints and see if they still apply to PG 13.
- Tostino 6y agoWait until 14 for that comparison and it'll look much better. Bottom up index deletion helped solve some of the write amplification issues.
- petergeoghegan 6y agoThere is also index deduplication in Postgres 13 and the B-Tree enhancements in Postgres 12. All of these enhancements significantly improved the situation for workloads affected by what the blog post calls write amplification. (I myself call this phenomenon index version churn, since it is more descriptive and has less baggage.) I was the author of all of the above, including the Postgres 14 work you mentioned (though Anastasia Lubennikova was the primary author of index deduplication). To me it feels like one very large project -- the effects are cumulative, and each major Postgres version had B-Tree work that built on the last release in one way or another.
- burnthrow 6y ago2016
- frankietaylr 6y agoHas Postgres architecture changed since Postgres 9.2 in terms of the inefficiencies mentioned in the article?
- pizza234 6y agoThe main point, clustered vs. nonclustered indexing, is architectural, and not inherently inefficient; it depends on the use case. "Highly advanced" databases give both options, but AFAIK, MySQL/PGSQL will likely not offer this, at least for a very long time, since it requires radical changes.
- evanelias 6y agoOn the one hand, MySQL has offered this for two decades, by virtue of pluggable storage engines being core to its design. Some storage engines use clustered indexes and some do not. The user can decide which one matches their use-case; very large companies can design their own custom special-purpose storage engines; etc. On the other hand, mixing storage engines in a single db instance has operational downsides (especially re: crash-safe replication). And InnoDB is by far the dominant storage engine, and is probably unlikely to offer nonclustered indexing, so from that perspective I agree with your point.
- Tostino 6y agoIt'll be interesting to see how things shake out when some of the other implementations using postgres's pluggable storage API start maturing. I wonder if it'll have some of the same operational downsides that mixing storage in MySQL has.
- evanelias 6y agoGood question. I assume it depends on how Postgres handles multi-engine transactions, and how it stores replication state metadata. A good discussion of the issue in MySQL/MariaDB is here: https://kristiannielsen.livejournal.com/19223.html https://kristiannielsen.livejournal.com/19223.html Apparently MariaDB 10.3+ has this solution implemented, which is cool, never knew that before. I don't think there's anything equivalent in MySQL.
- arnejenssen 6y agoDoes Uber use event sourcing?
- exhaze 6y agoYes
- blowski 6y agoI spent a whole decade saying "Why do I need Postgres? MySQL is fine." Started using Postgres a couple of years ago, and I now can't believe I ever lived without window functions, native arrays, custom types, etc.
- bombcar 6y agoI’ve wanted to try post geese but have never really had a chance - everything I do is “prepackaged” and things like Wordpress or Confluence really don’t seem to care if it is MySQL or Postgres.
- noir_lord 6y agoYou should definitely have a gander.
- dismalpedigree 6y agoThis really goosed my energy levels this morning!
- rrauenza 6y agoEspecially at the possibilities of a lack of down time.
- blowski 6y agoI feel that pain! I knew MySQL so well that it felt risky to use a different database, yet if I used it on a non-serious project, how would I get real-world experience? I learned a lot from a book from one of the core contributors to Postgres - https://theartofpostgresql.com/ https://theartofpostgresql.com/. It has actual real world examples with realistic datasets to experiment with.
- Proziam 6y agoI wish this resource existed (or that I knew it existed if it did) years ago. I always seem to learn about the things that would have made my life easier after I've already done things the hard way.
- KingOfCoders 6y ago2016.
- deleted 6y ago[deleted]
- villgax 6y agoLol, a db that squirms at unicode/utf-8 out of the box?
- petergeoghegan 6y agoI committed a patch that added a mechanism I called "bottom-up index deletion" recently: https://www.postgresql.org/docs/devel/btree-implementation.html#BTREE-DELETION https://www.postgresql.org/docs/devel/btree-implementation.h... https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d168b666823b6e0bcf60ed19ce24fb5fb91b8ccf https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... Bottom-up deletion is specifically designed to ameliorate what the blog post refers to as "write amplification". Testing has shown that it's very effective with many workloads.
- Tostino 6y agoJust wanted to say how impressed I was with this solution and the results it achieved when I was following the development on -hackers.
- petergeoghegan 6y agoThanks
- syspec 6y agoWhat is -hackers?
- petergeoghegan 6y agoThe Postgres community mailing list for development work -- pgsql-hackers.
- junon 6y agoThere's some historical context for this article. 2016 was a year of RAPID growth for Uber. There was a running statistic internally that your employee ID would be at the median point just 6 months after being hired. They were trying to hire (and poach) just about anyone they could around this time. Therefore, these articles are... very shiny, compared to the actual tech applied internally (note that even though Uber is referred to in the third person here, this is on uber.com and written by an Uber employee). I worked at Uber for a year. Schemaless was... meh. Nobody really liked using it, nobody really understood it, and you weren't really allowed to host your own instance - you had to have another internal team do it for you, which didn't help the "understanding" problem. It smelled distinctly of "not invented here" syndrome. A number of things inside Uber worked that way - the culture was so competitive and brutal, performance reviews were always a massacre, so everyone was trying to outshine their peers (or outright climb on their backs, etc). This resulted in a LOT of "tech" being "invented" that 1:1 did something already prominent in open-source or was already an enterprise solution (probably cheaper than paying engineers to do it) but since actually achieving it and having your name on it meant you would look better for a promotion or a bonus or whatever over a colleague meant it was worth it to the individual to reinvent the wheel. Rinse and repeat over and over again. I'm not an enemy of reinventing the wheel, mind you. But only if the new wheel works significantly better than the old one. This was rarely the case at Uber. Postgres was still used somewhat commonly at Uber when I was there, but they were really pushing for Schemaless internally. It felt very overkill for just about everything outside the platform teams and was always, without fail, a massive pain to deal with. Don't be fooled by these Uber engineering articles. This was PR to bolster up their OSS image to outsiders to help with hiring and poaching at the time. Things internally looked very different.
- midrus 6y agoI think this applies to most companies. What they write in their blogs is a shiny, optimistic, limited view of the best part of their best system or similar. Once inside, things are never that great. I myself was very ashamed of a company I worked for (also SF based) blog post... even the author of the post was a very well known open source maintainer of many libraries of a very popular programming language. Reading the posts in the blog was like.... I cannot believe we lie this big... internally things were just crap, and what the blog post made look like it was the norm, was just a side project of this person. So, never trust companies blog posts by default.
- lumost 6y agoTBH, everything that was listed as a complaint could be a complaint for nearly any transactional RDBMS. For workloads that require heavy always on replication and availability RDBMS's haven't been the go to solution for a long time vs. distributed DBs. Changing from Postgres to Mysql or MySQL to Postgres (or even Oracle) won't really buy you much if you're running into these issues. Even this one > The bug we ran into only affected certain releases of Postgres 9.2 and has been fixed for a long time now. However, we still find it worrisome that this class of bug can happen at all. (rare/specific) Data Corruption bugs around master-promotion and handoff occur in every major DB. MySQL is no different, and I've personally had to track down issues in a few popular products. If you run thousands of copies of a piece of software with different workloads and hardware configurations... you're going to find bugs. After all - how many DBs passed Jepsen on the first shot!
- huy-nguyen 6y agoHow does Schemaless compare to Vitess (https://vitess.io/ https://vitess.io/)?
- moonbug 6y agoArticle about Uber are always instructive: read and then do the exact opposite.
- stelf 6y agoAnd basically... I mean, so what in 2021?
- emrah 6y agoThe types of design decisions I run into everyday at Fivetran that Mysql made makes me cringe. Friends don't let friends use Mysql :P