Sunday, May 29, 2011

Slony and PostgreSQL Partitions

   To start with in our company we had a sales application which reads/writes sales data about US,EU and AU regions in one database. As the number of hits for our application grew our database  had performance issues along with the existing latency issue. Latency issue was due to both application and database being hosted in India(headquarters) and most of our customers were is United States, Australia and Europe. We took this opportunity to distribute our application globally and re-architected our application as shown in the diagram below:
  


Basically we hosted multiple instances of our application and databases as per our customer proximity. So for US customers we hosted a instance of our application and database in US data center and similarly for EU customers in EU data center and AU customers in AU data center.  This helped us improving performance and latency as well. Also as a side effect of this architecture when any disaster happens only that corresponding region was affected and the rest was operating without any issues.
  Next we wanted to consolidate the sales data of all our regions to global headquarters where accounting and settlement were happening. To achieve this we availed the services of Slony Replication and PostgreSQL Partitioning. On our head quarters database we created sales table as the master table and sales_us, sales_eu and sales_au were inherited from master table. Moreover sales_us, sales_eu and sales_au tables were all replicated from their respective regions to headquarters using Slony Replication.This helped us in consolidating all the sales data to headquarters which were then exposed via the Global Sales App.
  In short we showcased to our Management how we emulated MultiMaster Replication with Slony by completely avoiding conflict resolution cases with help of individual tables and Table Inheritance.
 For reference the cluster creation code can be found at Cluster Creation Code