AWS RDS Proxy Connection Pinning: prepared_statements: false inlines all bind values via SubstituteBinds

Steps to reproduce

We use AWS RDS Proxy for our AWS RDS Postgres database, and we're experiencing connection pinning from queries larger than 16kb in size. Under prepared_statements: false, every bind value gets inlined into the SQL text by Arel::Collectors::SubstituteBinds before the statement leaves Rails. For where(id: array), that means every element of the array lands in the SQL string and the statement grows linearly with the array.

I can rewrite queries so that they use find_by_sql with a manually-built QueryAttribute, but that is bypassing ActiveRecord::Relation and I have to find each call site and edit them. I would like to have the kind of solution that fixes it everywhere. I considered applying a monkeypatch (and I have one that I've cooked up with Claude & Codex), but my team members who are better at Ruby/Rails than I am have gently suggested that I should take this issue here.

Claude made me this script to demonstrate the problem (but it's easy to reproduce in any scenario where the size of the IN() is large). The script below runs with ruby script.rb. It needs a reachable Postgres server; libpq's standard environment variables (PGHOST, PGUSER, PGDATABASE, etc.) handle the rest of the connection.

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"
  gem "rails"
  gem "pg"
end

require "active_record"

ActiveRecord::Base.establish_connection(
  adapter:             "postgresql",
  database:            ENV.fetch("PGDATABASE", "postgres"),
  prepared_statements: false,
)

ActiveRecord::Schema.define { create_table(:posts, force: true) }
class Post < ActiveRecord::Base; end

ids = (1..5_000).to_a
sql = Post.where(id: ids).to_sql

puts "sql bytes:                  #{sql.bytesize}"
puts "last id (#{ids.last}) inlined:  #{sql.include?(", #{ids.last})")}"
# sql bytes:                  ~31000
# last id (5000) inlined:     true
#
# 5,000 ids is already past the 16 KB statement-size threshold that RDS Proxy
# uses to decide whether to pin a backend connection.

Expected behaviour

The hope is that we no longer generate mega-sized SQL statements that cause RDS connection pinning. I can imagine there are two paths forward here: extension and default behaviour change.

1. Add something to the API to support this. A way to mark a specific where bind for wire-protocol delivery — something like Model.where(id: Arel.array_bind(ids)) — that produces WHERE id = ANY($1::bigint[]) regardless of the prepared_statements setting.

2. Default behaviour change on PostgreSQL. Under prepared_statements: false, where(id: array) could emit ANY($1::bigint[]) via exec_params instead of an inlined IN (...).

I confess to not being an expert on rails (neither as a user nor a contributor) so I do not presume to say which is the right one.

Actual behaviour

Arel::Collectors::SubstituteBinds inlines every bind into the SQL text. Post.where(id: ids).to_sql produces:

SELECT "posts".* FROM "posts" WHERE "posts"."id" IN (1, 2, ..., N)

This produces a very long SQL string which results in connection pinning.

Why this matters

RDS Proxy multiplexes a small pool of backend Postgres connections across many app processes. It pins the backend connection for the lifetime of the session whenever any single statement's SQL text exceeds 16 KB. A pinned connection is dedicated to one app process and stops multiplexing from happening. If this happens enough, we can run out of connections.

System configuration

Rails version: 8.0.5

Ruby version: 3.4.7