Why SQLite Beats PostgreSQL in Embedded Environments – Version Guide & Concurrency Insights

Overview

SQLite is an embedded, file‑based SQL engine first released in 2004. Compared with the heavyweight server‑oriented PostgreSQL, SQLite can be faster for terminal‑level applications where a lightweight database is required.

SQLite version timeline

Key releases:

3.0 (2004) – initial B‑tree engine, POSIX/Windows NT support.

3.6.x (2008) – added WAL mode, required reliable file‑lock.

3.7.x (2010) – WAL stable, mmap support.

3.8.x (2013) – query‑planner improvements, partial and covering indexes.

3.9‑3.15 (2015‑2016) – JSON1 extension, full UTF‑8.

3.24+ (2018) – UPSERT support, better concurrency.

3.33+ (2020) – files >2 GB, improved filesystem sync.

3.39+ (2022) – STRICT table mode.

3.45+ (2024) – JSON5, decimal precision, current stable series.

OS‑specific SQLite bundles

Recent OS releases ship SQLite 3.45 (macOS Sonoma, iOS 17, Android 14) and earlier versions on older releases (e.g., macOS Catalina 3.28, Windows 10 3.39).

Concurrency model

SQLite provides two journal modes:

rollback‑journal – a single lock blocks both reads and writes; performance degrades under concurrent workloads.

WAL (Write‑Ahead Logging) – readers access the database concurrently while writers use a separate lock, greatly improving parallelism.

Simple concurrency test

# writer.sql
BEGIN TRANSACTION;
INSERT INTO test_data (name, value)
VALUES ('writer_' || random(), abs(random() % 100000));
COMMIT;

# reader.sql
SELECT count(*) FROM test_data
WHERE value > (abs(random() % 50000));

# run_test.sh
#!/bin/bash
for i in {1..50}; do
  while true; do
    sqlite3 /data/test.db < writer.sql
  done &
done
for i in {1..50}; do
  while true; do
    sqlite3 /data/test.db < reader.sql
  done &
done

Running the script with the default rollback‑journal mode produces a flood of “database is locked” errors, confirming the limitation.

Recommendation

For modern embedded and terminal applications, use SQLite 3.45 (or later) built with PRAGMA journal_mode=WAL;. It offers UPSERT, JSON5, improved file‑size handling (>2 GB), and full compatibility with current macOS, iOS, Android, and Linux distributions (e.g., Rocky Linux 8.10).

References

SQLite release history: https://www.sqlite.org/src/taglist

Official SQLite documentation.