I’ve maintained since about 2011, that the problem with scaling Postgres to truly immense installations could be solved by a query coordinator. Why? Most sharding systems utilize an application-level distribution mechanism, which may or may not leverage an inherent hashing algorithm. This means each Postgres instance can be treated independently of all the others if the distribution process is known. On a cleverly architected system, the application is algorithm aware, and can address individual shards through a driver proxy or accessor class.

Postgres theory, feature discussion, and advocacy are fun. But even I’ll admit it’s nice to have some practical application every once in a while. This week, we’re going to build an actual database.

But what would be small enough for a proof of concept, yet somewhat interesting? Well, I’m a fan of Hearthstone. It’s a silly card game much like Magic: The Gathering, but has the distinct aura of “eSports!” Regardless, it’s a fun little time waster, and has a few hundred data points we can manipulate.

With extremely fortuitous timing for my first article following the holidays, Postgres 9.5 was officially been released into the wild just yesterday. I tend to think about past releases when new versions come out, and consider everything that has changed since the early days. How early? I’ve personally been using Postgres since 2001, when my new employer bellyached about their Postgres 6.5 database crashing frequently and generally making their lives more difficult. So as a newly minted advocate of the LAMP stack, I purged it in favor of MySQL.

With Postgres 9.5 on the horizon, I figured it’s a good time to see how things have advanced since my last dive into that particular ocean. This is probably particularly relevant since even MongoDB, a JSON-driven NoSQL database, is now partially powered by Postgres. A lot of people found that particular revelation quite shocking, but maybe they shouldn’t, given the advancements embedded within the last couple of Postgres releases.

With all of the upheaval in the Postgres world thanks to advancements in extensions, foreign data wrappers, and background workers, it’s getting pretty difficult to keep track of everything! One of these rapidly moving targets is Postgres-XL and its role in helping Postgres scale outward. Large warehouses have a critical need for horizontal scaling, as the very laws of physics make it effectively impossible to perform aggregate queries on tables consisting of several billion rows. Even furiously indexed, partitioned, and clustered tables, on over-provisioned servers loaded with the best NVRAM-based storage, 64 CPUS, and 1TB of RAM, become lumbering beasts when someone wants a cumulative summary of last year’s activity.