4 ms·

> ... it was always technologically superior to MySQL No, it wasn't, and still isn't. MySQL supports replication correctly and out of the box, Postgres still

> ... it was always technologically superior to MySQL No, it wasn't, and still isn't. MySQL supports replication correctly and out of the box, Postgres still hasn't figured this out. (Replication is the #1 important feature for any setup that is larger than one server.)

junon 3y ago
> Replication is the #1 important feature for any setup that is larger than one server That's... just replication. And postgres handles replication fine in my experience, I could in fact say the opposite about MySQL. What issues do you have with it?
- samlambert 3y ago
  Please do say the opposite about MySQL. Tell us what is wrong with it? Linkedin use MySQL replication to ship data across regions and rebuild caches the other side because other protocols are too unreliable. Postgres documents that their replication protocol can lose committed transactions. https://patroni.readthedocs.io/en/latest/replication_modes.html https://patroni.readthedocs.io/en/latest/replication_modes.h... Looking real good.
  - jvdongen 3y ago
    This is a disingenuous comment at best. As explained in the docs you link to, Postgres has both synchronous and asynchronous replication modes. In asynchronous mode availability is favored over durability. Which means commits can get lost when the primary goes down in an uncontrolled fashion. In sync mode that will not happen at a cost to some performance. It’s the same trade-off any distributed system has to make, and the user gets to make the choice.
    - Spivak 3y ago
      MySQL can actually do one better which is semisync. The transactions get replicated synchronously but applying them is async. You can't lose data anymore due to a primary failure.
      - jvdongen 3y ago
        Postgres has a similar feature, if you chose ‘remote_write’ for the ‘synchronous_commit’ setting. You can also mix and match sync/ascync options within a cluster and even between individual transactions. Recent versions of Postgres have a really flexible replication configuration to cover a whole range of requirements. See e.g. https://www.postgresql.org/docs/current/warm-standby.html#SYNCHRONOUS-REPLICATION https://www.postgresql.org/docs/current/warm-standby.html#SY...
  - deleted 3y ago
    [deleted]
  - keep_reading 3y ago
    MySQL actually documents their atrocities: https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.html https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.... Let me TLDR them for you > Advantages of statement-based replication: Proven technology, can be used as an audit log > Disadvantages of statement-based replication: INSERT DELETE, UPDATE, and REPLACE may not replicate correctly Are you getting worried yet? > Advantages of row-based replication: All changes can be replicated. > Stored functions execute with the same NOW() value as the calling statement. However, this is not true of stored procedures. Wonderful. Also not a problem in Postgres AFAIK because you're replicating the WAL so it doesn't need to execute it locally. Don't forget about the pitfalls of replicating CREATE USER and ALTER USER in MySQL https://www.percona.com/blog/what-if-the-user-exists-on-the-replica-and-not-in-the-primary-or-any-other-objects/ https://www.percona.com/blog/what-if-the-user-exists-on-the-... MySQL has improved things in recent years but this list used to be much larger. Just go look through their bug tracker. It's not something that instills confidence and I need to deploy databases I can trust will not eat my data. I've had too many customers with broken MySQL databases over the years and zero with Postgres.
    - samlambert 3y ago
      Statement based replication is not the default in MySQL.
    - evanelias 3y ago
      > Disadvantages of statement-based replication Statement-based replication is deprecated: https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_format https://dev.mysql.com/doc/refman/8.0/en/replication-options-... > Stored functions execute with the same NOW() value as the calling statement. However, this is not true of stored procedures. That's from the "disadvantages of statement-based replication" section, not the row-based replication section. So to recap, the disadvantages you've quoted from the manual are specific to statement-based replication, which is deprecated. > Don't forget about the pitfalls of replicating CREATE USER and ALTER USER in MySQL As mentioned in that Percona blog post, all you need to do is add IF EXISTS / IF NOT EXISTS to your SQL and it solves the problem. Or in MariaDB, you also have the option of CREATE OR REPLACE syntax. Sure, it's a minor footgun for newbies, but not exactly a horrendous problem.
- otabdeveloper4 3y ago
  Postgres doesn't handle replication fine. It has two replication mechanisms, and both lack critical features and are broken by design. (Most of the problem comes from the fact that Postgres hasn't figured out that they need a standardised replication event wire protocol to do this correctly.)
  - Spivak 3y ago
    > both lack critical features and are broken by design I bet AWS gets a lot of money for their DMS service for fixing this.

junon 3y ago

> Replication is the #1 important feature for any setup that is larger than one server That's... just replication. And postgres handles replication fine in my experience, I could in fact say the opposite about MySQL. What issues do you have with it?

samlambert 3y ago
Please do say the opposite about MySQL. Tell us what is wrong with it? Linkedin use MySQL replication to ship data across regions and rebuild caches the other side because other protocols are too unreliable. Postgres documents that their replication protocol can lose committed transactions. https://patroni.readthedocs.io/en/latest/replication_modes.html https://patroni.readthedocs.io/en/latest/replication_modes.h... Looking real good.
- jvdongen 3y ago
  This is a disingenuous comment at best. As explained in the docs you link to, Postgres has both synchronous and asynchronous replication modes. In asynchronous mode availability is favored over durability. Which means commits can get lost when the primary goes down in an uncontrolled fashion. In sync mode that will not happen at a cost to some performance. It’s the same trade-off any distributed system has to make, and the user gets to make the choice.
  - Spivak 3y ago
    MySQL can actually do one better which is semisync. The transactions get replicated synchronously but applying them is async. You can't lose data anymore due to a primary failure.
    - jvdongen 3y ago
      Postgres has a similar feature, if you chose ‘remote_write’ for the ‘synchronous_commit’ setting. You can also mix and match sync/ascync options within a cluster and even between individual transactions. Recent versions of Postgres have a really flexible replication configuration to cover a whole range of requirements. See e.g. https://www.postgresql.org/docs/current/warm-standby.html#SYNCHRONOUS-REPLICATION https://www.postgresql.org/docs/current/warm-standby.html#SY...
- deleted 3y ago
  [deleted]
- keep_reading 3y ago
  MySQL actually documents their atrocities: https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.html https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.... Let me TLDR them for you > Advantages of statement-based replication: Proven technology, can be used as an audit log > Disadvantages of statement-based replication: INSERT DELETE, UPDATE, and REPLACE may not replicate correctly Are you getting worried yet? > Advantages of row-based replication: All changes can be replicated. > Stored functions execute with the same NOW() value as the calling statement. However, this is not true of stored procedures. Wonderful. Also not a problem in Postgres AFAIK because you're replicating the WAL so it doesn't need to execute it locally. Don't forget about the pitfalls of replicating CREATE USER and ALTER USER in MySQL https://www.percona.com/blog/what-if-the-user-exists-on-the-replica-and-not-in-the-primary-or-any-other-objects/ https://www.percona.com/blog/what-if-the-user-exists-on-the-... MySQL has improved things in recent years but this list used to be much larger. Just go look through their bug tracker. It's not something that instills confidence and I need to deploy databases I can trust will not eat my data. I've had too many customers with broken MySQL databases over the years and zero with Postgres.
  - samlambert 3y ago
    Statement based replication is not the default in MySQL.
  - evanelias 3y ago
    > Disadvantages of statement-based replication Statement-based replication is deprecated: https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_format https://dev.mysql.com/doc/refman/8.0/en/replication-options-... > Stored functions execute with the same NOW() value as the calling statement. However, this is not true of stored procedures. That's from the "disadvantages of statement-based replication" section, not the row-based replication section. So to recap, the disadvantages you've quoted from the manual are specific to statement-based replication, which is deprecated. > Don't forget about the pitfalls of replicating CREATE USER and ALTER USER in MySQL As mentioned in that Percona blog post, all you need to do is add IF EXISTS / IF NOT EXISTS to your SQL and it solves the problem. Or in MariaDB, you also have the option of CREATE OR REPLACE syntax. Sure, it's a minor footgun for newbies, but not exactly a horrendous problem.
otabdeveloper4 3y ago
Postgres doesn't handle replication fine. It has two replication mechanisms, and both lack critical features and are broken by design. (Most of the problem comes from the fact that Postgres hasn't figured out that they need a standardised replication event wire protocol to do this correctly.)
- Spivak 3y ago
  > both lack critical features and are broken by design I bet AWS gets a lot of money for their DMS service for fixing this.