Quick local database saves and restores with postgres

I recently added this rake task to our project. It performs fast database saves and restores using another postgres database. This is a very handy rake task to have around if you need to quickly go back to a previous version of your database and the normal create/restore process is too slow (for instances, restoring using a scrubbed database backup hosted on S3)

There is a lot going on here, but it is more or less wrapping this command, which creates a database from another

with some helpers to cut existing connections and automate the backup.

I’ve used this for quite some time, but with a flash drive, I can restore an 8.7 gig database in about 25 seconds instead of about 8 1/2 minutes from a local postgres backup.

To use it, it is simply:

Its a handy command to have in the toolbox. Whats in your toolbox?

Find the most popular capitalization of a word or phrase

Today, I needed to find the most popular capitalization of user entered product brands. Previously, I’d just used lowercase brands, something like this:

However, this was returning “whole foods” instead of “Whole Foods”, which was considered more desirable.

Using window functions and common table expressions, this is what I came up with:

Given this data:

Here is how it works:

The first part of the query gives us the total count for each brand, grouped by lower case, and for each unique capitalization of that brand provides a count and row number.

In this query, the rows with row_number == 1 are all the unique capitalizations

Of those, we want the ones with the highest count. Again, we partition with another common table expression. This time, ordering by the count per unique capitalization to ensure that the first row for each group was the most popular

From here, we just pick the first row for each brand and get the most popular capitalization

Simple enough eh?

Messy rails code demonstrating this and generating the tables in this post

More about window functions

More about common table expressions