> 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.

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

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.

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.

> 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.

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.

(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.

> 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.

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.

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.

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.

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.

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.

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.