7 ms·

RediSQL – A Redis module that provides a functional SQL database

• FloatingGhost 10y ago
  science has gone too far
• siscia 10y ago
  Author here, if you have any question please feel free to ask. Also I have introduced the module in a blog post here: http://redbeardlab.tech/2016/12/13/redisql http://redbeardlab.tech/2016/12/13/redisql
  • mozumder 10y ago
    Can I use this to atomically delete thousands of existing keys? Is there a way to map the new SQL tables to existing keys? Or is this strictly for new SQL data tables?
    • dom0 10y ago
      This is a SQLite in-memory database running inside the redis service. It doesn't interface with the redis database.
    • siscia 10y ago
      As the other question, for now I am simply using Redis as support for SQLite. You are talking about something definitely feasible but that require a really carefully implementation. For now, it is strictly for new SQL data tables. However I must say that put all your keys inside the SQL table is not difficult.
  • aalekhN 10y ago
    Have you thought of ways to implement queries involving LIKE operations ? I worked on a similar project some time ago and one of the limitation for such implementation I encountered was use of glob-style patterns in redis.
    • siscia 10y ago
      Didn't quite get your question. SQLite does implement the LIKE operator. From the module: 127.0.0.1:6379> REDISQL.EXEC "CREATE TABLE text_search(t TEXT);" OK 127.0.0.1:6379> REDISQL.EXEC "INSERT INTO text_search VALUES('hello');" OK 127.0.0.1:6379> REDISQL.EXEC "INSERT INTO text_search VALUES('banana');" OK 127.0.0.1:6379> REDISQL.EXEC "INSERT INTO text_search VALUES('apple');" OK 127.0.0.1:6379> 127.0.0.1:6379> REDISQL.EXEC "SELECT * FROM text_search WHERE t LIKE 'h_llo';" 1) 1) "hello" 127.0.0.1:6379> REDISQL.EXEC "SELECT * FROM text_search WHERE t LIKE '%anana';" 1) 1) "banana" 127.0.0.1:6379> REDISQL.EXEC "INSERT INTO text_search VALUES('anana');" OK 127.0.0.1:6379> REDISQL.EXEC "SELECT * FROM text_search;" 1) 1) "hello" 2) 1) "banana" 3) 1) "apple" 4) 1) "anana" 127.0.0.1:6379> REDISQL.EXEC "SELECT * FROM text_search WHERE t LIKE 'a%';" 1) 1) "apple" 2) 1) "anana"
    • alfanick 10y ago
      This is just a bridge to in-memory sqlite accessible from redis connection – it does not emulate sql for redis data - both databases are completely independent, unfortunately.
      • siscia 10y ago
        Why you say unfortunately? What are your uses cases? It is possible to have in SQLite the keys from redis, however it does require a level of complexity that must be justified.
      • aalekhN 10y ago
        Ah, I was under impression that it emulates SQL nature for redis data (kind of like an extension to InnoDB Memcache architecture). Anyways nice work @siscia, thanks for sharing !!
      • camtarn 10y ago
        This paragraph is what I needed to see in the project readme. Thanks.
      • deleted 10y ago
        [deleted]
  • pizza234 10y ago
    Which would be a use case, from a technical perspective? The blog mentions: If you are writing microservices to avoid bothering the master of DB with your need ephemeral needs of structured database and I see this module perfect for small dataset of unimportant data. In other words, why would one use this module in place of a straight SQLite database (possibly in-memory, if there is really such need for speed)? Such module doesn't simplify anything (as far as I understand), since SQL is used anyway. I would understand this as an experiment (something like "ReDoom"), but since there will be a paid version, I'm trying to position the product technology.
    • siscia 10y ago
      SQLite gives you a lot of simplicity over client-server databases such as Postgres or MySQL. A straight SQLite database doesn't quite give to you the same kind of possibility, imagine you have multiple machine that want to access the data. It is a middle ground between "full" SQL databases and simpler solution. It does use Redis for the client-server communication which gives reliability and is already well know in most companies. My suggestion to use it for small, unimportant data is because there is still not persistency on disk, I am working on it right now.
    • edibleEnergy 10y ago
      Well, SQLite has to be accessed over a filesystem. This sounds like it would proxy to a sqlite db located on the Redis server. Still sounds dicey but I could see the appeal if you didn't have the DBA skills necessary to set up a decent PostgreSQL or other SQL server.
      • pizza234 10y ago
        Well, although the requirements which would lead to such setup are clearer now, it's still smelly to me. Under this conditions there is still a false dichotomy between RediSQL on one side, and "complex RDBMS" on the other. What about simple RDMSs like H2? Such solution would meet the requirements: - network-accessible RDBMS - no configuration The only requirement I can think of which would make this solution inadequate would be "No JVM on the system", which sound quite far-fetched.
• deleted 10y ago
  [deleted]
• hoschicz 10y ago
  Where does it save the data? Just in a blob in Redis? Does it support replication? How about persistence - - can I use standard Redis persistence? I'm kinda new to modules in Redis. Definitely could use this for migration off SQLite to a solid DB.
  • siscia 10y ago
    Just update the readme. For now it works in memory, after Saturday it will save data in a standard SQLite file. Replication and SQL are fairly tricky beast together. What is your use case?
    • avmich 10y ago
      Can rqlite [1] be helpful here? [1] http://www.philipotoole.com/replicating-sqlite-using-raft-consensus/ http://www.philipotoole.com/replicating-sqlite-using-raft-co...
      • siscia 10y ago
        It may. The real question in my opinion is: What kind of replication we need? We want to replicate just to being sure that the data is safe? Then really you could simply stream to all the replicas what happens to the master. You want to increase read performance? Then you first start caching result, then you start stream to slave and only make read from them. You want to increase write performace? That is quite a bad beast. You start by making the single instance as fast as possible (96k insert per second [1]) then you pretty much hit the ceiling in my opinion. You could partition the SQL tables, but then what happens to joins and select? They must go over the network (high latency), spot distributed deadlock (highly variable latency) and it start to become an huge complex project. [1]: http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite http://stackoverflow.com/questions/1711631/improve-insert-pe...
    • hoschicz 10y ago
      My use case is: one SQLite DB for every client, scaling by distributing clients across servers. I would like to have all clients in one DB and make scaling easier.
• stevekemp 10y ago
  It's a cute project, I remember you posted it before. I remember somebody else had a go at this a few years back https://groups.google.com/forum/#!topic/redis-db/TLfwGfldgUU https://groups.google.com/forum/#!topic/redis-db/TLfwGfldgUU
  • siscia 10y ago
    It is a little different from Threadis since, in my understanding, threadis is more a redis fork. While rediSQL is a redis module. Using a redis module get you A LOT of thing from free and you really stand on the shoulder of giants.
    • gtrubetskoy 10y ago
      > threadis is more a redis fork. While rediSQL is a redis module. Thredis author here - Redis did not support modules back then, had I done this today it prolly would have been a module (though it's been a while since I hacked on Redis and I don't know how modules work)
      • siscia 10y ago
        Hi :) sure, I took a deep look at your project and really appreciate you open source it. Do you have any feedback to share? Cheers, Simone
• azureel 10y ago
  Hello siscia, do you have any benchmark against a sqlite Db on Ramdisk?
  • siscia 10y ago
    Hi Azureel, benchmark are a little tricky in this situation. You are sending data to the redis instance, that read your statement and pass it to the module, the module execute and finally reply. And everything really depends on your network and load on redis itself. However, to just give some number on my machine I do 1000 insert on 0.6 seconds, the longest insert took 0.01 second and overall I can claim a total of 1000 / 0.6 => 1600 insert per second. Consider that each insert is a isolated transaction. The test I run is exactly the one you can find here: https://github.com/RedBeardLab/rediSQL/blob/381e3796ad31c231719380afb92352c54b244b8c/test/performance/rediSQL_bench.py https://github.com/RedBeardLab/rediSQL/blob/381e3796ad31c231... Of course, if you start to insert JOINS everything is slowing down. Also please consider that, at the moment, it is a BLOCKING version, while executing the statements redis itself is waiting. Finally I believe there are possibilities to improve this numbers.
• m82labs 10y ago
  Fully in-memory SQL can also be accomplished using IMOLTP via MS SQL Express on Linux now as well. And you get a fairly feature complete engine along with multithreaded execution, no blocking, and some great client options. The only limits right now are 4 cores and ~350MB of in-memory data per database.
  • nolok 10y ago
    My memory is a bit fuzzy right now, but isn't mysql itself providing that since forever too? Or do you fully in memory db - with a hard copy on the hard drive?
    • m82labs 10y ago
      I think most RDBM systems have a way to pin a table or database in memory. The difference with the Microsoft solution is that it is a separate engine designed explicitly for memory. So there is no locking or blocking of pages. There are some other neat features as well, like hash indexes, but I have been able to push an 8 core box to ~80,000 inserts per second from a remote client, and over 200,000 per second for a test I ran on the host. I have not yet ran tests for express edition, but I will be interested to see how it performs. To answer your other question, you have the option to either keep it all in memory (and lose on reboot) or commit the data to disk. In my testing I only saw a ~10% performance penalty for committed data to disk (granted we have extremely fast SSDs).
• EugeneOZ 10y ago
  I tried to do the same (before great Lua support, using PHP), but overall performance was similar to MySQL (much more reliable than my experiment) so I abandoned it. So would be very interesting to see benchmarks and comparisons with MySQL - I think right now we have more ways to implement it better.
  • EugeneOZ 10y ago
    Really interesting to hear reasons of downvoting. You think I'm lying or there is comparison?
    • nik736 10y ago
      PHP.
      • EugeneOZ 10y ago
        PHP wasn't a bottleneck in this case, most of the time has been spent on communication with DB (as usually in web apps). I think (and hope) this solution can show better performance because of less communications required, but numbers are more interesting than assumptions. upd: comment about proxying sqlite eliminated this hope.
        • deleted 10y ago
          [deleted]
• dvirsky 10y ago
  I've been working on a secondary indexing module for redis with a subset of SQL WHERE for selection predicates, if anyone's interested. It's not stable yet, but it's coming along nicely. The idea was actually NOT to create an SQL like database, but just proxy ordinary redis commands via secondary index. So you select ids, and perform HGET or HGETALL on them, etc. And the same goes for indexing - you perform something like HMSET and "through" the index, and it performs the operation while indexing the hash automatically. Also, indexes can be used as direct data types with no automation, you just insert ids and tuples of data into them, and you can query them and get ids back. https://github.com/RedisLabsModules/secondary https://github.com/RedisLabsModules/secondary
• antirez 10y ago
  I think that what the module does is actually not clear here in the comments I'm reading. It's mostly a nice "Hello World" module that shows how Redis can act as a proxy, but it's just a small glue-module that calls the SQLite library with the string passed as query. So you are running queries on a local sqlite server, proxied by Redis. While this can be a good programming example for newcomers, I suggest to look at modules that are really implementing new DB paradigms on top of Redis. For instance this module builds a graph DB on top of Redis data types, with a query language and so forth: https://github.com/swilly22/redis-module-graph https://github.com/swilly22/redis-module-graph
  • arielm 10y ago
    I completely agree. As someone who uses redis sql heavily I understand both have their use cases and wouldn't want to merge both to begin with. On top of that, this looks like a simple proxy, so it's only making things less efficient. OP, I'd suggest explaining the benefits better. Maybe I missed it but I don't really get this at all.
    • siscia 10y ago
      The modules is born just to fulfill one of our needs: we had several micro-services using redis and no DB at all, but then we find ourselves in the situation where we needed SQL capabilities, redis module where being announced and we give them a shoot. What you mean by efficiency ? Operation at the second? Sure it is less efficient than Postgres or of SQLite embed in your application. However if you mean convenience and simplicity this solution may (arguably) be efficient enough. This module sit between a "full" client-server DB, a redis instance (that doesn't provide SQL capabilities) and SQLite that is not client-server. Being completely honest, how HN teach us, build a great technical product without showing it as soon as it provide just a tiny amount of value is not desirable. The comment in this thread indicate that there is some interest and I got some valuable suggestions and feedback on what to develop next.
      • antirez 10y ago
        Hello siscia. What's not clear to me if this is intended for production or if it's an example. I understand the general problem of proving SQLite with a network interface/API, and I guess, but I don't know, there are many solutions for that. If you are trying to solve the problem of SQLite not having a network interface by default, allowing the access from Redis via the Redis protocol, then the implementation is not optimal AFAIK because it does not use any threading but stops Redis while executing the queries in SQLite. Redis modules allow for non blocking commands doing the bulk of the work in threads, so I would suggest at least this approach if this is what you are doing. IMHO also there should be a more sensible conversion of data types from the SQL reply to the Redis protocol. If Redis has to be stopped during SQL queries IMHO it's better to use another network layer with SQLite.
• gtrubetskoy 10y ago
  I did something similar a while ago (2012) (it even made it to the top of HN). http://thredis.org/ http://thredis.org/ Thredis also added multi-threading to Redis. The code is here: https://github.com/grisha/thredis https://github.com/grisha/thredis
• xena 10y ago
  It'd be really interesting to use redis modules like this in the future to have redis itself be both the main interface to the database for programs and a caching layer.
• partycoder 10y ago
  Redis and NoSQL data stores in general are for merge-on-write not merge-on-read. That's why most of them are non relational (no relations, foreign keys or joins). This project encourages merge-on-read which is exactly the problem k/v stores try to solve in another way (merge-on-write). The problem with relational data stores is that once you have relations then you need consistency and then you need moving operations to the data store itself (stored procedures), and transactions, etc... and suddenly you need an army of certified DBAs and a $1+ million dollar budget on datastores alone. It's not the right way to go. Feel free to have SQL but adding joins is a bad idea.
  • Jweb_Guru 10y ago
    The problem of consistency is related to supporting multi-key, read-write transactions of any sort, and has nothing to do with support for joins, or relations. You're not escaping from it somehow by using a NoSQL database.
    • partycoder 10y ago
      In a relational database you are almost certainly going to maintain data about the same "entity" distributed across different tables. And that is encouraged (hence the term "relational databases"). In k/v datastores, e.g: document oriented, you can just put all the data related to one entity on one record and that's it... and dealing with multi-key operations is discouraged rather than encouraged. Why discouraged? because it's hard to load balance, creates the need for synchronization and locking at a broader level, adds complexity to the maintenance operations, and so on, so forth. The SQL thing exists because dBase, one of the first database products, had it. And then every product that entered the market had to go with it to diminish friction.
      • Jweb_Guru 10y ago
        Okay, so your solution to consistency is "grab a big ol' lock over all possible related data, and hope like hell that you got all of it and it can live in one key." That doesn't really work when the amount of related data you have gets large enough, and it's really easy to have an efficient SQL database behind the lock (that's essentially how VoltDB works). Behind the lock, you can still have multiple tables, joins, and everything people associate with RDBMSes, without any of the consistency issues you mention. Your discussion about what's "encouraged" or not is frankly irrelevant, as key/value stores are super easy compared to general relational databases and most RDBMSes are already quite good key/value stores (by contrast, most key/value stores make lousy RDBMSes). The simple fact of the matter is: if your key/value store doesn't support multikey read/write transactions, it has strictly less functionality than a database that does, regardless of how you use it, and if it does support multikey read/write transactions it'll have all the same performance issues you associate with RDBMSes (well, that's a slight oversimplification because it may provide a more limited API).