8 ms·

Rearchitecting: Redis to SQLite

360 points by thecodemonkey 2y ago

doubleorseven 2y ago
> Benchmarking is a dark art of deceiving yourself with highly precise numbers .
rini17 2y ago
If you need writes, can just use second sqlite database.
- HelloNurse 2y ago
  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.
ten13 2y ago
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.
- michaelbuckbee 2y ago
  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.
matharmin 2y ago
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.
- michaelbuckbee 2y ago
  (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.
- chipdart 2y ago
  > 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.
simonw 2y ago
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.
- closeparen 2y ago
  This is the type of architecture we use for feature flagging, but it's just a JSON file.
- michaelbuckbee 2y ago
  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.
  - jasonwatkinspdx 2y ago
    Years ago I had a conversation with a friend of a friend that worked at one of the big chip fabs in their lab dept. He mentioned they made very heavy use of sqlite as a file format for distribution. This was back in the "xml all the things" era and it struck me as such a smart breath of fresh air. I'm honestly surprised it isn't more pervasive.
- supriyo-biswas 2y ago
  > 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.
dangoodmanUT 2y ago
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.
- michaelbuckbee 2y ago
  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/ https://redis.io/docs/latest/develop/data-types/sorted-sets/
  - deleted 2y ago
    [deleted]
- epcoa 2y ago
  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.
  - mrl5 2y ago
    Thank you. First explanation what might be the root cause :)
- cynicalsecurity 2y ago
  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.
masfoobar 2y ago
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!
aquilaFiera 2y ago
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 https://github.com/btholt/redis-to-postgres
ragu4u 2y ago
So is the sqlite file on disk or in memory somehow?
- michaelbuckbee 2y ago
  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.
- TheDong 2y ago
  > 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.
macspoofing 2y ago
>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 =)
- deleted 2y ago
  [deleted]
- gwbas1c 2y ago
  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.
  - prirun 2y ago
    HashBackup author here, been using SQLite for about 15 years. Doing a vacuum after a large number of deletes might make sense. The only real purpose of vacuum IMO is to recover free space from a database. Vacuum may also optimize certain access patterns for a short while, though I have never tested this, and it would be highly dependent on the queries used. If fragmentation is a bigger concern for you than recovering free space, you can also compute the fragmentation to decide whether to vacuum by using the dbstat table: https://www.sqlite.org/dbstat.html https://www.sqlite.org/dbstat.html Then again, computing this will require accessing most of the database pages I'm guessing, so might take nearly as long as a vacuum. The other gotcha here is that just because db pages appear to be sequential in a file doesn't mean they are sequential on a physical drive, though filesystems do strive for that. SQLite has pragma commands to tell you the number of total and free db pages. When the percentage of free pages is greater than x% and it's a convenient time, do a vacuum. For a highly volatile db, you can add a table containing this percentage, update it every day, and make your decision based on an average, but IMO it's easier just to check for more than 50% free (or whatever) and do the vacuum. Vacuums used to be (circa 2019) pretty slow operations, but the SQLite team has sped them up greatly since then. Vacuuming a 3GB SQLite db on a SSD takes less than a minute these days. That's with the db 100% full; with only 50% used pages, it would be considerably faster. Vacuums are done in a statement transaction, so you don't have to worry about a "half vacuum that runs out of disk space" screwing up your database.
justinclift 2y ago
Wonder if they had indexes on their SQLite tables? Not seeing a mention of that in the article.
- michaelbuckbee 2y ago
  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.
  - filleokus 2y ago
    Really great article and I really appreciate seeing this "flavour" of "distributed" sqlite, think it can be useful in many no/low-write scenarios. But about the formatting of the data, is it completely inherent to the rest of the system / unchangeable? Spontaneously I would have guessed that for example a bitfield in redis would have performed better. Did you test any other formattings?
  - a12b 2y ago
    You should definitely write an article with all tricks you used to make it fast!
  - epcoa 2y ago
    Curious, what is the advantage of decimal? Why not base-64 or some larger and power of 2 base?
gwbas1c 2y ago
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?
- michaelbuckbee 2y ago
  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.
- wormlord 2y ago
  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 https://www.sqlite.org/atomiccommit.html
  - gwbas1c 2y ago
    You can, but that's not the point. I basically asked if they should (gasp) write code that did the lookup. See the other response from the article's author.
codingbot3000 2y ago
It's posts like this explaining architecture decisions in detail I am reading HN for. Thank you!
- michaelbuckbee 2y ago
  (author) - It's genuinely delightful to know that you liked it.
keybits 2y ago
People reading this might be interested in Redka - Redis re-implemented with SQLite in Go: https://github.com/nalgeon/redka https://github.com/nalgeon/redka
- nikisweeting 2y ago
  Holy cow this is amazing, I've been looking for something like this for years!! Thanks for sharing.
- meowface 2y ago
  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.
  - anonzzzies 2y ago
    We (keydb users; it's much faster than redis for all our cases) use redka for our dev machines; we develop everything on sqlite so there is no install of anything and in prod, we just switch to our mysql, clickhouse, redis etc cluster and it all works while having a light experience for dev.
    - mikeshi42 2y ago
      How are you guys using sqlite in dev instead of clickhouse? (Afaik there's a good bit of difference between the two dialects so I'm surprised it's possible without hurting dx through one compromise or another)
      - anonzzzies 2y ago
        We have our own query language based on prolog which compiles to efficient queries depending on the underlying db. We haven't caught any cases for about half a decade where humans could do better queries. We are in a niche market so this is not a catch all solution; it is specifically for our market.
tiffanyh 2y ago
FoundationDB Isn’t “redis to sqlite” effectively what foundationDB? https://www.foundationdb.org/ https://www.foundationdb.org/
favorited 2y ago
> 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.
- x0x0 2y ago
  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.
- michaelbuckbee 2y ago
  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.
- vundercind 2y ago
  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.
nikisweeting 2y ago
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.
- m_sahaf 2y ago
  There's Redka: https://github.com/nalgeon/redka/ https://github.com/nalgeon/redka/
  - nikisweeting 2y ago
    Wow amazing, thank you so much! I've spent many hours over the years looking for a project like this, but it makes sense that I haven't seen this yet as it's only 6mo old.
- sundbry 2y ago
  You can use smoothmq (SQS over sqlite) for a celery backend: https://smoothmq.com/ https://smoothmq.com/
vchynarov 2y ago
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.
- jasonwatkinspdx 2y ago
  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.
tony-allan 2y ago
Best quote: "SQLite does not compete with client/server databases. SQLite competes with fopen()."
jszymborski 2y ago
A bit strange they replaced Redis with SQLite rather than LMDB or RocksDB which are key-value stores
singpolyma3 2y ago
> 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?
- vundercind 2y ago
  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.
theamk 2y ago
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.
prirun 2y ago
Might want to check into this to do your SQLite db copies: https://www.sqlite.org/draft/rsync.html https://www.sqlite.org/draft/rsync.html
- michaelbuckbee 2y ago
  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).
tmaier 2y ago
I visited this site from safari on iOS while being in a Marriott hotel. I am blocked. So the WAF works.
lilatree 2y ago
I wish there was a repository with lots of posts like this one. Super useful to learn from!
avinassh 2y ago
Is the benchmark code available somewhere / open source?
avovana 2y ago
Could you clarify, please Redis usage? 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? 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! That is the case?
deleted 2y ago
[deleted]

360 points by thecodemonkey 2y ago

doubleorseven 2y ago

> Benchmarking is a dark art of deceiving yourself with highly precise numbers .

rini17 2y ago

If you need writes, can just use second sqlite database.

HelloNurse 2y ago
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.

ten13 2y ago

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.

michaelbuckbee 2y ago
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.

matharmin 2y ago

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.

michaelbuckbee 2y ago
(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.
chipdart 2y ago
> 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.

simonw 2y ago

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.

closeparen 2y ago
This is the type of architecture we use for feature flagging, but it's just a JSON file.
michaelbuckbee 2y ago
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.
- jasonwatkinspdx 2y ago
  Years ago I had a conversation with a friend of a friend that worked at one of the big chip fabs in their lab dept. He mentioned they made very heavy use of sqlite as a file format for distribution. This was back in the "xml all the things" era and it struck me as such a smart breath of fresh air. I'm honestly surprised it isn't more pervasive.
supriyo-biswas 2y ago
> 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.

dangoodmanUT 2y ago

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.

michaelbuckbee 2y ago
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/ https://redis.io/docs/latest/develop/data-types/sorted-sets/
- deleted 2y ago
  [deleted]
epcoa 2y ago
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.
- mrl5 2y ago
  Thank you. First explanation what might be the root cause :)
cynicalsecurity 2y ago
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.

masfoobar 2y ago

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!

aquilaFiera 2y ago

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 https://github.com/btholt/redis-to-postgres

ragu4u 2y ago

So is the sqlite file on disk or in memory somehow?

michaelbuckbee 2y ago
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.
TheDong 2y ago
> 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.

macspoofing 2y ago

>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 =)

deleted 2y ago
[deleted]
gwbas1c 2y ago
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.
- prirun 2y ago
  HashBackup author here, been using SQLite for about 15 years. Doing a vacuum after a large number of deletes might make sense. The only real purpose of vacuum IMO is to recover free space from a database. Vacuum may also optimize certain access patterns for a short while, though I have never tested this, and it would be highly dependent on the queries used. If fragmentation is a bigger concern for you than recovering free space, you can also compute the fragmentation to decide whether to vacuum by using the dbstat table: https://www.sqlite.org/dbstat.html https://www.sqlite.org/dbstat.html Then again, computing this will require accessing most of the database pages I'm guessing, so might take nearly as long as a vacuum. The other gotcha here is that just because db pages appear to be sequential in a file doesn't mean they are sequential on a physical drive, though filesystems do strive for that. SQLite has pragma commands to tell you the number of total and free db pages. When the percentage of free pages is greater than x% and it's a convenient time, do a vacuum. For a highly volatile db, you can add a table containing this percentage, update it every day, and make your decision based on an average, but IMO it's easier just to check for more than 50% free (or whatever) and do the vacuum. Vacuums used to be (circa 2019) pretty slow operations, but the SQLite team has sped them up greatly since then. Vacuuming a 3GB SQLite db on a SSD takes less than a minute these days. That's with the db 100% full; with only 50% used pages, it would be considerably faster. Vacuums are done in a statement transaction, so you don't have to worry about a "half vacuum that runs out of disk space" screwing up your database.

justinclift 2y ago

Wonder if they had indexes on their SQLite tables? Not seeing a mention of that in the article.

michaelbuckbee 2y ago
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.
- filleokus 2y ago
  Really great article and I really appreciate seeing this "flavour" of "distributed" sqlite, think it can be useful in many no/low-write scenarios. But about the formatting of the data, is it completely inherent to the rest of the system / unchangeable? Spontaneously I would have guessed that for example a bitfield in redis would have performed better. Did you test any other formattings?
- a12b 2y ago
  You should definitely write an article with all tricks you used to make it fast!
- epcoa 2y ago
  Curious, what is the advantage of decimal? Why not base-64 or some larger and power of 2 base?

gwbas1c 2y ago

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?

michaelbuckbee 2y ago
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.
wormlord 2y ago
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 https://www.sqlite.org/atomiccommit.html
- gwbas1c 2y ago
  You can, but that's not the point. I basically asked if they should (gasp) write code that did the lookup. See the other response from the article's author.

codingbot3000 2y ago

It's posts like this explaining architecture decisions in detail I am reading HN for. Thank you!

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

keybits 2y ago

People reading this might be interested in Redka - Redis re-implemented with SQLite in Go: https://github.com/nalgeon/redka https://github.com/nalgeon/redka

nikisweeting 2y ago
Holy cow this is amazing, I've been looking for something like this for years!! Thanks for sharing.
meowface 2y ago
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.
- anonzzzies 2y ago
  We (keydb users; it's much faster than redis for all our cases) use redka for our dev machines; we develop everything on sqlite so there is no install of anything and in prod, we just switch to our mysql, clickhouse, redis etc cluster and it all works while having a light experience for dev.
  - mikeshi42 2y ago
    How are you guys using sqlite in dev instead of clickhouse? (Afaik there's a good bit of difference between the two dialects so I'm surprised it's possible without hurting dx through one compromise or another)
    - anonzzzies 2y ago
      We have our own query language based on prolog which compiles to efficient queries depending on the underlying db. We haven't caught any cases for about half a decade where humans could do better queries. We are in a niche market so this is not a catch all solution; it is specifically for our market.

tiffanyh 2y ago

FoundationDB Isn’t “redis to sqlite” effectively what foundationDB? https://www.foundationdb.org/ https://www.foundationdb.org/

favorited 2y ago

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

x0x0 2y ago
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.
michaelbuckbee 2y ago
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.
vundercind 2y ago
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.

nikisweeting 2y ago

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.

m_sahaf 2y ago
There's Redka: https://github.com/nalgeon/redka/ https://github.com/nalgeon/redka/
- nikisweeting 2y ago
  Wow amazing, thank you so much! I've spent many hours over the years looking for a project like this, but it makes sense that I haven't seen this yet as it's only 6mo old.
sundbry 2y ago
You can use smoothmq (SQS over sqlite) for a celery backend: https://smoothmq.com/ https://smoothmq.com/

vchynarov 2y ago

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.

jasonwatkinspdx 2y ago
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.

tony-allan 2y ago

Best quote: "SQLite does not compete with client/server databases. SQLite competes with fopen()."

jszymborski 2y ago

A bit strange they replaced Redis with SQLite rather than LMDB or RocksDB which are key-value stores

singpolyma3 2y ago

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

vundercind 2y ago
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.

theamk 2y ago

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.

prirun 2y ago

Might want to check into this to do your SQLite db copies: https://www.sqlite.org/draft/rsync.html https://www.sqlite.org/draft/rsync.html

michaelbuckbee 2y ago
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).

tmaier 2y ago

I visited this site from safari on iOS while being in a Marriott hotel. I am blocked. So the WAF works.

lilatree 2y ago

I wish there was a repository with lots of posts like this one. Super useful to learn from!

avinassh 2y ago

Is the benchmark code available somewhere / open source?

avovana 2y ago

Could you clarify, please Redis usage? 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? 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! That is the case?

deleted 2y ago

[deleted]