← Back to context

Comment by paulryanrogers

5 hours ago

Upgrades are hard. There was no replication in the before times. The original block-level replication didn't work among different major versions. Slony was a painful workaround based on triggers that amplified writes.

Newer PostgreSQL versions are better. Yet still not quite as robust or easy as MySQL.

At a certain scale even MySQL upgrades can be painful. At least when you cannot spare more than a few minutes of downtime.

39 comments

paulryanrogers

Reply
darth_avocado  2 hours ago

> At least when you cannot spare more than a few minutes of downtime.

I think it boils down to this. We used to constantly be running the obsolete pg version until it became an emergency mostly because upgrading with the tooling available at the time was very painful. Today however, we stay relatively up to date. Once you figure out the data replication, you can almost do blue green deployments on databases with almost no down time.

slotrans  1 hour ago

"Not as robust as MySQL"? Surely you're joking.

  • sgarland  1 hour ago

    They’re not wrong. If you’ve ever spent meaningful time administering both, you’ll know that Postgres takes far more hands-on work to keep it going.

    To be clear, I like both. Postgres has a lot more features, and is far more extensible. But there’s no getting around the fact that its MVCC implementation means that at scale, you have to worry about things that simply do not exist for MySQL: vacuuming, txid wraparound, etc.

    • wbl  39 minutes ago

      Yeah but you don't need to worry about your data existing. MySQL has been known to silently fail the one job of a DB.

api  5 hours ago

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 functionality, but most apps don't need all that.

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.

  • tpmoney  5 hours 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  4 hours 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.

      9 replies →

    • sgarland  1 hour ago

      MySQL does have ON UPDATE for its DATETIME, though; something that Postgres inexplicably still lacks.

  • fzeindl  1 hour 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.

  • stickfigure  5 hours ago

    What's the alternative? MySQL? No transactional DDL, immediate fail.

    • jes5199  5 hours 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

      3 replies →

    • cosmotic  3 hours 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.

      1 reply →

  • justin_oaks  5 hours 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?

    • dalyons  4 hours ago

      It requires a ton of somewhat arcane maintenance at scale. Vacuum shenanigans, Index fragmentation requiring manual reindexing, Txid wraparounds. I like Postgres but it’s definitely way more work to maintain a large instance than mysql. MySQL just kinda works