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).
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.
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.
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.