Rearchitecting: Redis to SQLite

SQLite for distribution is neat. FWIW - this is at least partially inspired by your datasette project which we may still try and do something with later on the reporting and data exploration side of things.

> I’m really interested in this model where each application server has a copy of a SQLite database file which is then replaced on a scheduled basis.

BTW, this is also the model used by all CDNs, where the global configuration file containing the certificates, HTTP routing rules etc. for all customers will be updated into into a single-file b-tree structure*, and that "bundle" is distributed among all edge locations frequently.

* I'm yet to see someone use sqlite for this purpose, it's usually DBM style databases like LMDB or Kyoto Cabinet.

> Feature flags can be checked dozens of times per request

My strategy for resolving this is to fetch the flag value once, but to store it in the request object, so that a) you never have to take the expensive lookup hit more than once per request, and b) there's no risk of an inconsistent value if the flag is updated mid-request.

> a SQLite database file which is then replaced on a scheduled basis.

You could look into WAL replication if you wanted an efficient way to update the copies. Something like Litestream.

> Feature flags can be checked dozens of times per request and often need the kind of queries (user is a member of group A and has an IP located in country B) which could be well served by a local SQLite - and feature flags have a tolerance for updates taking a few seconds (or longer) to roll out.

This doesn't sound right. A feature flag only requires checking if a request comes from a user that is in a specific feature group. This is a single key:value check.

The business logic lies in assigning a user to a specific feature group, which the simplest way means pre assigning the user and in the most complex cases takes place at app start/first request to dynamically control dialups.

Either way, it's a single key: value check where the key is user ID+feature ID, or session ID + feature ID.

I mean, I guess you can send a boat load of data to perform the same complex query over and over again. I suppose. But you need to not have invested any thought onto the issue and insisted in making things very hard for you, QAs, and users too. I mean, read your own description: why are you making the exact same complex query over and over and over again, multiple times in the same request? At most, do it once, cache the result, and from therein just do a key:value check. You can use sqlite for that if you'd like.

I've wanted to implement this on a distributed web server environment I manage. Right now there's a centralized MySQL database that the web servers read from when rendering a web page, but there can be lots of queries for a single render (page, sections, snippets, attributes, assets, etc.), and sending that all over the wire, while fast, is slower than reading from a database running on the same host. It'd be great to be able to copy the "master" database onto each web server instance, maybe once per minute, or just on-demand when a change to the data is made. I imagine this would make reads much faster.

We used this model to distribute translations, feature flags, configuration, search indexes, etc at Airbnb. But instead of SQLite we used Sparkey, a KV file format developed by Spotify. In early years there was a Cron job on every box that pulled that service’s thingies; then once we switched to Kubernetes we used a deamomset & host tagging (taints?) to pull a variety of thingies to each host and then ensure the services that use the thingies only ran on the hosts that had the thingies.

In Ruby we called this “hammerspace” https://github.com/airbnb/hammerspace

rqlite[1] could basically do this, if you use read-only nodes[2]. But it's not quite a drop-in replacement for SQLite at the write-side. But from point of view of a clients at the edge, they see a SQLite database being updated which they can directly read[3].

That said, it may not be practical to have hundreds of read-only nodes, but for moderate-size needs, should work fine.

Disclaimer: I'm the creator of rqlite.

[1] https://rqlite.io/

[2] https://rqlite.io/docs/clustering/read-only-nodes/

[3] https://rqlite.io/docs/guides/direct-access/

This is the type of architecture we use for feature flagging, but it's just a JSON file.

We use something similar for our event based databases. We project millions of events into an in-memory object (usually a dictionary), and periodically persist that state as json in S3. It's guaranteed to be consistent across all service instances because the persisted state contains the event stream positions to catchup from. The only drawback of course is that it can use up to several GB of memory depending on how big the state is, but it's extremely fast and doing in-memory operations is trivial compared to using SQL or a library with api calls.

I’ve utilized this in Lambda@Edge for use case half feature flag-y, half HTTP routing/proxy serving rules as mentioned in a sibling comment. Lambdas pick up ~50-200MB of data on first boot, and keep their copy through their lifecycle.

As requests come in, gather their features, convert to effectively an int vector, filter for row where match and serve request

You may want to check out LiteFS and LiteStream by benbjohnson. There was a time in 2022 where he was trending every week for his projects. The following snippet is taken from the LiteFS webpage. "LiteFS is a distributed file system that transparently replicates SQLite databases. You can run your application like it’s running against a local on-disk SQLite database but behind the scenes the database is replicated to all the nodes in your cluster."

In a past life, I used this pattern in hadoop mapreduce clusters. A job would do "small-data" sql queries locally to pull configs, maps of facts related to the business domain, then pack them into sqlite db files and ship them up with the job. Hadoop already has a process called "job localization" where it can download files sent up with the job down to the PWD of the mapper/reducer. And then the mapper/reducer can use it read-only while doing big-data things.

Having now spent enough years thinking about feature flags during an extended migration (from our own internal system to LaunchDarkly), I've become convinced that a really useful primitive would be:

* SQLite, but every change you make increments a global version counter.

* Some way to retrieve and/or apply just the changes between version counter numbers.

Then, you could layer all manner of distribution mechanisms on top of that. You could imagine gossip networks, an explicit tree-shaped cache/distribution network, etc. Anyone who has a later version than you would be able to give you the update.

What would this get you?

* You can bundle the current version of a DB into your app artifact, but efficiently update it once your app runs. This would let you fall back to much better defaults than having no data at all. (eg. For feature flags or live config, this would let you fall back to a recent state rather than code-level defaults.)

* Any kind of client can send the global counter as an ETAG and get just the delta

* Reconnections if the network blips are really simple and cheap

* If the system also let you keep a couple of minutes of history, you could annotate the counter onto a microservice call (using W3C Baggage headers, for example), and evaluate your flags/config/data/whatever at a single version number across multiple microservices. Even without evaluate-at-time functionality, logging the generation number would help a lot when debugging what happened after the fact

Why sqlite but not something like Rocksdb which is optimised for NVMe.

While SQLite can be used on SSDs, it is not as heavily optimized for SSDs as RocksDB. SQLite is a general-purpose embedded database and was primarily designed for use on devices like mobile phones or lightweight embedded systems, where storage is often slower and smaller (e.g., flash storage or basic SSDs).

SQLite’s traditional B-tree structure involves more random I/O compared to RocksDB's LSM tree, which is less ideal for maximizing the performance of high-speed SSDs like NVMe.

SQLite’s limited concurrency (single-writer, multiple-readers) also means it cannot fully utilize the parallelism and high throughput potential of NVMe SSDs.

My experience with Redis is similar, where it often becomes a trap because people misunderstand it's strengths and weaknesses.

I think it's best to consider Redis a cache with richer primitives. It excels at this and used appropriately will be both fast and solid.

But then people start wanting to use it for things that don't fit into the primary rdbms. Soon you have a job queue, locks of various sorts, etc. And then it just becomes a matter of time until performance crosses a cliff, or the thing falls down for some other reason, and you're left with a pretty ugly mess to restore things, usually resulting in just accepting some data loss.

It takes some discipline to avoid this, because it happens easily by increments.

As for SQLite's performance, besides avoiding network overhead, a lot of people underestimate serialization and deserialization costs. Even though Redis uses a pretty minimalist protocol it adds up. With SQLite a lot of things boil down to an in process memcopy.

(article author here) - yes 100% and I hope that came through in the article that this is great solution given our particular use case and that it's not a 1:1 swap out of Redis or Postgres.

> Other alternatives may use static files loaded in-memory, but I'm guessing the data is more than you'd want to keep in memory in this case, making SQLite a nice alternative.

Ultimately a RDBMS like SQLite is what you'd get if you start with loading static files into memory and from that point onward you add the necessary and sufficient features you need to get it to work for the most common usecases. Except it's rock solid, very performant, and exceptionally tested out.

I think it's purely a simplicity thing.

Right now, most rails setups with decent traffic will have frontend boxes, a sql db, a KV store (redis or memcached), and a cache store pointed at the kv store, with, annoyingly, very different usage patterns than typical KV store usage, eg for maintaining api quotas or rate limiting.

Disk performance has gotten fast enough and SQL performance has gotten good enough that there's a movement to drop the KV store and split the usages (for traditional KV use and also backing a cache) to the sql db and disk, respectively. Plus new nvme disks are almost as fast and still much cheaper than ram so you can cache more.

It's a little YAGNI - I think the biggest driver of Redis in community was for exactly what you described aysnc jobs and the tool most folks reached for was Sidekiq.

The 2024 Rails community survey just came out and Redis is still listed as the top datastore that people use in their apps.

FWIW - we found that while many folks are _using_ Redis in their apps, they're just using it for things like Sidekiq and not actually taking advantage of it for holding things like real time leaderboards, vector db functions, etc. so it's a little fuzzy the actual usage.

I’ve found it useful in the past as basically very-smart (i.e. stuff like expiration built-in) shared memory. Potentially with clustering (so, shared across multiple machines).

In the era of k8s, and redis-as-a-service, though? It’s gonna be “shared memory” on another VM on another rack. At that point, just read & write a damn file off S3, you’ve already abandoned all hope of efficient use of resources.

More people should be using RocksDB as SSD these days are fast and you can have much larger KV store running off of it.

SQLite has its vacuum operation, which is kind-of like running a garbage collection. Every time I read the docs about when to run a vacuum, I end up confused.

The last time I shipped an application on SQLite, I ended up just using a counter and vacuuming after a large number of write operations.

> The "Fast" part is a little funny. If you don't care about joins, then row inserts and retrievals are pretty damn fast too =)

What makes SQLite exceptionally fast in a server environment is that you do not require a network call to do the query or even retrieve the data. Your remarks about joins and transactions are meaningless once you understand you're just reading stuff from your very own local HD, which is already orders of magnitude faster.

> Every database, Relational or Nonrelational, requires approximately the same level of management and maintenance

I disagree with this statement. Surely there is a difference on the effort for "management and maintenance" if your database requires the operation of independent server processes.

Going to extreme examples, do you really believe it makes no difference whether you use SQLite or Oracle?

Was interested and considering switching until I saw this part:

>According to the benchmarks, Redka is several times slower than Redis.

Still a cool project, don't get me wrong. But this kind of doesn't give me any incentive to switch.

Holy cow this is amazing, I've been looking for something like this for years!!

Thanks for sharing.

(author) - It's genuinely delightful to know that you liked it.

That's interesting, but it probably wouldn't work for our use case as we'd need to ship that binary utility to the platforms (unless I'm missing something).

In Redis, the data is a sorted-set that we forced into being lexicographically ordered by setting all the scores to 0. We went through a lot of iterations of it and to be clear it's not _slow_ it's just not as fast as essentially `fopen`

1 - Redis sorted sets - https://redis.io/docs/latest/develop/data-types/sorted-sets/

I do agree it is somewhat fishy of the large performance difference not being explained by comparatively fundamentally poor data access patterns.

However, Redis runs as an out of process server with marshaling and unmarshaling of data across sockets. SQLite is in process and with a prepared query is basically one library call to a purpose built data access VM. So I’m not sure why it would be hard to believe this cache and TLB friendly setup can beat Redis.

Just look at their use case. The have to store a large amount of logs of web-sites visitors or bots. Of course Redis is a very bad choice, because most servers don't have that much amount of memory. It's really useless to store logs in the RAM.

Post author and Wafris co-founder here. Conceptually "rate limiting to prevent abuse" (what we're doing here) and "rate limiting for API throttling" have different levels for tolerance.

With that in mind, it's setting higher levels of limiting and doing the math to push that out over many machines/instances/dynos. That helps for things like scraping prevention, etc.

For issues like credential stuffing attacks, you'd want a lower limit but also coupled with mitigations like IP bans, IP reputation, etc. to deal with underlying issue.

There's Redka: https://github.com/nalgeon/redka/

You can use smoothmq (SQS over sqlite) for a celery backend: https://smoothmq.com/

It would be a small command log (a batch of requested changes from that client) with a completely different schema from the main database.

But if we are sending deltas to a central server performance isn't critical: there can be a traditional web service to call, without uploading databases.

This is something we seriously considered. The SQLite dbs are several hundred megabytes in size (millions of IP ranges) so while it would be technically doable to send around rules files as JSON or something more specifically suited there's still a number of wins that SQLite gives us:

- Really strong support across multiple platforms (we have clients for most of the major web frameworks)

- Efficiency, sure we have lots of RAM on servers nowdays but on some platforms it's constrained and if you don't have to burn it, we'd just rather not.

- When we started mapping this out, we ended up with something that looked like a JSON format that we were adding indexes to....and then we were re-inventing SQLite.

I don't know how it works exactly, but I believe you can have a fully in-memory SQLite database. Bun's sqlite library and SqlAlchemy both let you operate on in-memory SQLite db's which you can then write to disk.

Edit: reading the docs it looks like it operates the same way, just reading sections of the db from memory instead of disk

https://www.sqlite.org/atomiccommit.html

The sqlite db is on disk sync'd down to the clients from our service.

The client is responsible for checking with our servers and, if rule updates are found, downloading a new database file. To avoid locking and contention issues, these are each uniquely named, and which DB is "current" is just updated.

Note: This is only in "managed" mode. If you'd rather, you can distribute a SQLite database of the rules alongside your app.

> on disk or in memory somehow?

Due to the magic of the page cache, the answer to that can be "both".

If the sqlite database is being read often and not being written, the page cache will be valid and reads will pretty much never go to the filesystme.

The answer is "yes." We had indexes - but it's also a little more complicated than that, as we're storing IPv4 and IPv6 ranges in a single table in a format _designed_ to be indexed a particular way.

In the article, we refer to this as "decimal lexical" formatting, where we're taking the IPs and making them integers but actually treating them as strings. We're doing this in both Redis with sorted sets and then in a single table in SQLite.

I was going to explain all this in the article, but it was too long already, so it will be a future blog post.

For some reason everyone connects to it over the network now.

As someone who was a relatively-early adopter of it on real servers and hand-managed VMs (i.e. we also controlled the hardware and host OS for the VMs) for a higher traffic site than most of the ones that think they need auto-scaling cloud shit from day one will ever reach, and was/is very enthusiastic about redis, I have exactly no idea why this is a popular way to use it. Cargo-culting and marketing (how else are you gonna sell it as a service?) are all I can figure as the motivations.

Connecting to it over a network is a handy feature for, like, ancillary things that need to connect to it remotely (maybe for reporting or something?) but as the main or only way of using it? Yeah I don’t get it, you’ve just wiped out a ton of its benefits.