perf(electric-db-collection): compile array-column membership to @> (GIN-eligible) #1583
main fromrefs/pull/1583/head·opened perf(electric-db-collection): compile array-column membership to @> (GIN-eligible)#
Summary#
inArray(value, arrayColumn) currently compiles to value = ANY(arrayColumn). That's correct, but Postgres cannot use a GIN index for scalar = ANY(array_column) — only for the containment operator array_column @> ARRAY[scalar]. The two expressions are logically equivalent, so this PR emits the containment form when the array operand is a column, letting the planner index-seek instead of scanning.
Membership in a literal value list (inArray(column, [a, b, c])) is unchanged — it still compiles to = ANY, which is correct there.
Why it matters#
For a low-cardinality value in an array column, the = ANY form is pathological in the common ORDER BY ... LIMIT N shape: the planner walks an ordering index and filters row-by-row, and because few rows match, it can never fill the LIMIT — so it scans the entire table.
Concrete case from a ~2M-row table with a tags text[] column and a GIN index on it:
-- before: 'ipo' = ANY(tags) → cannot use the GIN index → full index walk (~2M rows)
-- after: tags @> ARRAY['ipo'] → Bitmap Index Scan on the GIN index → ~7 rows
SELECT ... FROM news_item
WHERE tags @> ARRAY['ipo']
ORDER BY first_created DESC LIMIT 25;
A tag matching 7 of 2,000,000 rows went from a full-table scan to an index seek. Dense values (e.g. a tag on 25% of rows) are unaffected — the planner still picks the ordering-index walk, now adaptively, because @> exposes the GIN option rather than removing it.
Change#
In compileFunction's in branch, when the RHS is a column reference (args[1].type === 'ref'), emit ${rhs} @> ARRAY[${lhs}]; otherwise keep ${lhs} = ANY(${rhs}).
if (name === `in`) {
if (args[1]?.type === `ref`) {
return `${rhs} @> ARRAY[${lhs}]`
}
return `${lhs} ${opName}(${rhs})`
}
The two shapes are distinguishable without column type info:
inArray(value, arrayColumn)→args[1]is aref→ containment.inArray(column, [literal, list])→args[1]is aval→= ANY.
Tests#
Added an in operator describe block in sql-compiler.test.ts covering: literal value list → = ANY, array column → @>, and a mixed compound (@> AND = ANY) to confirm both paths coexist with correct param indices.
Notes#
- Result rows are identical; this is purely an index-eligibility (and therefore planner) improvement.
- No public API change — existing
inArraycallers benefit transparently.
Summary by CodeRabbit#
Performance
- Array membership checks now compile into a Postgres-friendly form, enabling index-backed lookups and improving query performance.
Tests
- Added comprehensive tests for array membership compilation, parameter binding/sequencing, combined cases, and edge conditions.
Bug Fix
- Membership operator now validates null/undefined inputs and rejects invalid array-on-array membership.
Documentation
- Updated release metadata to reflect the change.
- 1
Actionable comments posted: 2
🤖 Prompt for all review comments with AI agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. Inline comments: In `@packages/electric-db-collection/tests/sql-compiler.test.ts`: - Around line 159-189: Add tests to cover corner cases for the new in operator behavior in the sql-compiler.test.ts suite: add cases for empty-array (e.g., val([]) against ref status or roles), single-element-array (val([x]) and ARRAY with single value) and null/undefined operands (val(null), val(undefined), and ref that can be null/undefined) using compileSQL and func('in', [...]) to assert the compiled where clause and params match the intended behavior; include both permutations where the literal/array is the left operand and where the column ref is the left operand (e.g., func('in', [ref('status'), val([])]), func('in', [val('admin'), ref('roles')]) etc.) so the tests validate EMPTY, single-element, and null/undefined handling for both "= ANY" and "@> ARRAY" code paths.🪄 Autofix (Beta)
Fix all unresolved CodeRabbit comments on this PR:
- Push a commit to this branch (recommended)
- Create a new PR with the fixes
ℹ️ Review info
⚙️ Run configuration
Configuration used: defaults
Review profile: CHILL
Plan: Pro
Run ID:
2a9cbd23-926b-4f0b-bf80-e1a1bec0aba2📥 Commits
Reviewing files that changed from the base of the PR and between 4d1abded3e5bdec16457deb7486e28b6a228c670 and 6903ad04d2b39554dfbc71427e2160b261413940.
📒 Files selected for processing (3)
.changeset/array-column-containment.mdpackages/electric-db-collection/src/sql-compiler.tspackages/electric-db-collection/tests/sql-compiler.test.ts
Resolvedby viktor89(GitHub profile, opens in a new tab)@@ -356,7 +356,12 @@356356 }357357 }358358359- // Special case for = ANY operator which needs parentheses around the array parameter360359 if (name === `in`) {360+ // `value = ANY(arrayColumn)` and `arrayColumn @> ARRAY[value]` are361+ // equivalent, but only containment can use a GIN index. Emit @> when the362+ // array operand is a column; keep = ANY for a literal value list.363+ if (args[1]?.type === `ref`) {364+ return `${rhs} @> ARRAY[${lhs}]`365+ }361366 return `${lhs} ${opName}(${rhs})`362367 }⚠️ Potential issue | 🟠 Major | ⚡ Quick win
Guard
inagainst null/undefined operands to prevent broken parameter binding.At Line 359-367,
in(val(undefined), ref('roles'))compiles to"roles" @> ARRAY[$1], but Line 65 drops undefined params, so$1is unbound at execution time. Add explicit null/undefined validation forinoperands (same policy used for other comparison-like operators) before compiling args.Proposed fix
function compileFunction( exp: IR.Func<unknown>, params: Array<unknown> = [], encodeColumnName?: ColumnEncoder, ): string { const { name, args } = exp const opName = getOpName(name) @@ if (isComparisonOp(name)) { @@ } + + if (name === `in`) { + const nullArgIndex = args.findIndex((arg: IR.BasicExpression) => isNullValue(arg)) + if (nullArgIndex !== -1) { + throw new Error( + `Cannot use null/undefined value with '${name}' operator. ` + + `Filter out null values before building the query.`, + ) + } + }@@ -158,0 +169,21 @@169+ const result = compileSQL({170+ where: func(`in`, [val(`admin`), ref(`roles`)]),171+ })172+ expect(result.where).toBe(`"roles" @> ARRAY[$1]`)173+ expect(result.params).toEqual({ '1': `admin` })174+ })175+176+ it(`should compile array-column membership inside AND`, () => {177+ const result = compileSQL({178+ where: func(`and`, [179+ func(`in`, [val(`admin`), ref(`roles`)]),180+ func(`in`, [ref(`status`), val([`active`, `pending`])]),181+ ]),182+ })183+ expect(result.where).toBe(`"roles" @> ARRAY[$1] AND "status" = ANY($2)`)184+ expect(result.params).toEqual({185+ '1': `admin`,186+ '2': `{"active","pending"}`,187+ })188+ })189+ })🛠️ Refactor suggestion | 🟠 Major | ⚡ Quick win
Add required corner-case coverage for the new
inoperator behavior.This suite should also include empty-array, single-element-array, and null/undefined operand cases for
into satisfy required test coverage for changed logic and prevent regressions.As per coding guidelines, "
**/*.test.{ts,tsx,js}: ... Test corner cases including: empty arrays/sets, single-element collections, undefined vs null values ...".Suggested additional tests
describe(`in operator`, () => { + it(`should compile membership in an empty literal value list`, () => { + const result = compileSQL({ + where: func(`in`, [ref(`status`), val([])]), + }) + expect(result.where).toBe(`"status" = ANY($1)`) + expect(result.params).toEqual({ '1': `{}` }) + }) + + it(`should compile membership in a single-element literal value list`, () => { + const result = compileSQL({ + where: func(`in`, [ref(`status`), val([`active`])]), + }) + expect(result.where).toBe(`"status" = ANY($1)`) + expect(result.params).toEqual({ '1': `{"active"}` }) + }) + + it(`should throw for in with undefined/null operand`, () => { + expect(() => + compileSQL({ where: func(`in`, [val(undefined), ref(`roles`)]) }), + ).toThrow(`Cannot use null/undefined value with 'in' operator`) + expect(() => + compileSQL({ where: func(`in`, [val(null), ref(`roles`)]) }), + ).toThrow(`Cannot use null/undefined value with 'in' operator`) + })📝 Committable suggestion
‼️ IMPORTANT Carefully review the code before committing. Ensure that it accurately replaces the highlighted code, contains no missing lines, and has no issues with indentation. Thoroughly test & benchmark the code to ensure it meets the requirements.
describe(`in operator`, () => { it(`should compile membership in an empty literal value list`, () => { const result = compileSQL({ where: func(`in`, [ref(`status`), val([])]), }) expect(result.where).toBe(`"status" = ANY($1)`) expect(result.params).toEqual({ '1': `{}` }) }) it(`should compile membership in a single-element literal value list`, () => { const result = compileSQL({ where: func(`in`, [ref(`status`), val([`active`])]), }) expect(result.where).toBe(`"status" = ANY($1)`) expect(result.params).toEqual({ '1': `{"active"}` }) }) it(`should throw for in with undefined/null operand`, () => { expect(() => compileSQL({ where: func(`in`, [val(undefined), ref(`roles`)]) }), ).toThrow(`Cannot use null/undefined value with 'in' operator`) expect(() => compileSQL({ where: func(`in`, [val(null), ref(`roles`)]) }), ).toThrow(`Cannot use null/undefined value with 'in' operator`) }) it(`should compile membership in a literal value list as = ANY`, () => { const result = compileSQL({ where: func(`in`, [ref(`status`), val([`active`, `pending`])]), }) expect(result.where).toBe(`"status" = ANY($1)`) expect(result.params).toEqual({ '1': `{"active","pending"}` }) }) it(`should compile membership in an array column as @> (GIN-eligible)`, () => { const result = compileSQL({ where: func(`in`, [val(`admin`), ref(`roles`)]), }) expect(result.where).toBe(`"roles" @> ARRAY[$1]`) expect(result.params).toEqual({ '1': `admin` }) }) it(`should compile array-column membership inside AND`, () => { const result = compileSQL({ where: func(`and`, [ func(`in`, [val(`admin`), ref(`roles`)]), func(`in`, [ref(`status`), val([`active`, `pending`])]), ]), }) expect(result.where).toBe(`"roles" @> ARRAY[$1] AND "status" = ANY($2)`) expect(result.params).toEqual({ '1': `admin`, '2': `{"active","pending"}`, }) }) })🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. In `@packages/electric-db-collection/tests/sql-compiler.test.ts` around lines 159 - 189, Add tests to cover corner cases for the new in operator behavior in the sql-compiler.test.ts suite: add cases for empty-array (e.g., val([]) against ref status or roles), single-element-array (val([x]) and ARRAY with single value) and null/undefined operands (val(null), val(undefined), and ref that can be null/undefined) using compileSQL and func('in', [...]) to assert the compiled where clause and params match the intended behavior; include both permutations where the literal/array is the left operand and where the column ref is the left operand (e.g., func('in', [ref('status'), val([])]), func('in', [val('admin'), ref('roles')]) etc.) so the tests validate EMPTY, single-element, and null/undefined handling for both "= ANY" and "@> ARRAY" code paths.Source: Coding guidelines
✅ Addressed in commit 18ea875
Caution
Some comments are outside the diff and can’t be posted inline due to platform limitations.
⚠️ Outside diff range comments (1)
packages/electric-db-collection/src/sql-compiler.ts (1)
359-367: ⚠️ Potential issue | 🟠 MajorTighten/guard
inArray(value, arrayColumn)sovaluecan’t be array-valued whenarrayColumnis a columnrefinArrayis declared asinArray(value: ExpressionLike, array: ExpressionLike)and just constructsFunc('in', ...), so the public types don’t prevent passing an arrayval(...)as the first argument. Inpackages/electric-db-collection/src/sql-compiler.ts, theinbranch forargs[1]?.type === 'ref'emitsrhs @> ARRAY[${lhs}], which wraps an array-valuedlhsinto a nested/single-element array and can break the intended “contains these elements” semantics. Add a type refinement (scalar-onlyvaluewhenarrayis a column ref) or a runtime check that rejects an array-valuedargs[0]in this scenario.🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. In `@packages/electric-db-collection/src/sql-compiler.ts` around lines 359 - 367, The `inArray(value, array)` public API allows an array-valued `value` but the SQL emitter in sql-compiler.ts (the `if (name === 'in')` branch) assumes a scalar left-hand side when `args[1]?.type === 'ref'` and emits `${rhs} @> ARRAY[${lhs}]`, which breaks semantics for array-valued `lhs`; update the `in` handling so it validates/refines the first arg: in the `in` branch (same place where `args[1]?.type === 'ref'` is checked) add a runtime guard that detects array-valued `args[0]` (e.g., `args[0].type === 'val'` with an array payload or any internal array marker) and either throw a clear error or coerce to the correct SQL form, or tighten the public `inArray` typing so callers cannot pass array `value` when `array` is a column ref; reference the inArray factory and the `name === 'in'` emission logic to implement the check and fail fast if an array-valued `args[0]` is used with a column `args[1]`.🧹 Nitpick comments (1)
packages/electric-db-collection/tests/sql-compiler.test.ts (1)
206-216: 💤 Low valueConsider testing null/undefined in the RHS position for completeness.
The current tests verify that
func('in', [val(null), ref('roles')])andfunc('in', [val(undefined), ref('roles')])throw. The validation logic insql-compiler.ts(lines 181-183) usesfindIndexto catch null/undefined in any argument position, sofunc('in', [ref('status'), val(null)])andfunc('in', [ref('status'), val(undefined)])will also throw. Adding explicit tests for those cases would provide symmetric coverage and guard against future refactoring that might break the any-position behavior.Optional additional test cases
+ it(`should throw for null in RHS position`, () => { + expect(() => + compileSQL({ where: func(`in`, [ref(`status`), val(null)]) }), + ).toThrow(`Cannot use null/undefined value with 'in' operator`) + }) + + it(`should throw for undefined in RHS position`, () => { + expect(() => + compileSQL({ where: func(`in`, [ref(`status`), val(undefined)]) }), + ).toThrow(`Cannot use null/undefined value with 'in' operator`) + })🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. In `@packages/electric-db-collection/tests/sql-compiler.test.ts` around lines 206 - 216, Tests only check null/undefined in the LHS of func('in') but the compiler's validation (compileSQL) rejects null/undefined in any argument position; add symmetric tests asserting compileSQL throws for func('in', [ref('status'), val(null)]) and func('in', [ref('status'), val(undefined)]) so that compileSQL's behavior is covered for RHS operands as well.🤖 Prompt for all review comments with AI agents
Verify each finding against current code. Fix only still-valid issues, skip the rest with a brief reason, keep changes minimal, and validate. Outside diff comments: In `@packages/electric-db-collection/src/sql-compiler.ts`: - Around line 359-367: The `inArray(value, array)` public API allows an array-valued `value` but the SQL emitter in sql-compiler.ts (the `if (name === 'in')` branch) assumes a scalar left-hand side when `args[1]?.type === 'ref'` and emits `${rhs} @> ARRAY[${lhs}]`, which breaks semantics for array-valued `lhs`; update the `in` handling so it validates/refines the first arg: in the `in` branch (same place where `args[1]?.type === 'ref'` is checked) add a runtime guard that detects array-valued `args[0]` (e.g., `args[0].type === 'val'` with an array payload or any internal array marker) and either throw a clear error or coerce to the correct SQL form, or tighten the public `inArray` typing so callers cannot pass array `value` when `array` is a column ref; reference the inArray factory and the `name === 'in'` emission logic to implement the check and fail fast if an array-valued `args[0]` is used with a column `args[1]`. --- Nitpick comments: In `@packages/electric-db-collection/tests/sql-compiler.test.ts`: - Around line 206-216: Tests only check null/undefined in the LHS of func('in') but the compiler's validation (compileSQL) rejects null/undefined in any argument position; add symmetric tests asserting compileSQL throws for func('in', [ref('status'), val(null)]) and func('in', [ref('status'), val(undefined)]) so that compileSQL's behavior is covered for RHS operands as well.
ℹ️ Review info
⚙️ Run configuration
Configuration used: defaults
Review profile: CHILL
Plan: Pro
Run ID:
a82cf245-4531-485f-b7bd-89020fc496f7📥 Commits
Reviewing files that changed from the base of the PR and between 6903ad04d2b39554dfbc71427e2160b261413940 and 18ea87554e022b044022189d1a4adbd12979db75.
📒 Files selected for processing (2)
packages/electric-db-collection/src/sql-compiler.tspackages/electric-db-collection/tests/sql-compiler.test.ts
KyleAMathews(GitHub profile, opens in a new tab) closed this
Sign in to join this conversation.
No actionable comments were generated in the recent review. 🎉
ℹ️ Recent review info
⚙️ Run configuration
Configuration used: defaults
Review profile: CHILL
Plan: Pro
Run ID:
47d9042b-3ad8-40fe-ab82-237e880fc30f📥 Commits
Reviewing files that changed from the base of the PR and between 18ea87554e022b044022189d1a4adbd12979db75 and b4ef909fdd7e7fca19d3ee88cfbe2c2a7f518ce6.
📒 Files selected for processing (2)
packages/electric-db-collection/src/sql-compiler.tspackages/electric-db-collection/tests/sql-compiler.test.ts🚧 Files skipped from review as they are similar to previous changes (2)
📝 Walkthrough
Walkthrough#
The SQL compiler now emits
arrayColumn @> ARRAY[value]wheninchecks membership against an array column reference; literal-list membership still compiles to= ANY(...). Tests and a changeset note were added covering both forms and error cases.Changes#
Array column containment optimization
inwith array-columnpackages/electric-db-collection/src/sql-compiler.tscompileFunctionspecial-casesinwhen the RHS is a columnrefto emitarrayCol @> ARRAY[...];inis added toisComparisonOpso comparison-style null/undefined validation applies and array-left-literal usage against an array column is rejected.packages/electric-db-collection/tests/sql-compiler.test.ts,.changeset/array-column-containment.mdin operatortests for literal-list (= ANY) and array-column (@> ARRAY) compilation, param ordering across combined expressions, empty/single lists, and null/undefined/invalid-array-left errors; changeset updated to document the containment compilation behavior.Estimated code review effort#
🎯 3 (Moderate) | ⏱️ ~20 minutes
Poem#
🚥 Pre-merge checks | ✅ 4 | ❌ 1
❌ Failed checks (1 warning)#
✅ Passed checks (4 passed)
✨ Finishing Touches
🧪 Generate unit tests (beta)
Warning
There were issues while running some tools. Please review the errors and either fix the tool's configuration or disable the tool if it's a critical failure.
🔧 ESLint
ESLint install timed out. The project may have too many dependencies for the sandbox.
Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.
❤️ Share