When a Rails database query actually runs

Post.where(status: "published") does not query anything. Neither does chaining order onto it, neither does to_sql, and neither does passing the whole thing to a partial. Somewhere later a method asks for rows, and at that moment the difference between size and length is the difference between one SELECT COUNT(*) and ten thousand instantiated objects. The methods look interchangeable in the Ruby and they are not interchangeable in the log.

Everything below was run on a scratch Rails 8.1.3.1 application against PostgreSQL 17.7 on port 15432, Ruby 4.0.5, Apple M2 Max. The schema is two tables: authors, and posts with author_id, title, status, views and an index on status. 30,000 posts, 10,000 of them status = 'published'. Every log line and every error message is pasted from that run, and the claims are asserted in a Minitest file that finishes 15 runs, 55 assertions, 0 failures, 0 errors.

What where hands back

Post.where(status: "published") returns a relation, which is a promise to build SQL, not SQL.

rel = Post.where(status: "published").order(:id).limit(3)
puts "class:    #{rel.class}"
puts "loaded?:  #{rel.loaded?.inspect}"
puts "to_sql:   #{rel.to_sql}"
puts "loaded?:  #{rel.loaded?.inspect}"
rel.each { |p| }
puts "after each loaded?: #{rel.loaded?}"

class:    Post::ActiveRecord_Relation
loaded?:  nil
to_sql:   SELECT "posts".* FROM "posts" WHERE "posts"."status" = 'published' ORDER BY "posts"."id" ASC LIMIT 3
loaded?:  nil
after each loaded?: true

Two things in that output are worth keeping. loaded? returns nil before the first load rather than false, so rel.loaded? == false is not the test you want. And to_sql built the full statement without going near the database, which is why it is the cheapest debugging tool Active Record has.

The class line is a small trap of its own. rel.class.to_s is "Post::ActiveRecord_Relation", but rel.class.name is "ActiveRecord::Relation", and Post::ActiveRecord_Relation written out in your code raises NameError: private constant Post::ActiveRecord_Relation referenced. An assertion written against rel.class.name fails against the value puts rel.class had printed one line earlier, which is a confusing five minutes the first time.

Which method sends the SQL

Three methods on a relation answer "how many", and the three of them do different work. Run under ActiveRecord::Base.uncached so nothing is served from the query cache, with the Active Record logger writing to stdout:

ActiveRecord::Base.uncached do
  rel = Post.where(status: "published")
  puts "count  => #{rel.count}"
  puts "size   => #{rel.size}"
  puts "length => #{rel.length}"
  puts "count  => #{rel.count}"
  puts "size   => #{rel.size}"
  puts "length => #{rel.length}"
end

--- count, size, length on a relation that has not been loaded
  Post Count (2.6ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."status" = 'published' /*application='Dbq'*/
count  => 10000
  Post Count (1.3ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."status" = 'published' /*application='Dbq'*/
size   => 10000
  Post Load (12.6ms)  SELECT "posts".* FROM "posts" WHERE "posts"."status" = 'published' /*application='Dbq'*/
length => 10000

--- the same three, now that it is loaded
  Post Count (1.5ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."status" = 'published' /*application='Dbq'*/
count  => 10000
size   => 10000
length => 10000

count queries every time, including when the records are already sitting in memory. size asks the database only while the relation is unloaded and answers from the array afterwards. length loads the whole relation and counts the array, always. In a view that has already rendered the collection, @posts.count is a second round trip for a number you are holding; in a controller that only needs the number, @posts.length is 10,000 objects you will throw away.

The existence family splits the same way, and worse:

--- exists?
  Post Exists? (1.7ms)  SELECT 1 AS one FROM "posts" WHERE "posts"."status" = 'published' LIMIT 1
--- any?
  Post Exists? (0.3ms)  SELECT 1 AS one FROM "posts" WHERE "posts"."status" = 'published' LIMIT 1
--- present?
  Post Load (13.5ms)  SELECT "posts".* FROM "posts" WHERE "posts"."status" = 'published'
--- empty?
  Post Exists? (0.4ms)  SELECT 1 AS one FROM "posts" WHERE "posts"."status" = 'published' LIMIT 1

present? is the outlier because nothing overrides it. Relation defines blank? at active_record/relation.rb:1294 as records.blank?, Object#present? is !blank?, and so if @posts.present? in a view loads every matching row to decide whether to render a heading. any? is the same question for 0.3 ms.

One more pair, because the SQL differs in a way nobody expects until they read it. find_by sends no ORDER BY:

--- find_by
  Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."status" = 'published' LIMIT 1
--- where.first
  Post Load (0.7ms)  SELECT "posts".* FROM "posts" WHERE "posts"."status" = 'published' ORDER BY "posts"."id" ASC LIMIT 1

So find_by returns whatever PostgreSQL hands back first, which on a heap table is not a stable choice, and first pays for a sort to be deterministic. Neither is wrong. They are different queries with the same shape in Ruby.

Reading the plan, and the object that hides it

explain on a relation returns a proxy, and printing the proxy prints the proxy.

puts Post.where(status: "published").limit(10).explain

#<ActiveRecord::Relation::ExplainProxy:0x00000001223f08e0>

The plan lives in inspect. In a console that is automatic; in a script, or anywhere you reached for puts, it is not, and the first time it happens you assume explain is broken.

puts Post.where(status: "published").limit(10).explain(:analyze, :buffers).inspect

EXPLAIN (ANALYZE, BUFFERS) SELECT "posts".* FROM "posts" WHERE "posts"."status" = 'published' LIMIT 10 /*application='Dbq'*/
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.69 rows=10 width=54) (actual time=0.008..0.014 rows=10 loops=1)
   Buffers: shared hit=2
   ->  Seq Scan on posts  (cost=0.00..691.00 rows=10000 width=54) (actual time=0.007..0.011 rows=10 loops=1)
         Filter: ((status)::text = 'published'::text)
         Rows Removed by Filter: 18
         Buffers: shared hit=2
 Planning Time: 0.061 ms
 Execution Time: 0.023 ms
(8 rows)

status is indexed and the planner ignored the index, because a third of the table matches it and a sequential scan is cheaper than 10,000 heap lookups. That is the useful thing about running explain on real row counts rather than on a fixture: an index you added because the column is in a WHERE clause is not an index the planner will use.

The comment at the end of your development queries costs you prepared statements

/*application='Dbq'*/ on the end of every line above is ActiveRecord::QueryLogs, switched on by the single line rails new writes into config/environments/development.rb:

config.active_record.query_log_tags_enabled = true

Turning it on has a second effect that the comment above it does not mention. In active_record/railtie.rb, inside the same if, at line 402:

ActiveRecord.disable_prepared_statements = true

Query log tags have to be appended to the SQL string, and a prepared statement is keyed by that string, so tagging every query with the controller and action that issued it would mint a new prepared statement per call site and blow out pg_prepared_statements. Rails resolves that by turning prepared statements off entirely. Printed from the same scratch app in each environment:

development  tags=true prepared=false
test         tags=false prepared=true
production   tags=false prepared=true

And the server side agrees. With tags on, SELECT count(*) FROM pg_prepared_statements stays at 0 after a query; flipping query_log_tags_enabled to false in development.rb and running the same script again gives 1.

Mostly this does not matter, because it is development. It matters twice. Any local timing you take is measuring an unprepared statement and production will not be measuring the same thing. And, as the next-but-one section shows, prepared statements are the thing standing between a raw select string and a second SQL statement.

Two identical queries in one request are one query

The Rails query cache is per request and per connection, on by default, and invisible until you read the log. A controller that asks the same question twice:

class ProbesController < ActionController::Base
  def show
    a = Post.where(status: "published").count
    b = Post.where(status: "published").count
    render plain: "#{a} #{b}\n"
  end
end

Started GET "/probe" for ::1 at 2026-09-27 10:13:03 +0200
Processing by ProbesController#show as */*
  Post Count (2.3ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."status" = 'published' /*action='show',application='Dbq',controller='probes'*/
  CACHE Post Count (0.0ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."status" = 'published'
Completed 200 OK in 32ms (Views: 2.5ms | ActiveRecord: 10.4ms (2 queries, 1 cached) | GC: 0.0ms)

2 queries, 1 cached on the Completed line is the number to read. The next request re-queries: the cache is torn down at the request boundary, so this buys nothing across requests and everything within one. It is also on inside bin/rails runner, which is why a script that seems to be hammering the database may not be.

What it saves is the round trip, not the work Active Record does on the way back. 1,000 identical single-row reads in one process, Post.where(id: id).first each time, Ruby 4.0.5 on an M2 Max against PostgreSQL on localhost:

rails 8.1.3.1, 1000 identical single-row reads
uncached: 607.5 ms total, 608 us each
cached:   296.9 ms total, 297 us each

Across four runs the uncached figure moved between 551 and 1123 microseconds per read and the cached figure between 297 and 332. So the cache halves it and the remaining 300 microseconds is instantiation: the result set is memoised, the Post object is built fresh every time. If you are calling the same query in a loop, the query cache is not the fix.

The trap is what clears it. A write clears the cache for that connection, and nothing else does. Another process is not another connection you control:

Post.where(id: id).pluck(:views)          # queries
Post.where(id: id).pluck(:views)          # CACHE
system(%q{psql -p 15432 -d dbq_development -c "UPDATE posts SET views = 999999 WHERE id = 1"})
Post.where(id: id).pluck(:views)          # CACHE, still
Post.where(id: id).update_all(updated_at: Time.current)
Post.where(id: id).pluck(:views)          # queries

  Post Pluck (0.2ms)  SELECT "posts"."views" FROM "posts" WHERE "posts"."id" = 1 /*application='Dbq'*/
  CACHE Post Pluck (0.0ms)  SELECT "posts"."views" FROM "posts" WHERE "posts"."id" = 1

--- another process updates the row
--- third read, same request
  CACHE Post Pluck (0.0ms)  SELECT "posts"."views" FROM "posts" WHERE "posts"."id" = 1
views => [0]
--- after one write of our own
  Post Update All (0.8ms)  UPDATE "posts" SET "updated_at" = '2026-09-27 08:05:29.740361' WHERE "posts"."id" = 1
  Post Pluck (0.3ms)  SELECT "posts"."views" FROM "posts" WHERE "posts"."id" = 1 /*application='Dbq'*/
views => [999999]

Inside one request, a row somebody else changed stays at its old value until you write something. In a request that renders a page, that is correct and desirable. In a job that polls, or in a request that waits on an external callback and re-reads, it is a bug that only appears under two processes, and ActiveRecord::Base.uncached { } around the re-read is the whole fix.

When Active Record will not say it

Window functions, recursive CTEs, DISTINCT ON, a LATERAL join: the relation API has no vocabulary for them, and the answer is to write SQL. There are four doors and they return four different things.

sql = "SELECT id, title FROM posts WHERE status = 'published' ORDER BY id LIMIT 2"

Post.find_by_sql(sql)                    # Array of Post
ActiveRecord::Base.connection.select_all(sql)    # ActiveRecord::Result
ActiveRecord::Base.connection.select_one(sql)    # Hash
ActiveRecord::Base.connection.select_value("SELECT count(*) FROM posts")  # scalar

find_by_sql  -> Array of Post
  first      -> #<Post id: 2, title: "post 1">
  .views     -> ActiveModel::MissingAttributeError: missing attribute 'views' for Post
select_all   -> ActiveRecord::Result
  columns    -> ["id", "title"]
  to_a.first -> {"id" => 2, "title" => "post 1"}
select_one   -> {"id" => 2, "title" => "post 1"}
select_value -> 30000
select_values-> [1, 2, 3]

find_by_sql gives you real models, which is the attraction and the cost: the instances carry only the columns you selected, and every other attribute raises ActiveModel::MissingAttributeError rather than returning nil. That error surfaces in a partial three files away from the SQL. If the result is not going to be saved, select_all is the honest choice, and its values are already type cast, so views comes back as an Integer and not as "0".

Binding values is not optional in any of the four. All three of these forms ran:

Post.find_by_sql(["SELECT id FROM posts WHERE status = ? ORDER BY id LIMIT 2", status])
Post.find_by_sql(["SELECT id FROM posts WHERE status = :s ORDER BY id LIMIT 2", { s: status }])

binds = [ActiveRecord::Relation::QueryAttribute.new("status", status, ActiveRecord::Type::String.new)]
ActiveRecord::Base.connection.exec_query(
  "SELECT count(*) AS n FROM posts WHERE status = $1", "counting", binds
)

[{"n" => 10000}]

The exec_query form is verbose and it is the one that keeps a prepared statement and the single-statement guarantee. Post.sanitize_sql_array(["status = ? AND views > ?", evil, 10]) is there when you need the string rather than the execution, and it returns status = 'published'' OR ''1''=''1' AND views > 10.

Which of those arguments Rails sanitizes for you

Rails guards some raw-SQL arguments and not others, and the line between them is not where people assume it is. disallow_raw_sql!, defined at active_record/sanitization.rb:185, has exactly four callers in activerecord 8.1.3.1:

• relation/query_methods.rb:2093, in preprocess_order_args, so order and reorder
• relation/query_methods.rb:718, in in_order_of
• relation/calculations.rb:317, in pluck
• sanitization.rb:86, in sanitize_sql_for_order

insert_all.rb has a method of the same name at line 212 for on_duplicate and returning, which is a different method doing a different check.

select, group, having and the string form of where are not on that list. Passing the same string to each:

order: ActiveRecord::UnknownAttributeReference: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "views; DROP TABLE posts".
pluck: ActiveRecord::UnknownAttributeReference: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "views; DROP TABLE posts".
select: ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "FROM"
group: ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "AS"

The two syntax errors are luck, not protection. Post.select("views; DROP TABLE posts").to_sql is SELECT views; DROP TABLE posts FROM "posts", and the injected statement failed only because Active Record glued a FROM onto the end of it. A payload that closes the statement itself and comments out the tail does not have that problem. With a canary table to watch:

conn.execute("CREATE TABLE canaries (id int)")
Post.select("id FROM posts; DROP TABLE canaries --").to_a

prepared_statements: false
canaries before: 1
sql sent: SELECT id FROM posts; DROP TABLE canaries -- FROM "posts"
returned 0 rows, no error
canaries after (uncached): 0

The table is gone and nothing raised. The reason is the third branch of perform_query in active_record/connection_adapters/postgresql/database_statements.rb:142:

result = if prepare
  raw_connection.exec_prepared(stmt_key, type_casted_binds)
elsif binds.nil? || binds.empty?
  raw_connection.async_exec(sql)
else
  raw_connection.exec_params(sql, type_casted_binds)
end

exec_prepared and exec_params both refuse multiple commands. async_exec is libpq's simple query protocol and runs as many statements as you send it. A relation with no bind parameters takes that branch, in every environment. Add one bound condition and the same payload is refused, but only where prepared statements are on:

### development
prepared_statements: false, canaries: 1
sql: SELECT id FROM posts WHERE status = 'published'; DROP TABLE canaries -- FROM "posts" WHERE "posts"."status" = 'published'
rows: 0
canaries after: 0

### test
prepared_statements: true, canaries: 1
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  cannot insert multiple commands into a prepared statement
canaries after: 1

So the development default that turns prepared statements off also removes the last thing stopping a select string from running DDL. None of this is a Rails vulnerability: select is documented as taking raw SQL, and the fix has always been not to pass user input to it. The position worth taking is narrower than "sanitize your inputs". It is that order and pluck will stop you and select and group will not, so the reviewer's rule is that any string reaching select, group or having must be a literal in the source file or wrapped in Arel.sql by somebody who checked it. What would change that: a disallow_raw_sql! call added to select and group, which would break a lot of legitimate code and is presumably why it is not there.

Even without a second statement, a single select is enough to read anything the database user can read. Post.select("id, (SELECT name FROM authors ORDER BY id LIMIT 1) AS leaked").order(:id).first returns a Post whose leaked attribute is "author 0".

The check that lied about its own result

The canary experiment above reported, on its first run, that the table was still there and that Rails had blocked the injection. That was the wrong answer, and the script produced it honestly: it read SELECT to_regclass('canaries') before the payload and again after, in the same process, with the query cache on. The DROP TABLE went through async_exec and is not an Active Record write, so it cleared nothing, and the second read was a cache hit on the first answer.

The same check wrapped in conn.uncached { } gives the opposite result. Two sections of this page were nearly written backwards by the mechanism the section above them describes, which is the argument for putting uncached around every read in a script whose job is to verify something.

What this page does not cover

Not covered: includes, preload and eager_load, which are their own decision and have a page of their own; how relations compose and where merge quietly drops half your WHERE clause, which is in what a Rails scope actually returns; find_each and in_batches, where the interesting part is cursor stability rather than when the query runs; load_async and the thread pool behind it; Arel, since everything here is about the clause list Active Record assembles before Arel is asked for a node; and every adapter other than PostgreSQL, which matters more than usual in the last two sections because async_exec is a libpq call and the MySQL and SQLite adapters make the multiple-statement decision somewhere else.

The numbers are one laptop, one connection, one run, on a cold-ish page cache with 30,000 rows in a table PostgreSQL is holding entirely in shared buffers. Treat the ratios as the finding and the absolute microseconds as a footnote.