---
title: "Rails: Postgres Partial Indexing"
description: "Postgres has a nice feature where you can index only the values you want to query on. Today I learned that Rails has a nice shortcut for taking advantage of said feature."
url: "https://www.johnnunemaker.com/rails-postgres-partial-indexing/"
author: "John Nunemaker"
published: "2021-01-23"
updated: "2021-02-04"
tags: ["TIL","rails","performance"]
---

# Rails: Postgres Partial Indexing

While working on a new [Box Out](https://boxoutsports.com) 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](https://www.postgresql.org/docs/8.0/indexes-partial.html). 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.

```ruby
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](https://hakibenita.com/postgresql-unused-index-size) 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](https://github.com/ankane/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.