Avoid the N+1 problem in Rails by harnessing your database

The #1 cause of slow apps may be the N+1 problem, where the app queries a database over and over again to get information about a list of items.

To avoid this, learn the powers of your database so you can query it more efficiently.

Say that you have an app to manage meetings. When someone creates a meeting, there is a standard checklist that they must complete.

The standard checklist items, which are the same for every meeting, are stored in the Checklist table. When someone checks off an item for a particular meeting, that action is stored in Checklist Checked Items table.

Here is the database structure and the sample records we’ll use in this example. For the only meeting in the database, the user has completed two of the checklist items.


  • id: 1, title: A Meeting


  • id: 1, title: Book meeting location
  • id: 2, title: Arrange catering
  • id: 3, title: Invite people


  • id: 1, checklist_item_id: 1, meeting_id: 1
  • id: 2, checklist_item_id: 2, meeting_id: 1

Say you want to retrieve, for a given meeting, the checklist items and whether each has been checked. You want any checked items to appear at the end of the list.

Here is the app-centric way to do that:

  1. Query the checklist items
  2. Loop through the checklist items, and for each, query the checked items to see if it’s been checked for that meeting
  3. Place each of the items, and whether it’s been checked, in an array of hashes
  4. Sort the array of hashes to place checked items last

This is inefficient and presents the N+1 problem, with step 2 producing multiple queries that will only increase in number as your app grows. Furthermore, there is an unnecessary array middleman and a sorting operation on that array.

Here is the database-centric way:

  1. In one query, get the checklist items, whether each is checked, sorted by whether each is checked.

That’s right, one query has replaced many, and the databsae is doing all the work.

The secret lies in Postgres’s CASE function, which lets you insert conditional logic into queries.

Here, we join checklist_items and checklist_checked_items a checked field that shows whether the checklist item is checked for that particular meeting:

Or, using ActiveRecord:

This returns the checklist items and whether they’re checked, with checked items last:

  • title: Arrange catering, checked: false
  • title: Invite people, checked: true
  • title: Book meeting location, checked: true

By understanding Postgres’s CASE function, we were able to replace many queries with one, speeding up our app.

This is just one example of how your database can take the burden and handle it better. Read your database’s documentation to discover more.

Further reading:

Hired.com brings Rails job offers to you. Free for candidates. Salaries from $75,000 to $250,000. Sign up now!

2 thoughts on “Avoid the N+1 problem in Rails by harnessing your database”

Leave a Reply