Query-driven sync fails with HTTP 431 for large IN clauses

Problem

Query-driven sync with Electric collections fails with HTTP 431 (Request Header Fields Too Large) when using large IN clauses, particularly common with join table patterns. This occurs when filtering by hundreds of IDs (e.g., UUIDs).

User Report

As part of my app I use a join table (with the electric collection), which in turn leads to queries that try to select several hundred elements by ID, all those being uuids also results in a HTTP 431.

Is there a way to have the electric collection opt to not push down a predicate if it's too large / verbose? At least for now, while electric still can't handle joins in where clauses.

Root Cause Analysis

The issue stems from how Electric's HTTP API sends subset parameters:

1. Predicate Pushdown: IN clauses are compiled to PostgreSQL's = ANY() syntax:

   id = ANY($1)
   

2. Array Serialization: Arrays are serialized to PostgreSQL array literals:

   // For 500 UUIDs
   {"uuid1","uuid2","uuid3",...,"uuid500"}
   

3. HTTP Transport: Parameters are sent as URL query parameters:

   subset__where=id = ANY($1)
   subset__params[1]={"uuid1","uuid2",...,"uuid500"}
   

4. The Breaking Point: With hundreds of UUIDs (36 chars each), the serialized array easily exceeds HTTP header limits:

   • Default Nginx limit: 4-8KB per header, 16-64KB total
   • Example: 500 UUIDs × 36 chars = ~18KB for just the array values

Relevant Code Locations

• SQL Compilation: packages/electric-db-collection/src/sql-compiler.ts:176-178 (IN operator handling)
• Array Serialization: packages/electric-db-collection/src/pg-serializer.ts:41-54
• Subset Request: packages/electric-db-collection/src/electric.ts:749-750
• Predicate Deduplication: packages/db/src/query/subset-dedupe.ts

Potential Solutions

Solution 1: Client-Side Predicate Splitting (Quick Fix)

Split large IN clauses into multiple smaller requests and merge results client-side.

Pros:

• Can be implemented in @tanstack/db without Electric server changes
• Preserves existing deduplication logic
• Backward compatible

Cons:

• Multiple HTTP requests = higher latency
• More complex client-side logic
• Doesn't solve the fundamental architectural issue

Implementation: Modify DeduplicatedLoadSubset in packages/db/src/query/subset-dedupe.ts to detect and split large predicates.

Solution 2: POST-based Subset Requests (Proper Fix)

Change Electric's HTTP API to accept subset parameters in POST request body instead of query parameters.

Pros:

• Eliminates URL length limitations (POST bodies typically allow 1-2MB)
• Cleaner API design
• No artificial limits on predicate complexity
• Aligns with RESTful best practices (POST for complex queries)

Cons:

• Requires changes to Electric server
• Breaking change to Electric's HTTP API (would need API versioning)
• Need to coordinate with Electric SQL team

Solution 3: Predicate Size Guard with Fallback (Pragmatic Hybrid)

Detect oversized predicates and fall back to fetching full collection + client-side filtering.

Pros:

• Prevents errors gracefully
• No server changes needed
• User configurable thresholds

Cons:

• Falls back to less efficient path
• Still transfers unnecessary data
• May impact performance for large collections

Implementation: Add size checking in compileSQL() before calling stream.requestSnapshot():

export function compileSQL<T>(options: LoadSubsetOptions): SubsetParams {
  const { where, orderBy, limit } = options
  const params: Array<T> = []
  const compiledSQL: CompiledSqlRecord = { params }
  
  if (where) {
    compiledSQL.where = compileBasicExpression(where, params)
  }
  
  // Check if serialized params would exceed HTTP header limits
  const estimatedSize = estimateParamSize(params)
  if (estimatedSize > THRESHOLD) {
    // Option A: Throw error with helpful message
    // Option B: Return special marker to trigger fallback behavior
    // Option C: Split into multiple predicates (Solution 1)
  }
  
  // ... rest of compilation
}

Solution 4: Temporary Table Approach (Advanced)

For very large ID sets, create a temporary table and use a JOIN instead of IN.

Pros:

• Efficiently handles arbitrarily large ID sets
• Better database performance for large sets

Cons:

• Requires significant Electric server changes
• Complex implementation
• May not fit Electric's architecture
• Requires session/connection management

Recommended Approach

Two-phase strategy:

Phase 1 (Immediate): Solution 3 + Solution 1

Implement a predicate size guard with automatic splitting:

1. Add configurable threshold (default: 100-200 items in IN clause, or ~4KB serialized size)
2. When exceeded, automatically split into multiple smaller requests
3. Provide configuration option to control behavior:

   {
     maxPredicateSize: 100, // max items in IN clause
     onLargePredicateFallback: 'split' | 'full-sync' | 'error'
   }
   

4. Document the limitation clearly in docs

Phase 2 (Long-term): Solution 2

Work with Electric SQL team on POST-based API:

• This is the proper architectural fix
• Benefits the entire Electric ecosystem
• Requires coordination and API versioning

Additional Context

This issue is particularly problematic for common patterns like:

• Many-to-many relationships with join tables
• Batch operations on related records
• Permission systems with user-specific record access
• Any scenario requiring "fetch records where id in (large list)"

No current workaround exists without modifying the query pattern (e.g., breaking queries into smaller batches manually).

Related Issues

• This relates to the limitation that Electric can't handle joins in WHERE clauses yet
• Once joins are supported, this specific pattern may become less common, but the underlying HTTP parameter size issue would still exist for other use cases

Start by reading the SQL compilation and serialization paths in packages/electric-db-collection/src/sql-compiler.ts and pg-serializer.ts, then inspect subset request handling in electric.ts and deduplication in packages/db/src/query/subset-dedupe.ts. Compare the four proposed approaches and establish one agreed scope, including how oversized predicates are detected, handled, and documented.