7 ms·

I've always wondered why Postgres is so insanely popular. I mean it has some nice things like very powerful support for a very comprehensive subset of SQL funct

• stickfigure 2y ago
  What's the alternative? MySQL? No transactional DDL, immediate fail.
  • jes5199 2y ago
    I worked for a company that migrated from mysql to postgres, but then got big enough they wanted to hire fulltime database experts and ended up migrating back to mysql because it was easier to find talent
    • bastawhiz 2y ago
      Dunno if that says much about Postgres, but it says a lot about the company
    • deleted 2y ago
      [deleted]
    • appendix-rock 2y ago
      [dead]
    • icedchai 2y ago
      Ugh. I worked with MySQL earlier in my career (until about 10 years ago.) All the companies since have been Postgres. All my personal projects are Postgres. I can't imagine going back.
  • cosmotic 2y ago
    It's not just DDL that isn't transactional, there's a whole bunch of other things that aren't. And they break the transactionality silently. It's like an obstical course where bumping into something might be fatal.
    • evanelias 2y ago
      What specific non-DDL things are you referring to here? Aside from DDL, the only other major ones are manipulating users/grants, manipulating replication, a small number of other administrative commands, and LOCK TABLES. This is all documented very clearly on https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html. Hardly an "obstical course".
      • stickfigure 2y ago
        "Aside from missing his head, the patient appears to be in fine shape."
        • evanelias 2y ago
          That hardly seems equivalent. Why do you need to e.g. reconfigure replication inside of a transaction in the first place? The lack of transactional DDL is a totally valid complaint, but the non-DDL stuff is just a total head-scratcher to me. Aside from DDL, implicit commits have literally never impacted me in my 21 years of using MySQL.
          • stickfigure 2y ago
            Sorry - I was trying to make light of the discussion. DDL is so important that it's silly to talk about the other stuff.
  • cvalka 2y ago
    TiDB
• justin_oaks 2y ago
  > It really feels like early 1990s vintage Unix software. It's clunky and arcane and it's hard to feel confident doing anything complex with it. How software "feels" is subjective. Can you be more specific?
  • threeseed 2y ago
    The command line experience is old school style i.e. to show tables. \c database \dt Versus: use database show tables
    • dventimi 2y ago
      That's psql.
    • fhdsgbbcaA 2y ago
      It’s also faster to type.
      • eYrKEC2 2y ago
        Not after you have to google, "What's the equivalent of `show tables` in postgres?", because the psql command names are completely arbitrary.
        • fhdsgbbcaA 2y ago
          Which you need to do exactly once.
          • kalleboo 2y ago
            I need to manually admin my database server maybe once every 2 years or so. Definitely not remembering them 2 years later.
            • dventimi 2y ago
              Sounds like a YP
• tpmoney 2y ago
  > I've always wondered why Postgres is so insanely popular. In no particular order, my preference for postgres is driven by: * Date / time functions that don't suck * UTF-8 is really UTF-8 * 99% of a backup can be done live with nothing more than rsyncing the data directory and the WAL files * Really comprehensive documentation * LTREE and fuzzy string match extensions * Familiarity from using it for years MySQL/Maria I'm sure is fine, but it's one of hose things where it's just different enough and I haven't encountered a compelling use case for changing my preference.
  • fhdsgbbcaA 2y ago
    UTF-8 is what made me switch. It’s insane MySQL has something called UTF-8 that isn't really UTF-8, but do have a type UTF8MB4 that actually is correct. This means if you use UFT-8 in MySQL, you can’t use emoji for example.
    • bastawhiz 2y ago
      And the fact that adding real utf-8 support limited (limits?) the length of strings that can be indexed
      • evanelias 2y ago
        Postgres limits btree keys to 2704 bytes, which is actually slightly smaller than MySQL's limit of 3072 bytes, assuming the default InnoDB storage engine. That said, when using utf8mb4 in an index key, MySQL uses the "worst case" of each character being 4 bytes. So it effectively limits the max key size to 3072/4 = 768 characters, when a column is using the utf8mb4 character set. For practical purposes, this doesn't cause much pain, as it's generally inadvisable to use complete long-ish strings as a key. And there are various workarounds, like using prefixes or hashes as the key, or using binary strings as keys to get the full 3072 bytes (if you don't need collation behaviors).
        • bastawhiz 2y ago
          > So it effectively limits the max key size to 3072/4 = 768 characters, when a column is using the utf8mb4 character set. This is exactly what I mean. 768 characters for an index is woefully bad. And for no obviously great reason: you can just index the encoded UTF-8 text. This was literally reason why a former company (who will remain nameless) refused to add Unicode support. It's not even an imagined problem.
• DonHopkins 2y ago
  Because it's not tainted and cursed by Oracle, like MySQL (and Oracle).
  • immibis 2y ago
    That's what MariaDB is for, right? I'm surprised to hear people recommend the Oracle fork of MySQL (still called MySQL because they own the trademark) rather than the original project (now called MariaDB)
• fzeindl 2y ago
  * transactional DDL * comprehensive transaction model using different modes * PostGIS and lots of other great extensions * supports most of the current SQL standard and is clear on interpretation of edge-cases in the documentation * support for writing stored procedures in any major programming language * many useful functions regarding dates, sets, ranges, json, xml, ... * custom datatypes * extremely thought-out and standardized approach to development: if a feature is included it generally works well in interaction with everything else * syntax, semantics and performance are all very predictable * great documentation Regarding MySQL / MariaDB: MySQL optimized for performance first. Until 2010 the standard-storage-engine MyISAM didn't even support transactions. PostgreSQL always focused on correctness and stability and then made sure everything performed.
  • arkh 2y ago
    > * custom datatypes Good in theory. But last time I checked the main libs to connect to pgsql, everything you get back from the database are strings. So you need something in your app to convert those strings to the equivalent data structures.
    • ttfkam 2y ago
      You're thinking only in terms of application. Types in the db save storage space, allow for better validation than plain strings, can be correlated cleanly with other columns with the same type, etc. Yes, more drivers and libraries should support the more expansive data type list, but even just within the database itself there are multiple advantages.
• moogly 2y ago
  > I've always wondered why Postgres is so insanely popular Real answer: no licensing cost
• Scramblejams 2y ago
  > I've always wondered why Postgres is so insanely popular. Just another anecdote: MySQL lost data for me (2004). I spent some time evaluating the projects and Postgres’ development process seemed much more mature — methodical, careful, and focused on correctness. Boring, which I loved. I didn’t need whatever perf advantage MySQL had so I switched to Postgres and never looked back. And then the Oracle drama and Monty’s behavior around it — not saying he was wrong or right, but it was the opposite of boring — just reinforced my decision. I like to play with new tech in various spots of the stack, but for filesystems and databases I go boring all the way.
• vbezhenar 2y ago
  For me Postgres is 100% predictable and reliable. It's neither clunky nor arcane in my experience. I don't need to think about it, I just SQL it and that's about it. It quietly works in the background. At some scale there might be some issues, but there is always known path to solve things.
• paulryanrogers 2y ago
  I've never lost data with PostgreSQL. MySQL had enough data loss bugs and foot guns that I ran into a few of them.