Tech Talk

I recently noted that the COPY command in Postgres doesn’t have syntax to skip columns in source data when importing it into a table. This necessitates using one or more junk columns to capture data we’ll just be throwing away. During that, I completely forgot that friendly devs had contributed alternative file handling methods as Foreign Data Wrappers. Most people think of foreign wrappers as a method for interacting with remote databases. Perusing the full list however, reveals some surprising data sources. Twitter? Hive? Video cards?!

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.