Turso Incremental Computation with DBSP for Query Subscriptions: Implementation Guide

Turso embeds a DBSP (Differential-Batched-Streaming-Processing) engine directly into its SQLite-compatible core to incrementally maintain materialized views and stream only delta changes to subscribed clients, eliminating expensive full-table re-scans.

Turso (tursodatabase/turso) enables real-time reactive queries by integrating incremental computation through DBSP directly into the database engine. When you create a subscription to a materialized view, Turso automatically generates hidden state tables and indexes that track only the differences between query results, pushing minimal change sets to clients rather than polling entire datasets. This architecture significantly reduces latency and bandwidth while maintaining ACID compliance within the familiar SQLite ecosystem.

How Turso Implements DBSP for Incremental Computation

Turso implements incremental computation by embedding a DBSP engine that compiles view queries into data-flow circuits. The system automatically creates hidden infrastructure to maintain state and compute deltas when underlying tables change.

View Registration and Hidden State Tables

When you execute CREATE MATERIALIZED VIEW or register a view for subscription, Turso's planner in core/translate/view.rs generates hidden infrastructure. The system creates a hidden table named __turso_internal_dbsp_state_v<DBSP_VERSION>_<view_name> to store the current output of the view, along with a corresponding index __turso_internal_dbsp_state_v<DBSP_VERSION>_<view_name>_idx for efficient lookups.

According to the source code in core/translate/view.rs, the hidden table creation SQL is emitted between lines 199-219, while the index creation occurs at lines 232-245. These objects are managed automatically and remain invisible to standard SQL introspection.

DBSP Circuit Compilation

The view's query is compiled into a DBSP data-flow circuit using crate::incremental::compiler::DBSP_CIRCUIT_VERSION. The circuit definition is stored in the hidden table's B-tree pages. The compilation utilizes the IncrementalView type and the create_dbsp_state_index helper to materialize the necessary indexes.

This compilation step transforms the declarative SQL query into an incremental computation graph that can process deltas rather than recomputing full results.

Runtime Delta Computation in the VDBE

When write transactions modify tables that a view depends on, the DBSP executor in core/vdbe/execute.rs processes the changes incrementally. The executor maintains two critical hash maps in the connection object:

• dbsp_state_roots: HashMap<String, i64> – tracks the root page of each hidden state table
• dbsp_state_index_roots: HashMap<String, i64> – tracks the root page of each hidden state index

These maps are defined at lines 12341-12342 in core/vdbe/execute.rs and populated during execution at lines 12495-12532. When a change occurs, the executor runs the DBSP circuit and writes only the resulting delta into the hidden state table, avoiding full re-scans of base tables.

Subscription API and Change Streaming

The sync layer in sync/sdk-kit/… exposes a subscription endpoint that translates client SUBSCRIBE <query> requests into materialized views. When a client subscribes, Turso creates the corresponding hidden DBSP state table if it doesn't exist, then streams subsequent change sets containing only inserted or deleted rows.

This approach reduces bandwidth dramatically compared to polling, as clients receive only the incremental differences computed by the DBSP engine.

Garbage Collection of DBSP State

When a subscription is dropped, Turso cleans up the hidden infrastructure. The system calls remove_table and remove_indices_for_table in core/schema.rs (lines 1182-1183) to delete the hidden DBSP state table and its associated index, freeing storage immediately.

Key Source Files and Implementation Details

The DBSP implementation spans several core files that manage schema, compilation, and execution:

• core/schema.rs: Defines DBSP_TABLE_PREFIX (lines 181-182) and implements has_compatible_dbsp_state_table (lines 1140-1183) to verify state table compatibility during version upgrades. This file also manages the root-page maps for DBSP state.

• core/translate/view.rs: Generates the SQL that creates hidden DBSP state tables and indexes during view compilation (lines 199-245).

• core/vdbe/execute.rs: Implements the runtime execution engine that updates DBSP state maps and writes deltas to hidden tables during transaction processing (lines 12341-12532).

• core/util.rs: Provides utilities for copying DBSP state maps between connections, including the InnerDbState struct fields (lines 173-174) and cloning logic (lines 218-219).

Practical Examples: Creating Subscriptions with DBSP

Creating a Materialized View for Subscription

-- Create a view that tracks recent orders
CREATE MATERIALIZED VIEW recent_orders AS
SELECT order_id, total
FROM orders
WHERE created_at > datetime('now', '-1 hour');

When this executes, Turso automatically creates the hidden table __turso_internal_dbsp_state_v0_recent_orders and its corresponding index in core/translate/view.rs.

Subscribing via JavaScript SDK

import { TursoClient } from '@tursodatabase/turso';

const client = new TursoClient({ url: 'https://my-db.turso.io' });

const subscription = client.subscribe(`
    SELECT order_id, total
    FROM recent_orders
`);

subscription.on('change', (delta) => {
  // delta contains only incremental changes
  console.log('New orders:', delta.inserts);
  console.log('Cancelled orders:', delta.deletes);
});

subscription.start();

The SDK sends a SUBSCRIBE request; Turso internally manages the DBSP state table and streams computed deltas.

Cleaning Up Subscriptions

await subscription.stop();

This triggers the server to call schema.remove_table and remove_indices_for_table (lines 1182-1183 in core/schema.rs), removing the hidden DBSP infrastructure.

Summary

• Turso embeds a DBSP engine directly into its SQLite-compatible core to enable incremental computation for query subscriptions.
• Hidden state tables named __turso_internal_dbsp_state_v<DBSP_VERSION>_<view_name> store materialized view results automatically.
• The VDBE executor in core/vdbe/execute.rs maintains dbsp_state_roots and dbsp_state_index_roots hash maps to track state table locations.
• DBSP circuits compile view queries into incremental data-flow graphs that process only delta changes during write transactions.
• The subscription API streams minimal change sets to clients, eliminating the need for full-result polling.
• Garbage collection removes hidden state tables via remove_table and remove_indices_for_table in core/schema.rs when subscriptions end.

Frequently Asked Questions

How does Turso's DBSP implementation differ from traditional materialized views?

Traditional materialized views typically require manual refresh commands or full re-computation of the query result when base tables change. Turso's DBSP implementation automatically incrementally updates the view by computing only the delta changes through the DBSP circuit, maintaining the dbsp_state_roots map in core/vdbe/execute.rs to track modified rows without re-scanning entire tables.

What naming convention does Turso use for hidden DBSP state tables?

Turso names hidden state tables using the pattern __turso_internal_dbsp_state_v<DBSP_VERSION>_<view_name> and indexes using __turso_internal_dbsp_state_v<DBSP_VERSION>_<view_name>_idx, as implemented in core/translate/view.rs between lines 199-245. The DBSP_VERSION component ensures compatibility across schema changes.

How does the VDBE maintain DBSP state across transactions?

The Virtual Database Engine (VDBE) maintains per-connection state through two hash maps: dbsp_state_roots and dbsp_state_index_roots, defined in core/vdbe/execute.rs at lines 12341-12342. These maps persist the root page numbers of hidden DBSP tables, allowing the executor to locate and update state efficiently across multiple transactions without re-parsing schema definitions.

What happens when a subscription is dropped?

When a client unsubscribes, Turso invokes remove_table and remove_indices_for_table in core/schema.rs (lines 1182-1183) to immediately delete the hidden DBSP state table and its index. This garbage collection ensures that disk space is reclaimed promptly and no orphaned state tables accumulate in the database.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}