What are Stateful Subscriptions?
A stateful subscription is a query that usesLIMIT
or LIMIT + ORDER BY
clauses. These queries maintain state about which documents are currently within
the limit boundary, which can lead to performance challenges when documents
change.
Key Rule: Avoid filtering (
WHERE
clause) or sorting (ORDER BY
clause) on mutable fields when LIMIT
is used.Why This Matters
When a document within the limit boundary changes in certain ways, Ditto’s sync engine must invalidate its cache and re-execute the entire query. This cache invalidation significantly impacts sync performance, especially with:- High update frequencies
- Large result sets
- Multiple concurrent subscriptions
Understanding Cache Invalidation
Cache invalidation occurs when a document within the limit:- Is deleted or evicted
- No longer matches the filter (WHERE clause)
- Changes position due to ORDER BY (moves outside the limit boundary)
Query Performance Examples
✅ Good Queries
These queries minimize cache invalidation by filtering and sorting on immutable fields:Query | Why It Performs Well |
---|---|
SELECT * FROM orders LIMIT 50 | Cache invalidation only on document deletion |
SELECT * FROM orders WHERE _id.restaurantID = 1 LIMIT 50 | restaurantID is immutable, avoiding filter-based invalidation |
SELECT * FROM orders ORDER BY createdAt DESC LIMIT 50 | createdAt doesn’t change, preventing sort-based invalidation |
SELECT * FROM orders WHERE restaurantID = 1 ORDER BY createdAt DESC LIMIT 50 | Both filter and sort use fields that don’t change |
For queries sorted by
createdAt DESC
, new documents with larger timestamps are handled efficiently without cache invalidation through internal optimizations.⚠️ Suboptimal Queries
These queries filter on mutable fields while sorting on ones that don’t change frequently:Query | Performance Impact |
---|---|
SELECT * FROM orders WHERE !closed LIMIT 50 | Cache invalidates when closed status changes |
SELECT * FROM orders WHERE !closed ORDER BY createdAt ASC LIMIT 50 | Worse than above due to sort operation cost during invalidation |
SELECT * FROM orders WHERE !closed ORDER BY createdAt DESC LIMIT 50 | May perform better if new orders arrive faster than closures |
SELECT * FROM cars ORDER BY miles DESC LIMIT 50 | Depends on update frequency of miles field |
Sorting in descending order can reduce invalidations when new documents are inserted frequently, as they push older documents out of the limit naturally.
❌ Poor Queries
Avoid these patterns that filter and sort on frequently updated fields:Query | Why It’s Problematic |
---|---|
SELECT * FROM orders WHERE !closed ORDER BY updatedAt LIMIT 50 | Both filter and sort use mutable fields that change together |
Best Practices
- Identify Fields that do not change: Use fields like IDs, creation timestamps, or static properties for filtering and sorting.
- Minimize Usage: If you must filter on fields that change often, avoid combining them with ORDER BY.
-
Consider Query Redesign: Instead of complex stateful queries, consider:
- Removing LIMIT and handling pagination client-side
- Splitting into multiple simpler queries
- Monitor Performance: Use Ditto’s logging at WARN level to identify queries causing frequent cache invalidation.
Migration Strategies
If you’re seeing performance issues with existing queries:- Remove ORDER BY: This eliminates position-based cache invalidation
- Use Immutable Alternatives: Replace mutable field filters with immutable equivalents
- Implement Client-Side Filtering: For small datasets, fetch more data and filter locally
- Add Indexes: Ensure proper indexing for ORDER BY queries to minimize re-query costs