3 ms·

Aside from the replication issue, Postgres is just a better SQL database for actually managing your data than MySQL. It has proper typing, proper validations, s

by rswail 5y ago

Aside from the replication issue, Postgres is just a better SQL database for actually managing your data than MySQL. It has proper typing, proper validations, standard SQL and its documentation is second to none for any software product (FOSS or COTS) that I've used over the last 20 years. Replication and failover are actually hard problems to solve and the logical replication now gives Postgres the flexibility needed to avoid the WAL-shipping (and the Oracle et al equivalents). In particular, RDBMS with ACID require 2-phase commits to have true replication, which then requires a transaction controller, which then needs to be HA and have failover. Lots of people cut corners on this and only discover their loss of data when a failover event happens. PostGIS is truly amazing and I'm not sure any other RDBMS has a similarly extended GIS capability.

almog 5y ago
By 'true replication', do you mean no replication lag or something else?
- zozbot234 5y ago
  I assume that it's a reference to synchronous replication, where transactions have to be successfully propagated to standby servers before they're considered committed by the primary. Postgres offers low-level features (PREPARE TRANSACTION and COMMIT PREPARED) that would be needed to implement it, but not a complete out-of-the-box solution. The Postgres documentation on HA https://www.postgresql.org/docs/current/high-availability.html https://www.postgresql.org/docs/current/high-availability.ht... is also excellent and does a very nice job of clarifying potential solutions in this general area.
disgruntledphd2 5y ago
I recently discovered that Snowflake has geospatial support, which is handy. I'm not massively familiar with PostGIS but at a glance, I can accomplish a bunch of spatial queries in Snowflake (whereas I figured I'd need to spin up Postgres to make this project work). To be fair to Snowflake, they make a great product, even if their pricing model is bananas and they want to be Oracle when they grow up.

by rswail 5y ago

almog 5y ago

By 'true replication', do you mean no replication lag or something else?

zozbot234 5y ago
I assume that it's a reference to synchronous replication, where transactions have to be successfully propagated to standby servers before they're considered committed by the primary. Postgres offers low-level features (PREPARE TRANSACTION and COMMIT PREPARED) that would be needed to implement it, but not a complete out-of-the-box solution. The Postgres documentation on HA https://www.postgresql.org/docs/current/high-availability.html https://www.postgresql.org/docs/current/high-availability.ht... is also excellent and does a very nice job of clarifying potential solutions in this general area.

disgruntledphd2 5y ago

I recently discovered that Snowflake has geospatial support, which is handy. I'm not massively familiar with PostGIS but at a glance, I can accomplish a bunch of spatial queries in Snowflake (whereas I figured I'd need to spin up Postgres to make this project work). To be fair to Snowflake, they make a great product, even if their pricing model is bananas and they want to be Oracle when they grow up.