For the second year in a row, we attended the annual conference on PostgreSQL Clustering, High Availability and Replication. This year we presented a talk about Implementing MGRID (presentation PDF, 9MB).
Our story starts with Portavita and why we decided to spin off MGRID and develop a Massively Parallel PostgreSQL almost four years ago.

Portavita, an online chronic disease management application provider, has a number of successful products along with a daily increasing amount of patient data that cannot be archived. Portavita provides websites that bring a daily mixed load on this data, mainly consisting of simple OLTP-type queries combined with some OLAP-type queries regarding logistics and quality performance indicators. Experience with scaling up shows that not only is it costly from a human-resources perspective, but also hardware prices tend to increase faster than the database sizes it can serve. The architecture had a negative economy of scale, and that's bad if your company aims to grow.

Once we realized Portavita’s scale-up architecture implied a negative economy of scale, we knew we needed to migrate to a horizontal, scalable platform. All of the available solutions at the time had some caveats and incompatibilities with basic RDBMS products. For instance, that there is no support for stored functions, or that the SQL dialect is different. Needless to say, we didn't want to trade being able to scale out for decreasing the productivity of our software engineers, so we set out to implement a Massively Parallel version of PostgreSQL that is 100 percent compatible with the standard PostgreSQL version: MGRID.

While implementing MPP, we also implemented the ISO-21090 Healthcare Datatypes as native types in the database, which provide huge benefits for software engineers that need to work with medical data since there is no longer any need to worry about how to deal with unit conversion and basic arithmetic on physical quantities. Also draconic timestamp expressions in SQL that try to perform time interval overlap and computations are no longer needed when using the ISO-21090 standard ts, ivl_ts and qset_ts datatypes.

Though MPP is usually associated with data warehousing, the performance figures we show demonstrate that MGRID is able to reach a considerable OLTP load, and that the benefits of a scale-out architecture really kicks in when the OLTP load is mixed with an OLAP-like load. One of the main benefits is that there is now a way to control the response time of heavy complex queries by controlling the shard size. We demonstrate that we can keep the query response time within a fixed bound, under an increasing database size, by increasing the number of servers and shards simultaneously. A platform that can grow with Portavita's growth, without having to pay relatively more hardware cost for more data was the main goal of Portavita.

When operating a non-trivial amount of servers, manual configuration of local and remote backup servers would be error-prone and impair DBA productivity. That's why MGRID has high-level primitives to control degree of redundancy. That, together with a configuration concept called "“redundancy groups,” allows the administrator to control where MGRID will place replicas of data. With two synchronous replicas on the same location, durability and high availability is achieved.

Our talk (9MB PDF) concludes with the fact that MPP PostgreSQL is possible and suited for OLTP. It also provides several links to papers and other talks that inspired us to create MGRID. If you are interested to learn more about MGRID, don't hesitate to email or give us a call.

Lastly, the 2ndQuadrant team has done an excellent job of hosting the conference and providing great extracurricular activities. We enjoyed it very much. Thanks and see you next year!