Skip to main content
The SQLite accelerator provides lightweight, embedded transactional database acceleration. It’s optimized for OLTP workloads with frequent inserts, updates, deletes, and row-based point queries.

​
When to Use SQLite

  • Transactional workloads: Frequent updates and deletes
  • Point queries: Row-based lookups by primary key
  • ACID guarantees: Strong consistency required
  • Lightweight: Minimal resource overhead
  • File-based: Need persistence without external database

​
Configuration

​
Memory Mode

Data stored in RAM (volatile): 
datasets:
  - name: user_sessions
    from: postgres://db/sessions
    acceleration:
      enabled: true
      engine: sqlite
      mode: memory

​
File Mode

Data persisted to disk: 
acceleration:
  enabled: true
  engine: sqlite
  mode: file
  params:
    sqlite_file: /data/my_dataset.db

​
Default File Location

If sqlite_file not specified, uses {spice_data_dir}/accelerated_sqlite.db: 
acceleration:
  enabled: true
  engine: sqlite
  mode: file

​
File Create Mode

Deletes existing file on startup: 
acceleration:
  enabled: true
  engine: sqlite
  mode: file_create
  params:
    sqlite_file: /data/fresh_dataset.db

​
Refresh Modes

​
Full Refresh

Replaces all data: 
acceleration:
  enabled: true
  engine: sqlite
  refresh_mode: full
  refresh_interval: 10m

​
Append Mode

Appends only new data: 
acceleration:
  enabled: true
  engine: sqlite
  refresh_mode: append
  refresh_interval: 1m
  time_column: updated_at

​
Snapshots

Bootstrap from S3 snapshots for fast cold starts: 
acceleration:
  enabled: true
  engine: sqlite
  mode: file
  snapshot:
    enabled: true
    source: s3://my-bucket/snapshots/my_dataset/
    refresh: true

​
Busy Timeout

Configure timeout for locked database: 
params:
  busy_timeout: 10000  # 10 seconds (milliseconds)
Default: 5000ms (5 seconds). Increase for high-concurrency workloads.

​
Primary Keys and Constraints

​
Primary Key

acceleration:
  enabled: true
  engine: sqlite
  primary_key: id

​
Composite Primary Key

primary_key:
  - user_id
  - session_id

​
On Conflict Behavior

acceleration:
  enabled: true
  engine: sqlite
  primary_key: id
  on_conflict: upsert

​
Federation

SQLite accelerators can federate queries across multiple file-mode SQLite databases. Spice automatically attaches other SQLite databases. 
datasets:
  - name: orders
    acceleration:
      engine: sqlite
      mode: file
      params:
        sqlite_file: /data/orders.db
  
  - name: customers
    acceleration:
      engine: sqlite
      mode: file
      params:
        sqlite_file: /data/customers.db
Query across both: 
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

​
File Extensions

SQLite supports multiple file extensions:
  • .db
  • .sqlite

​
Decimal Support

SQLite accelerator includes the decimal extension for precise decimal arithmetic: 
SELECT decimal('123.45') + decimal('67.89') as total;
Automatically loaded on initialization.

​
Performance Characteristics

​
Query Performance

OperationPerformanceNotes
Point queriesExcellentRow-based storage, indexes
InsertsExcellentFast single-row inserts
UpdatesExcellentIn-place updates
DeletesExcellentFast row removal
Full table scanFairRow-based (not columnar)
AggregationsFairNot optimized for analytics
JoinsGoodSmall to medium joins

​
Storage

FeatureDetails
FormatRow-based (B-tree)
CompressionNone (compact format)
Max file size~140TB (theoretical), <10GB ideal
ACIDFull ACID compliance

​
Transactions

SQLite provides full ACID transactions: 
BEGIN TRANSACTION;
  INSERT INTO orders (id, customer_id, amount) VALUES (1, 100, 50.00);
  UPDATE customers SET total_orders = total_orders + 1 WHERE id = 100;
COMMIT;

​
Write-Ahead Logging (WAL)

SQLite uses WAL mode for better concurrency:
  • Multiple readers don’t block writers
  • Better performance for concurrent access
  • Automatic checkpointing

​
Example Configurations

​
User Session Store

datasets:
  - name: sessions
    from: redis://localhost:6379/sessions
    acceleration:
      enabled: true
      engine: sqlite
      mode: memory
      refresh_interval: 30s
      primary_key: session_id

​
Transactional Cache

datasets:
  - name: inventory
    from: mysql://db/inventory
    acceleration:
      enabled: true
      engine: sqlite
      mode: file
      params:
        sqlite_file: /data/inventory.db
        busy_timeout: 10000
      primary_key: product_id
      on_conflict: upsert
      refresh_interval: 1m

​
Persistent Lookup Table

datasets:
  - name: product_catalog
    from: postgres://db/products
    acceleration:
      enabled: true
      engine: sqlite
      mode: file
      params:
        sqlite_file: /data/products.db
      snapshot:
        enabled: true
        source: s3://snapshots/products/
      refresh_interval: 1h

​
Monitoring

-- File size
SELECT 
  dataset_name,
  file_size_bytes / 1024 / 1024 as size_mb
FROM runtime.metrics
WHERE name = 'acceleration_file_size';

-- Row count
SELECT 
  dataset_name,
  value as row_count
FROM runtime.metrics
WHERE name = 'acceleration_rows';

​
Parameters

ParameterTypeDescriptionDefault
sqlite_filestringPath to SQLite database fileauto
busy_timeoutintegerTimeout in milliseconds for locked DB5000

​
Limitations

  • Row-based storage (not ideal for analytics)
  • Single-file database
  • No built-in partitioning
  • Limited to ~10GB for best performance
  • Write concurrency limited (single writer at a time)

​
When to Use DuckDB Instead

Consider DuckDB if:
  • Analytical queries with aggregations
  • Dataset > 10GB
  • Need columnar compression
  • Read-heavy workload

​
When to Use PostgreSQL Instead

Consider PostgreSQL if:
  • Need advanced database features (triggers, stored procedures)
  • Multi-user concurrent writes
  • Dataset > 100GB
  • Need replication

​
Next Steps

Build docs developers (and LLMs) love

Get started for freeTalk to us