81–90 of 274 posts
Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends towards small, self-hosted networking services[0] that would often have a single user, or maybe federated with Now obviously if I wanted to scale up, at some point you would have too many users to fit in memory. But do programs at that scale actually need to e…
We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…
There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past tim…
Note -- the single process write at any one time is a killer for most web apps, where for example within SaaS you have many users doing things at the same time.
It's not perfect, but it fills the niche nicely.
There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past tim…
Note -- the single process write at any one time is a killer for most web apps, where for example within SaaS you have many users doing things at the same time.
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 30000;
We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…
That said, I love the idea this architecture. Might use it for whatever next dumb little web service I cook up! I love how this simplifies a lot of dev/deployment ops, perfect for a side project.
There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. Only a single process can write to the database at any time; it does not support concurrent writers. The backup tools do not support point-in-time recovery to a specific past tim…
In WAL mode, a write can mean writing some data in memory on the local machine. With Postgres/MySQL/etc, it has to go over the network. I can't parse [0] right now about the overhead of TCP and round trips with the database server, but it's basically a question of whether one SQLite write is 10x, 100x, 1000x, or more faster than a database server write. That should make a lot of difference. [0] https://gist.github.co…
In terms of latency it'll still be difficult to beat a database that lives in the same process as your application, but it won't be as bad as going over the network might be.
We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…
How is this setup fault tolerant? What happens if there is a hardware failure? How do you partition access in a way that means an extremely active user doesn't impact availability?
It is not.
> What happens if there is a hardware failure?
The product would suffer a total outage until manual intervention takes place. A restore of the VM from snapshot would be carried out by the customer. Some loss of the most recent business data would occur (i.e. between latest snapshot and time of failure). All of this is understood and agreed to by our customers.
> How do you partition access in a way that means an extremely active user doesn't impact availability?
Partitioning is not applicable. Our entire product fits on 1 machine and dividing the I/O along any dimension does not add much value to the performance equation.
https://corecursive.com/066-sqlite-with-richard-hipp/
An interview with one of the creators:Mr. Richard Hipp - for a better and deeper understanding what pitch they took and what industries they were in to. Their approach to overcome the db-world that they saw in front of them. See the obstacles and the solutions and why it came to be that underestimated 'sqlite' that powers a good chunk of all you mobile actions triggered by your apps - but just read that interview - i cannot reproduce the dramatic here in my own words (underestimated).
It gives me a handy way to run queries on data.
I tried to make it super smart too and really make educated guesses on what data types to use and even linking foreign keys.