4 ms·
I notice you often ask that question on MySQL threads.[1][2] Here's the recurring pattern: if it's a single-instance database, either PostgreSQL or MySQL will
by jasode 10y ago
I notice you often ask that question on MySQL threads.[1][2]
Here's the recurring pattern: if it's a single-instance database, either PostgreSQL or MySQL will work. However, if it is sharded database with multi-master replication, the overwhelming industry preference is MySQL instead of PostgreSQL.
When it's just a single-instance db, a compelling case can be made for PostgreSQL because of features such as stricter type checking and stronger stored procedure language.
However for sharded databases, there have been several high-profile case studies of migrations from PostgreSQL to MySQL including Etsy and Uber. I can't think of any major company doing the reverse of migrating a multi-master db from MySQL to PostgreSQL.[3] From an operational standpoint, the replication of Postgre dbs is fragile compared to MySQL. Instagram is a famous example of a large website using PostgreSQL but keep in mind that they did(do) master-slave instead of master-master. Reddit on Postgre is another example configured as master-slave. As for this particular thread about Pinterest, they use master-master and I've seen no evidence that Postgres+3rd party tools is superior for that scenario.
If you disagree with the above assessment, it would be helpful if you explain how PostreSQL is equivalent or better than MySQL for multi-master db replication architectures. If you just repeatedly ask, "is Postgres good for this?", the replies don't seem to give you the answers you're looking for. If you have a strong position, you should state it so it guides further discussion.
[1]https://news.ycombinator.com/item?id=10087412 https://news.ycombinator.com/item?id=10087412
[2]https://news.ycombinator.com/item?id=10926854 https://news.ycombinator.com/item?id=10926854
[3] when Uber migrated from MySQL to PostgreSQL in 2013, it was still a single-instance to single-instance migration. They did it to take advantage of PostGIS features. The subsequent 2016 operational difficulties of multi db replication pushed them back to MySQL.
- olavgg 10y agoI cannot understand the fuzz about multi-master replication, it either involves dirty/phantom reads, or it is awfully slow. Wikipedia has a lot of examples of these issues https://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Non-repeatable_reads https://en.wikipedia.org/wiki/Isolation_%28database_systems%... There should be a book about the dark side of multi-master replication at scale, about what can you trade away and still be happy with. Preferring either PostgreSQL or MySQL for this is just avoiding the technical complexity that multi-master replication is.
- rwultsch 10y agoHi, I was the first MySQL DBA hired Pinterest and before that I worked at Facebook and GoDaddy. At none of these places did we run active/actice. One of the first things I did at Pinterest was rip out the multi-master configure because it is dangerous. Why MySQL? Really easy replication, no vacuum, and great point lookup performance.
- cookiecaper 10y agoYou're obviously qualified so I'd love to get more info on how you deployed MySQL replication. I have a low opinion of it after using it at my employer for the last few years. MySQL allows writes to slaves (yikes) and the binlog coordinates are a constant hassle. ROW, STATEMENT, and MIXED binlog formats can make interoperability a mess and further increase the risk that there will be discrepancies between slave and master data (for some reason, we're on STATEMENT and/or MIXED replication, which means that the slave receives a list of the queries to execute and may have different output than the master). To sync up a new slave, you have to set up slave_skip_errors to ignore primary key conflicts and other common errors that occur because it's impossible to really get a firm binlog coord (it's really only marginally possible due to Percona's efforts with innobackupex/mydumper). I know that GTIDs are available now and are supposed to resolve the issues with binlog coords, but we haven't activated/used this featureset yet. Is it really a magic bullet? MySQL just seems to do replication in a very unstable way that no other major RDBMS is willing to approach. I use PostgreSQL replication on a personal project and while it takes a smidgen longer to set up (maybe 20 minutes instead of 5), it seems well worth it to me. I never have to worry about whether my slave's output matches the master (because the query is performed on the master and the result shipped out via the WAL and that's the only way to use replication). I don't have to worry that I connected to the wrong endpoint and accidentally wrote to the slave, causing a conflict that requires a new full dump and resync. I don't have to worry that binlog coords are going to be wrong or that a write will go in at just the wrong moment and potentially require me to redump and resync the whole thing again (but at least require me to skip errors). How do you handle these issues with MySQL replication, and justify the risks in order to shave a few minutes off the upfront setup time?
- 10y ago
- pbreit 10y agoI was just genuinely curious and not experienced enough to hold an opinion stronger than that generally Postgres seems to be a good choice for most of what could be thrown at it in the web world.