Strategies

Every query that arrives at Mori is classified, routed to a strategy, and executed against the appropriate backend(s). This page is the exhaustive reference for how each operation type is handled.

Query Classification

The classifier parses each incoming statement to extract:

OpType — READ, WRITE, DDL, TRANSACTION, or OTHER
SubType — specific operation (e.g., SELECT, INSERT, UPDATE, DELETE, ALTER, BEGIN)
Tables — all tables referenced in the query
Primary Keys — extractable PKs from WHERE clauses (e.g., WHERE id = 42)
Feature flags — IsJoin, HasAggregate, HasSetOp, IsComplexRead, HasReturning, HasOnConflict, HasWindowFunc, HasDistinct, HasCursor, IsMetadataQuery

Classification by Engine

Engine	Classification Method	Notes
PostgreSQL, CockroachDB	AST-based (`pg_query_go`)	Full SQL parse tree
MySQL, MariaDB	AST-based (`vitess/sqlparser`)	Vitess SQL parser
MSSQL	Regex-based	No Go-native TDS parser
SQLite, DuckDB	Regex-based	pgwire-exposed embedded engines
Redis	Command-based (static lookup, ~155 commands)	No AST — fixed command grammar
Firestore	gRPC method-name based (18 methods)	No SQL — method name classification

Statement Types (SQL Engines)

Category	Statements	SubType
Read	SELECT	SubSelect
Write	INSERT, UPDATE, DELETE, TRUNCATE	SubInsert, SubUpdate, SubDelete, SubTruncate
DDL	CREATE TABLE/INDEX, ALTER TABLE, DROP TABLE, RENAME	SubCreate, SubAlter, SubDrop
Transaction	BEGIN, COMMIT, ROLLBACK, SAVEPOINT, RELEASE	SubBegin, SubCommit, SubRollback, SubSavepoint, SubRelease
Cursor	DECLARE, FETCH, CLOSE	SubCursor
Session	SET, SHOW	SubSet, SubShow
Explain	EXPLAIN (rejects ANALYZE)	SubExplain
Prepared	PREPARE, EXECUTE, DEALLOCATE	SubPrepare, SubExecute, SubDeallocate
Listen	LISTEN, UNLISTEN	SubListen
Unsupported	NOTIFY, COPY, LOCK TABLE, DO $$, CALL, EXPLAIN ANALYZE	SubNotSupported

Unsupported Operations by Engine

Engine	Unsupported Operations
PostgreSQL	NOTIFY, COPY, LOCK TABLE, DO $$, CALL, EXPLAIN ANALYZE
MySQL	CALL, EXPLAIN ANALYZE
MSSQL	BULK INSERT, EXEC/EXECUTE
DuckDB	COPY/EXPORT/IMPORT, EXPLAIN ANALYZE
Redis	PUBLISH (would affect production subscribers)

Routing Decision Matrix

The router takes the classification output plus the current delta/tombstone/schema state and selects one of 12 execution strategies.

Strategy	Description
Prod Direct	Forward to prod unchanged, return result. Zero overhead.
Merged Read	Query both backends, filter tombstones/deltas, merge results.
Join Patch	Execute JOIN on prod, patch delta rows from shadow.
Shadow Write	Execute INSERT on shadow only.
Hydrate and Write	Hydrate affected rows from prod → shadow, then write.
Shadow Delete	Delete from shadow, add tombstone.
Shadow DDL	Execute DDL on shadow, update schema registry.
Transaction	Forward transaction control to both backends.
Not Supported	Return error to client.
Forward Both	Forward to both backends (e.g., SET).
Truncate	Execute on shadow, mark table fully shadowed.
Listen Only	Forward LISTEN to prod only.

Decision Table (SQL Engines)

OpType	Condition	Strategy
SELECT	All tables clean (no deltas/tombstones/schema diffs)	Prod Direct
SELECT	Affected table + set operation (UNION/INTERSECT/EXCEPT)	Merged Read
SELECT	Affected table + complex read (CTEs, derived tables)	Merged Read
SELECT	Affected table + aggregate/GROUP BY	Merged Read
SELECT	Affected table + JOIN	Join Patch
SELECT	Affected table (simple single-table)	Merged Read
INSERT	No ON CONFLICT	Shadow Write
INSERT	Has ON CONFLICT	Hydrate and Write
UPDATE	Always	Hydrate and Write
DELETE	Always	Shadow Delete
TRUNCATE	Always	Truncate
DDL	Always	Shadow DDL
BEGIN/COMMIT/ROLLBACK	Always	Transaction
SAVEPOINT/RELEASE	Always	Transaction
SET	Always	Forward Both
SHOW	Always	Prod Direct
EXPLAIN	Clean tables	Prod Direct
EXPLAIN	Affected tables	Not Supported
CURSOR	Always	Merged Read
LISTEN	Always	Listen Only
PREPARE	Always	Shadow Write
DEALLOCATE	Always	Forward Both
NOTIFY	Always	Not Supported

Decision Table (Redis)

Redis uses the same strategy model, but classification is command-based rather than SQL-based.

Command Type	Condition	Strategy
Read (GET, HGETALL, LRANGE, etc.)	Key not in delta/tombstone	Prod Direct
Read (GET, HGETALL, LRANGE, etc.)	Key in delta map	Merged Read (route to shadow)
Read (GET, HGETALL, LRANGE, etc.)	Key tombstoned	Merged Read (return type-appropriate nil)
MGET	Mixed keys	Merged Read (per-key routing)
SCAN	Any deltas exist	Merged Read (two-phase cursor merge)
SET, MSET, SETNX	Always	Shadow Write
INCR, HSET, LPUSH, SADD, ZADD, etc.	Always	Hydrate and Write
DEL, UNLINK, SREM, ZREM, etc.	Always	Shadow Delete
FLUSHDB, FLUSHALL	Always	Truncate (marks DB fully shadowed)
MULTI/EXEC/DISCARD	Always	Transaction
SUBSCRIBE/PSUBSCRIBE	Always	Forward Both (fan-in from both)
PUBLISH	Always	Not Supported

Decision Table (Firestore)

Firestore classification is gRPC method-based.

gRPC Method	Condition	Strategy
GetDocument	No deltas for collection	Prod Direct
GetDocument	Document in delta map	Merged Read (route to shadow)
GetDocument	Document tombstoned	Merged Read (return NOT_FOUND)
BatchGetDocuments	Mixed documents	Merged Read (per-document routing)
ListDocuments, RunQuery	Any collection deltas	Merged Read (over-fetch + merge)
RunAggregationQuery	Any collection deltas	Merged Read (decompose + re-aggregate)
CreateDocument	Always	Shadow Write
UpdateDocument	Always	Hydrate and Write
DeleteDocument	Always	Shadow Delete
Commit, BatchWrite	Always	Hydrate and Write (per-write)
BeginTransaction	Always	Transaction
Rollback	Always	Transaction
Listen	Always	Listen Only

Read Operations

Prod Direct

When a SELECT touches only tables with no local mutations, the query passes straight through to prod. Zero overhead beyond the proxy hop.

Merged Read (Single Table)

When a SELECT touches a table with delta or tombstone entries:

Shadow Query

Execute the original query against shadow. Returns the local subset of results.

Prod Query (over-fetched)

Execute against prod with adjusted LIMIT: original_limit + |deltas| + |tombstones|. If schema divergence exists, the query is rewritten first (remove predicates on shadow-only columns, revert renamed columns).

Filter Prod Results

Discard rows where (table, pk) exists in the Delta Map (shadow has the authoritative version) or the Tombstone Set (locally deleted).

Adapt Prod Results

Per the Schema Registry: inject NULLs for added columns, strip dropped columns, apply renames and type casts.

Merge and Deduplicate

Combine shadow results with filtered/adapted prod results. Deduplicate by PK — shadow wins on conflict.

Re-apply Ordering and Limits

Sort by original ORDER BY, apply LIMIT/OFFSET to produce the final result set.

PK injection: The proxy injects PK columns into the SELECT if they aren’t already present (needed for deduplication). They’re stripped from the final output. LIMIT over-fetching: Prod queries add delta_count + tombstone_count to the LIMIT to compensate for rows that will be filtered out.

Redis merged reads use a simpler per-key routing model instead of the SQL merge pipeline. Each key is checked against the delta map and tombstone set individually. MGET batches per-key routing. SCAN uses a two-phase cursor: Phase 0 scans prod (filtering delta/tombstoned keys), Phase 1 scans shadow for new/modified keys.

Firestore merged reads use document-path-based deduplication. GetDocument routes per-document (tombstone → NOT_FOUND, delta → shadow, else → prod). ListDocuments and RunQuery over-fetch from prod, query shadow, merge by document path, re-evaluate WHERE filters, and re-sort.

Join Patch

When a SELECT joins tables and at least one has local mutations:

Execute on Prod

Run the JOIN as-is on prod. Produces correct results for all non-delta rows.

Identify Affected Rows

For each row in the result, check if any primary key from a delta table is in the Delta Map or Tombstone Set. Partition into clean, delta, and dead rows.

Discard Tombstoned Rows

Remove rows referencing locally-deleted PKs.

Patch Delta Rows

Fetch shadow versions of delta rows and replace the delta columns in the joined result.

Execute on Shadow

Run the JOIN on shadow to catch locally-inserted rows that should appear in the result.

Merge and Deduplicate

Combine patched prod results with shadow results. Deduplicate by composite key (all joined table PKs), then re-apply ORDER BY and LIMIT.

Schema diff handling for JOINs: When joined tables have schema changes, dirty tables are materialized into temp tables and the JOIN is rewritten to reference them.

Aggregates

Aggregate queries (COUNT, SUM, AVG, MIN, MAX with GROUP BY) can’t be merged by simply combining prod and shadow results — aggregation must be re-applied on the combined data. Approach:

Build a row-level base query (strip aggregates, keep GROUP BY columns and source columns)
Run the base query through the merged read pipeline to get all rows
Materialize into a temp table on shadow
Re-aggregate in the proxy (in-memory grouping + function application)
Apply HAVING filters on re-aggregated results

Complex aggregates (array_agg, json_agg, string_agg, etc.) materialize into a temp table and re-execute the original query against it on shadow, leveraging the database’s native aggregate implementation.

Firestore aggregations (COUNT, SUM, AVG only) decompose to a base RunQuery through the merged pipeline, then re-compute aggregations in memory. No temp tables — Firestore has no SQL execution engine.

Complex Reads

CTEs (WITH clause):

Non-recursive CTEs: Materialize dirty CTEs independently into temp tables
Recursive CTEs: Materialize dirty base tables, rewrite query to reference temp tables

Derived Tables (subqueries in FROM):

Recursively rewrite subqueries, replacing dirty subquery results with temp tables

LATERAL Joins:

Materialize referenced dirty base tables (not the LATERAL body itself)
Preserve LATERAL semantics through table materialization

Set Operations

UNION, INTERSECT, EXCEPT are decomposed into leaf SELECT nodes. Each leaf runs through the merged read pipeline independently, then in-memory set operations are applied:

UNION: Combine + deduplicate
UNION ALL: Combine without dedup
INTERSECT: Keep rows in both sets
EXCEPT: Keep rows only in first set

Window Functions

Window functions require the complete dataset to compute correctly — they can’t be decomposed.

Build a base SELECT (same FROM/WHERE, no window functions)
Run base through merged read pipeline
Materialize result into temp table on shadow
Rewrite original query to read from temp table
Execute on shadow (leverages the database’s native window implementation)

Cursors

DECLARE CURSOR: Classifies inner query; materializes if affected tables
FETCH: Reads from cursor on the appropriate backend
CLOSE: Closes cursor on both backends

Write Operations

All writes execute on shadow. Production is never modified.

INSERT

Simple INSERT (Shadow Write):

Enforce FK constraints (validate parent rows exist)
Execute INSERT on shadow
Capture RETURNING clause to extract inserted PKs for precise delta tracking
Add all inserted PKs to the Delta Map
Persist state

Shadow’s offset sequences assign PKs in the local range (e.g., 10,000,001+), guaranteed to not collide with prod. Upsert — INSERT … ON CONFLICT (Hydrate and Write):

Detect conflict key columns
Check prod for rows matching conflict keys
Hydrate matching prod rows into shadow
Execute upsert on shadow
Track all affected rows (hydrated + newly inserted) in Delta Map

MySQL-specific: MySQL lacks RETURNING. INSERT PK extraction uses a 3-tier approach: (1) extract from VALUES clause, (2) use LAST_INSERT_ID(), (3) fall back to information_schema PK lookup. MySQL-specific: INSERT ... ON DUPLICATE KEY UPDATE and REPLACE INTO route through the hydrate-and-write path.

UPDATE

Point Update (PK extractable from WHERE, e.g., WHERE id = 42):

Enforce FK constraints on SET values
Check if row exists in shadow — if not, hydrate from prod
Execute UPDATE on shadow
Add affected PK to Delta Map

Bulk Update (PK not extractable, e.g., WHERE status = 'pending'):

Query prod for all matching rows (with schema rewriting for prod compatibility)
Hydrate all matching rows into shadow
Rewrite UPDATE WHERE clause with concrete PK values
Execute on shadow
Track all affected PKs in Delta Map

Cross-table Updates (UPDATE … FROM):

Extract subquery predicates from FROM clause
Hydrate referenced tables before applying the update

Redis writes: SET/MSET route to shadow directly (Shadow Write). Commands that modify existing keys (INCR, HSET, LPUSH, SADD, ZADD, etc.) use DUMP/RESTORE to hydrate the key from prod to shadow before executing (Hydrate and Write).

Firestore writes: CreateDocument routes to shadow directly. UpdateDocument hydrates the document from prod first. Commit and BatchWrite hydrate per-write. Document transforms (serverTimestamp, increment, arrayUnion, arrayRemove) are tracked as deltas.

DELETE

Point Delete (PK extractable):

Enforce FK RESTRICT/NO ACTION constraints (reject if child rows exist)
Execute DELETE on shadow
Add tombstone for each affected PK
Enforce FK CASCADE (recursively tombstone/delete child rows)
Return correct row count (including prod-only rows)

Bulk Delete (PK not extractable):

Pre-query prod for matching PKs
Compute total count: shadow actual deletes + prod-only matches
Tombstone all matching PKs
Execute DELETE on shadow

DELETE … RETURNING: Hydrates from prod before deletion to synthesize return data.

TRUNCATE

Execute on shadow
Mark table as fully shadowed in Schema Registry — all subsequent reads skip prod entirely
Clear all tombstones for the table
CASCADE: recursively mark FK-related tables as fully shadowed

Redis: FLUSHDB/FLUSHALL mark the entire database as fully shadowed. All subsequent reads go to shadow only.

DDL Handling

DDL operations execute on shadow only. Production schema is never modified.

Strip FK constraints from DDL before shadow execution (the proxy enforces FKs instead)
Extract and store FK metadata in Schema Registry
Execute modified DDL on shadow
Update Schema Registry with the structural change

DDL	Registry Update	Impact on Reads
`ADD COLUMN`	Records column name, type, default	Prod rows get NULL/default injected
`DROP COLUMN`	Records dropped column name	Column stripped from prod results
`RENAME COLUMN`	Records `old → new` mapping	Prod queries use old name, results renamed
`ALTER TYPE`	Records `old_type → new_type`	Values cast in prod results
`CREATE TABLE`	Marks table as shadow-only	Table is shadow-only, no prod reads
`DROP TABLE`	Marks table as dropped	Queries return “table not found”
`CREATE INDEX`	Shadow only, silent	No impact on reads
`RENAME TABLE`	Records `old → new` mapping	Reads redirect to new name

Redis and Firestore have no DDL. Redis is schemaless (key-value); Firestore is schemaless (document). The Schema Registry is unused for these engines except for the IsFullyShadowed flag (set by FLUSHDB in Redis).

Transaction Management

Lifecycle

Operation	Behavior
`BEGIN`	Open read-only transaction on prod (REPEATABLE READ) + read-write transaction on shadow. Snapshot delta/tombstone/schema state.
`COMMIT`	Commit shadow, close prod, promote staged deltas/tombstones to persistent state.
`ROLLBACK`	Rollback shadow, close prod, discard staged deltas/tombstones. Restore schema registry from snapshot.

Prod uses REPEATABLE READ isolation to ensure consistent reads across the transaction — the merged read always sees a stable prod snapshot.

Engine-Specific Transaction Behavior

Engine	Transaction Model	Notes
PostgreSQL, CockroachDB	Full BEGIN/COMMIT/ROLLBACK + savepoints	Prod uses REPEATABLE READ
MySQL, MariaDB	Full BEGIN/COMMIT/ROLLBACK + savepoints	Prod uses REPEATABLE READ
MSSQL	Full BEGIN/COMMIT/ROLLBACK	Savepoints supported
SQLite, DuckDB	Full BEGIN/COMMIT/ROLLBACK	Single-writer; no concurrent txns
Redis	MULTI/EXEC/DISCARD	No savepoints; WATCH-based optimistic locking
Firestore	BeginTransaction/Commit/Rollback	Prod-side snapshot isolation via ReadTime

Staged Deltas

Within a transaction, all delta/tombstone additions are staged — held in a per-transaction buffer. This prevents phantom deltas from failed transactions: if a transaction rolls back, the Delta Map and Tombstone Set remain unchanged. Autocommit mode (implicit single-statement transactions) applies deltas immediately — no staging needed.

Savepoints

Savepoints are fully supported (SQL engines) with stack-based snapshot management:

SAVEPOINT name: Capture current delta/tombstone/schema/insert-count state, push onto stack
RELEASE SAVEPOINT name: Pop snapshot from stack (changes persist)
ROLLBACK TO SAVEPOINT name: Restore state from snapshot, keep savepoint on stack for re-use

Foreign Key Enforcement

Why FKs are Stripped

Shadow starts empty. INSERTs referencing prod rows via foreign key would fail because the referenced row doesn’t exist in shadow. The proxy enforces referential integrity instead.

Parent Row Validation (INSERT/UPDATE)

Validation order (fast-path first):

Delta map check: If parent PK in delta → exists in shadow
Tombstone check: If parent PK tombstoned → REJECT
Shadow query: Check shadow for parent row
Prod query: Fall back to prod if not found in shadow

Supports multi-column (composite) foreign keys.

Referential Action Enforcement (DELETE)

RESTRICT / NO ACTION: Reject parent delete if child rows exist (checks shadow + prod)
CASCADE: Tombstone child rows when parent deleted, recursively cascade
SET NULL / SET DEFAULT: Falls through to shadow enforcement

Redis and Firestore have no foreign key concept. FK enforcement is SQL-engine-specific.

Known Limitations

Limitation	Engines	Impact	Mitigation
Multi-column ORDER BY	All SQL	Only single-column sort re-applied after merge	Multi-column ORDER BY returns in backend order
Mutating CTEs only see shadow data	All SQL	CTE reads from clean tables return empty	v1.1: split into write + read
Stored procedures with side effects	All SQL	`SELECT my_function()` routes as READ to prod	v2: function introspection or config allowlist
Tables without PKs	All SQL	Read-only, writes produce warning	Rare in practice
Bulk ops on large tables	All SQL	Hydrating thousands of rows is slow	Acceptable for dev tool
JOIN PK patching	All SQL	Requires PK in SELECT list	Patching skipped when PK not selected
Parameterized LIMIT	All SQL	`LIMIT $1` not rewritten for over-fetching	Uncommon in practice
`sp_executesql` parameter resolution	MSSQL	Parameterized SELECT not resolved for merged reads	Use inline SQL where possible
Blocking commands (BLPOP, etc.)	Redis	Won’t see pushes from non-Mori clients to prod	Inherent to copy-on-write model
Real-time listeners (Listen)	Firestore	Only see prod data, not shadow mutations	By design — listeners reflect prod state
Don’t rename PK columns	All SQL	Delta tracking depends on stable PK column names	Avoid renaming PK columns

​Query Classification

​Classification by Engine

​Statement Types (SQL Engines)

​Unsupported Operations by Engine

​Routing Decision Matrix

​Strategies

​Decision Table (SQL Engines)

​Decision Table (Redis)

​Decision Table (Firestore)

​Read Operations

​Prod Direct

​Merged Read (Single Table)

​Join Patch

​Aggregates

​Complex Reads

​Set Operations

​Window Functions

​Cursors

​Write Operations

​INSERT

​UPDATE

​DELETE

​TRUNCATE

​DDL Handling

​Transaction Management

​Lifecycle

​Engine-Specific Transaction Behavior

​Staged Deltas

​Savepoints

​Foreign Key Enforcement

​Why FKs are Stripped

​Parent Row Validation (INSERT/UPDATE)

​Referential Action Enforcement (DELETE)

​Known Limitations

Query Classification

Classification by Engine

Statement Types (SQL Engines)

Unsupported Operations by Engine

Routing Decision Matrix

Strategies

Decision Table (SQL Engines)

Decision Table (Redis)

Decision Table (Firestore)

Read Operations

Prod Direct

Merged Read (Single Table)

Join Patch

Aggregates

Complex Reads

Set Operations

Window Functions

Cursors

Write Operations

INSERT

UPDATE

DELETE

TRUNCATE

DDL Handling

Transaction Management

Lifecycle

Engine-Specific Transaction Behavior

Staged Deltas

Savepoints

Foreign Key Enforcement

Why FKs are Stripped

Parent Row Validation (INSERT/UPDATE)

Referential Action Enforcement (DELETE)

Known Limitations