5 ms·

Feature wise I like postgresql. Operationally? Mysql. I pick MySQL 8 whenever I can because over the lifetime of a project it's easier. Pragmatically I'm comi

by porker 5y ago

Feature wise I like postgresql. Operationally? Mysql. I pick MySQL 8 whenever I can because over the lifetime of a project it's easier. Pragmatically I'm coming to the uncomfortable (as a Foss advocate) idea that Microsoft SQL Server is a good choice and worth paying for. Features and relative ease of use.

jamesfinlayson 5y ago
Very much this - most of my experience is with Postgres but the rough impression I have is that SQL Server is the best of both Postgres and MySQL, and Postgres and MySQL are improving, but not in any great direction towards what SQL Server offers.
- fuy 5y ago
  From the operations perspective, SQL Server is definitely way better that Postgres - monitoring is such a pain point in Postgres, for example. From application development/feature perspective, it's much closer, and server-side programming in Postgres (if that's what you like/need) is simply better that in SQL Server.
  - fuy 5y ago
    *better than, not better that.
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
- 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
yyyk 5y ago
SQL Server is good, but note the system is oriented differently than Postgres - Postgres is very friendly towards programming directly in the DB. In SQL Server world, you can do that, but you'd be much better off with a solution where the logic lives elsewhere (Not counting Reports or Bulk Imports which have MSSQL-native solutions).

by porker 5y ago

jamesfinlayson 5y ago

Very much this - most of my experience is with Postgres but the rough impression I have is that SQL Server is the best of both Postgres and MySQL, and Postgres and MySQL are improving, but not in any great direction towards what SQL Server offers.

fuy 5y ago
From the operations perspective, SQL Server is definitely way better that Postgres - monitoring is such a pain point in Postgres, for example. From application development/feature perspective, it's much closer, and server-side programming in Postgres (if that's what you like/need) is simply better that in SQL Server.
- fuy 5y ago
  *better than, not better that.

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
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

yyyk 5y ago

SQL Server is good, but note the system is oriented differently than Postgres - Postgres is very friendly towards programming directly in the DB. In SQL Server world, you can do that, but you'd be much better off with a solution where the logic lives elsewhere (Not counting Reports or Bulk Imports which have MSSQL-native solutions).