LiteFS vs. PostgreSQL Benchmark Suite

This repository contains the source code and infrastructure configuration for a comparative benchmark between a distributed embedded database system (SQLite with LiteFS) and a traditional client-server RDBMS (PostgreSQL).

This project was developed as part of a Bachelor's thesis to evaluate the suitability of "Distributed SQLite" for modern edge-heavy web applications compared to established industry standards.

System Architecture

The benchmark suite is designed to run in a containerized environment (Docker) to simulate realistic network conditions and distributed topologies.

1. PostgreSQL Setup (Client-Server)

• Topology: Classic Primary/Replica architecture with Async Streaming Replication.

• Smart Client Pattern: The benchmark application maintains two separate TCP connection pools:

  • Write Pool: Connected to Primary.
  • Read Pool: Connected to Replica.

• Network Simulation: Uses Linux Traffic Control (tc) to inject artificial latency:

  • ~7ms RTT for Writes (simulating remote region).
  • ~1ms RTT for Reads (simulating local availability zone).

2. LiteFS Setup (Distributed Embedded)

• Topology: Two nodes (Primary and Replica) managed via Consul (Lease Management).
• FUSE Mount: SQLite databases are mounted via the LiteFS FUSE file system.
• Application-Level Forwarding: Since SQLite is single-writer and LiteFS replicas are read-only at the file system level, the benchmark application implements a custom HTTP proxy. Write requests hitting a replica are serialized and forwarded to the Primary node via an internal HTTP API.
• Replication: Asynchronous replication via LTX files.

Getting Started

Prerequisites

• Docker & Docker Compose (V2)
• Go 1.20+ (if running locally without Docker)

Running the PostgreSQL Benchmark

This setup launches a Primary, a Read-Replica, and the Benchmark Runner.

# Start the PostgreSQL cluster
docker-compose -f docker-compose.postgres.yml up --build

# The Web UI will be available at:
# http://localhost:8080

Running the LiteFS Benchmark

This setup launches Consul, a Primary Node, and a Replica Node.

# Start the LiteFS cluster
docker-compose -f docker-compose.litefs-consul.yml up --build

# You can access the application on different nodes:
# Primary Node UI: http://localhost:8081
# Replica Node UI: http://localhost:8082

Note: To test "Data Locality," you should trigger benchmarks from the Replica Node (Port 8082).

Dashboard Controls & Configuration

The web interface allows granular control over the load generator to simulate various traffic patterns. Here is how the individual controls work:

1. Database Seeding (Reset & Reseed)

Before running benchmarks, the database must be populated with a consistent dataset.

• What it does: Resets all tables and inserts a defined number of rows.
• How it works: Uses bulk inserts to generate random user data, with gofakeit.

2. Warmup

• What it does: Runs the configured workload for a set time. Should be run before every normal test.
• How it works: Executes queries against the database to fill internal caches.

3. Read/Write Ratio (Slider)

• What it does: Defines the mix of traffic sent to the database (from 100% Read to 100% Write).
• How it works: The load generator uses a probabilistic distribution. For every request, a random number (0-100) is generated. If it is below the threshold, a Write operation is performed; otherwise, a Read operation is triggered.
  • Example: A setting of 95% Reads means only 5% of requests will result in an INSERT or UPDATE.

4. Query Complexity

Defines which SQL queries are executed during the test.

• Flat / Simple:

  • Goal: Determine maximum theoretical throughput.
  • Details: Executes simple SELECT and INSERT statements on a single table without foreign keys. To avoid caching effects, random rows (ORDER BY RANDOM()) are queried.

• Realistic (Joins & Relational Integrity):

  • Goal: Simulate a real-world web application with relational dependencies.
  • Details: Read operations use JOINs across three tables. Write operations require checking FOREIGN KEY constraints.

• Transactional / ACID (Stress):

  • Goal: Validate atomic handling of multi-step business processes.
  • Details: Simulates an ordering process encapsulating multiple dependent inserts within a single transaction. Since LiteFS uses HTTP forwarding on replicas, interactive transactions cannot be mapped there; this test primarily serves to demonstrate this architectural limitation.

Functional Testing

Beyond raw performance, the suite tests the architectural limits of distributed consistency.

A. Replication Lag (PostgreSQL & LiteFS)

Measures how long it takes for data written to the Primary to appear on the Replica.

• How it works:
  1. The benchmark app writes a unique token (UUID + Timestamp) to the Primary.
  2. It immediately starts polling the Replica (every 10ms) looking for that token.
  3. Result: Time_Seen_On_Replica - Time_Written_On_Primary.

B. Failover & Recovery (LiteFS Only)

Tests the self-healing capabilities of the LiteFS cluster using Consul.

• Note: This test is specifically designed for the LiteFS Replica Dashboard to observe the promotion process.
• How it works:
  1. The benchmark starts a continuous write stream.
  2. User Action: You must manually stop the Primary container (docker stop <primary_container>).
  3. The application detects the failure (HTTP 500 / Timeout).
  4. It waits for the Lease Manager (Consul) to expire the old lease and for the Replica to acquire the lock and promote itself to Primary.
  5. Result (MTTR): The duration from the first failed write to the first successful write on the new leader.

Thesis Benchmark Scenarios

For the purpose of the Bachelor's Thesis, four standardized scenarios were defined and evaluated. These presets can be manually configured using the controls above.

Scenario Name	R/W Ratio	Complexity	Description / Purpose
1. Baseline	95% Read / 5% Write	Flat / Simple	Simulates a high-throughput workload. Used to prove the "Data Locality" advantage of LiteFS (Reads are local syscalls vs. Postgres network calls).
2. Realistic	80% Read / 20% Write	Realistic	Represents a typical E-Commerce or Social Media application. Uses JOINs and Foreign Key constraints to stress the Query Optimizer.
3. Stress Test	50% Read / 50% Write	Realistic	Designed to break the system with high write concurrency. Tests the "Write Bottleneck" in LiteFS, as every write must be forwarded to the Primary, saturating the network.
4. Transactional	50% Read / 50% Write	Transactional / ACID	Uses ACID transactions (BEGIN...COMMIT). Serves as a negative test for LiteFS Replicas, demonstrating that interactive transactions cannot be forwarded via the proxy layer.

Project Structure

├── benchmark/           # Core benchmarking logic
│   ├── runner.go        # Load generator and scenario definitions
│   ├── stats.go         # Metrics calculation (P50, P99, RPS)
│   ├── failover.go      # MTTR measurement logic
│   └── lag.go           # Replication lag measurement
├── cmd/
│   └── web-benchmark/   # Main entry point for the Go application
├── db/                  # Database abstraction layer
│   ├── database.go      # Common interface
│   ├── postgres.go      # pgx driver implementation & Smart Client
│   ├── sqlite.go        # mattn/go-sqlite3 implementation
│   └── litefs.go        # HTTP Write-Forwarding logic
├── docker-config/       # Config files for Postgres, LiteFS, and Consul
├── web/                 # HTML templates and HTTP server for the UI
├── docker-compose.*.yml # Orchestration for different environments
└── Dockerfile.*         # Container definitions

License

This project is open-source and available under the Apache License 2.0.