Replication is one of the most popular features used in RDBMS’s today. Replication is used for disaster recovery purposes (i.e. backup or warm stand-by servers), reporting systems where query activity is offloaded onto another machine to conserve resources on the transactional server, and scale-out architectures that use sharding or other methods to increase overall query performance and data throughput.

Replication is not restricted to only the major proprietary databases; open source databases such as MySQL and PostgreSQL also offer replication as a feature. While MySQL has offered built-in replication for a number of years, PostgreSQL replication used to be accomplished via community software that was an add-on to the core Postgres Server. That all changed with the release of version 9.0 of PostgreSQL, which now offers built-in streaming replication that is based on its proven write ahead log technology.

With the two most popular open source databases now providing built-in replication, questions are being asked about how they differ in their replication technologies. What follows is a brief overview of both MySQL and PostgreSQL replication, with a brief compare and contrast of the implementations being performed immediately afterwards.

https://www.theserverside.com/feature/Comparing-MySQL-and-Postgres-90-Replication

I think the article was very good until the final conclusion. The conclusion was quite neutral as if the article has not compared the two engines.

We had a big MySQL installation (1 master 4-5 slaves, each server with 1 or 2 quad CPUs). The painful section was when the master would go down, a slave was out of sync or a similar small problem which causes problems to the replication.

We were sometimes forced to bring down the website (sometimes up to 8000 concurrent users) to restablish the replication which sometimes would take a few hours (we had a 30-40G database).

I have very bad memories of that time. Later we paid a very large amount to buy a 32 core server and we now have a single DB server for that website.

After reading the article I find that PostgreSQL would cause even more headache. And that's if we forget the lower performance of PostgreSQL on the same hardware. We would need to pay much more for hardware and for a website which uses farely basic DB features (very few and simple stored procedures).

Hey, I just realized this was a pretty ancient thread... It got me thinking though, so in the event that replication still interests anyone, I’ll post here anyway :)

This is an interesting comparison, thanks @Cameron. Regardless of which solution is better, the fact that PostgreSQL and MySQL support synchronous multi-master schemes (partially or fully) doesn’t mean this is a smooth process, see problems like @Siamak’s -- data not synching properly between the two servers, major downtime, etc. It’s also interesting to ask about how each of these providers treat partitioning, and when it comes to scalability and performance questions, how they treat specific practices like horizontal database sharding. I know that PostgreSQL support sharded tables but I’m not sure how they treat auto-sharding, if they do at all. MySQL support sharding automatically but it has its cons, for instance, cross-shard transactions are a bit of an issue (if interested, this post about MySQL sharding discusses this issue in more detail). Anyway, I would enjoy reading a similar comparison between some major database solutions with regards to partitioning, and thinking about the implications of scaling up, out, and beyond :)...