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.<p>Here it’s being used for web application firewall rules.<p>Another place I’ve thought about using this is feature flag configuration. 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.
Apart from network latency, one of the behaviours I've seen with Redis is that reads/write latencies are fairly linearly proportional to the amount of keys queried - which seems to be shown in your chart as well.<p>We had a different problem, where our monolithic app used both Postgres / Redis for different use cases and worked relatively well. However - it was a lot easier to shove new functionality in the shared Redis cluster. Because Redis is single-threaded, one inconsiderate feature that does bulk reads (100K+ keys) may start to slow down other things. One of the guidelines I proposed was that Redis is really good when we're reading/writing a key, or small fixed-cardinality set of keys at a time, because we have a lot of random things using Redis (things like locks and rate limits on popular endpoints, etc).<p>However, in your case, I'm guessing Redis shines in the case of a naive single-key (IP address) lookup, but also doesn't do well with more complicated reads (representing your range query representation?). Cool write up overall, I don't have a deeper understanding of how SQLite performs so well when compared to a local Redis instance, so that was unexpected and interesting to observe.
Somewhat related: for the Neon internal hackathon a few weeks ago I wrote a little Node.js server that turns Redis's wire protocol (RESP) into Postgres queries. Very fun hack project: <a href="https://github.com/btholt/redis-to-postgres">https://github.com/btholt/redis-to-postgres</a>
It sounds like a niche use case where SQLite does work quite well server-side without needing any replication, since the database is read-only.<p>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.
> Further, when we exhibited at RailsWorld 2023, there was a definite "blood in the water" vibe regarding Redis and the assumption that you'd automatically need a Redis server running alongside your Rails application.<p>I've only worked on one production Rails application in my career (and it did use Redis!), so I'm way out of the loop – is the ecosystem turning against Redis from a business perspective (I know there have been some license changes), or is it a YAGNI situation, or something else?<p>IIRC we used it mainly with Rescue to schedule asynchronous jobs like indexing, transcoding, etc., but it seemed like a neat tool at the time.
>While Redis is "fast" in comparison to traditional RDBMS, it's still a database that you have to manage connections, memory, processes, etc., which introduces more brittleness into the stack (the opposite of what we're trying to achieve).<p>Every database, Relational or Nonrelational, requires approximately the same level of management and maintenance when you start dealing with non-toy levels of transactions.<p>The "Fast" part is a little funny. If you don't care about joins, then row inserts and retrievals are pretty damn fast too =)
People reading this might be interested in Redka - Redis re-implemented with SQLite in Go: <a href="https://github.com/nalgeon/redka">https://github.com/nalgeon/redka</a>
Could you clarify, please Redis usage?<p>v1
1) In v1 they had waf and redis on the same server
2) Client went to the admin panel to set new rules
3) Rules went to redis that is on the same server with admin panel
4) Thanks to redis internal synchronization mechanism rules were updated to all of the redises(that are stand locally with waf all over the globe)
5) When new request come to some waf, waf verified request/ip with updated redis rules
Do I understand v1 correctly? Redis infrastructure was used to spread new rules by itself?<p>v2:
1) They deleted the redis cluster
2) Every waf server now has sqlite db
3) They made some synchronization mechanism to spread new rules from admin panel to every server that contains waf and sqlite
4) When a new request comes to some waf, waf verifies request/ip with updated sqlite rules. And that is very fast!<p>That is the case?
The dataset is 1.2 million entries, which looks big, but really is not that much.<p>If this is uncompressed IPv4 addresses, it's just 4.8 MB; and with some trival compression (like a 2-level trie), it could be about 2x smaller. Even if it's uncompressed IPv6, that's still just 32 megabytes.<p>Does Ruby support mmap? If yes, I'd suggest direct IP list. Lots of fun to write, big speedup over sqlite, and zero startup time.
Might want to check into this to do your SQLite db copies:<p><a href="https://www.sqlite.org/draft/rsync.html" rel="nofollow">https://www.sqlite.org/draft/rsync.html</a>
I have a hard time believing that Redis local was beat by SQLite local unless the workload was poorly fit for Redis structures, or the integration code wasn't well written.<p>But always happy to see a discovery of a better solution. I agree removing the network is a win.
Nice post! I’m curious how the SQLite-per-instance model works for rate-limiting in the scale-out scenario. I took a cursory glance at the docs but nothing jumped out at me about how it works.
I really wish there were a compatibility layer that could sit on top of SQLite and make it pretend to be redis, so we could switch more things to use SQLite. It doesn't even need to satisfy all the distributed systems guarantess or even implement proper pub/sub, it could just do everything with polling and a single event loop. It would be great for smaller projects that want to run something like celery or any app that depends on redis without needing to install redis.
NICE!<p>I have not used Redis myself, but have been using Sqlite more and more over the years.. and found a perfect application I wrote using Sqlite under the hood.<p>Powerful and convienient database system!
How large is the SQLite database you're syncing?<p>Is it even "worth" using SQLite at this point? What about a configuration file, and straight-up code that works with in-memory data structures?
> Even if the SQLite performance was significantly worse (like 2x worse) in the benchmark, it would still probably be faster in the "real world" because of network latency, even to a Redis that was in the same data center/region<p>... Why not run redis on localhost?
FoundationDB<p>Isn’t “redis to sqlite” effectively what foundationDB?<p><a href="https://www.foundationdb.org/" rel="nofollow">https://www.foundationdb.org/</a>