RFC: Cross-Table Shape Filtering with Subquery Support

[KyleAMathews]

Summary

Electric currently limits shapes to single-table filtering, preventing developers from expressing common authorization patterns that require cross-table relationships. This RFC proposes extending the shape system to support subqueries in WHERE clauses by automatically creating and maintaining "index shapes" - specialized shapes that materialize subquery results and propagate changes to dependent parent shapes, enabling developers to write natural queries like WHERE author_id IN (SELECT user_id FROM team_members WHERE team_id = 123) without schema denormalization or client-side workarounds.

Background

Electric is a Postgres sync engine that streams data from Postgres into local applications using HTTP-based shape subscriptions. Shapes define subsets of data using a table name, optional WHERE clause, and optional column selection. The Electric sync service maintains a log of operations for each shape and streams changes to subscribed clients in real-time.

Currently, shape WHERE clauses can only reference columns within the target table. This restriction exists because Electric processes the Postgres logical replication stream, which provides row-level changes without join context. When a row changes, Electric can evaluate simple predicates against that row's columns but cannot resolve relationships to other tables without additional infrastructure.

This limitation forces developers to implement authorization patterns through schema denormalization (duplicating user_id or tenant_id across all tables), client-side filtering (over-fetching data and filtering locally), or proxy-based authorization (implementing complex middleware that reconstructs and filters shapes). Each workaround introduces complexity, performance overhead, or security risks.

The previous Electric implementation attempted to solve multi-table filtering using a per-user directed graph to track foreign key relationships. This approach proved memory-intensive and was abandoned before optimization. The graph maintained row-to-row relationships to handle "move-out" scenarios where related rows must be removed when their parent row no longer matches a filter.

Problem

The inability to express cross-table relationships in shape WHERE clauses prevents developers from implementing standard authorization patterns with Electric. This creates three specific problems:

1. Authorization Requires Schema Denormalization

Applications typically implement access control through relational patterns: users belong to teams, content has owners, resources exist within organizational hierarchies. Without subqueries, developers must duplicate authorization fields (user_id, team_id, org_id) across every table they want to sync. This denormalization increases storage costs, complicates migrations, and risks data inconsistency.

2. Middleware Complexity for Relationship-Based Filtering

Developers must implement authorization logic in API proxies or middleware layers, reconstructing SQL joins in application code. This moves database logic into the application layer, creating maintenance burden and potential security vulnerabilities. Each relationship pattern requires custom proxy code that must be kept in sync with schema changes.

3. Performance Degradation from Workaround Overhead

Current workarounds introduce significant performance penalties. Schema denormalization requires updating multiple tables on every authorization change, creating write amplification. Proxy-based filtering adds network hops and computation overhead. Client-side filtering wastes bandwidth by over-fetching. These workarounds make Electric unsuitable for applications with complex authorization models or high performance requirements.

These limitations prevent Electric adoption in multi-tenant SaaS applications, collaborative tools, and any system with relationship-based access control—effectively excluding most production applications.

Proposal

This RFC proposes extending Electric shapes to support subqueries in WHERE clauses by automatically creating and maintaining "index shapes" - specialized shapes that materialize subquery results.

Core Architecture

When a shape is created with a subquery WHERE clause like:

posts WHERE author_id IN (SELECT user_id FROM team_members WHERE team_id = 123)

Electric will:

1. Parse the WHERE clause and identify required subqueries
2. Create an index shape for team_members WHERE team_id = 123 if it doesn't exist
3. Maintain bidirectional dependency tracking between parent and index shapes
4. Propagate changes from index shapes to dependent parent shapes

Index Shape Design

Index shapes are regular Electric shapes with special lifecycle management:

• Identity: Keyed by normalized table + WHERE clause to enable sharing
• Creation: Automatically created when first referenced by a parent shape
• Persistence: Stored like regular shapes with operation logs on disk
• Memory Management: Hot index shapes cached in memory with LRU eviction
• Lifecycle: Reference counted but destruction deferred (no immediate GC)

Change Propagation

When changes arrive via logical replication:

For Index Shape Changes:

1. Update the index shape's operation log
2. Look up dependent parent shapes via reverse index
3. For each parent shape, determine if change requires propagation

For Move-Ins (rows added to index shape):

1. Extract the key values (e.g., new user_id added to team)
2. Query Postgres for related rows in parent table
3. Write INSERT operations to parent shape logs
4. Batch multiple move-ins to minimize Postgres queries

Move-In Consistency Considerations (to investigate during prototyping):

• Buffering Strategy: Should we buffer incoming transactions while querying Postgres, or continue processing and reconcile later?
• Shape State During Move-Ins: Should we pause the parent shape entirely, continue writing to its log but delay the "up-to-date" message, or use some other consistency mechanism?
• Filter Update Timing: When should we update the parent shape's effective WHERE clause - immediately when the index shape changes, or after the Postgres snapshot completes?
• Handling Concurrent Updates: How do we handle updates that arrive for rows that match the new filter but haven't been moved in yet? Options include creating them as inserts, buffering until move-in completes, or other strategies.
• Log Position Correctness: How do we ensure move-in operations are placed at the correct position in the parent shape log relative to concurrent operations?
• Transaction Consistency: Should move-ins be tagged with the transaction ID of the original index shape change to help clients maintain consistency across related shapes?

For Move-Outs (rows removed from index shape):

1. Extract the removed key values from index shape DELETE operation
2. Scan parent shape log with metadata (avoiding JSON deserialization)
3. Write DELETE operations for matching rows
4. Optimize with indexes on foreign key fields in shape log metadata

Implementation Details

Metadata Storage: Add foreign key values to shape log entries as indexed metadata, either in the log file or a separate index file, to enable efficient scanning without JSON parsing.

Index Storage Evolution: Initial implementation will use in-memory structures for index shapes. Future optimization may convert index shapes into embedded SQLite databases for better query performance and memory efficiency, but this will be investigated after validating the core approach.

Query Normalization: Normalize WHERE clauses to canonical form to maximize index shape sharing across parent shapes with equivalent conditions.

Concurrent Creation: Coordinate index shape creation when multiple clients request shapes with the same subquery simultaneously to avoid duplicate work.

Complex WHERE Clauses: Support multiple subqueries by creating multiple index shapes and evaluating the composite conditions using Electric's existing WHERE clause matching logic.

Error Handling: Failed Postgres queries for move-ins follow existing Electric error handling patterns (retry with backoff, shape error states).

Limitations

Phase 1 supports only:

• Single-table subqueries with IN operators
• Simple WHERE conditions in subqueries
• No aggregations or complex functions
• No nested subqueries

Phase 2 will extend to:

• Multiple subqueries with AND/OR combinations
• More complex WHERE patterns
• Multi-level relationships

Definition of Success

The implementation will be considered successful when it meets the following criteria:

Functional Correctness

• Shapes with subquery WHERE clauses produce identical results to equivalent Postgres queries
• Move-in operations correctly sync all related rows when entries are added to index shapes
• Move-out operations correctly remove all dependent rows when entries are removed from index shapes
• Complex WHERE clauses with multiple subqueries evaluate correctly using AND/OR logic
• No data inconsistencies between parent shapes and their index shape dependencies

Performance Targets

• Shape creation time with pre-existing index shapes matches single-table shape creation (within 10% variance)
• Authorization changes propagate to affected shapes within 100ms for 100k+ affected users
• Memory usage remains bounded by number of tables, not number of shapes or users
• Move-out operations via log scanning complete in under 50ms for shapes with <1M entries
• Postgres query batching reduces move-in query load by >80% compared to naive implementation

Developer Experience

• Zero client code changes required - subquery shapes appear identical to single-table shapes
• Clear error messages for unsupported subquery patterns
• Natural SQL WHERE clause syntax without special encoding
• No schema changes required to existing applications
• Works with all existing Electric deployment modes (embedded, Docker, cloud)

Production Readiness

• Handles 10k+ concurrent shapes with subqueries without degradation
• Supports tables with 10M+ rows in both parent and index shapes
• Graceful degradation under memory pressure via LRU cache eviction
• No memory leaks or unbounded growth over extended operation
• Crash recovery rebuilds index shapes from persisted parent shape definitions

Phase 1 Delivery

A working prototype that demonstrates:

• Basic IN (SELECT ...) subquery support
• Correct move-in/move-out behavior
• Memory-based index shape implementation
• Performance within 5x of production targets

This foundation will validate the architecture before optimizing for Phase 2 production requirements.

[thruflo]

One minor observation on the text is that it focused on "authorization":

preventing developers from expressing common authorization patterns
Authorization changes propagate to affected shapes within 100ms for 100k+ affected users

I think authorization is a subset of the primary case here which is filtering across tables / propagating changes to parent shapes.

On the DX, Clear error messages for unsupported subquery patterns is great. I would add the positive corollary:

• Clear documentation for supported subquery patterns

I think it's important we clearly document this, because the natural "it's just familiar SQL"-ness of it carries the potential to confuse users who may assume they can use any Postgres features. It's the same limitations as for existing top level where clauses but I suspect because it looks like complex subquery SQL it's extra important to be clear about what you can and can't use.

[zachdaniel]

Interesting 🤔 Looks very nice, would need some fancy work in Ash to make our query builder use WHERE IN but thats not anyone else's problem 😆

There are many things that come to mind though: in this subquery can you:

• join to additional tables?
• I assume you can't reference the outer result set (otherwise it would be a lateral join), so I can't do things like this:

SELECT * FROM users
WHERE id in (SELECT user_id FROM support_tickets WHERE priority > users.notification_priority_threshold)

• is/might there be in the future support for selecting from these subqueries or is this currently only for filtering?

[icehaunter]

Hey!

We're working on the initial pass for now, where the baseline inner queries are implemented: #3005. It'll likely be available under env-variable feature flag for a while before we're happy with performance & edge cases.

For your questions - all of them are different JOIN types, which all require a lot of materialized intermediate state to make the stream of change make sense. For example, SELECT a.*, b* FROM a INNER JOIN b ON a.x = b.y - on the replication stream from Postgres we see only changes to one table, e.g. a - then to reconstruct the full row for a.*, b.* to send to the client we suddenly need to have all values for b.* around, at a correct point in time (i.e. can't query back to postgres), and properly structured to have a fast lookup for the join. It's very much possible, but it's going to take a bit of work to make good, performant, and scalable

Because there's a lot of complexity here, so we've chosen to start with a simpler subset of the problem - just filtering

[thruflo]

#3005

[balegas]

Cross-table Shapes Now Available as Experimental Feature 🎉

You can now create shapes filtered by subqueries to other tables! This unlocks common patterns for retrieving related data across tables like common authorization patterns.

How to Use

Enable the experimental feature with the environment variable: ELECTRIC_FEATURE_FLAGS=allow_subqueries

Specify shapes with subqueries in the where clause:

{
  table: 'issues',
  columns: 'id,title,description,owner_id,project_id',
  where: 'project_id IN (SELECT project_id FROM projects WHERE status = \'active\')'
}

Current Limitations

While functionally complete, this initial implementation prioritizes getting the feature into your hands over performance optimization. We maintain necessary indexes in memory and handle subquery changes by rebuilding affected shapes. For many use cases with small subquery result sets, this approach may already meet your needs.

Here is a picture of the memory footprint for number of shapes vs. rows in the subquery

Known Issues:

• Handle composite primary keys in subqueries #3025
• Subqueries should work across 3+ levels #3036
• Subqueries on non-pk columns #3037

Implementation Trade-offs:

Memory Usage

The dependency graph lives entirely in memory. We plan to move the graph into disk using a backend store and optimize memory usage.

Shape Recreation

When subquery results change, we rebuild the entire shape rather than updating incrementally. Clients will see HTTP 409 responses and must re-establish their shapes. This particularly affects use cases with frequently changing subqueries, like fetching comments for dynamic issue selections.

What's Next

We're working to address these limitations to make the feature more stable and bug-free. After resolving the critical issues above, we'll optimize the core algorithm for better performance and stability.

Try it out and share your feedback!

[CelesteComet]

dear sir, is unnest supported? Not super clear on what SQL is supported or not. Thank you

[jaeyson]

waiting on that as well

[lekhnath]

eagerly waiting for join support in sub query

[balegas]

We've released a new implementation of subqueries algorithm that handles move-ins/outs without dropping shapes. Make sure to update to latests client/electric-collection versions since the client needs to handle new protocol messages.

You can enable it with the tagged_subqueries feature flag.

It's still under development, as we continue to handle more complex subquery patterns and improve memory usage. Try it out and let us know if you find any issues.

[whollacsek]

Do we need to use allow_subqueries as well or tagged_subqueries itself is enough? Also what is the meaning of being tagged?

[balegas]

yes, pass the two flags. tagged_subqueries is what we call the algorithm internally. We'll be making the feature generally available soon.

[dsanmart]

@balegas Thanks for the update on tagged_subqueries! This looks like it solves the "Orphaned Parent" problem I described in #3651 .

Based on the syntax in the RFC, is this the correct way to structure the "Graph Sync" for a sliding window?

I want to sync recent messages and ensure their parent conversations exist locally (even if the conversation is old).

// 1. Sync the child window (Recent Messages)
const messagesShape = {
  table: 'messages',
  where: `created_at >= now() - interval '30 days'`
}

// 2. Sync the specific parents required by that window (Graph Integrity)
// Goal: Fetch conversations ONLY if they are referenced by the recent messages above
const conversationsShape = {
  table: 'conversations',
  where: `id IN (
    SELECT conversation_id 
    FROM messages 
    WHERE created_at >= now() - interval '30 days'
  )`
}

Two questions:

• Is this IN (SELECT ...) pattern supported by the new tagged_subqueries algorithm?
• Does the tagged_subqueries algorithm handle the "reactivity" here? (e.g., if a new message arrives in the 30-day window, will it trigger the conversations shape to re-evaluate and fetch the parent if missing?)

[balegas]

You're right, you can use subqueries like the examples you show and it will handle reactivity if any data arrives in the "inner" query.

To clarify, the pattern is supported by subqueries in general. tagged_subqueries is a new algorithm for subqueries.