Can anybody speak to their experience using sqlite for data analysis purposes?
Am I wrong in thinking it's just plain old row oriented storage and not something more aggregate oriented?
Appropriate Uses for SQLite
21–30 of 39 posts
Re: Appropriate Uses for SQLite
#22SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. I am curious to know how many people here solely use SQLite to power the back-end of their web application(s), especially when the page states, " SQLite does not compete with client/server databases. "…
On work, my area has a conflicting relationship with IT, thus we are often required to use unusual setups... Well, we've tried distributing applications that needed to share a DB backend. We tried a MS Access backend first, but it stopped working after about 5 people were using it. Then we migrated to SQLite, it handled well up to near 50 people, then its over-restrictive locking become a problem. Luckly by that time…
SQLite's WAL support might alleviate your locking problems.
Re: Appropriate Uses for SQLite
#23How does SQLite handle replication? Can I have 10 app server nodes reading from one SQLite DB? NFS?
I'm not sure if SQLite writes over NFS work, I wouldn't trust it. Previously I have done SQLite replication by simply rsyncing the database to application servers at certain times.
Writes definitely work, but concurrent writes will hose the database. SQLite relies on filesystem locking (which NFS does not handle properly) to handle write concurrency.
Re: Appropriate Uses for SQLite
#24How does SQLite handle replication? Can I have 10 app server nodes reading from one SQLite DB? NFS?
SQLite has a backup api, so not entirely what you might need - but you can get a consistent snapshot without affecting much the db using the backup API:
https://www.sqlite.org/backup.html
The way I've used it (for a db of 300-400mb loaded in-memory in a 64-bit app) was to load the .db in memory, then detect if there are changes outside (notified, or updated the .db file timestamp from a different app using the db), then load a second copy of the .db and compare, then build diffs and report them in some kind of API fashion.
It was used for live update of various level editing data for a game level editor. Possibly not the best fit (too much hierarchical data), but worked nonetheless.
One thing that I miss dearly from SQLite is some form of postgres arrays, but I'm so glad that recursive with has been added recently to SQLite which allows for hierarchical data (child/parent storing ids to each other) to fetch without some extra information.
Re: Appropriate Uses for SQLite
#25How does SQLite handle replication? Can I have 10 app server nodes reading from one SQLite DB? NFS?
The database is just a file, so just copy it (rsync works great for compressing this process over a slower network). If you need to replicate without blocking writes, you can do that with some scripting around filesystem (e.g. btrfs) or block layer (LVM) snapshots.
Though again, if you want true streamed (i.e. per query) replication, features like hot standby, etc... that's not part of the model and you're better served by a server-model RDBMS.
Re: Appropriate Uses for SQLite
#26Can anybody speak to their experience using sqlite for data analysis purposes? Am I wrong in thinking it's just plain old row oriented storage and not something more aggregate oriented?
I use it quite a bit for data analysis, in particular for user-defined functions with python. Being able to explore your data (via SQL and it's powerful syntax) in addition to functions and aggregators that I define, is REALLY useful. You could do the whole thing in python (and data imports), but adding the SQL part in is so much easier than building dictionaries and filtering, sorting etc...
Re: Appropriate Uses for SQLite
#27Don't get mislead by the "lite" in sqlite. I can do about 200k sql requests per second with a sqlite inmemory db and node.js
Re: Appropriate Uses for SQLite
#28Can anybody speak to their experience using sqlite for data analysis purposes? Am I wrong in thinking it's just plain old row oriented storage and not something more aggregate oriented?
I use sqlite to store all of my small datasets. Minimally, I think of it as a replacement for zipped CSV files. But it also has the added benefit of a relational structure and SQL.
It is super easy to access from julia, R, python, etc, so instead of importing a CSV and manipulating the data, I find it a lot easier to connect to the sqlite database and use SQL for the a lot of the joining and manipulating.
Re: Appropriate Uses for SQLite
#29SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. I am curious to know how many people here solely use SQLite to power the back-end of their web application(s), especially when the page states, " SQLite does not compete with client/server databases. "…
The main limitation is number of clients here. If you have a RESTful API, some ETL loaders, and several webservers running on EC2 all talking to your database, then you need a real client-server architecture. However, if you're running your website on Apache, on a single webserver, then there's really only ONE client for your database, in which case SQLite works great, even if there's a heck of a lot of load. SQLite…
[deleted]
Re: Appropriate Uses for SQLite
#30I was fooled by the name for a long time. "SQLite? That's what you use to store 160 phone book entries or something." How wrong I was.
I'm using WebSQL / Sqlite to store TV show data on the user's system. works like a charm, databases regularly grow to 10+mb, never had a problem with crashes, speed or anything like it. Plus, everything runs locally Now I really hope that Spartan can implement WebSQL as well...
I used SQLite to store the entire last.fm user graph there, it was more than a gigabyte or so. MongoDB was abysmal, so I switched to SQLite, which was very, very fast, and I had zero problems with it. I have immense respect for it ever since.