Active Record, Ruby on Rail’s ORM, has support for Prepared Statements that work if you structure your query for it. Because of my work on GoodJob, which can make a lot of nearly identical database queries every second to pop its job queue, I’ve invested a lot of time trying to make those queries as efficient as possible.

Prepared Statements are a database feature that allow the database to reuse query parsing and planning when queries are structurally the same. Prepared statements, at least in Postgres, are linked to the database connection/session and stored in memory in the database. This implies some things:

  • There can be a performance benefit to making queries “preparable” for Prepared Statements which Active Record will decide for you based on how a query is structured.
  • There can be a performance harm (or at least non-useful database processing and memory growth) if your application produces a lot of preparable queries that are never reused again.

By default, Rails will have the database store 1,000 queries (statement_limit: 1000). Many huge Rails monoliths (like GitHub, where I work) disable prepared statements (prepared_statements: false) because there is too much query heterogeneity to get a performance benefit and the database spends extra and unnecessary cycles storing and evicting unused prepared statements. But that’s maybe not your application!

Structurally similar queries can still have variable values inside of them: these are called bind parameters. For example, in GoodJob, I want pop jobs that are scheduled to run right now. In SQL that might look like:

SELECT * FROM good_jobs WHERE scheduled_at < '2024-03-31 16:44:11.047499`

That query has the downside of the timestamp changing multiple times a second as new queries are emitted. What I want to do is extract out the timestamp into a bind parameter (a ? or $1 depending on the database adapter) instead of embedded in the query:

SELECT * FROM good_jobs WHERE scheduled_at < ?

That’s the good stuff! That’s ideal and preparable 👍

But that’s raw SQL, now how to do that in Active Record? In the following exploration, I’m using the private to_sql_and_binds method in Active Record; I’m also using the private Arel API. This is all private and subject to change, so be sure to write some tests around this behavior if you do choose to use it. Here’s some quick experiments I’ve done:

job = Job.create(scheduled_at: 10.minutes.ago)

# Experiment 1: string with ?
relation = Job.where("scheduled_at < ?", Time.current)
# =>  Job Load (0.1ms)  SELECT "good_jobs".* FROM "good_jobs" WHERE (scheduled_at < '2024-03-31 16:34:11.064614')
expect(relation.to_a).to eq([job])
_query, binds, prepared = Job.connection.send(:to_sql_and_binds, relation.arel)
expect(binds.size).to eq 0 # <-- Not a bind parameter
expect(prepared).to eq false # <-- Not preparable

# Experiment 2: Arel query with value
relation = Job(Job.arel_table['scheduled_at'].lt(Time.current))
# =>  Job Load (0.1ms)  SELECT "good_jobs".* FROM "good_jobs" WHERE scheduled_at < '2024-03-31 16:34:11.064614'
expect(relation.to_a).to eq([job])
_query, binds, prepared = Job.connection.send(:to_sql_and_binds, relation.arel)
expect(binds.size).to eq 0 # <-- Not a bind parameter
expect(prepared).to eq true # <-- Yikes 🥵

# Experiment 3: Arel query with QueryAttribute
relation = Job.where(Job.arel_table['scheduled_at'].lt(ActiveRecord::Relation::QueryAttribute.new('scheduled_at', Time.current, ActiveRecord::Type::DateTime.new)))
# =>  Job Load (0.1ms)  SELECT "good_jobs".* FROM "good_jobs" WHERE scheduled_at < $1  [["scheduled_at", "2024-03-31 16:34:11.064614"]]
expect(relation.to_a).to eq([job])
_query, binds, prepared = Job.connection.send(:to_sql_and_binds, relation.arel)
expect(binds.size).to eq 1 # <-- Looking good! 🙌
expect(prepared).to eq true # <-- Yes! 👏

That very last option is the good one because it has the bind parameter ($1) and then the Active Record logger will show the values in the nested array next to the query. The successful combination uses:

  • Arel comparable syntax
  • Wrapping the value in an ActiveRecord::Relation::QueryAttribute

Note that many Active Record queries will automatically do this for you, but not all of them. In this particular case, it’s because I use the “less than” operator, whereas equality does make it preparable. You’ll have to inspect each query yourself. For example, it’s also necessary with Arel#matches/ILIKE. It’s also possible to temporarily disable prepared statements within a block in the undocumented (!) Model.connection.unprepared_statement { your_query }.

The above code is true as of Rails 7.1. Jean Boussier has improved Active Record in newer (currently unreleased) Rails to also properly bind Job.where("scheduled_at < ?", Time.current) query syntax too 🙇

Update: I realized I didn’t try beginless/endless range values. Good news: they create bind parameters 🎉

# Experiment 4: beginless range
relation = Job.where(scheduled_at: ...Time.current)
# =>  Job Load (0.1ms)  SELECT "good_jobs".* FROM "good_jobs" WHERE scheduled_at < $1  [["scheduled_at", "2024-03-31 16:34:11.064614"]]
expect(relation.to_a).to eq([job])
_query, binds, prepared = Job.connection.send(:to_sql_and_binds, relation.arel)
expect(binds.size).to eq 1 # <-- Looking good! 🙌
expect(prepared).to eq true # <-- Yes! 👏