Design Decision: Synchronous Queries

[tantaman]

LiveStore aims to provide a 100% synchronous API for data access from the UI thread in the browser. The problem, however, is if you ever go to disk from the UI thread the browser forces the call to be async. This means LiveStore needs to create a second copy of the database stored entirely in-memory. This in-memory copy lazily persists to the canonical copy on disk. In other words, writes are not durably committed.

All writes flow from the memory-db to the disk db. No writes flow from the worker to the memory DB — the full copy of the disk db is pulled into the memory db at startup.

This in-memory DB is hydrated once from disk at app startup.

Benefits

1. All reads and writes are synchronous, creating a simple programming model
2. Reads and writes are extremely fast (10-100x faster than disk backed sqlite) given all data is in-memory

Complications

This design creates a few complications for:

1. Dataset size
2. Exporation: Cross Tab Sync #40
3. App startup time

Alternative Designs

1. Async reads, writes & reactivity
2. Async reads, non-durable synchronous writes with synchronous reactivity

[tantaman]

I wonder if it is possible to do this with a VFS. Rather than two separate SQLite instances, can the VFS make this split for us?

Questions:

• When does SQLite go to disk on a write?
  • To check foreign key constraints?
  • To write the actual data (of course)
  • Other?
• Would an in-memory WAL obviate the need to hit disk?
  • We still would have to acquire write locks for SQLite unless we come up with a different write coordination strategy. Lock acquisition is async which means it is still a problem.

Possibility:

• Each tab gets an in-memory WAL
• Do not acquire write locks, each tab writes its own in-memory WAL
• Have a single worker that checkpoints the WALs to the main DB file
• The main DB file can push updates to in-memory WALs. The incorporate the updates via truncating incorporated writes and rebasing unincorporated writes. Similar to Exporation: Cross Tab Sync #40 (comment)

This is still a problem for reads... LiveStore assumes synchronous reads. We'd need a way to load all data for reads into memory ahead of time. Maybe this can be driven by the routing library.

[tantaman]

Related: rhashimoto/wa-sqlite#138