electric-sql / electric-sql/electric

Add indexing support for additional JSONB operators

Open
#3,714 0 comments 1 reaction 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.