It's hard to say, but one can often find people switching from Postgres to MySQL (and vice-versa). For example, see this [0] and the related HN discussion [1].
I'm not saying this is the case at Uber, but it's quite common for MariaDB users to refer to it as MySQL, or to use the two names interchangeably. It's difficult to get out of the habit as all of the CLI tools are still mysql*, clients (i.e. the protocol) are all named "mysql", CM modules (like Ansible's) are all named "mysql", etc. And sometimes it's just simpler (if lazier) to say you're using MySQL rather than explain what MariaDB is and why it's preferable.
Well there new database is basically a key value store built on MySQL, so I think there isn't much to be gained by using MariaDB and it will add needless additional complexity.
this reads like a laundry list of buzzwords that were designed to justify not throwing any effort into postgresql and just going with a new shiny toy (not mysql. yes. i know it's been around for a while).
so you're pulling one line from the article to tell me that i'm wrong?
on-disk format/write amplification:
> For tables with a large number of secondary indexes, these superfluous steps can cause enormous inefficiencies. For instance, if we have a table with a dozen indexes defined on it, an update to a field that is only covered by a single index must be propagated into all 12 indexes to reflect the ctid for the new row.
How wide is their data? Depending on the answer to this, it could be that they've over-indexed, have a poor indexing strategy, or are reacting to the poor queries generated by an ORM (not sure if they use one, or if they hand-code their own SQL).
i'm not here to debate whether postgres is better than mysql. i'm just saying that it seems like a lot of research went into justifying a switch. who knows, maybe that research could have been spent optimizing their current environment.
>so you're pulling one line from the article to tell me that i'm wrong?
Better than telling them that they are wrong while not only not pulling even one line from their article, but misattributing it to be something very different from what it is.
In what world does the response to the concerns and analysis in the article can ever be: "data corruption: everyone has bugs"...
the same world where mysql lets you corrupt your own data. did you just stop reading? in what world does a data corruption event prompt you to change platforms to another platform that has a history of data corruption?
They do address other stuff. They have huge writes and needs better writes performance. Maybe not what you and I need, but hey, I suppose they know Uber needs better?
MySQL handles it differently than Postgres, and gives them better performance for their purpose (based on their experience/test). They were explaining it in the parlance of the terms that MySQL and Postgres. If those are buzzwords, then MySQL and Postgres are both created using buzzwords?
Their explanation is not perfect (for me, why do their datamodel needs massive updates?). But I wouldn't write it off as buzzwords and dismissing Postgres because of data corruption. There are a lot of other things they were trying to explain there.
It is. But at least MySQL provides both ways of shipping changes, WAL-shipping like row-based replication and the less reliable statement-based replication, and the DBA can choose which to use when.
Combining that fact with the way Postgres and InnoDB handle secondary indexes means WAL-shipping not only ships the entire new row, but also all the disk blocks of all the secondary index updates, unlike MySQL's row-based replication.
This is actually something I greatly like and want to see in Postgres. Perhaps the decision to choose secondary indexes with direct links to rows was taken because at that time (before replication) it was less read-heavy and the write-amplification wasn't such a concern. But now, when replication is a common requirement (and network IO is not as fast as disk IO) it makes a lot of sense to switch to a single-point-of-update allowing way of storing secondary indexes
"Schemaless" on top of it and a shiny new "microservices" layer. Please, by all means, continue to take one aspect of the stack in an attempt to misrepresent me.
The amount of research about the on-disk internals of both PostgreSQL and MySQL are a lot more effort than I would have probably spent (granted, I don't have a team of highly paid devs at my disposal, but still, I've seen technical decisions made on the basis of Google Trends...).
In my personal opinion, those bits of research aren't much, and they would have found it when they looked around for the cause of the write-amplification. A truly good amount of research would also have popped up heap-only-tuples, as mentioned here[0], or other ways to mitigate too many secondary-index updates.
The "laundry list of buzzwords" is a detailed, up to engineering standards, analysis of the issues they faced, with example cases and explanatory follow-up.
As far away from a "laundry list of buzzwords" as you could possibly get. Not to mention they did the very opposite of switching willy nilly to some new "shiny toy".
I've heard from technical leaders at multiple now well established unicorns how they'd never use postgres or switched from postgres simply because MySQL has a lot more tooling built it and many more people are exposed to its shortcomings at "web scale" so that it's very well known where and when things will break.
Disclaimer, I'm a hardcore Postgres user myself, but I also keep tabs on the other tools.
OTOH, it's important to consider that most places are not and will never be "unicorns", so analyzing things at that scale may not make sense. Just because Facebook managed to make something work doesn't mean that you should follow the same path.
I've been a hardcore and exclusive Postgres user since 2004. Always rolled my eyes when folks say they use MySQL or moved to it from Postgres. Reading about that bug sent shiver's down my spine. I know no system is bug free, but Postgres is one of those systems that have held up very well for me, and now I'm super paranoid. :-( I guess I understand tho, I moved from BSD to Linux for more tooling.
Facebook 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].
And yes, MySQL has a lot of problems, but it seems to be better for "seat of the pants" usage (that is, when you don't need too many SQL capabilities and wants to use it mostly as a data store)
I believe they use it for almost everything. They built all the graphs on top of MySQL data. I don't know what permanent storage is, but if you are referring to storing images and videos, I doubt. Highly doubt that. They may be storing pointers, but as a file system, I doubt. But I wouldn't know for sure, I don't work there. Oh, they do have Cassandra (well they built Cassandra)...
There was a great talk from a FB engineer who talked about using MySQL as a key-value store, then another engineer posted a blog about how they use MySQL and queries and joins etc, I asked about the video and he got super defensive and said it was wrong and the guy had no idea what hes talking about.
It really put me off ever wanting to even consider working at Facebook.
Digging around I can't find either the blog or video :(
I've talked to several Facebook devs who confirmed to me that FB primarily uses MySQL as a key-value store (e.g. this is why GraphQL makes so much sense for them). They were quick to point out however that of course they don't know everything FB does.
The problem with making absolute statements about what FB does is that it seems to be structured as a large number of mostly independent teams that are free to choose and develop whatever technology they need to solve their problems. There's actually a talk about scaling by a FB dev (don't have the link right now) that uses this as an example for what it means when they talk about "Facebook scale" (another example is that their monorepo outgrew git so they created their own extension to mercurial instead).
It's entirely possible that the main use of MySQL at FB is as a key-value store while at the same time there are small parts of applications using it with plain old queries and joins.
Ahh yeah good point. FB's scale is very interesting, it's like we solved scalability then FB came along and was like, ah crap we need to resolve scalability. And different teams are solving their own problems different ways.
Well, this is heresy. Does that mean we are now officially boycotting Uber?
Joke asides, one thing I've been trying to figure out for awhile is the limitation at which certain components/ systems broke down. Basically, something along the line of "given X records, this operations would take Y time, or would cause Z A B C problems". I've actually got developers friends asking me how fast a simple "SELECT * FROM X WHERE index=?" would take on a million row table, since they were surprised that some NoSQL DB could do a query on hundred million rows in a few seconds.
I guess that's part of why you only learned how to scale after having done it once.
"surprised that some NoSQL DB could do a query on hundred million rows in a few seconds"
The tone seems to suggest this is fast, a simple index query in an RDBMS of even a hundred million rows will take milliseconds on even a weak computer.
Justify that with actual math, please? I don't think you know how computers (processors, bus, memory access, etc) work. How exactly do you think an RDBMS can query an index at a hundred billion rows per second?
Illniyar is right, a b-tree with a branching factor of 100 can index 100M rows in 4 levels. Even with the index on a spinning disk, should have no trouble coming in well under 100ms.
Indexes like this are also typically in memory to begin with, making it even faster, since you only have to find the actual record on disk.
Even better, if you only need one field from the record, and it's part of a compound index, you can frequently return the data from just the indexes; no disk seeks required. Small tip with InnoDB on MySQL - any non-primary key index is automatically a compound index with the primary key.
The parent said a query of a hundred million rows, i.e.: `select * from users where id = ?` when there are a hundred million users. That's obviously not scanning a hundred million rows, since the index will be a b-tree which by the power of logarithms is not so many levels deep.
Like Illniyar, that's also what I interpreted GP to mean when they said:
> "surprised that some NoSQL DB could do a query on hundred million rows in a few seconds"
which is not fast at all. However, if it actually did something on a hundred million rows that would be impressive. Not sure exactly sure which they meant, though.
I was testing some JSONB indexing and did a date-range query on a json doc for a table with 100m records to test with, it returned top 10 records that met the criteria in ~4s, figured out how to index the json doc for dates and this dropped to ~25ms.
This was running on an old Core i5 / 16gb of ram with a lot of junk running on Windows.
It would probably have been faster on a production environment.
We too often rely on a buzz-word heuristic and that's how you end up with dozens of random technologies that are harder to maintain and don't necessarily solve any of your problems. This method is good, because it shows that when you understand the problem the right way, you can find the right solution, even if by popularity it looks like a "step backwards"
I strongly disagree. it would have been useful f they'd stuck to problems without well-known solutions.
Sadly, they also mixed in issues which are easily solved, or in a particularly egregious case, where they just complain about a bug. As though MySQL never had a bug. That was silly.
My read of it was: Postgres annoyed us a few times, and we got fed up with its, so now something different will annoy us. Please look forward to our blog post in 4 years about how we're using X instead of MySQL/Schemaless because those were also imperfect.
I got a similar impression... though with Uber's scale, funding and resources, they probably could have worked with and through their issues with Postgres. I'm actually surprised they didn't take a multi-pronged approach to their issues. Since they're using Schemaless, I'm curious why they didn't go for one of the many non-sql databases that may well be a much closer match to their use case.
It seems to me that Cassandra (C*) may have been a better match to their needs... yes it means more administrative and application tuning, but would definitely scale to meet their needs. RethinkDB would also likely be a better match to what they are wanting to do.
That said, I've been holding out for some time on PostgreSQL's in the box replication story to take root and mature. There are definitely more mature features and solutions to sharding and replication around MySQL, I just tend to find MySQL to be brittle and every time I've ever worked with it, I have at least a half dozen WTF moments... from binary data handling/indexing, foreign key syntax, ANSI out of spec, and others. PostgreSQL has some great features, and once the replication issues settle in, it will become the default choice for a lot of projects in a lot of organizations. Though, mySQL/maria and even MS-SQL are currently better options for many SQL use cases.
I'll preface what I'm about to say with "I have never worked for Uber and I don't know terribly much about their internal structure", but from my interviews with Uber and a few of their hires I know, it seems that they tend towards hiring totally independent teams from the existing staff when tackling big projects...including hiring an outsider manager to hire a whole team. I won't speculate as to the reasons for this publicly but I've drawn some interesting conclusions from this.
A multi-pronged approach that might involve multiple stakeholders just doesn't seem like their way of doing things.
But, what is the current replication setup that comes with postgres that is well documented with the PostgreSQL (current-version) documentation... the past, when I've looked there's mention of 2-3 solutions (none in-the-box) and others that require at least a 5-figure support contract.
Compare to MongoDB, RethinkDB, MS-SQL and others where the tooling for replication comes in the box. Yes, to of the examples are "no-sql" but even the mysql replication is in the box and supported as such.
Making a lot of "buzz-word" errors like that, I now use the simple heuristic: choose the oldest available technology that solves your problem.
Also known as Lindy effect
https://en.wikipedia.org/wiki/Lindy_effect
id first last birth_year
1 Blaise Pascal 1623
2 Gottfried Leibniz 1646
3 Emmy Noether 1882
4 Muhammad al-Khwārizmī 780
5 Alan Turing 1912
6 Srinivasa Ramanujan 1887
7 Ada Lovelace 1815
8 Henri Poincaré 1854
(I know, I know. It is possible to configure MySQL encodings correctly. And given that they've put a lot of engineering thought into choosing MySQL, they certainly have.)
I used both Postgres and MySQL and in my opinion encodings are more difficult to setup with Postgres. You need to change some "template" when creating a database to use unicode. Otherwise it will use latin1. I do not even understand what a database "template" is, how it is related to encodings, and why it is so overcomplicated.
In MySQL there is no templates and you can change the encoding of a table at any time using ALTER TABLE statement.
And in cloud IDEs like c9.io you cannot use unicode collations in Postgres because they need to be installed separately and they are not installed.
You certainly don't need to change any templates when creating a database, CREATE DATABASE and createdb both support specifying the encoding.
Changing encoding of a table (or a database after creation) is usually not a wise thing to do; since the db engine is not going to go over all the data and convert it anyway, so if your data is corrupted now it will remain corrupted. If one simply needs to tell the DB to treat the data differently without worrying about data conversion, the encoding of a database is stored in `pg_database` and can be changed with an UPDATE query.
And this is all from the official documentation. I'm not a Postgres expert.
I can confirm. I recently set up a private mattermost server. Because the host I'm running it on didn't have the UTF-8 locale on by default (I think), Postgres defaulted to Latin-1, and mattermost ran fine, right up until someone sent a UTf-8 character which is unrepresentable in Latin-1.
Don't know if it's still an issue, but I once setup mysql, with an id of binary(16) to store UUIDs, and used 0-padded conversions for legacy id's... when trying to port over existing records I discovered the index on a binary field was a "case-insensitive" textual index by default, so started getting conflicts when the ascii equivalent of "a" and "A" as a byte was inserted, etc. This was about 15 or so years ago, but the irksome nature still sticks with me to this day.
I wouldn't be surprised if that behavior remains the same. MySQL seems to be fine with long-standing bugs and other sub-optimal behavior hanging around, eg. https://bugs.mysql.com/bug.php?id=20786 .
The connection handling section was surprising to me, reading that Postgres uses a process per connection! This is pretty shocking to me, in a bad way.
you install it on the clients of the servers themselves and "connect locally" to the bouncer or have a bouncer step (server pair) in front of the database connections themselves.
One could use connection pooling. Quoting from the tuning guide [0]:
max_connections sets exactly that: the maximum number of client
connections allowed. This is very important to some of the
below parameters (particularly work_mem) because there are some
memory resources that are or can be allocated on a per-client
basis, so the maximum number of clients suggests the maximum
possible memory use. Generally, PostgreSQL on good hardware can
support a few hundred connections. If you want to have
thousands instead, you should consider using connection pooling
software to reduce the connection overhead.
Replication, Clustering, and Connection Pooling [1]
Different design goals,
Mysql lot of connections with shared memory model and fast running small queries, ex: PHP one connection per page view.
Postgres dedicated memory model long running complex queries.
So PG has a process pool instead of a thread pool. (I doubt either database is spawning procs/thds anew willy nilly for each request).
This means the PG has explicit IPC overhead, vs the quick and seductive path (to the dark side?) of simply sharing memory between threads. Safety vs speed.
Wow, this is such a detailed analysis. Having used Postgres and suffered issues with data replication as well as database crashes, this post was really helpful.
while Postgress might be better if you use it 'as-is'....
community of MySQL is much better and the tools available are more mature...
just goes on to prove that even if something is not-that-good..
it still might be successful,scalable and popular if there is a strong community behind it..
I wonder what the design decisions are behind (or what it would take) to make Postgres store secondary indexes on disk like InnoDB does. Sure, the extra index lookup through the primary index is a cost, but it seems like write-amplification can sure be a greater concern too. Ultimately, it would be nice if Postgres gave the DBA a choice of — if not move outright to — secondary-index indirection through the primary index like InnoDB does.
I would definitely not want PostgreSQL to "move to" the mechanism used by InnoDB as that mechanism is slower for reads (which this article even admits). This disk layout is one of the reasons I continue to use PostgreSQL. If you care more about write performance than reads you might even want to look into something entirely different than a traditional database.
FWIW, PostgreSQL's mitigation to the write amplification problem mentioned here are "heap only tuples". It is highly likely that Uber could have prevented a lot of this pain by learning more about this feature--which is notably not mentioned even once in this entire article, which to me completely undermines the feel they are trying to achieve of "we really really really know what we are doing"--and tuning their table density parameters to take maximal advantage.
Instead of "why we moved off of X" it would be much better to see "we are considering moving off of X: anyone know what we are doing wrong?". Sure, maybe Uber knows about HOT, and did extensive analysis to determine it wasn't a solution to their problem; but it frankly does not seem at all to be the case. Anyone who knows a lot about PostgreSQL would have explained HOT to them, so they probably didn't even consult with PostgreSQL experts behind the scenes.
Sadly, "we are looking for help with a complex technical challenge" is something the market punishes under the premise that everyone has to be entirely self-sufficient gods of their technology stack :(. The only time I remember ever having seen a company reach out to the community for help was reddit (with respect to something involving PostgreSQL or Cassandra... I don't remembee the specific issue).
Thank you for introducing me to "heap only tuples", I did not know about it. From what I read about it from Postgres's documentation[0], it would have helped in the updates where no indexed column was updated.
This is a fantastic read. I hope the pg folks can turn as many of the issues brought up here into bug reports as possible (I think many of the issues, especially re: replication, are known), this kind of feedback is invaluable.
Some of these may be tradeoffs rather than bugs. I'd love to read a reply from someone knowledgeable about Postgres internals - it'd be very interesting.
Yep! saurik's reply above sheds some light on the issue (read more important than write). Also, to the downvoter, sometimes the solution to a bug report is improved and more visible documentation of existing features.
Accordingly, using pgbouncer to do connection pooling with
Postgres has been generally successful for us. However, we have
had occasional application bugs in our backend services that
caused them to open more active connections (usually “idle in
transaction” connections) than the services ought to be using,
and these bugs have caused extended downtimes for us.
What I don't understand: no system gets to maintain open transactions for free. MySQL keeps UNDO logs, so the effect there, much like Oracle, is possibly running out of UNDO space, as well as slowing down reads that have to apply UNDO to get the last-committed row version. So what gives? Did Uber Engineering fix the dangling transaction issues while migrating off, or are they relying on some other property of MySQL vs. Postgres?
We did something very similar at EA Playfish, at least one alumni of which is part of the Uber engineering team.
We used a 2 column InnoDB-backed table for all of our data storage, massively sharded, and run in a 3-host master-slave-slave configuration.
At that time EC2 would routinely kill hosts without the courtesy of a poke via ACPI and as such we became very good at quickly recovering shards. In a nutshell this mechanism was to have the new host contact a backup slave, perform an lvm snap, pipe the compressed snap over a TCP connection, unroll it and carry on, letting replication take up the delta.
That enabled us to not only manage the 10 million or so daily active users of that title, but was also the platform under the 12 or so additional titles that studio had.
We had lots and lots of very simple things and failures were contained.
I think at the time we were the 3rd-largest consumer of EC2 after Netflix and "another" outfit I never learned the name of. EA being what it was, however, we were never permitted to open source a lot of the cool stuff Netflix and ourselves seemed to develop in parallel.
this is frikking awesome!
do you have any of the lvm and pipe scripts publicly available ? i'm kinda struggling with building and setting up lvm on ec2 automatically and was wondering if there is any tidbits you can pass along.
Unfortunately not. That company and codebase is very much dead, and I don't own any of it. Even if I did it would be a bunch of context-less shell (in the first iteration) and then a bunch of context-less Chef (in the second platform iteration).
Things I do remember:
- We used ephemeral volumes for all data stores. This was pre-provisioned IOPs and EBS was flaky as heck back then. I can't remember the disk layout, although we did experiment a great deal.
- We took great pains to ensure there was enough space to make the snapshot (IIRC is was a telemetry/monitoring item)
Why would anyone run hundreds of connections? A server can only process number_of_processor_cores connections at once. Sure, few connections might wait for I/O, but not hundreds, unless database is very untypical.
For example: You are using Python and you have 10 web servers and 20 background servers connected to a common DB. Each server has 10 threads, and each thread holds 1 connection open. That is 300 open connections.
Opening and closing connections is very slow and expensive, so almost always better to keep these 300 connections open than to try to be fancy.
You COULD try to say give each server only 3 connections and make them share, which cuts you to 90 connections.. but then you have to try to share state between different python processes (not easy), and will often end up with deadlocks and sync overhead.
Agreed, which is why many clients create a pool of connections that gets reused. Connection cost is expensive, and the rdbms already handles concurrency and even a couple thousand connections shouldn't be a significant overhead.
I'm trying to find evidence on what memory usage is for MySQL for 1k connections vs postgres with 1k connections. I am finding a lot of people saying postgres has heavier connections but for 1k connections what's the difference. 1MB of memory? 1GB?
Its a little outdated but I've found it largely holds. That is, you'd want a mighty box for 1000 concurrent connections.
That said, connection queuing works really well with postgres such that throughput is frequently better at lower connection counts than at higher ones.
I have no experience with mysql that is less than 15 years out of date.
Like you said, the connection is still in use while waiting for I/O, at which point, another process or thread runs a bit and can also kick off some I/O on yet another connection.
Numbers vary, but that's the principal that uses more connections than cores.
In my experience DB connections are overwhelmingly idle. Even if you omit the obvious dead times between requests and consider only actively executing connections, a request will spend most of the time waiting for a resource or another (read IO, commit flush, latches, locks). It takes an extraordinarily well tuned app to be able to drive all connections to be anywhere near 100% CPU bound and never conflict or wait.
the connections are idle, are routed to dozens or hundreds of separate processes themselves, and are held ready for fast response when that process needs to serve a request.
Unfortuantely, MySQL has no good replacement for Postgresql's PGBouncer which greatly mitigates the issue of cross-process connection pooling. (I'm actually working on one, but for Uber to use it they'd have to switch back to SQLAlchemy >:) )
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 clustered b-tree indexes... which is really what MySQL does that makes it somewhat "better." I wonder how Uber adds an index to a table that already has 1B+ rows in it with mysql?
Postgres have WAL level replication is a better guarantee of actually replicating the data correctly. I cannot tell you how many times I've had to tell my boss that the "mysql replicas might be slightly out of sync with the master" because of various replication issues. The way it handles triggers and scheduled events alone is garbage and can very easily break replication and/or silently cause inconsistency.
As for data corruption, if there is a bug that causes corruption, then there is a bug. I don't think that is a fundamental design flaw as implied in this article. You shouldn't rely on 1/2 assed replication design to accidentally save you from the data corruption bug. There are many downsides to the design MySQL has that are simply not listed here.
I have been both a professional MySQL administrator as well as Postgresql (as well as SQL Server and many NoSQL engines). Many of these Postgres issues are only issues at crazy huge scale, and I would say at that point you probably want to move away from relational anyway. MySQL has its own very large set of problems at scale as well.
It sounds like Uber is using MySQL as just a data bucket with primary keys ("Schemaless") which is good -- because you can't alter tables to save your life with MySQL.
At the end of the data each developer/business needs to use what works for them, but I would really shy away from pointing to this article as a linchpin in the "MySQL vs. Postgres" war (if there even is such a thing.)
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 recommends using this tool to safely perform alters for any Galera-based replication product (Percona XtraDB Cluster, MariaDB Galera Cluster, etc.)
While this is true, i don't personally think Percona tools alone makes these changes 'safe'. You can create scenarios where performance is so degraded that you introduce replication delay or service interruptions (and yes the tools have ways to work around that, but it's a significant challenge to tune this correctly on production), data can be lost during the table switch over, and the complication of managing a product / database with a table in an extended migration is very difficult.
This space is far from done, until we can change data structures on the fly...we're going to find there to be a constant struggle between schema and schema-less and neither side will be right or wrong.
Solution: Quantum states; We suspect every possible scenario is happening already, so we should just migrate our database to the appropriate quantum state, where the data is in the structure we desire. Sounds insane, it probably is.
Your absolutely correct that it is not risk free, however, if you do not use foreign keys (which no huge scale mysql install like schemaless does) and you know what your doing (read: calculating the expected load, adding throttles and sanity checks, backups, etc) online alters are extremely feasible with mysql and folks have been doing them for years.
I know first hand shops like FB use a similar method in production.
Also, big note, the whole point of "schemaless" (And other things, like fb's friend feed architecture) is that you don't make schema changes.
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.
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 Twitter ditched Rails. The focus should be inward... how they chose a tool that didn't support their use case and how they solved the problem, but instead they're about the flaws (that aren't really flaws) of the tool.
While the article is discussing PostgreSQL vs MySQL, Uber's actual win seems to be from switching their data model to their sharded Schemaless key=value store. I expect they would have got this win no matter what backend they chose for Schemaless.
> It sounds like Uber is using MySQL as just a data bucket with primary keys
They have a couple posts about "Schemaless", but I still don't understand why they used MySQL as the data store instead of something like Cassandra. ( https://eng.uber.com/schemaless-part-one/ ) From that post it looks like they basically built a no-sql database on top of a relational database.
The only reason given was operational trust ( "If we get paged at 3 am when the datastore is not answering queries and takes down the business, would we have the operational knowledge to quickly fix it?" ). The project took nearly a year to roll out, and in that time the operation knowledge could surely be trained, hired, or contracted.
I was at MesosCon and ended up talking to some Uber people. They are currently using Cassandra in prod. I can't speak as to why they use MySQL the way they do though.
So arguably, they are using mysql as a storage engine rather than as a database.
They don't explicitly answer the question "Why didn't you use InnoDB/WiredTiger/etc. for your dataplane?", but you get the idea that they were very happy with the specific characteristics of MySQL for their use case and so they built on top of it. It also sounds like they had some deadlines (specifically, the death of their datastore) that they had to meet :).
Operating Cassandra at the scale that Uber is going to require is going to be painful and as operationally draining as MySQL if not more.
There are really not a large number of options here anymore with the departure of FoundationDB from the market. CockroachDB might be an option in a few years, though I'm still confused why they are moving towards a SQL-ish vs key-value interface...
Pissed me off so much. Only thing close to Google's F0 RDBMS on the market, at a reasonable rate, and the beginning of a good offer to enterprises. Then, "poof!" It's a good example of why I tell companies to not put anything critical into something from a startup. If they do, better have a synchronized, backup option tested and ready to go.
"why they are moving towards a SQL-ish vs key-value interface..."
That's easy: most databases and buyers use SQL. Key-value is preferred by startups & non-critical, side projects in big companies you see here a lot but aren't representative of most of the market. Need first-rate, SQL support. I think EnterpriseDB shows that it's also a good idea to clone a market leader's features onto alternative database.
I had that same thought, that the time spent rolling their own system could be better spent just learning some existing good-enough thing.
A great way to get familiar with something is to be the folks who write it. It's also much more fun to design and implement something new than to just learn some other fella's software. I'm guilty of this myself.
But I've started to remind myself that "somebody else has had this problem" and there's probably a good enough solution out there already.
Put another way, is what you are trying to do really so novel? In the case of Uber's infrastructure, you would have to talk for awhile to convince me that they really really need something not-off-the-shelf.
I wouldn't have trusted Cassandra back then either. 0.9, 1.0 or maybe 1.2 was reaching sufficient maturity to actually be recommended. Modern Cassandra has come leaps and bounds, with the 2.x series finally becoming stable this year and just recently 3.0.x finally getting blessed by the community as stable enough for production. And ScyllaDB hot on their heels.
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. Definitely worth a try.
But again, the title of the article sounded nothing more than a rant to me.
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.
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.
> Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates.
While the master may be technically up for writes during this period, it's not much a goer if your table is large and has any write traffic at all, as anything writing will stall for what may be an extended period.
It should never be an extended period. Everyone everywhere will advise to keep your transactions as short as possible.
Sure this is sometimes boring additional work - eg you don't delete 1M records with one statement, you break it into 1,000 statements each deleting 1,000 records.
Sucks, but keeps your db and your users happy.
BTW this is true for PostgreSQL and MySQL and Oracle and every db that allows concurrent DML.
No, the migration itself will cause transactions to stall.
For example, if you have a table with 1M user records, and you run a migration to add a column in MySQL, then any updates to the table will be stalled while the table is rewritten to add the extra column (which may take a while). This is independent of how many records it touches - even if the transaction only touched 1 record and would take 10ms to execute, if the migration takes 10 minutes it may be stalled for up to 10 minutes.
In Postgres you can add a nullable column, and the table will only be locked for a very short amount of time, independent of the size of the table.
Interesting post! While I suspect that a MySQL installation is just as likely to have its own problems in the long run, I'm not smart enough to provide any kind of compelling point-by-point refutation. However, a number of the points made strike me as having possible trade-offs that were not really addressed in-depth.
My summary of the arguments against Postgres and some basic thoughts on each:
1. Writes are more expensive because all secondary indexes must be updated with a new physical location.
This may be true, but the MySQL model of using primary keys from secondary indexes will mean that reads are inherently expensive. They even mention this:
> This design means that InnoDB is at a slight disadvantage to Postgres when doing a secondary key lookup, since two indexes must be searched with InnoDB compared to just one for Postgres.
So it seems like a classic read vs. write trade-off.
I'm also a little skeptical of any performance claims that don't include any numbers. It's possible that efficient coding in Postgres makes this much more of a wash in terms of performance than claimed here.
2. Replication is less efficient because it's sending a lot of physical information out along the stream.
This is quite true, but IMO unlikely to be a major issues for most users unless they're dealing with a huge amount of data and streaming it over a slow connection (i.e. across the continent like Uber's disaster recovery center).
3. Data corruption from a bug found in 9.2.
Certainly a bad situation, but IMO not really a valid claim for situation. 9.2 is way behind at this point, and there's not much to say that they wouldn't have encountered a similar bug or something worse in MySQL in all that time, especially operating at scale.
To give a counter-anecdote, I operated Postgres at scale for a long time across many versions starting at 9.1 and was lucky enough to have never once encountered a bug with data corruption.
4. Postgres' MVCC model makes it easy for replicas to accidentally fall behind their master.
This one is valid (and annoying), but there are very good reasons for it, and you have some switches to control the behavior based on value transactions finishing on followers or prompt replication more highly.
5. Upgrades are difficult because the WAL stream works at a physical level and is not compatible between database versions.
Again, this is valid, but the statement-based replication is a scary idea. Row-level replication is more interesting and probably something that Postgres should have though.
Some good news is that Postgres is getting closer to logical WAL streaming, which should make in-place upgrades possible.
My big question here is why they decided to move over to MySQL instead of using the Citus (also open source) Postgres extension. They don't mention it, so we don't know whether it was considered, and if so, why it was not selected. Postgres' rapid feature growth in the past years is interesting in itself.
Citus currently has major problems joining non-distributed tables with distributed tables, and it doesn't fully support schemas yet. It's almost there, but not quite.
Postgres Professional and Enterprise DB are two companies that provide Prostgres products and services. The linked roadmaps are their respective plans on what they want to add to the open source Postgres database.
I'm not sure this post is illustrative of any generally applicable considerations (re: the title) in the choice of Postgresql vs MySQL, since Uber seems to no longer be using a relational model for most of their data and is using MySQL effectively as a key-value store.
> say a developer has some code that has to email a receipt to a user. Depending on how it’s written, the code may implicitly have a database transaction that’s held open until after the email finishes sending. While it’s always bad form to let your code hold open database transactions while performing unrelated blocking I/O, the reality is that most engineers are not database experts and may not always understand this problem, especially when using an ORM that obscures low-level details like open transactions.
I have to very seriously disagree here, ORMs make a lot of things easy - and you can get away with building stuff for a while without understanding the underlying databases or SQL but only to a certain scale (I'd say more like medium-scale, definitely not large or Uber level). If you have engineers writing code that interacts with a database without understanding transactional semantics, the engineer in question not the database is the problem.
> We started out with Postgres 9.1 and successfully completed the upgrade process to move to Postgres 9.2. However, the process took so many hours that we couldn’t afford to do the process again.
There seem to be ways [0][1] to do online upgrades with Postgres (before logical decoding in 9.4), although I haven't personally used them. Not sure if they explored these options at Uber or not?
In spirit I agree with you, its the engineer's fault for not reading the documentation of their ORM or equivalent.
But in these big ships with hundreds of programmers, leaving transactions open in hibernate is a daily occurrence somewhere.
Usually caught before production, but happens oh-so-frequently, that anything that exacerbates the pain from this would be seen in a negative light for sure.
So basically, if you don't intend to use it as a relational database, and you have enough scale to run cross-data-center (and across-the-world) master-master replication, then you should maybe switch from PostgreSQL to MySQL?
305 comments
[ 2.5 ms ] story [ 358 ms ] threadAnyone know why they are using MySQL over MariaDB[1]?
1. https://mariadb.org/
[0] http://insights.dice.com/2015/03/19/why-i-choose-postgresql-...
[1] https://news.ycombinator.com/item?id=9231751
it happens everywhere.
> [...] This design difference means that the MySQL replication binary log is significantly more compact than the PostgreSQL WAL stream.
Doesn't sound like what you described at all.
on-disk format/write amplification: > For tables with a large number of secondary indexes, these superfluous steps can cause enormous inefficiencies. For instance, if we have a table with a dozen indexes defined on it, an update to a field that is only covered by a single index must be propagated into all 12 indexes to reflect the ctid for the new row.
How wide is their data? Depending on the answer to this, it could be that they've over-indexed, have a poor indexing strategy, or are reacting to the poor queries generated by an ORM (not sure if they use one, or if they hand-code their own SQL).
data corruption: everyone has bugs. https://bugs.mysql.com/search.php?search_for=&status=Active&... add in mysql's tendency to loosely-adhere to the SQL standard and there are many ways that you can actually corrupt your own data.
i'm not here to debate whether postgres is better than mysql. i'm just saying that it seems like a lot of research went into justifying a switch. who knows, maybe that research could have been spent optimizing their current environment.
Better than telling them that they are wrong while not only not pulling even one line from their article, but misattributing it to be something very different from what it is.
In what world does the response to the concerns and analysis in the article can ever be: "data corruption: everyone has bugs"...
MySQL handles it differently than Postgres, and gives them better performance for their purpose (based on their experience/test). They were explaining it in the parlance of the terms that MySQL and Postgres. If those are buzzwords, then MySQL and Postgres are both created using buzzwords?
Their explanation is not perfect (for me, why do their datamodel needs massive updates?). But I wouldn't write it off as buzzwords and dismissing Postgres because of data corruption. There are a lot of other things they were trying to explain there.
Combining that fact with the way Postgres and InnoDB handle secondary indexes means WAL-shipping not only ships the entire new row, but also all the disk blocks of all the secondary index updates, unlike MySQL's row-based replication.
This is actually something I greatly like and want to see in Postgres. Perhaps the decision to choose secondary indexes with direct links to rows was taken because at that time (before replication) it was less read-heavy and the write-amplification wasn't such a concern. But now, when replication is a common requirement (and network IO is not as fast as disk IO) it makes a lot of sense to switch to a single-point-of-update allowing way of storing secondary indexes
(And even microservices are established practices -- heck Amazon was built on them more than a decade ago).
The amount of research about the on-disk internals of both PostgreSQL and MySQL are a lot more effort than I would have probably spent (granted, I don't have a team of highly paid devs at my disposal, but still, I've seen technical decisions made on the basis of Google Trends...).
0: https://news.ycombinator.com/item?id=12167161
The "laundry list of buzzwords" is a detailed, up to engineering standards, analysis of the issues they faced, with example cases and explanatory follow-up.
As far away from a "laundry list of buzzwords" as you could possibly get. Not to mention they did the very opposite of switching willy nilly to some new "shiny toy".
In other words, the comment is not even wrong.
Disclaimer, I'm a hardcore Postgres user myself, but I also keep tabs on the other tools.
[0] https://github.com/facebook/mysql-5.6
[1] https://www.youtube.com/watch?v=jqwegP9xwVE
[2] https://www.youtube.com/watch?v=UBHcmP2TSvk
And yes, MySQL has a lot of problems, but it seems to be better for "seat of the pants" usage (that is, when you don't need too many SQL capabilities and wants to use it mostly as a data store)
It really put me off ever wanting to even consider working at Facebook.
Digging around I can't find either the blog or video :(
The problem with making absolute statements about what FB does is that it seems to be structured as a large number of mostly independent teams that are free to choose and develop whatever technology they need to solve their problems. There's actually a talk about scaling by a FB dev (don't have the link right now) that uses this as an example for what it means when they talk about "Facebook scale" (another example is that their monorepo outgrew git so they created their own extension to mercurial instead).
It's entirely possible that the main use of MySQL at FB is as a key-value store while at the same time there are small parts of applications using it with plain old queries and joins.
Joke asides, one thing I've been trying to figure out for awhile is the limitation at which certain components/ systems broke down. Basically, something along the line of "given X records, this operations would take Y time, or would cause Z A B C problems". I've actually got developers friends asking me how fast a simple "SELECT * FROM X WHERE index=?" would take on a million row table, since they were surprised that some NoSQL DB could do a query on hundred million rows in a few seconds.
I guess that's part of why you only learned how to scale after having done it once.
The tone seems to suggest this is fast, a simple index query in an RDBMS of even a hundred million rows will take milliseconds on even a weak computer.
Even better, if you only need one field from the record, and it's part of a compound index, you can frequently return the data from just the indexes; no disk seeks required. Small tip with InnoDB on MySQL - any non-primary key index is automatically a compound index with the primary key.
Like Illniyar, that's also what I interpreted GP to mean when they said:
> "surprised that some NoSQL DB could do a query on hundred million rows in a few seconds"
which is not fast at all. However, if it actually did something on a hundred million rows that would be impressive. Not sure exactly sure which they meant, though.
This was running on an old Core i5 / 16gb of ram with a lot of junk running on Windows.
It would probably have been faster on a production environment.
We too often rely on a buzz-word heuristic and that's how you end up with dozens of random technologies that are harder to maintain and don't necessarily solve any of your problems. This method is good, because it shows that when you understand the problem the right way, you can find the right solution, even if by popularity it looks like a "step backwards"
Massive Kudos.
Sadly, they also mixed in issues which are easily solved, or in a particularly egregious case, where they just complain about a bug. As though MySQL never had a bug. That was silly.
My read of it was: Postgres annoyed us a few times, and we got fed up with its, so now something different will annoy us. Please look forward to our blog post in 4 years about how we're using X instead of MySQL/Schemaless because those were also imperfect.
Doing so deliberately is whats commendable.
It seems to me that Cassandra (C*) may have been a better match to their needs... yes it means more administrative and application tuning, but would definitely scale to meet their needs. RethinkDB would also likely be a better match to what they are wanting to do.
That said, I've been holding out for some time on PostgreSQL's in the box replication story to take root and mature. There are definitely more mature features and solutions to sharding and replication around MySQL, I just tend to find MySQL to be brittle and every time I've ever worked with it, I have at least a half dozen WTF moments... from binary data handling/indexing, foreign key syntax, ANSI out of spec, and others. PostgreSQL has some great features, and once the replication issues settle in, it will become the default choice for a lot of projects in a lot of organizations. Though, mySQL/maria and even MS-SQL are currently better options for many SQL use cases.
A multi-pronged approach that might involve multiple stakeholders just doesn't seem like their way of doing things.
Then they wouldn't get to build cool new stuff and write blog posts about how they had to build cool new stuff because OMG UBER SCALE.
It would randomly die, but that was always either my fault or the applications fault, never pgsql itself.
The lack of master-master seems to be the big thing everyone mentions, but PostgresXL is currently in a usable-in-production state.
Compare to MongoDB, RethinkDB, MS-SQL and others where the tooling for replication comes in the box. Yes, to of the examples are "no-sql" but even the mysql replication is in the box and supported as such.
https://www.postgresql.org/docs/current/static/high-availabi...
I'm not sure what more you can ask from documentation.
Does MySQL document Vitess, Galera, MaxScale, etc...?
Make no mistake, they are all great mathematicians and deserve a place in math pantheon.
In MySQL there is no templates and you can change the encoding of a table at any time using ALTER TABLE statement.
And in cloud IDEs like c9.io you cannot use unicode collations in Postgres because they need to be installed separately and they are not installed.
Ugh.
Changing encoding of a table (or a database after creation) is usually not a wise thing to do; since the db engine is not going to go over all the data and convert it anyway, so if your data is corrupted now it will remain corrupted. If one simply needs to tell the DB to treat the data differently without worrying about data conversion, the encoding of a database is stored in `pg_database` and can be changed with an UPDATE query.
And this is all from the official documentation. I'm not a Postgres expert.
That was No Fun™ to solve.
[0] https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serv...
[1] https://wiki.postgresql.org/wiki/Replication,_Clustering,_an...
This means the PG has explicit IPC overhead, vs the quick and seductive path (to the dark side?) of simply sharing memory between threads. Safety vs speed.
Can you backup your statements with some facts. I have seen the postgres community to be much better particularly now with Oracle taking ownership.
It won't specifically address the problems posted here, but will solve other ones like trying to scale a system beyond a single node.
FWIW, PostgreSQL's mitigation to the write amplification problem mentioned here are "heap only tuples". It is highly likely that Uber could have prevented a lot of this pain by learning more about this feature--which is notably not mentioned even once in this entire article, which to me completely undermines the feel they are trying to achieve of "we really really really know what we are doing"--and tuning their table density parameters to take maximal advantage.
Instead of "why we moved off of X" it would be much better to see "we are considering moving off of X: anyone know what we are doing wrong?". Sure, maybe Uber knows about HOT, and did extensive analysis to determine it wasn't a solution to their problem; but it frankly does not seem at all to be the case. Anyone who knows a lot about PostgreSQL would have explained HOT to them, so they probably didn't even consult with PostgreSQL experts behind the scenes.
Sadly, "we are looking for help with a complex technical challenge" is something the market punishes under the premise that everyone has to be entirely self-sufficient gods of their technology stack :(. The only time I remember ever having seen a company reach out to the community for help was reddit (with respect to something involving PostgreSQL or Cassandra... I don't remembee the specific issue).
0: https://wiki.postgresql.org/wiki/Index-only_scans#Interactio...
We used a 2 column InnoDB-backed table for all of our data storage, massively sharded, and run in a 3-host master-slave-slave configuration.
At that time EC2 would routinely kill hosts without the courtesy of a poke via ACPI and as such we became very good at quickly recovering shards. In a nutshell this mechanism was to have the new host contact a backup slave, perform an lvm snap, pipe the compressed snap over a TCP connection, unroll it and carry on, letting replication take up the delta.
That enabled us to not only manage the 10 million or so daily active users of that title, but was also the platform under the 12 or so additional titles that studio had.
We had lots and lots of very simple things and failures were contained.
I think at the time we were the 3rd-largest consumer of EC2 after Netflix and "another" outfit I never learned the name of. EA being what it was, however, we were never permitted to open source a lot of the cool stuff Netflix and ourselves seemed to develop in parallel.
Things I do remember:
- We used ephemeral volumes for all data stores. This was pre-provisioned IOPs and EBS was flaky as heck back then. I can't remember the disk layout, although we did experiment a great deal.
- We took great pains to ensure there was enough space to make the snapshot (IIRC is was a telemetry/monitoring item)
- The pipe scripts were essentially "netcat".
The best I can offer is this talk: http://vimeo.com/57861199
Opening and closing connections is very slow and expensive, so almost always better to keep these 300 connections open than to try to be fancy.
You COULD try to say give each server only 3 connections and make them share, which cuts you to 90 connections.. but then you have to try to share state between different python processes (not easy), and will often end up with deadlocks and sync overhead.
Its a little outdated but I've found it largely holds. That is, you'd want a mighty box for 1000 concurrent connections.
That said, connection queuing works really well with postgres such that throughput is frequently better at lower connection counts than at higher ones.
I have no experience with mysql that is less than 15 years out of date.
Numbers vary, but that's the principal that uses more connections than cores.
Unfortuantely, MySQL has no good replacement for Postgresql's PGBouncer which greatly mitigates the issue of cross-process connection pooling. (I'm actually working on one, but for Uber to use it they'd have to switch back to SQLAlchemy >:) )
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 clustered b-tree indexes... which is really what MySQL does that makes it somewhat "better." I wonder how Uber adds an index to a table that already has 1B+ rows in it with mysql?
Postgres have WAL level replication is a better guarantee of actually replicating the data correctly. I cannot tell you how many times I've had to tell my boss that the "mysql replicas might be slightly out of sync with the master" because of various replication issues. The way it handles triggers and scheduled events alone is garbage and can very easily break replication and/or silently cause inconsistency.
As for data corruption, if there is a bug that causes corruption, then there is a bug. I don't think that is a fundamental design flaw as implied in this article. You shouldn't rely on 1/2 assed replication design to accidentally save you from the data corruption bug. There are many downsides to the design MySQL has that are simply not listed here.
I have been both a professional MySQL administrator as well as Postgresql (as well as SQL Server and many NoSQL engines). Many of these Postgres issues are only issues at crazy huge scale, and I would say at that point you probably want to move away from relational anyway. MySQL has its own very large set of problems at scale as well.
It sounds like Uber is using MySQL as just a data bucket with primary keys ("Schemaless") which is good -- because you can't alter tables to save your life with MySQL.
At the end of the data each developer/business needs to use what works for them, but I would really shy away from pointing to this article as a linchpin in the "MySQL vs. Postgres" war (if there even is such a thing.)
Percona also recommends using this tool to safely perform alters for any Galera-based replication product (Percona XtraDB Cluster, MariaDB Galera Cluster, etc.)
This space is far from done, until we can change data structures on the fly...we're going to find there to be a constant struggle between schema and schema-less and neither side will be right or wrong.
Solution: Quantum states; We suspect every possible scenario is happening already, so we should just migrate our database to the appropriate quantum state, where the data is in the structure we desire. Sounds insane, it probably is.
I know first hand shops like FB use a similar method in production.
Also, big note, the whole point of "schemaless" (And other things, like fb's friend feed architecture) is that you don't make schema changes.
We allocate our own IDs, and disable foreign keys for bulk loading.
It only failed catastrophically, causing a critical production incident, twice.
Each time took dozens of engineer hours to vet in advance, thus costing thousands of dollars.
Online DDL changes and indexing with pg cost us... basically nothing, and never caused downtime.
My and Pg each have their place... but if you want to modify large tables, MySQL is almost certainly the wrong tool for the job.
Other than the "schemaless" layer, the issues mentioned are fairly common.
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 Twitter ditched Rails. The focus should be inward... how they chose a tool that didn't support their use case and how they solved the problem, but instead they're about the flaws (that aren't really flaws) of the tool.
It's always the craftsman.
Been there, really no fun.
Also, MySQL* is getting LSM-Tree support lately, which makes high performance data ingestion combined with OLTP workload quite feasible.
* https://github.com/facebook/mysql-5.6/tree/webscalesql-5.6.2...
"As long as you can shard "
Not sure how sharding helps with 1B+ tables when adding indices, care to share?
And then do your joins, in your application code.
[1] https://www.percona.com/doc/percona-toolkit/2.1/pt-online-sc...
Saved my life a few time :)
Plus, in 5.7, there are a fair number of online-DDL changes, and adding indexes is (usually) one of them.
They have a couple posts about "Schemaless", but I still don't understand why they used MySQL as the data store instead of something like Cassandra. ( https://eng.uber.com/schemaless-part-one/ ) From that post it looks like they basically built a no-sql database on top of a relational database.
The only reason given was operational trust ( "If we get paged at 3 am when the datastore is not answering queries and takes down the business, would we have the operational knowledge to quickly fix it?" ). The project took nearly a year to roll out, and in that time the operation knowledge could surely be trained, hired, or contracted.
They don't explicitly answer the question "Why didn't you use InnoDB/WiredTiger/etc. for your dataplane?", but you get the idea that they were very happy with the specific characteristics of MySQL for their use case and so they built on top of it. It also sounds like they had some deadlines (specifically, the death of their datastore) that they had to meet :).
There are really not a large number of options here anymore with the departure of FoundationDB from the market. CockroachDB might be an option in a few years, though I'm still confused why they are moving towards a SQL-ish vs key-value interface...
Pissed me off so much. Only thing close to Google's F0 RDBMS on the market, at a reasonable rate, and the beginning of a good offer to enterprises. Then, "poof!" It's a good example of why I tell companies to not put anything critical into something from a startup. If they do, better have a synchronized, backup option tested and ready to go.
"why they are moving towards a SQL-ish vs key-value interface..."
That's easy: most databases and buyers use SQL. Key-value is preferred by startups & non-critical, side projects in big companies you see here a lot but aren't representative of most of the market. Need first-rate, SQL support. I think EnterpriseDB shows that it's also a good idea to clone a market leader's features onto alternative database.
Did you mean F1 (instead of F0)?
A great way to get familiar with something is to be the folks who write it. It's also much more fun to design and implement something new than to just learn some other fella's software. I'm guilty of this myself.
But I've started to remind myself that "somebody else has had this problem" and there's probably a good enough solution out there already.
Put another way, is what you are trying to do really so novel? In the case of Uber's infrastructure, you would have to talk for awhile to convince me that they really really need something not-off-the-shelf.
You can't use Cassandra if you need atomic increments (yes, they're included but painfully slow due to several trips required to satisfy PAXOS).
Also there are no transaction rollbacks (atomic batches always go one way - forward).
You may hit GC pauses if the JVM is not tuned properly.
If the use case involves its of deletes then tombstone related issues need to be considered.
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. Definitely worth a try.
But again, the title of the article sounded nothing more than a rant to me.
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.
Note that the master is still available for read and write during this. Replicas will lag though.
> Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates.
While the master may be technically up for writes during this period, it's not much a goer if your table is large and has any write traffic at all, as anything writing will stall for what may be an extended period.
Sure this is sometimes boring additional work - eg you don't delete 1M records with one statement, you break it into 1,000 statements each deleting 1,000 records.
Sucks, but keeps your db and your users happy.
BTW this is true for PostgreSQL and MySQL and Oracle and every db that allows concurrent DML.
For example, if you have a table with 1M user records, and you run a migration to add a column in MySQL, then any updates to the table will be stalled while the table is rewritten to add the extra column (which may take a while). This is independent of how many records it touches - even if the transaction only touched 1 record and would take 10ms to execute, if the migration takes 10 minutes it may be stalled for up to 10 minutes.
In Postgres you can add a nullable column, and the table will only be locked for a very short amount of time, independent of the size of the table.
Unless you're on a replica.
My summary of the arguments against Postgres and some basic thoughts on each:
1. Writes are more expensive because all secondary indexes must be updated with a new physical location.
This may be true, but the MySQL model of using primary keys from secondary indexes will mean that reads are inherently expensive. They even mention this:
> This design means that InnoDB is at a slight disadvantage to Postgres when doing a secondary key lookup, since two indexes must be searched with InnoDB compared to just one for Postgres.
So it seems like a classic read vs. write trade-off.
I'm also a little skeptical of any performance claims that don't include any numbers. It's possible that efficient coding in Postgres makes this much more of a wash in terms of performance than claimed here.
2. Replication is less efficient because it's sending a lot of physical information out along the stream.
This is quite true, but IMO unlikely to be a major issues for most users unless they're dealing with a huge amount of data and streaming it over a slow connection (i.e. across the continent like Uber's disaster recovery center).
3. Data corruption from a bug found in 9.2.
Certainly a bad situation, but IMO not really a valid claim for situation. 9.2 is way behind at this point, and there's not much to say that they wouldn't have encountered a similar bug or something worse in MySQL in all that time, especially operating at scale.
To give a counter-anecdote, I operated Postgres at scale for a long time across many versions starting at 9.1 and was lucky enough to have never once encountered a bug with data corruption.
4. Postgres' MVCC model makes it easy for replicas to accidentally fall behind their master.
This one is valid (and annoying), but there are very good reasons for it, and you have some switches to control the behavior based on value transactions finishing on followers or prompt replication more highly.
5. Upgrades are difficult because the WAL stream works at a physical level and is not compatible between database versions.
Again, this is valid, but the statement-based replication is a scary idea. Row-level replication is more interesting and probably something that Postgres should have though.
Some good news is that Postgres is getting closer to logical WAL streaming, which should make in-place upgrades possible.
With MySQL the indexes are usually kept in memory so there should not be noticeable overhead.
* Postgres Professional roadmap ( Pluggable storages, Multimaster cluster with sharding, Effective partitioning, Adaptive query planning, Page-level data compression, Connection pooling, Native querying for jsonb with indexing support, ....) https://wiki.postgresql.org/wiki/Postgres_Professional_roadm...
* EnterpriseDB database server roadmap ( Parallelism, Replication, Vertical Scalability, Performance ) https://wiki.postgresql.org/wiki/EnterpriseDB_database_serve...
====
And "Scalable PostgreSQL for real-time workloads https://www.citusdata.com " --> https://github.com/citusdata/citus
https://github.com/citusdata/citus/blob/release-5.1/CHANGELO...
"citus v5.0.0 (March 24, 2016)
- Public release under AGPLv3
- PostgreSQL extension compatible with PostgreSQL 9.5 and 9.4"
I'm not sure this post is illustrative of any generally applicable considerations (re: the title) in the choice of Postgresql vs MySQL, since Uber seems to no longer be using a relational model for most of their data and is using MySQL effectively as a key-value store.
> say a developer has some code that has to email a receipt to a user. Depending on how it’s written, the code may implicitly have a database transaction that’s held open until after the email finishes sending. While it’s always bad form to let your code hold open database transactions while performing unrelated blocking I/O, the reality is that most engineers are not database experts and may not always understand this problem, especially when using an ORM that obscures low-level details like open transactions.
I have to very seriously disagree here, ORMs make a lot of things easy - and you can get away with building stuff for a while without understanding the underlying databases or SQL but only to a certain scale (I'd say more like medium-scale, definitely not large or Uber level). If you have engineers writing code that interacts with a database without understanding transactional semantics, the engineer in question not the database is the problem.
> We started out with Postgres 9.1 and successfully completed the upgrade process to move to Postgres 9.2. However, the process took so many hours that we couldn’t afford to do the process again.
There seem to be ways [0][1] to do online upgrades with Postgres (before logical decoding in 9.4), although I haven't personally used them. Not sure if they explored these options at Uber or not?
[0] https://github.com/markokr/skytools [1] http://slony.info/
In spirit I agree with you, its the engineer's fault for not reading the documentation of their ORM or equivalent.
But in these big ships with hundreds of programmers, leaving transactions open in hibernate is a daily occurrence somewhere.
Usually caught before production, but happens oh-so-frequently, that anything that exacerbates the pain from this would be seen in a negative light for sure.