I just got back from PGCon2007. My favorite part of conferences like PGCon is the informal discussions that take place in the halls between sessions and each night at the local pubs. A popular topic at both the formal and informal parts of the conference is how people are dealing with replication.

I was initially surprised at the number of people who I spoke to that have large scale deployments of home-grown replication setups. One company had built their own replication system that allows read/write operations to any of 40 database servers. They designed the system and their application together to avoid (or at least minimize) conflicts. The system might not allow them to connect to an arbitrary database server and execute statements similar to UPDATE customer SET email=random() but if their application doesn’t typically do queries like that this isn’t a problem.

Another group was talking about a “new multi-master replication system written in pl/perl” that they were soon going to release. They told me that they’ve been running it on their production e-commerce site for a number of months without serious incident. The system uses business rules that the DBA can define to resolve conflicts.

The overall theme I saw was that people using general purpose replication systems such as Slony where complaining about them while the people with custom tailored replication systems realized the limitations of their systems and where happy working inside of them.

Does this mean everyone should run off and build there own replication systems? I don’t think so because building a replication system that performs in a consistent manner is hard and requires people who understand what they are doing. Home-grown replication systems also tend to have significant limitations and can worsen performance if not done carefully. I think the answer is to understand what it is you are trying to do with a replication system and what limitations you are prepared to live with. Use this information to pick an existing system and learn its limitations and accept them. Be prepared to customize your application workflows around the limitations of your replication system.

It also means that the developers of replication systems need to keep trying to come up with ways of making the deficiencies of their systems easier to live with.

While at the conference I also attended a presentation on PGCluster-II. They are trying to build a synchronous shared disk, multi-master replication system by modifying the Postgres backend. They have a version that apparently works but has a serious write performance issue; Time will tell if these issues are implementation details or theoretical flaws. Unlike Postgres-R (which was based on a total write order) they obtain lots of distributed locks.