Ask HN: PostgreSQL or MySQL? • Hacker News Board

Re: Ask HN: PostgreSQL or MySQL?

#161

Post by madhadron » Mon, Sep 23, 2019, 4:18 PM UTC

evanelias wrote: post #151

madhadron wrote: post #146

Earlier quoted context omitted.

> This is basically just as much work as developing a new database from scratch, i.e. more work than something like migrating to Postgres. This completely debunks the "changing is basically impractical" claim. I disagree for three reasons: 1. The long tail of code using MySQL at the company, like at any large software company, is prohibitive. You would have to maintain MySQL and PostgreSQL in parallel for years. A ne…

You're severely underestimating the amount of effort that went into MyRocks. The development and deployment was a 3+ year effort spanning quite a few different teams. Automating the physical rollout (as you correctly described) is the easy part. That doesn't account for all the many difficult spots that occurred prior to it: the massive complexity of mysql storage engine development in general; huge numbers of variou…

> You say that FB is using MySQL because "changing is basically impractical", but also say MyRocks "provided major wins", which seems to be a contradiction.

I think I must not be expressing myself clearly. 3+ year projects involving a large number of teams to get back to where you started are impractical. That's what migrating to PostgreSQL would be. Perhaps I should have written "switching from MySQL to PostgreSQL would be impractical"?

Re: Ask HN: PostgreSQL or MySQL?

#162

Post by madhadron » Mon, Sep 23, 2019, 4:19 PM UTC

grumpydba wrote: post #152

madhadron wrote: post #147

Earlier quoted context omitted.

It's been four years since I looked at it, but WiredTiger didn't replicate atomically under high write rates. We had a master and replica running on the same machine and we were doing packet capture and inspection writing to the database. Our capture pipeline wrote to the master, and our system queried the replica so we didn't destroy the write rate. We were doing a zero allocation capture pipeline where the pages th…

Well technically wiredtiger is not in charge of the replication.

That's fair. We didn't have any particular problems with WiredTiger itself.

Re: Ask HN: PostgreSQL or MySQL?

#163

Post by nicwolff » Mon, Sep 23, 2019, 4:48 PM UTC

evanelias wrote: post #57

quicksilver03 wrote: post #22

Earlier quoted context omitted.

Did you mean replication rather than partitioning perhaps? I've found MySQL replication quite easy to use, whereas PostgreSQL has been rather clunkier in my experience. However, I've had the exact opposite experience with respect to partitioning.

MySQL partitioning shines with one specific use-case: time-based data retention. e.g., by using RANGE partitioning over a temporal value, you can very efficiently drop large amounts of old data when no longer needed, and can automate this fairly easily. For anything else, MySQL partitioning rule-of-thumb is you'll have a bad time, mainly for performance reasons.

We use PostgreSQL date RANGE partitions in our datamart DB for exactly this – functionally instant deletion of old data, without leaving dead tuples in the main table.

Re: Ask HN: PostgreSQL or MySQL?

#164

Post by evanelias » Mon, Sep 23, 2019, 5:23 PM UTC

madhadron wrote: post #161

evanelias wrote: post #151

Earlier quoted context omitted.

You're severely underestimating the amount of effort that went into MyRocks. The development and deployment was a 3+ year effort spanning quite a few different teams. Automating the physical rollout (as you correctly described) is the easy part. That doesn't account for all the many difficult spots that occurred prior to it: the massive complexity of mysql storage engine development in general; huge numbers of variou…

> You say that FB is using MySQL because "changing is basically impractical", but also say MyRocks "provided major wins", which seems to be a contradiction. I think I must not be expressing myself clearly. 3+ year projects involving a large number of teams to get back to where you started are impractical. That's what migrating to PostgreSQL would be. Perhaps I should have written "switching from MySQL to PostgreSQL w…

Apologies if I'm misunderstanding. To take a step back and paraphrase this subthread, as I understand it:

* `dezzeus said MySQL was better for read-intensive workloads, Postgres better for mixed read/write

* I replied saying there are a number of huge social networks with insane write rates, which is contrary proof against that claim. (Having personally spent most of the past decade working on massive-scale MySQL at several social networks / UGC sites, this topic is near and dear to my heart...)

* You replied saying, iiuc, that FB is only using MySQL for historical reasons and difficulty of switching. (IMO, your initial comment was tangential to the original topic of comparative read vs write perf anyway. Regardless of why FB is using MySQL, factually they are an example of extremely high write rate, previously via InnoDB for many years. That said, I wasn't the person who downvoted your comment.)

* I replied saying that's inaccurate, as FB demonstrably does have the resources and talent to switch to another DB if there was a compelling reason, and furthermore MySQL+MyRocks provides a combination of feature set + compression levels that other databases (including Postgres) simply cannot match at this time. At FB's scale, this translates to absolutely massive cost savings, meaning that MySQL+MyRocks is a better choice for FB for technical and business reasons rather than just historical reasons or difficulty of switching.

I may have misunderstood, but it definitely felt like your original comments were throwing shade at MySQL, and/or publicly stating historically inaccurate reasons for why FB is currently using MySQL.

Re: Ask HN: PostgreSQL or MySQL?

#165

Post by mike22 » Mon, Sep 23, 2019, 5:39 PM UTC

Thaxll wrote: post #35

rayrrr wrote: post #33

MySQL: Wait a sec, what do I use for this table, InnoDB or MyISAM? What are the tradeoffs again? Oh, right, do I want ACID transactions or fulltext search? Man, this tradeoff sucks. PostgreSQL: Wait a sec, which schema (namespace) should I put this table in? Man, it's nice to have namespaces within a DB.

No one has been using MyISAM for the last 10 years.

We specifically had to switch to MyISAM for some things when upgrading from 5.5 to 5.7, in 2017. We were hitting InnoDB’s 4k row limit in many cases (without clobs). Fortunately the tables in question are filled once, updated a couples times right after, and from then on only read, so lack of ACID is ok just for that. (All the real transactional tables are InnoDB.)

Re: Ask HN: PostgreSQL or MySQL?

#166

Post by flarg » Mon, Sep 23, 2019, 6:27 PM UTC

In case it's not been said already, Oracle own mysql, use mariadb instead

Re: Ask HN: PostgreSQL or MySQL?

#167

Post by Spooky23 » Mon, Sep 23, 2019, 8:58 PM UTC

asdkhadsj wrote: post #160

barrkel wrote: post #45

Earlier quoted context omitted.

Every time I've used sqlite for any kind of throwaway web app, I've regretted it due to the almost complete lack of support for concurrent operations. Sqlite is for file formats, not anything that might have concurrent writes.

TIL. I really thought SQLite had better support for concurrent behavior. Do you know if the problem you cite is distinct separate processes of SQLite? Ie, can a single process (aka one web app) handle concurrent behavior properly with SQLite? Or will it fail even in that scenario?

Why write code to deal with that complexity when you can just use pg or MySQL?

Re: Ask HN: PostgreSQL or MySQL?

#168

Post by asdkhadsj » Mon, Sep 23, 2019, 9:13 PM UTC

Spooky23 wrote: post #167

asdkhadsj wrote: post #160

Earlier quoted context omitted.

TIL. I really thought SQLite had better support for concurrent behavior. Do you know if the problem you cite is distinct separate processes of SQLite? Ie, can a single process (aka one web app) handle concurrent behavior properly with SQLite? Or will it fail even in that scenario?

Why write code to deal with that complexity when you can just use pg or MySQL?

Because depending on the use case you don't want to run a 2nd server. For example, I work on some FOSS that lets users self host. The intention is for minimally experienced users to self host, as well as host on small laptops/etc. For obvious reasons, spinning up a full database has issues - requiring them to install Docker or etc is not feasible.

With that said, knowing how DB concurrency behaves in inherently concurrent applications (anything HTTP facing, for example) seems vital. If it needs to be not concurrent, it's paramount to know that.

To be clear, knowing how and when the concurrency breaks down does not mean advocating for it. Merely that if concurrency wasn't supported, you may need to write your applications differently to properly lock the non-threadsafe behavior.

Re: Ask HN: PostgreSQL or MySQL?

#169

Post by tmk1108 » Tue, Sep 24, 2019, 3:44 AM UTC

madhadron wrote: post #67

1. Do you have experience running PostgreSQL or MySQL in production? Use what you know. Do you have friends or colleagues with that experience? Use what they know. Is the stack you end up using predominantly used with one or the other, so you have lots more people in the community who will have hit issues you might have? Use that one. Failing any of those? Default to PostgreSQL. 2. I think you mean SQL+JSON, and the…

Can you expand on the 4kb part of row sizes? I have been experimenting with storing json in MySQL tables and I'm curious about this limit

Re: Ask HN: PostgreSQL or MySQL?

#170

Post by madhadron » Tue, Sep 24, 2019, 3:28 PM UTC

tmk1108 wrote: post #169

madhadron wrote: post #67

1. Do you have experience running PostgreSQL or MySQL in production? Use what you know. Do you have friends or colleagues with that experience? Use what they know. Is the stack you end up using predominantly used with one or the other, so you have lots more people in the community who will have hit issues you might have? Use that one. Failing any of those? Default to PostgreSQL. 2. I think you mean SQL+JSON, and the…

Can you expand on the 4kb part of row sizes? I have been experimenting with storing json in MySQL tables and I'm curious about this limit

MySQL expects rows to be fairly small, which leads to decisions about how the system handles loading data. You can put larger blobs in, but it may have adverse performance implications.