- 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.
Here it’s being used for web application firewall rules.
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.
by vchynarov
1 subcomments
- 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.
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).
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.
by aquilaFiera
0 subcomment
- 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: https://github.com/btholt/redis-to-postgres
by matharmin
2 subcomments
- 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.
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.
by favorited
4 subcomments
- > 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.
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?
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.
by macspoofing
4 subcomments
- >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).
Every database, Relational or Nonrelational, requires approximately the same level of management and maintenance when you start dealing with non-toy levels of transactions.
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: https://github.com/nalgeon/redka
by tony-allan
0 subcomment
- Best quote:
"SQLite does not compete with client/server databases. SQLite competes with fopen()."
- The dataset is 1.2 million entries, which looks big, but really is not that much.
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.
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.
by codingbot3000
1 subcomments
- It's posts like this explaining architecture decisions in detail I am reading HN for. Thank you!
- Might want to check into this to do your SQLite db copies:
https://www.sqlite.org/draft/rsync.html
by doubleorseven
0 subcomment
- > Benchmarking is a dark art of deceiving yourself with highly precise numbers
.
by dangoodmanUT
3 subcomments
- 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.
But always happy to see a discovery of a better solution. I agree removing the network is a win.
by nikisweeting
2 subcomments
- 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 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.
- Is the benchmark code available somewhere / open source?
- If you need writes, can just use second sqlite database.
- NICE!
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.
Powerful and convienient database system!
- I wish there was a repository with lots of posts like this one. Super useful to learn from!
- How large is the SQLite database you're syncing?
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?
- So is the sqlite file on disk or in memory somehow?
- I visited this site from safari on iOS while being in a Marriott hotel. I am blocked. So the WAF works.
by jszymborski
0 subcomment
- A bit strange they replaced Redis with SQLite rather than LMDB or RocksDB which are key-value stores
by justinclift
1 subcomments
- Wonder if they had indexes on their SQLite tables?
Not seeing a mention of that in the article.
by singpolyma3
1 subcomments
- > 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
... Why not run redis on localhost?
- FoundationDB
Isn’t “redis to sqlite” effectively what foundationDB?
https://www.foundationdb.org/
Hacker News