SQLite At The Edge: Choosing A Local Database Persistence Layer

Rust, SQLX and SQLite for edge computing

"Are you sure SQLite is the right choice for edge computing? But what about the async writing limitations?"

I heard this question just recently in a discussion, and it made me pause. Because I knew that it worked, but maybe their doubts were justified? So I looked into the optimisations that went into the implementation, for a Rust based service that handled 10k ops/s.

Let me show you why SQLite isn't just a viable choice for edge computing, but it's often the best one. And when it isn't. But first, let's check the performance claims you'll see online and what they actually mean in practice.

I've run benchmarks to revalidate my claims. The results? SQLite delivered 120,000+ operations/second - far exceeding typical edge computing requirements. Take it with a grain of salt, since the payloads were quite small. Real world scenarios might contain larger JSON payloads for example.

TL;DR

SQLite can handle serious edge computing workloads (up to 120K+ operations/second proven in benchmarks) with proper optimisations, but context matters.
Most online performance claims lack important caveats about hardware and configuration requirements.
Distributed alternatives like TursoDB have their place but aren't automatically better or needed. Choose based on actual requirements like geographic sync needs.
Start local, measure performance, scale when you have evidence of limitations.

Let me tell you why SQLite isn't just a viable choice for edge computing. It's often the superior one. And when it isn't, I'll show you exactly how to recongise those scenarios and what to do about them. But first, let's be honest about the performance claims you'll see online and what they actually mean in practice.

The Edge Computing Reality

At Weber Food Technology GmbH we build a Rust-based monitoring agent for their industrial food processing equipment and third party machines. The requirements were clear: Handle large amounts of messages of varying payloads, operate reliably in factory environments with spotty internet connectivity, and provide offline capability.

The typical "cloud-first" approach would suggest shipping all this data to a distributed database cluster. But here's what that would have looked like in practice:

250ms+ latency for every database query over the internet (seen in this blog post)
Complete system failure during network outages (which happen regularly in industrial settings)
Mobile data costs that would make the CFO cry in the rain (depending on the data size and usage)
Compliance headaches shipping sensitive production data off-site

All of these do not include the IT Admin screaming at you to not expose anything to the WWW.

Instead of dealing with this, we went with SQLite running locally on a decent edge device. The result? Sub-millisecond query times, 99.9% guaranteed uptime even during network failures, and a system that could process messages faster than the logic controllers could generate it.

SQLite's Real Performance at the Edge

Let's talk numbers, but let's be realistic about them. The performance claims you see for SQLite online often represent optimal scenarios. SSD storage, proper optimisations, and ideal conditions (also reproduced by my benchmarks). Here's what we actually achieved in production and what you can realistically expect.

Achievable Performance (With Caveats)

I've adapted parts of the implementation for example purposes and showing Iou how to implement SQLite optimisations with the amazing SQLX crate.

use sqlx::{Row, Sqlite, Transaction};
use serde::{Deserialize, Serialize};
use std::time::Instant;
use uuid::Uuid;

#[derive(Debug, Serialize, Deserialize)]
pub struct SensorReading {
    pub device_id: String,
    pub timestamp: i64,
    pub temperature: f64,
    pub pressure: f64,
    pub flow_rate: f64,
}

impl SensorReading {
    /// Insert batch sensor readings with performance measurement
    /// Generic over any SQLx SQLite executor (connection, transaction, pool)
    pub async fn batch_insert<T>(
        executor: &mut T,
        readings: &[SensorReading],
    ) -> Result<(usize, std::time::Duration), sqlx::Error>
    where
        for<'e> &'e mut T: sqlx::Executor<'e, Database = Sqlite>,
    {
        let start = Instant::now();
        let mut inserted_count = 0;

        for reading in readings {
            sqlx::query!(
                r#"
                INSERT INTO sensor_readings (device_id, timestamp, temperature, pressure, flow_rate)
                VALUES (?1, ?2, ?3, ?4, ?5)
                "#,
                reading.device_id,
                reading.timestamp,
                reading.temperature,
                reading.pressure,
                reading.flow_rate
            )
            .execute(&mut *executor)
            .await?;

            inserted_count += 1;
        }

        let duration = start.elapsed();
        dbg!(
            "Batch insert: {} records in {}μs (avg: {}μs/record)",
            inserted_count,
            duration.as_micros(),
            duration.as_micros() / inserted_count as u128
        );


        Ok((inserted_count, duration))
    }

    pub async fn batch_insert_tx<T>(
        executor: &mut T,
        readings: &[SensorReading],
    ) -> Result<(usize, std::time::Duration), sqlx::Error>
    where
        for<'e> &'e mut T: sqlx::Executor<'e, Database = Sqlite>,
    {
        let start = Instant::now();

        // Begin explicit transaction for batch operation
        let mut tx = executor.begin().await?;
        let mut inserted_count = 0;

        for reading in readings {
            sqlx::query!(
                r#"
                INSERT INTO sensor_readings (device_id, timestamp, temperature, pressure, flow_rate)
                VALUES (?1, ?2, ?3, ?4, ?5)
                "#,
                reading.device_id,
                reading.timestamp,
                reading.temperature,
                reading.pressure,
                reading.flow_rate
            )
            .execute(&mut *tx)
            .await?;

            inserted_count += 1;
        }

        tx.commit().await?;
        let duration = start.elapsed();

        dbg!(
            "Transactional batch: {} records in {}μs (avg: {}μs/record)",
            inserted_count,
            duration.as_micros(),
            duration.as_micros() / inserted_count as u128
        );

        Ok((inserted_count, duration))
    }
}

In our deployments, we consistently saw:

Write latency: 0.8-1.2ms per sensor reading (on enterprise SSDs with optimized settings)
Read latency: 0.3-0.6ms for dashboard queries (simple indexed lookups)
Throughput: 10,000+ ops/second sustained (using batch transactions and dedicated pools)

These numbers align with documented SQLite benchmarks showing 25K-96K inserts/second under optimal conditions, and my own benchmarks. But caveat: They're only achievable with proper optimization and hardware.

Benchmark Validation

To validate the 10k/ops/s claim, I setup my own benchmarks and replicated the same Rust + SQLx implementation seen in the code above. The results exceeded the 10k/ops/s 12x fold, but take this with a grain of salt:

Batch Insert Performance

Dataset Size	Operations/Second	Latency per Record
5,000 records	133,711	~7.4 μs
25,000 records	126,550	~7.9 μs
100,000 records	127,465	~7.8 μs

⚡ Single Insert Performance

Dataset Size	Operations/Second	Latency per Insert
Small (5K)	15,806	~63 μs
Medium (25K)	14,514	~69 μs
Large (100K)	15,104	~66 μs

📖 Concurrent Read Performance

Concurrent Readers	Operations/Second	Query Latency
1 reader	~1,350	~740 μs
5 readers	~685	~1.4 ms
10 readers	~460	~2.2 ms
20 readers	~345	~2.9 ms

Notable::

Batch processing provides 8-10x performance improvement
Performance remains consistent across dataset sizes
Zero "database locked" errors with proper dual-pool architecture
Sub-millisecond queries confirmed for single readers

The Critical Optimizations

Taken from the previously mentioned blog post we've further optimised the connection handling, including various PRAGMA queries and dedicated pools as mentioned above. Here's an excerpt from the codebase containing the SQLite configuration that made these numbers possible, including a crucial pattern for handling SQLite's single-writer limitation:

use sqlx::{sqlite::SqlitePoolOptions, SqlitePool, Sqlite, Row};
use std::time::Duration;
use anyhow::Result;

pub struct EdgeDatabase {
    read_pool: SqlitePool,
    write_pool: SqlitePool,
}

impl EdgeDatabase {
    /// Initialize dual-pool SQLite setup optimized for edge computing
    ///
    /// Key insight: Use separate pools to handle SQLite's single-writer limitation:
    /// - Read pool: Many connections for concurrent dashboard queries
    /// - Write pool: Single connection to avoid "database is locked" errors
    pub async fn new(database_url: &str) -> Result<Self> {
        // Read-only pool with high concurrency for sensor data queries
        let read_pool = SqlitePoolOptions::new()
            .max_connections(20)  // Large number for concurrent reads
            .min_connections(5)
            .acquire_timeout(Duration::from_secs(30))
            .idle_timeout(Some(Duration::from_secs(600)))
            .max_lifetime(Some(Duration::from_secs(1800)))
            .connect(&format!("{}?mode=ro", database_url))
            .await?;

        // Write pool with single connection to handle SQLite's single-writer constraint
        let write_pool = SqlitePoolOptions::new()
            .max_connections(1)   // Critical: Only 1 connection for writes
            .min_connections(1)
            .acquire_timeout(Duration::from_secs(30))
            .idle_timeout(Some(Duration::from_secs(600)))
            .max_lifetime(Some(Duration::from_secs(1800)))
            .connect(database_url)
            .await?;

        // Apply optimizations to both pools
        Self::apply_read_optimizations(&read_pool).await?;
        Self::apply_write_optimizations(&write_pool).await?;

        Ok(Self { read_pool, write_pool })
    }

    /// Optimizations for read-only connections
    async fn apply_read_optimizations(pool: &SqlitePool) -> Result<()> {
        let mut conn = pool.acquire().await?;

        // Essential optimizations documented at https://www.sqlite.org/pragma.html
        sqlx::query("PRAGMA journal_mode = WAL")
            .execute(&mut *conn).await?;

        // Read-only optimizations
        sqlx::query("PRAGMA query_only = ON")
            .execute(&mut *conn).await?;

        // 64MB cache for sensor data workloads
        sqlx::query("PRAGMA cache_size = -64000")
            .execute(&mut *conn).await?;

        // 128MB memory-mapped I/O for large datasets
        sqlx::query("PRAGMA mmap_size = 134217728")
            .execute(&mut *conn).await?;

        println!("Read pool optimizations applied");
        Ok(())
    }

    /// Critical optimizations for write connections
    async fn apply_write_optimizations(pool: &SqlitePool) -> Result<()> {
        let mut conn = pool.acquire().await?;

        // WAL mode: enables concurrent readers during writes
        sqlx::query("PRAGMA journal_mode = WAL")
            .execute(&mut *conn).await?;

        // Balanced durability/speed for edge environments
        sqlx::query("PRAGMA synchronous = NORMAL")
            .execute(&mut *conn).await?;

        // CRITICAL: Set busy timeout to handle occasional lock contention
        // Even with pool size of 1, this prevents "database is locked" errors
        sqlx::query("PRAGMA busy_timeout = 5000")
            .execute(&mut *conn).await?;

        // 64MB cache for write operations
        sqlx::query("PRAGMA cache_size = -64000")
            .execute(&mut *conn).await?;

        // In-memory temporary tables and indexes
        sqlx::query("PRAGMA temp_store = MEMORY")
            .execute(&mut *conn).await?;

        // 128MB memory-mapped I/O
        sqlx::query("PRAGMA mmap_size = 134217728")
            .execute(&mut *conn).await?;

        println!("Write pool optimizations applied");
        Ok(())
    }

    /// High-performance batch sensor insert with IMMEDIATE locking
    pub async fn batch_insert_sensors(
        &self,
        readings: &[SensorReading],
    ) -> Result<Duration> {
        let start = std::time::Instant::now();

        // Use write pool with single connection
        let mut tx = self.write_pool.begin().await?;

        // CRITICAL: Set transaction locking mode to IMMEDIATE
        // This prevents other transactions from starting until this one completes
        sqlx::query("BEGIN IMMEDIATE")
            .execute(&mut *tx).await?;

        // Process in chunks to avoid memory pressure
        for chunk in readings.chunks(1000) {
            for reading in chunk {
                sqlx::query!(
                    r#"
                    INSERT INTO sensor_readings (device_id, timestamp, temperature, pressure, flow_rate)
                    VALUES (?1, ?2, ?3, ?4, ?5)
                    "#,
                    reading.device_id,
                    reading.timestamp,
                    reading.temperature,
                    reading.pressure,
                    reading.flow_rate
                )
                .execute(&mut *tx)
                .await?;
            }
        }

        tx.commit().await?;
        let duration = start.elapsed();

        println!(
            "Batch inserted {} sensor readings in {}ms ({}μs/record)",
            readings.len(),
            duration.as_millis(),
            duration.as_micros() / readings.len() as u128
        );

        Ok(duration)
    }

    /// Get read pool for application queries
    pub fn read_pool(&self) -> &SqlitePool {
        &self.read_pool
    }

    /// Get write pool for application inserts/updates
    pub fn write_pool(&self) -> &SqlitePool {
        &self.write_pool
    }
}

#[derive(Debug)]
pub struct SensorAggregates {
    pub device_id: String,
    pub avg_temperature: f64,
    pub max_pressure: f64,
    pub avg_flow_rate: f64,
}

Quick takeaways:

Dual pool architecture: Read pool with 20 connections for concurrent dashboard queries, write pool with 1 connection to eliminate lock contention
IMMEDIATE transaction locking: Prevents other transactions from interfering with batch operations
Busy timeout of 5000ms: Essential even with single write connection - prevents occasional "database is locked" errors
Transaction usage for multi-query methods: Ensures consistency for related sensor data aggregations

Important: These optimisations assume SSD/NVME/C-Fast storage and local filesystem access. Because WAL mode doesn't work reliably over network storage, and performance on traditional HDDs drops significantly.

What About Concurrency

Here's where we need to address SQLite's single-writer limitation. Yes, it's real, and yes, it matters - but not always in the way you might think.

SQLite handles one write transaction at a time, period. In high-concurrency scenarios with multiple writers, this becomes a bottleneck. But in edge computing scenarios, this limitation is often irrelevant because:

Most edge workloads are single-process: One application collecting sensor data
Write patterns are predictable: Batch inserts from sensor streams
Read/write ratios favor reads: Operations dashboards query historical data

Our service could handle 10,000 messages/second easily because we batched sensor readings and wrote them in transactions, not because we had 10,000 concurrent writers. The benchmarks show this approach can scale up to 120,000+ messages/second with proper optimizations.

When SQLite Becomes the Wrong Choice

There are scenarios where distributed alternatives like TursoDB make sense, and recognizing them early can save you from painful migrations.

The Geographic Distribution Problem

If your edge devices need to share data across multiple locations in real-time, SQLite's local-only nature becomes a liability, take this "pseudo-code":

// This is where SQLite starts to break down
async fn sync_inventory_across_factories() -> Result<(), Box<dyn std::error::Error>> {
    // Factory A updates inventory
    let factory_a_db = Connection::open("factory_a.db")?;
    factory_a_db.execute(
        "UPDATE inventory SET quantity = quantity - 100 WHERE product_id = 'widget_123'",
        [],
    )?;

    // Factory B needs this update IMMEDIATELY for production planning
    // With SQLite: Manual sync, potential conflicts, data staleness
    // With distributed DB: Automatic replication, but added complexity

    Ok(())
}

The Multi-Writer Reality

When you genuinely need multiple processes or devices writing concurrently to shared state. There are existing solutions to this problem made by the creators of TursoDB. I haven't checked them out yet to be fully transparent, but I will soon look into this as well.

Decision Framework For SQLite

After building edge systems for Weber and other industrial clients, here are my criteria's for choosing SQLite. Stick with SQLite when:

Latency requirements: Sub-5ms response times are critical
Network reliability is questionable: Industrial, remote, or mobile environments
Single-process architecture: One application manages the data
Simple deployment wins: Single binary beats infrastructure complexity
Read-heavy workloads: Analytics, monitoring, configuration management

Consider distributed alternatives when:

Geographic synchronization: Multiple locations need real-time data consistency
Genuine multi-writer scenarios: Multiple processes need concurrent write access
Collaborative features: Users across locations modify shared state
Compliance requires centralization: Audit trails and governance needs

Performance Reality Check

Let me share realistic numbers from production deployments, with proper context:

Metric	SQLite (Optimized SSD)	SQLite (Typical HDD)	TursoDB (Edge)	PostgreSQL (Cloud)
Read Latency	0.74ms*	5-15ms	45ms**	150-300ms
Write Latency	0.008ms (batch)*	10-50ms	100ms**	200-400ms
Throughput	120K+ QPS*	1-3K QPS	Varies**	3-8K QPS
Network Dependency	None	None	Sync only	Complete
Deployment Complexity	Single binary	Single binary	Auth + CLI	Full infrastructure

*Validated through comprehensive benchmarking (July 2025)
**Performance varies significantly by geographic location and network conditions

Sources: SQLite performance post, PostgreSQL latency analysis , my own benchmarks.

Conclusio

SQLite isn't a toy database. It's a production-grade engine that powers everything from mobile apps to aerospace systems. The key is understanding when its architectural decisions align with your requirements and when they don't.

For edge computing, where predictable latency, operational simplicity, and network independence matter more than theoretical scalability, SQLite often isn't just good enough. It's optimal.

When you do need to scale beyond SQLite's single-writer limitations, distributed alternatives provide clear migration paths. But make those decisions based on measured requirements, not architectural fashion.

The "Start Simple" Philosophy

Here's the most compelling argument for choosing SQLite initially: you can always migrate later. According to TursoDB's migration documentation, the process is remarkably straightforward:

# Convert to WAL mode (if not already)
sqlite3 your-database.db "PRAGMA journal_mode=WAL;"

# Install Turso CLI
curl -sSL tur.so/install | sh

# Import your entire database in one command
turso db import ~/path/to/your-database.db

That's it. Your entire SQLite database, schema, data, indexes, transfers to TursoDB's distributed infrastructure. No complex migration scripts, no data transformation, no downtime.

This migration simplicity means you can:

Start with SQLite's proven performance for your edge workload
Avoid premature optimization and distributed system complexity
Scale when you have evidence of actual limitations, not theoretical ones
Maintain the same SQL and application code during migration

The risk of choosing SQLite first is essentially zero because the migration path is so clean. The risk of choosing a distributed system first? Operational complexity, debugging challenges, and potential over-engineering for your actual requirements.

Try this approach in your next edge computing project:

Start with SQLite and proper optimization (reference the official docs)
Measure actual performance under realistic load conditions
Monitor for scaling signals: "database locked" errors, latency increases, sync requirements
Migrate to TursoDB only when you have concrete evidence of limitations

Your future self (and your operational complexity) will thank you.

We build way too complex systems more often enough anyway.

Have you built edge computing systems with SQLite? I'd love to hear about your real-world experiences, including the failures and unexpected bottlenecks. DM me on LinkedIn or drop me an e-mail.