7 ms·
As a non-dev intruder I have to say that I love SQLite. I do a lot of data-analysis and it makes everything easy, from fast SQL Wizardry to sharing the DB just
by iagovar 6y ago
As a non-dev intruder I have to say that I love SQLite. I do a lot of data-analysis and it makes everything easy, from fast SQL Wizardry to sharing the DB just coping a file! Just how amazing is that?!
It must sound naive to some of you, but the first time stumbled upn sqlite I was so excited!
- dragonshed 6y agoI totally agree. I'm a front-end dev that can wing backend from time to time, and I use SQLite as much as possible. On multiple projects now I've run into complications due to complexity or environments, and adding a simplified local development backend with sqlite kept down time to a minimum. SQLite is awesome.
- jventura 6y agoMost of my web apps’ databases are an SQLite file. It’s more than enough for the ammount of traffic they serve and the db files are easy to set up and backup..
- eska 6y agoI also always wondered why sqlite isn't used more in websites. Especially if you split heavy write workloads to a separate database file it scales quite far.
- qes 6y agoThere's no reasonable way to share a SQLite database between processes on separate machines or VMs.. What website doesn't use at least 2 instances for HA? How do you make sure you don't lose data in a SQLite DB?
- presumably 6y agoWhat’s not reasonable about TiDB?
- samtho 6y agoNot sure about parent’s case, but I’ve used it for small blog-type applications where the only writes are from me (the admin) creating new entries. The rest is read only and behind caches, cdns, etc. The blog is effectively a singleton. Analytics and comments are handled by 3rd party providers.
- detaro 6y agoMany websites just run on a single machine somewhere, with maybe some caching in front or off-loaded static files. Even HN is as far as I know still just a single box.
- freedomben 6y agoI throw together plenty of simple sites intended for personal use or just a small group. I don't need nor want the added complexity of going for HA. I can easily spin up a cheap VPS with an SSD and scale amazingly far with one instance and a SQLite DB. When it's for personal use or small group it's not a problem to take it offline for maintenance/deploys. I don't even use a load balancer (tho I often throw nginx in front to do mTLS and compression). I can build a site like that in 30 minutes. I deal with massive scale all day every day for work (kuberntes/openshift/public clouds) so I find the simplicity amazingly refreshing. I might also say the performance of my "single box" websites is also amazingly good. I've had even developer friends ask me how I get response times so fast, and they are often surprised when I tell them.
- ww520 6y agoJust use DRDB and Linux-HA to replicate the disks hosting the master SQLite database and the standby database.
- deleted 6y ago[deleted]
- emadda 6y agoYou can use a regional GCP disk that replicates over all zones in a region (https://cloud.google.com/compute/docs/disks#repds https://cloud.google.com/compute/docs/disks#repds). You cannot mount the same disk to many VMs, but you can force mount the disk to a new VM.
- lenkite 6y agoHow do you architect this ? This works only for single process web-apps right ? With no HA or failover ?
- jventura 6y agoRight! But as I said, the low traffic volume doesn’t require more than this kind of architecture. I do regular file backups and have a cron job restarting the web apps each 20 minutes, it’s enough for now. I would love to have a more solid architecture, but it matches the traffic volume and it’s good enough..
- duskwuff 6y agoTBH, it'll work for a lot of multi-process apps, so long as the app runs on a single server and the app doesn't do anything foolish like write to the database on every page hit. For read-mostly applications -- like a blog, wiki, or even a small web forum -- SQLite is a surprisingly adequate database.
- mmsimanga 6y agoI work in data analysis too and SQLite is to me what MS Excel is to other people in the industry. I have development background so I never picked up in MS Excel to same degree as my colleagues from statistics backgrounds. In corporate organisations where DBAs monitor the use of their database with an eagle eye SQLite is a fantastic option to analyse several millions of lines of data on your desktop. I find DB Browser for SQLite (DB4S)[0] is invaluable when using SQLite. [0]https://sqlitebrowser.org/ https://sqlitebrowser.org/
- iagovar 6y agoExcel is nice as a simple sandbox, and PowerQuery is amazing IMO, the problem is that it's basically impossible to work with large datasets. If you didn't know about it I recommend you to play a bit with it. And check SQLiteStudio for Windows, is nice too.
- nojito 6y agoI handle hundreds of millions of rows in excel trivially. PowerPivot is extremely amazing.
- speedgoose 6y agoPowerBI is quite nice too.
- kyllo 6y agoMost people don't realize that Excel (like PowerBI) has an in-memory, compressed, column store database inside of it. Loading hundreds of millions of rows into it takes a while, but given a commensurate amount of RAM and a reasonable data model (single table or star schema), performing aggregations with a pivot table is pretty snappy.
- iagovar 6y agoWell, for me with my personal PC with a quad core and 24gb DDR3, it gets unusable for about 1 million rows, but it's true that most of my datasets have around 10 columns or so.
- hobs 6y agoI can't even tell you how many times a dev came to me with some weird question that was very simply answered by "just use sqlite." I've never thrown more than 20GB of data at it, but it always handles things admirably, and it often (to me) makes the right calls about the level of complexity required (accepts column type defs but really just doesnt care if it maps to string.)
- nolok 6y agoSQLite handles dozens of DB databases surprisingly well, especially in a single user for data analysis scenario.
- darksaints 6y agoI have done several multi-terabyte queries on it. One time I had a race with another dev to see who could get the right answer on a 3TB dataset. I was finished before his spark cluster had even spun up.
- giancarlostoro 6y agoAt my job we started using H2 which much like SQLite has shareable databases. We now wish more databases were this simple. Its just too efficient to share a db in its total current state and someone with a little more experience or who does not have tunnel vision can probe and resolve issues. Plus you can always go between states of broken vs working.
- ak39 6y agoSame. The last time I was this excited about an RDBMS in your pocket was when BerkleyDB released their Java database in a single JAR file. I am not sure when they initially released it but I remember doing some hobby projects in 2003.
- mmsimanga 6y agoI do occasionally use Apache Derby[0] in similar manner to SQLite. Mostly when data types are critical. Works well when using DBeaver or any SQL editor that allows you to add JDBC connections. [0]https://db.apache.org/derby/ https://db.apache.org/derby/
- StavrosK 6y ago> It must sound naive to some of you, but the first time stumbled upn sqlite I was so excited! Why naive? That's the experience of all of us with SQLite.