Stateful Subscription Performance Guidelines

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:

Is deleted or evicted
No longer matches the filter (WHERE clause)
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:

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

Need Help?

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

Overview & Setup

SDKs

Accessing Data

Syncing Data

Authentication and Authorization

Managing the Mesh

Deploying your App

Stateful Subscription Performance Guidelines

What are Stateful Subscriptions?

Why This Matters

Understanding Cache Invalidation

Query Performance Examples

✅ Good Queries

⚠️ Suboptimal Queries

❌ Poor Queries

Best Practices

Migration Strategies

Need Help?

​What are Stateful Subscriptions?

​Why This Matters

​Understanding Cache Invalidation

​Query Performance Examples

​✅ Good Queries

​⚠️ Suboptimal Queries

​❌ Poor Queries

​Best Practices

​Migration Strategies

​Need Help?

What are Stateful Subscriptions?

Why This Matters

Understanding Cache Invalidation

Query Performance Examples

✅ Good Queries

⚠️ Suboptimal Queries

❌ Poor Queries

Best Practices

Migration Strategies

Need Help?