Did you see this warning in your Ditto logs? If so, this page is for you.

The following sync subscription uses LIMIT or ORDER BY, 
which will cause cache invalidation and reduce sync performance. 
For more information, please see https://docs.ditto.live/stateful-subscriptions 
${query}

​
What are Stateful Subscriptions?

A stateful subscription is a query that uses LIMIT 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:

  1. Is deleted or evicted
  2. No longer matches the filter (WHERE clause)
  3. Changes position due to ORDER BY (moves outside the limit boundary)

The third condition only applies to queries with ORDER BY, making them particularly susceptible to performance issues.

​
Query Performance Examples

​
✅ Good Queries

These queries minimize cache invalidation by filtering and sorting on immutable fields:

QueryWhy It Performs Well
SELECT * FROM orders LIMIT 50Cache invalidation only on document deletion
SELECT * FROM orders WHERE _id.restaurantID = 1 LIMIT 50restaurantID is immutable, avoiding filter-based invalidation
SELECT * FROM orders ORDER BY createdAt DESC LIMIT 50createdAt doesn’t change, preventing sort-based invalidation
SELECT * FROM orders WHERE restaurantID = 1 ORDER BY createdAt DESC LIMIT 50Both 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:

QueryPerformance Impact
SELECT * FROM orders WHERE !closed LIMIT 50Cache invalidates when closed status changes
SELECT * FROM orders WHERE !closed ORDER BY createdAt ASC LIMIT 50Worse than above due to sort operation cost during invalidation
SELECT * FROM orders WHERE !closed ORDER BY createdAt DESC LIMIT 50May perform better if new orders arrive faster than closures
SELECT * FROM cars ORDER BY miles DESC LIMIT 50Depends 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:

QueryWhy It’s Problematic
SELECT * FROM orders WHERE !closed ORDER BY updatedAt LIMIT 50Both filter and sort use mutable fields that change together

​
Best Practices

  1. Identify Fields that do not change: Use fields like IDs, creation timestamps, or static properties for filtering and sorting.

  2. Minimize Usage: If you must filter on fields that change often, avoid combining them with ORDER BY.

  3. Consider Query Redesign: Instead of complex stateful queries, consider:

    • Removing LIMIT and handling pagination client-side
    • Splitting into multiple simpler queries

  4. 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:

  1. Remove ORDER BY: This eliminates position-based cache invalidation
  2. Use Immutable Alternatives: Replace mutable field filters with immutable equivalents
  3. Implement Client-Side Filtering: For small datasets, fetch more data and filter locally
  4. Add Indexes: Ensure proper indexing for ORDER BY queries to minimize re-query costs

​
Need Help?

If you’re experiencing sync performance issues or need help optimizing your queries, please contact Ditto support.