5 ms·

Can you explain more why MySQL is operational more easy? I personally don‘t see any difference.

by tpetry 5y ago

jamesfinlayson 5y ago
I haven't used the features but I thought replication and failover were much easier (out of the box at least).
- otterley 5y ago
  You also don’t usually need connection pooling with MySQL because it can handle thousands of simultaneous connections without falling over.
  - shrumm 5y ago
    I know PG14 significantly improved this situation [0]. I'm not familiar enough with MySQL to know how it compares now though. [0] https://pganalyze.com/blog/postgres-14-performance-monitoring https://pganalyze.com/blog/postgres-14-performance-monitorin...
vanviegen 5y ago
Besides what's already mentioned by siblings: - Forwards (and usually also backwards) compatible disk format, meaning version updates don't require more than a few minutes of downtime. On large datasets, postgresql can require days or weeks. - Replication works across version differences, making upgrades without any downtime at all easier. - No need for a vacuum process that can run into trouble with a sustained high write load. - Page-level compression, reducing storage needs for some types of data quite a bit. - Clustered primary keys. Without these, some types of queries can become rather slow when your table doesn't fit in memory. (Example: a chat log containing many billions of messages, which you're querying by conversion-id.) - xtrabackup (innobackupex) is awesome.
- darkr 5y ago
  > Replication works across version differences, making upgrades without any downtime at all easier. Postgres has logical replication these days, which can be used to achieve this
  - funny_falcon 5y ago
    Except logical replication doesn't handle DDL yet. And 2 phase commit (prepared transactions) as well.
    - aeyes 5y ago
      Two phase commit for logical replication is now available in Postgres 14. The biggest issue I see at the moment is that sequences are not replicated.
- mattashii 5y ago
  > - Forwards (and usually also backwards) compatible disk format, meaning version updates don't require more than a few minutes of downtime. How so? Postgres' data formats are forwards (and mostly backwards) compatible with release 8.4 in 2009; where effectively only the catalogs need upgrading. Sure, that can be a lot of data, but no different from MySQL or any other RDBMS with transactional DDL. > - Replication works across version differences Logical replecation is available since at least 9.6
  - boxed 5y ago
    Not the dump format. The actual data on disk of the database itself. If you try upgrading even one major postgres version and you're not aware of this you lose all your data (you don't really as you can roll back to the previous version, but it doesn't even tell you that!)
    - mattashii 5y ago
      That is what I'm talking about, yes. Page format for tables hasn't changed since 8.4, tuple format hasn't changed in a backwards-incompatible manner since 8.4 (probably earlier). BTree indexes have changed a bit, but ones created in 9.0.x can still be loaded and used in 14.x. GIN/GiST/SP-GiST/BRIN all don't seem to have had backwards-incompatible changes, looking at https://why-upgrade.depesz.com/show?from=8.4&to=14.2 https://why-upgrade.depesz.com/show?from=8.4&to=14.2 The only thing that changed is the catalogs, upgradable through pg_upgrade.
jitl 5y ago
There is Vitess for MySQL, no such cluster manager for Postgres. Citus is now owned by Microsoft, and getting harder to use outside of Microsoft’s cloud. Eg, no more Citus on AWS

Can you explain more why MySQL is operational more easy? I personally don‘t see any difference.

by tpetry 5y ago

Can you explain more why MySQL is operational more easy? I personally don‘t see any difference.

jamesfinlayson 5y ago

I haven't used the features but I thought replication and failover were much easier (out of the box at least).

otterley 5y ago
You also don’t usually need connection pooling with MySQL because it can handle thousands of simultaneous connections without falling over.
- shrumm 5y ago
  I know PG14 significantly improved this situation [0]. I'm not familiar enough with MySQL to know how it compares now though. [0] https://pganalyze.com/blog/postgres-14-performance-monitoring https://pganalyze.com/blog/postgres-14-performance-monitorin...

vanviegen 5y ago

Besides what's already mentioned by siblings: - Forwards (and usually also backwards) compatible disk format, meaning version updates don't require more than a few minutes of downtime. On large datasets, postgresql can require days or weeks. - Replication works across version differences, making upgrades without any downtime at all easier. - No need for a vacuum process that can run into trouble with a sustained high write load. - Page-level compression, reducing storage needs for some types of data quite a bit. - Clustered primary keys. Without these, some types of queries can become rather slow when your table doesn't fit in memory. (Example: a chat log containing many billions of messages, which you're querying by conversion-id.) - xtrabackup (innobackupex) is awesome.

darkr 5y ago
> Replication works across version differences, making upgrades without any downtime at all easier. Postgres has logical replication these days, which can be used to achieve this
- funny_falcon 5y ago
  Except logical replication doesn't handle DDL yet. And 2 phase commit (prepared transactions) as well.
  - aeyes 5y ago
    Two phase commit for logical replication is now available in Postgres 14. The biggest issue I see at the moment is that sequences are not replicated.
mattashii 5y ago
> - Forwards (and usually also backwards) compatible disk format, meaning version updates don't require more than a few minutes of downtime. How so? Postgres' data formats are forwards (and mostly backwards) compatible with release 8.4 in 2009; where effectively only the catalogs need upgrading. Sure, that can be a lot of data, but no different from MySQL or any other RDBMS with transactional DDL. > - Replication works across version differences Logical replecation is available since at least 9.6
- boxed 5y ago
  Not the dump format. The actual data on disk of the database itself. If you try upgrading even one major postgres version and you're not aware of this you lose all your data (you don't really as you can roll back to the previous version, but it doesn't even tell you that!)
  - mattashii 5y ago
    That is what I'm talking about, yes. Page format for tables hasn't changed since 8.4, tuple format hasn't changed in a backwards-incompatible manner since 8.4 (probably earlier). BTree indexes have changed a bit, but ones created in 9.0.x can still be loaded and used in 14.x. GIN/GiST/SP-GiST/BRIN all don't seem to have had backwards-incompatible changes, looking at https://why-upgrade.depesz.com/show?from=8.4&to=14.2 https://why-upgrade.depesz.com/show?from=8.4&to=14.2 The only thing that changed is the catalogs, upgradable through pg_upgrade.

jitl 5y ago

There is Vitess for MySQL, no such cluster manager for Postgres. Citus is now owned by Microsoft, and getting harder to use outside of Microsoft’s cloud. Eg, no more Citus on AWS