Category: Databases

Managing Postgres with Pulumi and Terraform

Adventures with Infrastructure as Code

I’m a big fan of Pulumi, and I’ve previously used it for cloud infrastructure. That’s their main selling point; Pulumi’s slogan is literally “Program the cloud.” Interestingly, Pulumi can also be used to manage on-premise infrastructure, including PostgreSQL databases. Let’s dive into the details.

Pulumi basics

Pulumi takes a declarative infrastructure definition and handles provisioning said infrastructure, just like Terraform, AWS CloudFormation, and Azure Resource Manager. The main difference is that in Pulumi you’re writing real code (Node, Python, or Go) to build that infrastructure definition instead of a YAML or JSON file, it’s great.

Pulumi/Terraform PostgreSQL Provider

After Pulumi has executed your code to build up an infrastructure definition, it needs to interact with external infrastructure resources (cloud providers, databases, etc.) to turn that definition into a reality. Some of Pulumi’s functionality for working with external resources is derived from Terraform providers. For example, Pulumi has a PostgreSQL provider which is derived from the Terraform PostgreSQL provider.

At a high level, the PostgreSQL provider lets you define databases, roles, schemas, permissions, and PostgreSQL extensions. It stops short of letting you manage DDL objects and data within the database (which is probably for the best given the complexities of schema+data migrations).

Defining a Postgres database with TypeScript

Pulumi can be used to instantiate entire Postgres clusters, but for this tutorial let’s assume you already have an existing cluster managed outside of Pulumi. It can be running anywhere: in the cloud, in a container, on your local machine, whatever.

First, install Pulumi then instantiate a new TypeScript project with pulumi new typescript. This scaffolds a bare-bones Node+TypeScript Pulumi project with the following files:

index.ts                package-lock.json       tsconfig.json
Pulumi.yaml             node_modules            package.json

Install the Pulumi PostgreSQL provider with npm i @pulumi/postgresql (or just edit your package.json directly).

Next, we need to tell Pulumi which Postgres cluster to connect to and how. The PostgreSQL provider’s configuration points are documented here. My Postgres cluster is running on a local VM with the hostname fedora-vm, so I set the postgresql:host variable like so:

pulumi config set postgresql:host fedora-vm

This creates a YAML configuration file for the current Pulumi stack, or modifies one if it already exists. For my dev stack, this creates a file named Pulumi.dev.yaml with the following contents:

config:
  postgresql:host: fedora-vm

You’ll want to do the same for postgresql:username and postgresql:password, to tell Pulumi which credentials to use. Sensitive configuration values like passwords can be encrypted using the --secret flag.

Finally, we’re ready to work with the fun stuff: configuring a database in real TypeScript code. Let’s say we want to instantiate a Pulumi-managed database, create a role with login permissions, and grant the role SELECT permission on tables in the public schema in the new database. This can all be done in the index.ts file like so:

import * as pulumi from "@pulumi/pulumi";
import * as postgresql from "@pulumi/postgresql";

const config = new pulumi.Config();

const managedDatabase = new postgresql.Database("managedDatabase", {
  name: "pulumi"
});

const publicReaderRole = new postgresql.Role("publicReaderRole", {
  login: true,
  name: "public_reader",
  password: config.require("publicReaderPassword")
});

const publicReaderSelectTablesGrant = new postgresql.Grant(
  "publicReaderSelectTablesGrant",
  {
    database: managedDatabase.name,
    objectType: "table",
    privileges: ["SELECT"],
    role: publicReaderRole.name,
    schema: "public"
  }
);

Relational Database History

1983 called. They said SQL needs a makeover

I recently read 2 papers that I’d highly recommend if you ever wonder about the underpinnings of modern relational databases:

What Goes Around Comes Around (Stonebraker and Hellerstein, 2005)

This is an opinionated history of database architectures from roughly 1970 to 2005, and it’s got some serious credentials behind it – Michael Stonebraker was the driving force behind Postgres and many other things. It’s an easy read, and it’s a good explanation of how relational DBs and SQL came to (mostly) rule the world. It was written amidst a lot of hype over XML databases, and it (rightly, in retrospect) critiques XML DBs and suggests that they won’t see much commercial adoption.

One interesting bit: Stonebraker and Hellerstein do not like SQL, and they claim that it became dominant largely because of IBM’s dominance in the marketplace during the 1980’s. Which leads us to the next item…

A Critique of the SQL Database Language (Chris Date, 1983)

This was cited approvingly in the 1st paper as a “scathing critique of the semantics of SQL”, which caught my eye - virtually every database supports SQL, how bad can it be?

I won’t summarize the entire thing, it’s a laundry list of complaints, but this resonated with me:

Notice that it is just table-names that appear in the FROM clause. Completeness suggests that it should be table-expressions (as Gray puts it, “anything in computer science that is not recursive is no good”).

…a simple table-reference (i.e. a table-name) ought to be just a special case of a general table-expression

To give a (trivial) example, say we want to union 2 tables and extract a column. We’d write that like this in SQL:

SELECT EMPLOYEE_ID FROM EMPLOYEES
UNION
SELECT EMPLOYEE_ID FROM CONTRACTORS

But if table names were just special cases of table expressions, we could write it like this instead:

SELECT EMPLOYEE_ID FROM (EMPLOYEES UNION CONTRACTORS)

SQL gets the job done, and it could be a lot worse - but it is a little sad that many of the issues academics have known about for 35+ years still exist today. It’s a reminder of how entrenched something that’s only “good enough” can become.

Quick T-SQL Regression Testing

Using CHECKSUM() and CHECKSUM_AGG()

Here’s something I’ve found useful in SQL Server, but it should apply in any DBMS with checksum functions. Next time you’re refactoring some database objects and you want to do some regression testing, give the CHECKSUM (MSDN) and CHECKSUM_AGG (MSDN) functions a try.

CHECKSUM and CHECKSUM_AGG

They behave pretty much as you would expect; CHECKSUM returns 1 checksum given 1 row, and CHECKSUM_AGG is an aggregate function that returns 1 checksum given multiple checksum rows. Between the two of them, you can get a checksum for any arbitrary collection of data:

-- Returns 2 rows with 2 columns
SELECT Field1, Field2
FROM (VALUES ('foo','bar'),('baz','foo')) TempTable(Field1,Field2)

-- Returns 2 rows with 1 checksum column
SELECT CHECKSUM(Field1,Field2)
FROM (VALUES ('foo','bar'),('baz','foo')) TempTable(Field1,Field2)
 
-- Returns 1 row with 1 checksum column
SELECT CHECKSUM_AGG(CHECKSUM(Field1,Field2))
FROM (VALUES ('foo','bar'),('baz','foo')) TempTable(Field1,Field2)

Regression testing

These help with a common problem: when you’re refactoring a database object, how can you be sure that you haven’t changed the existing functionality? Sometimes it’s enough to spot check a few inputs, but often the logic is complex enough that this does not provide a high level of confidence. With CHECKSUM and CHECKSUM_AGG, you can quickly check large numbers of test cases.

For example, say we have a TVF called MyDateTVF that takes in a date parameter and returns columns Column1 and Column2. I want to refactor this function and then test that the functionality is unchanged for every date in 2018. If we have a table called DateList which contains every date (more useful than you might expect in a DB with a lot of temporal aspects), I can simply run the following before and after my change:

SELECT CHECKSUM_AGG(CHECKSUM(MDT.Column1, MDT.Column2))
FROM DateList DL
CROSS APPLY (
    SELECT Column1, Column2 from dbo.MyDateTVF(DL.Date)
) MDT
WHERE DL.Date BETWEEN '2018-01-01' AND '2018-12-31'

If that query returns the same checksum before and after my change, we can rule out any regressions for those inputs. Nice and easy!

Databases as file formats

The .mbtiles way

I’m building an interactive online map of all properties in Vancouver, and along the way there have been a few pleasant surprises. Most recently: the .MBTiles tileset format is surprisingly cool.

Background

Mapbox is one of the biggest players in the open source mapping space (especially now that Mapzen and Carto have thrown in the towel – Mapzen is closing and Carto is now using Mapbox tech). One of the many nice things about Mapbox is that they developed an efficient open standard for vector map tiles, appropriately named Mapbox Vector Tiles (read this if you’re not sure why vector tiles are great).

Map tiles are often pre-computed for each zoom level, and once you’ve done that you need to store them somewhere. Enter the .MBTiles tileset format.

Poking around under the hood

My first encounter with this file format occurred when I used Eric Fischer’s excellent tippecanoe tool to simplify my data set at lower zoom levels. Tippecanoe generates .mbtiles files, which are easy to serve to clients either by uploading to Mapbox, using a third party tile server, or even by rolling your own server with something like the mbtiles Node.js package.

All great… but after setting up a server my Mapbox GL JS client refused to render the tiles. I tried a few things without much luck, and then as a last resort I decided to poke around in the .mbtiles file. I was expecting to need a hex editor or similar, but then I saw this beauty in the spec:

MBTiles is a specification for storing tiled map data in SQLite databases

The files themselves are just relational databases in a known schema – how cool is that? Emboldened, I grabbed a SQLite client and opened up my .mbtiles file:

headshot

Cities & Code

Things that don't quite fit in 280 characters.

Top Categories

View all categories

About

I'm a software engineer in Vancouver, Canada. I'm interested in databases, urban planning, computing history, and whatever else catches my fancy.

Learn More / Contact me