Better Database Migrations in Postgres

As your database grows and scales there are some operations that you need to take more care of than you did when you were just starting. When working with your application in your dev environment you may not be fully aware of the cost of some operations until you run them against production. And at some point most of us have been guilty of it, running some migration that starts at 5 minutes, then 15 minutes in it’s still running, and suddenly production traffic is impacted.

There are two operations that tend to happen quite frequently, each with some straightforward approaches to mitigate having any noticable amount of downtime. Let’s look at each of the operations, how they work and then how you can approach them in a safer way. Read on

Postgres Backups: Logical vs. Physical an Overview

It’s not a very disputed topic that you should backup your database, and further test your backups. What is a little less discussed, at least for Postgres, is the types of backups that exist. Within Postgres there are two forms of backups and understanding them is a useful foundation for anyone working with Postgres. The two backup types are

  1. Physical: which consist of the actual bytes on disk,
  2. Logical: which is a more portable format.

Let’s dig into each a bit more so you can better assess which makes sense for you. Read on

Postgres Open Silicon Valley Line-up: First Take

This year Postgres open and PGConf SV have combined to great a bigger and better conference right in downtown San Francisco. I’m obviously biased as I’m one of the co-chairs, and I know every conference organizer says picking the talks was hard, but I’m especially excited for the line-up this year. The hard part for me is going to be which talks do I miss out on because I’m sitting in the other session that’s ongoing. You can see the full list of talk and tutorial sessions, but I thought it’d be fun to do a rundown of some of my favorites. Read on

Working With Time in Postgres

A massive amount of reporting queries, whether really intensive data analysis, or just basic insights into your business involving looking at data over a certain time period. Postgres has really rich support for dealing with time out of the box, something that’s often very underweighted when dealing with a database. Sure, if you have a time-series database it’s implied, but even then how flexible and friendly is it from a query perspective? With Postgres there’s a lot of key items available to you, let’s dig in at the things that make your life easier when querying. Read on

Why Use Postgres (Updated for Last 5 Years)

Five years ago I wrote a post that got some good attention on why you should use Postgres. Almost a year later I added a bunch of things I missed. Many of those items bear repeating, and I’ll recap a few of those in the latter half of this post. But in the last 4-5 years there’s been a lot of improvements and more reasons added to the list of why you should use Postgres. Here’s the rundown of the things that make Postgres a great database you should consider using. Read on

Getting Started With JSONB in Postgres

JSONB is an awesome datatype in Postgres. I find myself using it on a weekly basis these days. Often in using some API (such as clearbit) I’ll get a JSON response back, instead of parsing that out into a table structure it’s really easy to throw it into a JSONB then query for various parts of it.

If you’re not familiar with JSONB, it’s a binary representation of JSON in your database. You can read a bit more about it vs. JSON here.

In working with JSONB here’s a few quick tips to get up and running with it even faster: Read on

Simple but Handy Postgres Features

It seems each week when I’m reviewing data with someone a feature comes up that they had no idea existed within Postgres. In an effort to continue documenting many of the features and functionality that are useful, here’s a list of just a few that you may find handy the next time your working with your data.

Psql, and \e

This one I’ve covered before, but it’s worth restating. Psql is a great editor that already comes with Postgres. If you’re comfortable on the CLI you should consider giving it a try. You can even setup you’re own .psqlrc for it so that it’s well customized to your liking. In particular turning \timing on is especially useful. But even with all sorts of customization if you’re not aware that you can use your preferred editor by using \e then you’re missing out. This will allow you to open up the last run query, edit it, save‚Äďand then it’ll run for you. Vim, Emacs, even Sublime text works just take your pick by setting your $EDITOR variable.

Read on

Syncing From Postgres to Salesforce - Data Mappings

For the second time now I’ve had to implement a system that syncs from my system of record into, the first at Heroku and now at Citus Data. The case here is pretty simple, I have a software-as-a-service, B2B product. It’s a homegrown application in these cases in Ruby, but could be Python, .Net, any language of your choosing. The problem is I don’t want to have to be rebuilding my own CRM, reporting, etc. on top of all of my internal database. And as soon as you’re at some reasonable size (sales guy of 1 or more) you need to be able to provide insights on what’s in that system of record database to others.

While my tooling isn’t a full fledged product by any means, here’s a bit of how I’ve developed this process a few times over and some of the annoying bits of code to help get you started. In this post I’ll walk through some of the basic datatypes, then we’ll follow-up with the overall architecture and tweaks you need to make to Salesforce, and finally we’ll provide some example code to help you create this setup yourself. Read on

Open DNS for When DNS Outages Occur

Open DNS is a DNS resolver that caches records beyond their TTL if the upstream DNS server cannot be found. In cases like today’s major outage it can be handy to swap your DNS settings out for this, or it may be worth using as a standard default. Resolution may be a bit slow as it will try to see if the upstream server cannot be found, but it at least can get you back to a working state.

If you know what you’re doing then all you need to do is configure your DNS settings to: and

If you need a little more guidance you can go into your System Preferences on Mac, select Network, then Advanced and finally the DNS tab. You should set it up to look as follows:

DNS Configuration

A Tour of Postgres’ Foreign Data Wrappers

SQL can be a powerful language for reporting. Whether you’re just exploring some data, or generating reports that show month over month revenue growth it’s the lingua franca for data analysis. But, your data isn’t always in a SQL database, even then if you’re using Postgres you can still likely use SQL to analyze, query, even joing with that data. Foreign data wrappers have been around for years in Postgres, but are continuing to mature and be a great option for joining disparate systems.

Overview of foreign data wrappers

If you’re unfamiliar, foreign data wrappers, or FDW, allow you to connect from within Postgres to a remote system. Then you can query them from directly within Postgres. While there is an official Postgres FDW that ships with Postgres itself, that allows you to connect from one Postgres DB to another, there’s also a broad community of others.

At the core of it Postgres provides certain APIs under the covers which each FDW extension can implement. This can include the ability to map SQL to whatever makes sense for a given system, push down various operators like where clauses, and as of Postgres 9.3 can even write data. Read on