by Andrew Vogel on April 2, 2019

Last week, I was called to jump on a Rails project that needed some performance optimizations. Our chosen approach was to take a bulk lookup-and-update process out of ActiveRecord land and move everything into Postgres. The performance improvement was huge for large record sets. At the high end, we saw request times go from > 5 minutes to sub 2 seconds.

I had used SQL and Postgres before, but I wasn't totally familiar with its full set of features. At first, I paired with a coworker who has a deeper knowledge of Postgres. After a few days of writing queries and refactoring the test suite, everything was green and I was ready to deploy to staging for testing.

Here's some interesting SQL and PostgreSQL things I learned along the way.

Although not exclusive to PostgreSQL, CTE's, or Common Table Expressions, are a way to write reusuable queries in a database system.

Let's say we have 2 tables - books and authors.

We can write a really simple CTE to get the books with the authors:

with authors_and_books as (
    SELECT
        b.id as book_id,
        b.title,
        a.name as author_name
    FROM books b
    JOIN authors a on b.author_id = a.id;
);

Then, selecting from our CTE, we get the following:

SELECT * FROM authors_and_books;

This example is simple but you can see how this can really come in handy when you need to reuse complex queries.

Another really useful database feature is the ability to create temporary tables. In my case, this was really useful for staging "unprocessed" data that was posted from the front end. To create a temp table, you'll need to define its schema like your normal create table .. statement.

CREATE TEMPORARY TABLE temp_isbns (
    title varchar(255) NOT NULL,
    isbn varchar(255) NOT NULL,
    author_name varchar(255) NOT NULL
) ON COMMIT DROP;

The important part here is the ON COMMIT at the end. You'll need to tell Postgres how to handle the temporary table at the end of the transaction block. DROP tells Postgres to drop the temporary table at the end of the transaction block. The Postgres docs describe more of the ON COMMIT options.

The COALESCE function returns the first non-null value passed to it. This function accepts an unlimited number of arguments and it returns the first non-null argument, evaluated from left-to-right.

Here's a few examples of what that might look like:

select coalesce(null, 1);

select coalesce(null, null, 1, null);

select coalesce(2, null, 1);

Upserting, or updating-and-inserting, is a superful feature in Postgres. It allows you to handle inserts with conflict resolution if a record already exists in the database.

Remember our books table?

We'll insert some records into books but if the book exists, we'll choose to do nothing:

INSERT INTO books (id, title, author_id, genre) VALUES
    (3, 'Harry Potter and The Chamber of Secrets', 2, 'fantasy'),
    (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy')
ON CONFLICT DO NOTHING;

Now let's look at our table:

SELECT * FROM books;

Other other hand, let's insert records into books and update the record with the corresponding ID. Per the Postgres docs, you'll need to use the EXCLUDED table to reference the values proposed for insertion.

INSERT INTO books (id, title, author_id, genre) VALUES
    (3, 'Harry Potter and The Goblet of Fire', 2, 'fantasy'),
    (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy')
ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title;

Looking at our table one last time:

SELECT * FROM books;

Thanks for following along with this blog post. If there's a particular Postgres feature you'd like to hear about, feel free to reach out at andrew.vogel@hashrocket.com.