pg_any_where

One gem. One bind parameter. Zero plan-cache pollution.

pg_any_where patches ActiveRecord so that where(column: array) emits PostgreSQL's array operators instead of IN (…):

-- Before (standard ActiveRecord)
SELECT * FROM widgets WHERE id IN ($1, $2, $3)

-- After (pg_any_where)
SELECT * FROM widgets WHERE id = ANY($1::integer[])

A 1 000-element array still produces a single bind parameter, keeping your prepared-statement cache sane and pg_stat_statements readable.

---

Why?

PostgreSQL's query planner caches plans keyed on the shape of a prepared statement, not its bind values. A standard IN ($1, $2, …) generates a new cache entry for every distinct array length, causing:

• plan-cache bloat
• pg_stat_statements pollution (thousands of near-identical rows)
• occasional re-planning overhead on busy servers

= ANY($1::type[]) fixes all three: one shape, one plan, cached forever.

Further reading: rails/rails#49388

Empty-array edge cases

Standard ActiveRecord	pg_any_where
WHERE 1=0 (empty IN)	WHERE col = ANY(ARRAY[]::integer[])
WHERE 1=1 (empty NOT IN)	WHERE col != ALL(ARRAY[]::integer[])

The footguns are gone. Empty arrays behave correctly and symmetrically.

---

Installation

Add to your Gemfile:

gem "pg_any_where"

Then run:

bundle install

Requirements

Dependency	Version
Ruby	≥ 3.0
ActiveRecord	≥ 6.1
PostgreSQL adapter (pg)	≥ 1.2

---

Usage

Rails (automatic)

The gem ships a Railtie — no initializer code required. Simply add it to your Gemfile and restart your server. Done.

To verify it is active:

Widget.where(id: [1, 2, 3]).to_sql
# => "SELECT ... WHERE \"widgets\".\"id\" = ANY($1::integer[])"

Non-Rails

require "pg_any_where"

ActiveRecord::Base.establish_connection(ENV["DATABASE_URL"])
PgAnyWhere.patch!   # call once after connecting

---

Configuration

Block-style (recommended)

# config/initializers/pg_any_where.rb
PgAnyWhere.configure do |config|
  # Disable the extension entirely (default: true).
  config.enabled = true

  # Only rewrite arrays with at least N elements; fall back to IN for smaller
  # ones.  Useful if you want IN for 1- or 2-element lists where the planner
  # might prefer an index seek with literal values.  (default: 0)
  config.min_array_size = 0
end

Environment variables

Variable	Default	Description
PG_ANY_WHERE_ENABLED	"true"	Set to "false" to disable
PG_ANY_WHERE_MIN_ARRAY_SIZE	"0"	Minimum array size to rewrite

ENV values act as defaults — an explicit Ruby config call always wins.

---

SQL reference

ActiveRecord	SQL emitted
where(col: [1, 2, 3])	col = ANY($1::integer[])
where(col: [])	col = ANY(ARRAY[]::integer[])
where.not(col: [1, 2])	col != ALL($1::integer[])
where.not(col: [])	col != ALL(ARRAY[]::integer[])
where(str_col: %w[a b])	str_col = ANY($1::character varying[])

---

Compatibility notes

Ransack / raw Arel

Ransack and manual attribute.in([…]) calls build Arel::Nodes::In nodes whose values are already-quoted Arel::Nodes::Node objects. Re-casting those would produce an empty array, so pg_any_where deliberately leaves them unchanged. Your Ransack queries continue to work exactly as before.

min_array_size

When min_array_size > 0, arrays shorter than the threshold fall back to the standard IN / NOT IN behaviour — including the 1=0 / 1=1 edge cases for empty arrays.

---

Contributing

1. Fork the repository.
2. Create a feature branch (git checkout -b feature/my-change).
3. Run the test suite (bundle exec rspec).
4. Run the linter (bundle exec rubocop).
5. Open a pull request.

Running tests locally

createdb pg_any_where_test
DATABASE_URL=postgres://localhost/pg_any_where_test bundle exec rspec

Coverage is enforced at ≥ 95 % via SimpleCov.

---

License

MIT — see LICENSE.txt.