6 ms·

#1: depends on your scenario. Postgres is more like Oracle--there are types of query it can do natively that would have to be externally assisted with MySQL.

#1: depends on your scenario. Postgres is more like Oracle--there are types of query it can do natively that would have to be externally assisted with MySQL. MySQL is simpler, has all kinds of warts, but scales decently. Here's why uber moved to MySQL: https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/ #2-4: My use of SQLite has always been very basic, so no idea. Per SQLite's author: "SQLite does not compete with client/server databases. SQLite competes with fopen()."

jimktrains2 7y ago
Re: Uber it's not like they didn't cause some of their own issues by keeping very long running transactions. Also, they don't use MySQL directly, my understanding is thqt they wrote their own database and use MySQL as a kv store.
- evanelias 7y ago
  Based on what I have read, "kv store" is a major over-simplification. A number of companies have built special-case storage services/APIs on top of MySQL. This is not the same thing as writing your own database. In any case, it shows the strength and stability of MySQL for high-volume OLTP use-cases. Also I don't think "very long running transactions" were the singular core of Uber's problem. InnoDB MVCC doesn't handle those well either; a long-running tx blocks the purge thread and causes a pile-up of old row versions. While the impact of that is less severe in InnoDB than in Postgres, it's still very bad and will lead to performance degradation and undo-log size bloat.
  - jimktrains2 7y ago
    > InnoDB MVCC doesn't handle those well either; a long-running tx blocks the purge thread and causes a pile-up of old row versions. Remember that what they moved to isn't directly MySQL eitger, but only uses it underneath.
    - evanelias 7y ago
      It's still MySQL/InnoDB under the hood. I don't follow your point -- why does it matter whether an application is talking directly to MySQL, vs using an intermediate service with a non-SQL API? If the intermediate service supports long-running transactions, you'll hit the same underlying issue regardless... If your point is that they also happened to move to a new access pattern that eliminated long-running transactions, and conflated that access pattern change with their migration off of pg, that's a fair criticism if true. Hopefully someone from Uber can clarify this aspect. Also from what I recall (been a while since I read Uber's posts about this), Uber's specific MVCC issues related to pg's use of physical replication and locking impact on MVCC; and also separately its storage of old row versions "inline" vs InnoDB's use of a separate undo log. And on a different point, I remember some aspects of InnoDB's clustered index design being advantageous to Uber's workload as well. In total, this is why I suspect simple "long-running transactions" alone were not the primary/singular reason for the switch (at least if "long" means "minutes or hours" in order to negatively affect InnoDB).
      - jimktrains2 7y ago
        Yes, sorry, I didn't mean to imply that long running transactions were the only cause, but I believe I remember. I just need to go back and read the conversation on the pg-general list, it was very good in that it did discuss that some of the issues mentioned by Uber were real. https://www.postgresql.org/message-id/579795DF.10502%40commandprompt.com https://www.postgresql.org/message-id/579795DF.10502%40comma... and perhaps some other blogs from around the time this was being discussed.
baragiola 7y ago
That's a very interesting article. Does anybody knows if Postgres 10 and 11 addressed any of those issues?
- estsauver 7y ago
  https://news.ycombinator.com/item?id=14222721 https://news.ycombinator.com/item?id=14222721 is a continued discussion of the response.

jimktrains2 7y ago

Re: Uber it's not like they didn't cause some of their own issues by keeping very long running transactions. Also, they don't use MySQL directly, my understanding is thqt they wrote their own database and use MySQL as a kv store.

evanelias 7y ago
Based on what I have read, "kv store" is a major over-simplification. A number of companies have built special-case storage services/APIs on top of MySQL. This is not the same thing as writing your own database. In any case, it shows the strength and stability of MySQL for high-volume OLTP use-cases. Also I don't think "very long running transactions" were the singular core of Uber's problem. InnoDB MVCC doesn't handle those well either; a long-running tx blocks the purge thread and causes a pile-up of old row versions. While the impact of that is less severe in InnoDB than in Postgres, it's still very bad and will lead to performance degradation and undo-log size bloat.
- jimktrains2 7y ago
  > InnoDB MVCC doesn't handle those well either; a long-running tx blocks the purge thread and causes a pile-up of old row versions. Remember that what they moved to isn't directly MySQL eitger, but only uses it underneath.
  - evanelias 7y ago
    It's still MySQL/InnoDB under the hood. I don't follow your point -- why does it matter whether an application is talking directly to MySQL, vs using an intermediate service with a non-SQL API? If the intermediate service supports long-running transactions, you'll hit the same underlying issue regardless... If your point is that they also happened to move to a new access pattern that eliminated long-running transactions, and conflated that access pattern change with their migration off of pg, that's a fair criticism if true. Hopefully someone from Uber can clarify this aspect. Also from what I recall (been a while since I read Uber's posts about this), Uber's specific MVCC issues related to pg's use of physical replication and locking impact on MVCC; and also separately its storage of old row versions "inline" vs InnoDB's use of a separate undo log. And on a different point, I remember some aspects of InnoDB's clustered index design being advantageous to Uber's workload as well. In total, this is why I suspect simple "long-running transactions" alone were not the primary/singular reason for the switch (at least if "long" means "minutes or hours" in order to negatively affect InnoDB).
    - jimktrains2 7y ago
      Yes, sorry, I didn't mean to imply that long running transactions were the only cause, but I believe I remember. I just need to go back and read the conversation on the pg-general list, it was very good in that it did discuss that some of the issues mentioned by Uber were real. https://www.postgresql.org/message-id/579795DF.10502%40commandprompt.com https://www.postgresql.org/message-id/579795DF.10502%40comma... and perhaps some other blogs from around the time this was being discussed.

baragiola 7y ago

That's a very interesting article. Does anybody knows if Postgres 10 and 11 addressed any of those issues?

estsauver 7y ago
https://news.ycombinator.com/item?id=14222721 https://news.ycombinator.com/item?id=14222721 is a continued discussion of the response.