SQLite At The Edge: Choosing A Local Database Persistence Layer
In short: SQLite performs surprisingly well for edge computing (up to 120K+ ops/sec). Distributed solutions aren't always better. Start with SQLite locally, measure real performance, then scale only when you hit actual limits.
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 project interview, 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.