13 ms·

Another recent Postgres-complaint post from one of the best engineers I've worked with: https://blog.nelhage.com/post/some-opinionated-sql-takes/ https://blog.n

• paulryanrogers 6y ago
  It's interesting how personal scars can entrench ones perspective. After MySQL 8's renaming-table-will-crash-server bug I'm reluctant to use it for new projects.
  • aaron-santos 6y ago
    In any sufficiently large dev team the technology choices trend toward zero. Because everyone has their "I will not work with this tech after dealing with its nasty bug."
    • paulryanrogers 6y ago
      Indeed. And products can get better and worse over time. If there's a consistent pattern of critical bugs at least with FOSS there is the possibility of forks and 3rd party patches.
  • jrockway 6y ago
    I'm personally guilty of this mindset, but it's something I'm working on. After you get burned by a system, you know of the bug, and you can fix it. But the instinct is to switch to a new system, or to rewrite the system. That does get rid of all the bugs in the old system! But, in the process you've replaced them with brand new bugs that nobody has seen or heard from, until they decide to crawl into your mouth while you're asleep and you wake up in a panic. It's bugs all the way down, folks... this is software we're dealing with! I think sendmail is the classic example of a program with so many bugs it had to be rewritten from scratch, and many people did. All of those alternatives, even qmail (widely debated: https://www.qualys.com/2020/05/19/cve-2005-1513/remote-code-execution-qmail.txt https://www.qualys.com/2020/05/19/cve-2005-1513/remote-code-...), ended up with a bug or security problem too. And they seem to have even fixed sendmail. It's still around and it doesn't take down the whole Internet every three weeks anymore. Wow! Sometimes there are just a million bugs, and you fix all one million of them, and then there aren't any more bugs.
    • psanford 6y ago
      > It's still around and it doesn't take down the whole Internet every three weeks anymore. Maybe that's because the vast majority of email no longer goes through sendmail.
    • hvidgaard 6y ago
      In reality, the smart engineering choice is to know exactly what you are expecting from a specific system, and test it. Bugs, mistakes and even cosmic rays happen, so you must specifically test that it works. If you have a backup power system, test it.
    • citrin_ru 6y ago
      sendmail is not a good example of a bad rewrite: 1st qmail is no more rewritten sendmail than Linux is rewrittnen Unix - qmail/exim/postfix just different software for the same use case. 2dn many other MTA (e. g. Postfix) managed to maintain much lower amount of security vulnerabilities than sendmail.
  • Corrado 6y ago
    Agreed! My personal bias against a system depends on if it failed at it's primary purpose or not. I've had a lot of things fail in a lot of ways and I'm generally pretty forgiving, but there are some things that are just inexcusable. Some examples: * A popular (at the time) version control system started to silently overwrite files when it ran out of disk space. We discovered this when we retrieved source code that had parts of another file in it. * A UPS vendor had a product that tested the battery by turning off the power. When the battery ultimately failed our servers were powered off automatically. This happened randomly about every 3 weeks (usually on a weekend evening). It took us months to find the cause. I can't come back from these problems. So, if the system is a bit slow or crashes sometimes or has other weird problems, I'm OK with that. If it bites me specifically where it's supposed to protect me, it's out, forever.
• oauea 6y ago
  As long as mysql can't run ddl statements in a transaction it's worthless as far as I'm concerned. Also the thing where they (used to?) silently truncate your data when it wouldn't fit a column is absolutely insane. I'll take operational footguns over losing half my data every damn time.
  • stepbeek 6y ago
    The lack of ddl in a transaction is what scared me away too. Having to manually clean up after a failed migration just felt like something I shouldn't be thinking about.
  • carlos_rpn 6y ago
    They don't truncate data anymore, unless you enable it in the configuration (it's disabled by default). Invalid data (0000-00-00) is also not accepted anymore.
  • lokedhs 6y ago
    I'm definitely no fan of mysql. I have, like many others, been scarred by its misfeatures. However, not having DDL in transactions isn't really a barrier for being useful. Oracle doesn't have transactional DDL either, and say what you will about the company, the product itself has proven itself.
    • jhgb 6y ago
      But Firebird does have it, too. I find it funny how supposedly top products sometimes lack the most basic features.
  • Foobar8568 6y ago
    MySQL is as advanced as Oracle on this topic (DDL in transaction), unless Oracle has changed in the recent years.
    • jeltz 6y ago
      Which means equally useless. I agree with him about transactional DDL. Having worked both with it and without it I would never want to go back to MySQL.
• jbluepolarbear 6y ago
  They’re so right about performance gotchas. I worked on a large Java project a few years back and they were transitioning from MySQL to Postgres, after the upgrade performance was abysmal. I then spent the next 5 months optimizing queries. A lot of the issues were inner joins and how MySQL and Postgres handled lookups in inner joins differently. I would still pick Postgres over MySQL because the tools and features around it are too very good.
  • runeks 6y ago
    Devil’s advocate: could it simply be that someone spent 5 months optimizing queries for MySQL before switching to Postgres? Such that Postgres performance isn’t “worse”, it just doesn’t plan queries in the same way that MySQL does.
    • jbluepolarbear 6y ago
      It was definitely that. The queries were built to take advantage of MySQL features.I joined after they just switched and was told to optimize queries. Was a pretty toxic job, I was hired work on streaming media systems (like Hulu, ESPN, etc) but instead they had me doing query optimizations. I quit after 5 months. I was never able talk to my boss once, he avoided me and was always too busy.
      • Shorel 6y ago
        That's the kind of job I enjoy doing. It is just not sexy and some startup rejected me when I said that's what I do.
    • nomel 6y ago
      The purpose of a query engine is so I don't have to think about this stuff. Computers are supposed to do work for me, no the other way around, dangit!
  • twic 6y ago
    My experience was the opposite - a Java app running on MySQL that had painfully slow joins, that immediately got much faster on porting to PostgreSQL!
• rattray 6y ago
  Does anyone know of a quality, comprehensive book that enumerates all the things to watch out for and problems to proactively prevent when operating Postgres at scale?
  • eor 6y ago
    It's not a book, but Christophe Pettus' blog (https://thebuild.com/blog https://thebuild.com/blog) has a lot of really good information. In particular, his talk "Breaking PostgreSQL at Scale" goes through the problems you run into as you hit different levels of scale (https://thebuild.com/presentations/2019-fosdem-broken.pdf https://thebuild.com/presentations/2019-fosdem-broken.pdf)
    • rattray 6y ago
      Thanks! Very helpful, that talk looks like a (very) condensed version of what I was looking for. It looks like the video for that talk is here: https://www.youtube.com/watch?v=XUkTUMZRBE8 https://www.youtube.com/watch?v=XUkTUMZRBE8
      • twunde 6y ago
        FYI, for anyone interested Pettus does consulting work as the founder of https://pgexperts.com/ https://pgexperts.com/. Highly recommend working with him if you need a postgres DBA.
    • gshulegaard 6y ago
      Thanks for that! Been doing a lot of Postgres work but first time seeing that slide deck.
• gshulegaard 6y ago
  Sorry for the long, rambling comment. After I wrote it I wasn't sure it added much, but since I invested so much time writing it I figured someone might find something in it useful so in that off chance I am posting it. --- Those were really interesting reads, and it's obvious to me that the author is well experienced even if I find myself at odds with some of the points and ultimate conclusion. To be explicit, there _are_ points which resonated strongly with me. I am by no means an expert, and fairly middling in experience by any nominal measure, but I _have_ spent a significant portion of my professional experience scaling PostgreSQL so I thought I would throw out my $0.02. I have seen many of the common issues: - Checkpoint bloat - Autovacuum deficiencies - Lock contention - Write amplification and even some less widely known (maybe even esoteric) issues like: - Index miss resulting in seq scan (see "random_page_cost" https://www.postgresql.org/docs/13/runtime-config-query.html https://www.postgresql.org/docs/13/runtime-config-query.html) I originally scaled out Postgres 9.4 for a SaaS monitoring and analytics platform, which I can only describe as being a very "hands on" or a manual process. Mostly because many performance oriented features like: - Parallel execution (9.6+) (originally limited in 9.6 and expanded in later releases) - Vacuum and other parallelization/performance improvements (9.6+) - Declarative partitioning (10.0) (Hash based partitions added in 11.0) - Optional JIT compiling of some SQL to speed up expression evaluation (11.0) - (and more added in 12 and 13) Simply didn't exist yet. But even without all of that we were able to scale our PostgreSQL deployment to handle a few terabytes of data ingest a day by the time I left the project. The team was small, between 4-7 (average 5) full time team members over 3 years including product and QA. I think that it was possible--somewhat surprisingly--then, and has been getting steadily easier/better ever since. I think the general belief that it is difficult to scale or requires a high level of specialization is at odds with my personal experience. I doubt anyone would consider me a specialist; I personally see myself as an average DB _user_ that has had the good fortune (or misfortune) to deal with data sets large enough to expose some less common challenges. Ultimately, I think most engineers would have come up with similar (if not the same) solutions after reading the same documentation we did. Another way to say this is I don't think there is much magic to scaling Postgres and it is actually more straight forward than common belief suggests; I believe there is a disproportionate amount of the fear of the unknown rather than PostgreSQL being intrinsically more difficult to scale than other RDBMS's. The size and scope of the PostgreSQL feature set can make it somewhat difficult to figure out where to start, but I think this is a challenge for any feature-rich, mature tool and the quality of the PostgreSQL documentation is a huge help to actually figuring out a solution in my experience. Also, with the relatively recent (last 5 years or so) rise of PostgreSQL horizontal-scale projects like Citus and TimescaleDB I think it is an even easier to scale PostgreSQL. Most recently, I used Citus to implement a single (sharded) storage/warehouse for my current project. I have been _very_ pleasantly surprised by how easy it was to create a hybrid data model which handles everything from OLTP single node data to auto-partitioned time series tables. There are some gotchas and lessons learned, but that's probably a blog post in it's own right so I'll just leave it as a qualification that it's not a magic bullet that completely abstracts the nuances of how to scale PostgreSQL (but it does a darned lot). TL;DR: I think scaling PostgreSQL is easier than most believe and have done it with small teams (< 5) without deep PostgreSQL expertise. New features in PostgreSQL core and tangential projects like Citus and TimescaleDB have made it even easier.
• rzwitserloot 6y ago
  > it’s much worse than MySQL for operational footguns and performance cliffs As wikipedians would say, [citation needed]. The post you link to concludes with: > Operating PostgreSQL at scale requires deep expertise and > I hate performance cliffs However, both of these statements are true for _any_ major SQL-based DB engine available, including MySQL. As the post itself shows, psql is at least doing its job in guaranteeing consistency of the data, and has tools to figure out what is going on, which is absolutely crucial when 'operating at scale'. In other words, yeah, you need deep expertise. However, no, it's not 'much worse' than MySQL for operational footguns. MySQL has a ton of footguns just the same.