4 ms·

I could never warm up to PostgresSQL. Guess I'll always stick with MySQL

ainar-g 3y ago
Can I ask why? I generally only see the “I switched from MySQL to PostgreSQL and loving it” comments in my info-bubble, so it'd be interesting to know what people who prefer to use MySQL feel is still lacking in PostgreSQL.
- mbork_pl 3y ago
  Not the parent, but I have a friend who knows both Postgres and MySQL and prefers MySQL because "it is simpler and hence there are fewer ways things can get broken". (Not that I necessarily agree, but he seems to have a point.)
  - hot_gril 3y ago
    How is MySQL simpler? I'm a big fan of simplicity even at the cost of features, but they seem like the same level of complexity.
- mschuster91 3y ago
  From an admin perspective: Updates are a hot, complex mess which means I put them off until it's no longer feasible to do so (=because some software requires a newer version). MySQL is easy: apt-get update/docker stop && docker rm && docker run/kubectl apply, depending on your stack that is literally all you need to do. PostgreSQL in contrast is hell. You have to shut down the existing database server, install the new one in parallel, manually do the upgrade (that involves copying the whole dataset), remove the old stuff, and then start back up again. Or you have to export the whole database into an SQL dump and import it on a fresh instance. In any case, way more involved, way more likely to go bonkers, and way, WAY more downtime needed.
  - reitanuki 3y ago
    You do not need to copy the DB, see pg_upgrade
    - mschuster91 3y ago
      That depends on the circumstances. To quote the manpage: > If you use link mode, the upgrade will be much faster (no file copying) and use less disk space, but you will not be able to access your old cluster once you start the new cluster after the upgrade. Link mode also requires that the old and new cluster data directories be in the same file system. (Tablespaces and pg_wal can be on different file systems.) Clone mode provides the same speed and disk space advantages but does not cause the old cluster to be unusable once the new cluster is started. Clone mode also requires that the old and new data directories be in the same file system. This mode is only available on certain operating systems and file systems.
      - reitanuki 3y ago
        I'm not familiar this much with MySQL then: if you upgrade your MySQL database, can you still roll back to the old version? If you can't then this is the same restriction when you use the `pg_upgrade` tool in link mode. (Note that one approach if concerned about the potential for downtime if it goes wrong might be to `pg_upgrade` your secondary in a hot-standby replication setup, then failover and `pg_upgrade` the former primary. In practice, `pg_upgrade` runs very quickly — seconds even on multi-terabyte databases — afaik it doesn't touch the actual data but just the metadata, but read the man pages for more intricate details on that :)).
  - dpedu 3y ago
    +1 for administrative pains with postgres. My favorite example is that if you want to run a multi-node highly available postgres cluster (read: with automatic failover) you're going to have to use 3rd party software, whereas with mysql it's part of the core (group replication). I'm also not a fan of the difficult-to-remember backslash codes in the psql cli used to inspect databases (like \d+, and others) whereas mysql just uses queries ("describe table").
- veddan 3y ago
  I'm a bit behind on modern PostgreSQL so I might be wrong, but I believe MySQL has better support for what they call "online DDL" (modifying tables without blocking simultaneous queries). Last time I checked, MySQL supported it in more cases. MySQL can also be explicit about it via "lock assertions": ALTER TABLE ..., LOCK=NONE; will give an error if the requested operation can't be performed while still allowing concurrent reads and writes to the table (if you're fine with preventing writes you can use LOCK=SHARED). The LOCK clause isn't just an assertion and can actually affect how MySQL performs the operation, but I tend to think of it as asserting "this won't cause downtime by locking this multi billion-row table while it spends an hour rewriting it".
lbriner 3y ago
Personally as someone who has only ever used SQL Server, once I accepted that Postgres is different in certain ways, it has been great. Stable, predictable and I have had no performance surprises. For me, the big win is to have a cluster without having to pay for SQL Server Enterprise edition therefore having money to invest into the community by having a Postgres consultant help configure it for me.
hot_gril 3y ago
This shouldn't be downvoted. If someone can give you a particular reason to use Postgres, maybe this is the place. Personally I enjoy Postgres the most after having used MySQL before, but it's only for little reasons like the EXPLAIN output being nicer.

Can I ask why? I generally only see the “I switched from MySQL to PostgreSQL and loving it” comments in my info-bubble, so it'd be interesting to know what people who prefer to use MySQL feel is still lacking in PostgreSQL.

mbork_pl 3y ago
Not the parent, but I have a friend who knows both Postgres and MySQL and prefers MySQL because "it is simpler and hence there are fewer ways things can get broken". (Not that I necessarily agree, but he seems to have a point.)
- hot_gril 3y ago
  How is MySQL simpler? I'm a big fan of simplicity even at the cost of features, but they seem like the same level of complexity.
mschuster91 3y ago
From an admin perspective: Updates are a hot, complex mess which means I put them off until it's no longer feasible to do so (=because some software requires a newer version). MySQL is easy: apt-get update/docker stop && docker rm && docker run/kubectl apply, depending on your stack that is literally all you need to do. PostgreSQL in contrast is hell. You have to shut down the existing database server, install the new one in parallel, manually do the upgrade (that involves copying the whole dataset), remove the old stuff, and then start back up again. Or you have to export the whole database into an SQL dump and import it on a fresh instance. In any case, way more involved, way more likely to go bonkers, and way, WAY more downtime needed.
- reitanuki 3y ago
  You do not need to copy the DB, see pg_upgrade
  - mschuster91 3y ago
    That depends on the circumstances. To quote the manpage: > If you use link mode, the upgrade will be much faster (no file copying) and use less disk space, but you will not be able to access your old cluster once you start the new cluster after the upgrade. Link mode also requires that the old and new cluster data directories be in the same file system. (Tablespaces and pg_wal can be on different file systems.) Clone mode provides the same speed and disk space advantages but does not cause the old cluster to be unusable once the new cluster is started. Clone mode also requires that the old and new data directories be in the same file system. This mode is only available on certain operating systems and file systems.
    - reitanuki 3y ago
      I'm not familiar this much with MySQL then: if you upgrade your MySQL database, can you still roll back to the old version? If you can't then this is the same restriction when you use the `pg_upgrade` tool in link mode. (Note that one approach if concerned about the potential for downtime if it goes wrong might be to `pg_upgrade` your secondary in a hot-standby replication setup, then failover and `pg_upgrade` the former primary. In practice, `pg_upgrade` runs very quickly — seconds even on multi-terabyte databases — afaik it doesn't touch the actual data but just the metadata, but read the man pages for more intricate details on that :)).
- dpedu 3y ago
  +1 for administrative pains with postgres. My favorite example is that if you want to run a multi-node highly available postgres cluster (read: with automatic failover) you're going to have to use 3rd party software, whereas with mysql it's part of the core (group replication). I'm also not a fan of the difficult-to-remember backslash codes in the psql cli used to inspect databases (like \d+, and others) whereas mysql just uses queries ("describe table").
veddan 3y ago
I'm a bit behind on modern PostgreSQL so I might be wrong, but I believe MySQL has better support for what they call "online DDL" (modifying tables without blocking simultaneous queries). Last time I checked, MySQL supported it in more cases. MySQL can also be explicit about it via "lock assertions": ALTER TABLE ..., LOCK=NONE; will give an error if the requested operation can't be performed while still allowing concurrent reads and writes to the table (if you're fine with preventing writes you can use LOCK=SHARED). The LOCK clause isn't just an assertion and can actually affect how MySQL performs the operation, but I tend to think of it as asserting "this won't cause downtime by locking this multi billion-row table while it spends an hour rewriting it".

lbriner 3y ago

Personally as someone who has only ever used SQL Server, once I accepted that Postgres is different in certain ways, it has been great. Stable, predictable and I have had no performance surprises. For me, the big win is to have a cluster without having to pay for SQL Server Enterprise edition therefore having money to invest into the community by having a Postgres consultant help configure it for me.

hot_gril 3y ago

This shouldn't be downvoted. If someone can give you a particular reason to use Postgres, maybe this is the place. Personally I enjoy Postgres the most after having used MySQL before, but it's only for little reasons like the EXPLAIN output being nicer.