Rails: Postgres Partial Indexing

While working on a new Box Out feature, I needed to query for graphics that have been shared on social media.

I had a graphics table with a tweet_id column. I wanted to query for graphics that have been shared on twitter (aka tweet_id IS NOT NULL).  

The table is in the hundreds of thousands of rows, so I wanted to index tweet_id to help the query along. I was going to a plain old add_index. But then I remembered that you can do partial indexes.

I knew that this was possible with Postgres. And I've used partial indexes in the past. What I didn't know is that you do this through Rails migrations without dropping down to execute and vanilla SQL (not that there is anything inheritantly bad about SQL).

where

A few googles later I learned that Rails actually has support built in. The where key allows you to define a partial index.

add_index :graphics, :tweet_id, 
  where: "tweet_id IS NOT NULL", 
  algorithm: :concurrently

Slick! This means only graphics with a tweet_id will go into the index. Just a minor space savor for me, but could be a big deal for you and one of your tables.

For more space saving tips like this, check out this post I found.

aglorithm: :concurrently

If you haven't seen aglorithm: :concurrently, it allows you to avoid downtime by indexing the column in the background.

It takes a bit longer to complete and can slow things down a bit (which makes sense, as extra work is happening). But it means your app can be up and available while adding the index. No locks or downtime.

strong_migrations

Lastly, while I'm on the topic of migrations, one of my favorite gems is strong_migrations. It automatically catches migrations that could cause problems in production while you are still in development and lets you know. Usually, it provides you with the steps to get to where you want to be as well.

For example, on this migration I initially forgot to use the algorithm: :concurrently. As soon as I tried to bin/rails db:migrate, strong_migrations complained quite loudly at me by raising an error. I updated the migration and then it ran smoothly.