4 ms·
I don't know anything about graph databases, but ... why do we want to use SQLite as one? Doesn't the graph database community have something better to offer?
by roenxi 6y ago
I don't know anything about graph databases, but ... why do we want to use SQLite as one? Doesn't the graph database community have something better to offer?
SQL has a knack for doing really easy things well, and moderately complicated things badly. I would assume by default that anything involving graphs should not involve SQL. Recursive CTEs to me scream "you're about to spend hours debugging something trivial".
- zimpenfish 6y ago> Doesn't the graph database community have something better to offer? Probably not in the same kind of resource-constrained way, no. Whilst it might be a suboptimal graph database, it's also not going to require 2GB of RAM to run...
- oever 6y agoAn interesting upcoming graph database is oxigraph. It's written in Rust and might be able to cope well in 2GB. https://github.com/oxigraph/oxigraph/ https://github.com/oxigraph/oxigraph/
- zimpenfish 6y agoOoh, ta, that looks handy, especially with supporting SPARQL. If it can load my 1.1GB TTL, I'll be beyond happy.
- pbronez 6y agoI’d love to learn more about your use case... I think SPARQL and RDF are pretty interesting technologies, but haven’t seen many real applications using them.
- zozbot234 6y agoI'd describe SPARQL and RDF as general interfaces focused on interop, not "technologies" per se. You could use any technology to provide a SPARQL/RDF endpoint, including a relational database.
- zimpenfish 6y agoMy current use case is that I'm working on something which is basically things in groups (which may also be in groups) and seeing if things like "items in group A which are linked to group C via linkages to items in group B" are easier with SPARQL + graph database than they are with Postgres. (Previous use case was converting music industry data into RDF and providing query interfaces on top. That 1.1GB TTL is a CWR file converted to RDF as a stress tester.)
- zozbot234 6y ago> "items in group A which are linked to group C via linkages to items in group B" Relational databases are actually quite convenient for such cases because you can model each "group" of items via a database table, including potentially an associative table which provides "linkage" between items in the database. Graph-based models are generally more limited than that, e.g. RDF and SPARQL are limited to simple triples which link a "source" and a "target" entity (both of which are essentially non-typed) according to a fixed "predicate". You can sort of materialize triples and endow them with extra information, but it gets clunky.
- zimpenfish 6y ago> Relational databases are actually quite convenient for such cases I believe that someone may have done this in a nice way but every time I've encountered it (3 times thus far), it's always ended with complex SQL and tables being bent out of shape to try and keep performance. > RDF and SPARQL are limited to simple triples which link a "source" and a "target" entity according to a fixed "predicate" But can also infer transitive relations based on those predicates - `A canSee B`, `B canSee C` => `A canSee C` - which is handy when you're trying to discover those relationships in your data.
- zozbot234 6y ago> But can also infer transitive relations based on those predicates - `A canSee B`, `B canSee C` => `A canSee C` - which is handy when you're trying to discover those relationships in your data. You can do this sort of inference in a view if you use relational databases. (A view is a sort of "virtual" table based on the result of some database query. Many databases can also materialize views for improved performance, though this can make it a bit challenging to manage updates.)
- hobofan 6y agoIf we are just going by resource usage, I'm sure that RedisGraph (as it's "just" a Redis module) would also fit the bill.
- detaro 6y agoIsn't that keeping everything in memory?
- zimpenfish 6y agoIt seems it can persist to disk just like normal Redis stuff but only work with in-memory graphs - tricky if it's too big to fit, I suppose. cf https://github.com/RedisGraph/RedisGraph/issues/152 https://github.com/RedisGraph/RedisGraph/issues/152
- hobofan 6y agoIt's keeping everything in memory (index and entities) with disk persistence, but doesn't have a big memory overhead when running with little data.
- deleted 6y ago[deleted]
- zozbot234 6y ago> I would assume by default that anything involving graphs should not involve SQL. That would be a questionable assumption. SQL databases are a widely tested tool, and SQL itself can allow you to augment your "graph" with constraints and semantics that many graph-focused systems have trouble with. CTE's, while not entirely trivial, are not overly complex; they're not something you'd spend "hours" debugging.
- philjohn 6y agoSome of the early RDF Graph Stores were indeed based on SQL databases (Jena RDB/SDB) but you're correct in that they didn't scale particularly well. There are pure graph stores out there, AllegroGraph, OpenLink Virtuoso (although this is a strange hybrid of SQL + Graph technologies) and others - and for more advanced graph query constructs like path finding there are optimisations that are difficult/not well supported in SQL.
- dmitriid 6y ago> why do we want to use SQLite as one Because SQLite is ubiquitous. It's simply everywhere. And it doesn't need a dedicated server to run.
- anitil 6y agoI would also add the following - another good reason is that it's probably going to be the most reliable, best tested part of any stack you decide to use.
- pizza234 6y ago> SQL has a knack for doing really easy things well, and moderately complicated things badly. I would assume by default that anything involving graphs should not involve SQL A schema may have only one, or anyway, comparatively very few instances, of recursion. In this case, using two separate data stores would be too much overhead; if the CTE doesn't do anything particularly fancy, that is, if it just retrieves records recursively associated via keys, it's not implicitly hard to debug (actually, there isn't much to debug). A point in case is GitLab's users management, which dropped MySQL also because of the lack of CTEs (at the time).
- mongol 6y agoYes why not? If you have a data model where parts of the data is graph-like, and other classic relational, why would you not want to store the entire thing in the same database? If your domain is completely graph-oriented, and you have complex requirements on your database to support graph-oriented operations, it would be one thing. But evaluating use of a well known and mature SQL database is no bad starting point.
- roenxi 6y ago> Yes why not? If you have a data model where parts of the data is graph-like, and other classic relational, why would you not want to store the entire thing in the same database? SQL is an implementation of relational algebra, and databases that implement it are relational databases for storing relational data. Pointing at the name of a thing isn't an argument, I admit it, but asking "what could go wrong if we want to use a system tailored to relational data to deal with non-relational data?" strikes me as the sort of question that might turn out to have a compelling answer 6 months in when it is too late to easily change the database. If I were involved in a project, I would be representing very hard that the data comes out of the relational DB using "SELECT * FROM table WHERE conditions" and then the clever graph things start happening. If the data needs to be read from disk using clever graph based algorithms, then use a clever, graph-based database. There are a bunch out there according to Wikipedia.
- shawnz 6y agoIf the project involves spending 6 months writing code involving navigating graphs, then SQLite probably isn't the right choice. If it's one or two queries, that's a different situation
- roenxi 6y agoSure, but (and I am apologetic to be labouring on this point) if you have a small one-off blob of data that needs to be processed as a graph, SQL is the worst language to do the processing and a database is the worst place to be doing it. Get it into memory, use a programming language that supports recursive functions. This is the basic argument here. If the situation is delicate enough we need the database to be doing graph operations, why would we pick SQLite? If it isn't, why would we pick SQL over Python? I don't think it is bad that SQLite supports this, just ... what are the circumstances where this is a good idea? Is there a reason to do graph-style algorithms in SQL?
- emodendroket 6y agoI don't feel that way at all. I think a lot of that feeling comes from the fact that everyone's so dependent on ORMs they just rarely write SQL. Once you start doing it more you realize how powerful and elegant it can be.
- rocqua 6y agoMy experience with graph databases so far has been that they are tuned more for nice schemas and queries than for graph analysis. I've actually been considering migrating away from neo4j to sql with recursive queries for performance reasons.