I've looked at an JSON implementation on top of SQLite a bit more. It seems pretty fast. I haven't tried creating JSON indexes, or creating queries that use them. The only quirk I can find so far is that it treats JSON bool values into integers. That seems tolerable.
At first blush - without explicit indexes, it seems to be writing about 70mb/sec over NFS to my server in the basement. I did declare a uniqueness constraint on the hash value - so it created a primary index. Next thing is to create an implementation similar to the one I created for flat files - keeping in mind I want to be able to create queries that take advantage of indexes. -- Alan Robertson al...@unix.sh On Wed, Feb 14, 2018, at 8:41 AM, Alan Robertson wrote: > Hi Clint, > > On Wed, Feb 14, 2018, at 7:19 AM, Clint Byrum wrote: > > Excerpts from Alan Robertson's message of 2018-02-12 19:12:41 -0700: > > > So, that's a total of 4 possible methods of storing the data... > > > (a) in Neo4j, (b) in Postgres, (c) in flat files, (d) in S3. Of > > > those four, only Postgres will provide indexing. > > > If on the other hand, we break out big JSON blobs into individual nodes > > > in Neo4j, we can search that > > > instead (as Michael Hunger suggested). > > > > What about SQLite? > > I hadn't thought of it to be honest. > > > It's no worse than flat JSON and gives you transactions > > and indexing. The major drawback is that it uses database-level locking, > > so writes are going to serialize entirely. > > Not sure what it does with JSON. If it can't index the JSON, then > there's no advantage that I can see. Given the nature of this JSON data > (invariant), transactions are not an issue... > > _[Goes away and reads stuff...]_ > > They state that it's faster and smaller in SQLite than in flat files. I > believe that. It's certainly fewer inodes ;-). > > It looks like one can use SQLite with the JSON1 extension to store and > query JSON with indexes (indexes based on JSON expressions) if one is > careful on how one writes the queries. I suspect we can do that... > > Write locking isn't likely to kill us. It'll clearly be much better than > what we've been doing. There are solutions to that if it becomes an > issue. > > I'll go away and read some more about locking and how it would interact > with our (disabled-by-default) REST interface to the database... > > Thanks for the suggestion! > > _[Goes away to read more stuff...]_ > > It looks like WAL mode in SQLite solves the problem of concurrent > writers :-D. And its locking (in any of its modes) would be friendly > to our use of the database. > > So this looks reasonable. I'll give it more thought. Maybe write some code... > > Everyone should feel free to offer your insights and experiences > regarding SQLite... > > > -- > Alan Robertson > al...@unix.sh > _______________________________________________ > Assimilation mailing list - Discovery-Driven Monitoring > Assimilation@lists.community.tummy.com > http://lists.community.tummy.com/cgi-bin/mailman/listinfo/assimilation > http://assimmon.org/ _______________________________________________ Assimilation mailing list - Discovery-Driven Monitoring Assimilation@lists.community.tummy.com http://lists.community.tummy.com/cgi-bin/mailman/listinfo/assimilation http://assimmon.org/
