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/

Reply via email to