Rearchitecting: Redis to SQLite

SQLite as a distributed, read-heavy store

• Many commenters like the pattern: each app instance gets a local, read-only SQLite file that is periodically replaced or updated (e.g., via sync jobs or WAL replication tools).
• This model is seen as a good fit for WAF rules, CDN-style configuration, translations, and other “small-write / heavy-read” data.
• Using SQLite avoids keeping large rule sets fully in memory and leverages the OS page cache for fast reads.

Feature flags and configuration

• Several people see SQLite-as-a-file-format as attractive for feature flags and complex configuration where:
  • Rules depend on user attributes, IP geography, plan type, etc.
  • Slight propagation delay is acceptable.
• Others argue that feature flags should reduce to simple key–value lookups (user/session + flag), with complex logic handled once during group assignment and cached per request.

B-tree vs hash table and DBM-style stores

• Discussion on why B-tree–based formats (SQLite, LMDB, Kyoto Cabinet, RocksDB, etc.) are preferred for on-disk distribution:
  • Hash tables waste space with empty slots, which is costly over the network.
  • B+trees give compact, update-friendly, ordered files.
• Some note similar patterns implemented historically with BerkeleyDB, LMDB, and custom KV formats (e.g., Sparkey).

Performance, query planner, and indexing

• Some report SQLite join and planner quirks: query plans can vary unexpectedly, requiring manual tuning and careful index design.
• Others counter that SQLite’s planner is sophisticated but predictable; performance is good if you design and inspect plans thoughtfully.
• The original use case stores IP ranges with a custom “decimal lexical” representation designed for indexing; details are deferred to future writeups.

Redis vs SQLite tradeoffs

• Debate on why local SQLite can outperform Redis:
  • Redis is out-of-process and often accessed over TCP; even with a local socket there is IPC and serialization overhead.
  • SQLite runs in-process and can be close to “just fopen,” especially for read-mostly workloads.
• Some think the large performance gap might be due to Redis data modeling, but acknowledge the benefits of eliminating network and marshaling costs.
• Others warn Redis often becomes a “junk drawer” (locks, queues, rate limits) and can degrade when misused or hammered by bulk operations.

Operations, maintenance, and alternatives

• SQLite is praised for simplicity: no extra service to run, broad platform support, strong testing.
• There’s discussion of when to run VACUUM (mainly after many deletes, or when free pages exceed a threshold).
• Alternatives mentioned for similar roles: RocksDB, LMDB, LiteFS/Litestream, rqlite, JSON files, and Redis-over-SQLite shims like Redka or SQS-over-SQLite systems.