electric-sql / electric-sql/electric
Add indexing support for additional JSONB operators
- Dominant language
- TypeScript
- Stars
- 10.4k
- Forks
- 375
- Avg merge
- 3d 1h
- Merged PRs (30d)
- 18
Description
## Summary
This issue tracks adding Electric-native index support for additional JSONB operators to improve filtering performance when many shapes use JSONB-based where clauses.
## Background
Electric uses its own in-memory indexing system (not PostgreSQL indexes) to efficiently match changes to shapes. Currently, the following operators are indexed:
| Operator | Index Module | Status |
|----------|-------------|--------|
| `=` | `EqualityIndex` | ✅ Implemented |
| `@>` (arrays) | `InclusionIndex` | ✅ Implemented |
| `?` | `KeyExistenceIndex` | ✅ Implemented in #3711 |
Without indexing, operators fall back to per-shape evaluation, which is O(n) where n = number of shapes.
## Proposed Additions
### 1. `?|` (any key exists) and `?&` (all keys exist)
**Pattern:** `data ?| ARRAY['key1', 'key2']` or `data ?& ARRAY['key1', 'key2']`
**Approach:** Extend `KeyExistenceIndex` or create `MultiKeyExistenceIndex`:
- For `?|`: Index by each required key, union results
- For `?&`: Index by key set, intersection-based matching
**Complexity:** Medium
### 2. `@>` for JSONB objects (not just arrays)
**Pattern:** `data @> '{"type": "premium", "active": true}'`
**Current state:** `InclusionIndex` only handles arrays (`when is_list(value)` guard)
**Approach:** Create `JsonbContainmentIndex`:
- Index by flattened key-value pairs from the required object
- Match when all required key-value pairs exist in the record
**Complexity:** Medium-High (need to handle nested structures)
### 3. `->>` with equality (compound expression)
**Pattern:** `(data ->> 'status') = 'active'`
This is the most common JSONB filtering pattern but requires recognizing a compound expression.
**Approach:** Create `JsonbFieldEqualityIndex`:
- Pattern match on `Func{name: "=", args: [Func{name: "->>", ...}, Const{...}]}`
- Index by `{field, json_key, value}` tuple
- Direct O(1) lookup when record arrives
**Complexity:** Medium (requires deeper AST pattern matching)
## Implementation Notes
The indexing framework is extensible:
1. Add pattern to `optimise_where/1` in `where_condition.ex`
2. Add dispatcher clause in `index.ex`
3. Create new index module in `indexes/`
See `KeyExistenceIndex` for a clean example of the pattern.
## Priority
Suggested order based on usage patterns and complexity:
1. `->>` with equality (most common pattern)
2. `@>` for JSONB objects
3. `?|` and `?&`
## Related
- PR #3711: Added JSONB operators and `?` indexing
- `lib/electric/shapes/filter/indexes/` for existing implementations
Contributor guide
Assessment
This issue has not been assessed yet.