This "little thingie" is so powerful, that words are missing to tell the story.
Consider SQLite
141–150 of 274 posts
Re: Consider SQLite
#142Earlier quoted context omitted.
Interesting. For an extremely specific use case and with users who understand and accept the caveats of this approach I'm sure it would work well enough. The most confusing thing to me is that there is apparently an intersection of users who are ok with an outage and data loss with users who want a product which can > execute queries and reliably receive results within microseconds What is your product? Who are these…
Quoted post unavailable.
No need to be condescending when I'm trying to learn. The premise of using SQLite because it was easier and to save fractions of a second didn't make sense to me as a tradeoff for potentially losing data
Re: Consider SQLite
#143Earlier quoted context omitted.
It's not really an issue if you have 1 db per customer
If a customer has 1000 employees all using your app, it is.
If the app is designed correctly, then the thousand employees would write to their own temporary databases, and a background job would pull their changes into the main database sequentially.
If the app is not specifically designed to do this, then SQLite would not be an option.
Re: Consider SQLite
#144Earlier quoted context omitted.
Interesting. For an extremely specific use case and with users who understand and accept the caveats of this approach I'm sure it would work well enough. The most confusing thing to me is that there is apparently an intersection of users who are ok with an outage and data loss with users who want a product which can > execute queries and reliably receive results within microseconds What is your product? Who are these…
I can think of plenty of services that an occasional (once a year? less?) outage is okay. Heck, anything relying on AWS us-east-1 is going to have outages that frequently based on the last few months. Meanwhile, almost any service is better off when its response times are cut drastically. I’ve seen many instances where a service’s response times are more than half waiting for a db to respond.
It's not the threat of an outage with data loss that is concerning to me- I just want to understand use case that needs fractions of a second shaved off of query times by using SQLite in this way that is also ok with the possibility of data loss.
Re: Consider SQLite
#145There 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.
Does an event sourcing architecture help with this? I'm thinking a bunch of client piping events into a queue with a single writer working through that queue. Might be stretching the eventual part of eventual consistency if the latency gets too bad, but I don't think SQLite is top of mind for most SaaS solutions anyway so more of a thought exercise.
Re: Consider SQLite
#146Earlier quoted context omitted.
> How is this setup fault tolerant? 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 y…
Interesting. For an extremely specific use case and with users who understand and accept the caveats of this approach I'm sure it would work well enough. The most confusing thing to me is that there is apparently an intersection of users who are ok with an outage and data loss with users who want a product which can > execute queries and reliably receive results within microseconds What is your product? Who are these…
I agree that 'reliably' would be a poor choice of diction when considered in your context.
The user group of our product is effectively only professional bankers and their managers. No one in the general public has any direct access.
Re: Consider SQLite
#147Earlier quoted context omitted.
If a customer has 1000 employees all using your app, it is.
If the app is designed correctly, then the thousand employees would write to their own temporary databases, and a background job would pull their changes into the main database sequentially. If the app is not specifically designed to do this, then SQLite would not be an option.
Serious question, is this just a "how do I get SQLite to work in this scenario?" thing, or is there actually some other benefit to having this sort of data architecture?
Re: Consider SQLite
#148Earlier quoted context omitted.
I don't know what backup tools you have in mind... But since a SQLite database is a single file (modulo write ahead journal and whatnot), making whatever you need is trivial.
> modulo write ahead journal and whatnot That's like saying immortality is trivial modulo death and whatnot. If you don't integrate with sqlite's locking system, you can easily "backup" a file mid-write, corrupted and unusable. That's why sqlite has a built-in backup command .
I guess my secret is having almost no writes (the couple things I deployed with SQLite would see a dozen writes per month).
Re: Consider SQLite
#149Earlier quoted context omitted.
> There is no easy way to save the data directly to the file system. That's what absurd SQL is for (link in the parent comment).
I read that one and agree it feels absurd. Not something I want to depend on.
I’m 100% sure this is (very nearly) suitable for production. It works and works well.
It is only a matter of time before browsers implement the proposed sandboxed virtual file system apis with block level access and this would be superseded.
Re: Consider SQLite
#150In the past I had a website with hundreds of gigs of data that needed updating regularly, but could be read-only from the web server perspective.
I used sqlite for that, and had a mysql server for the user data and stuff that needed to be written to. Performance was fantastic, users were happy, data updates were instantaneous ; copy the new data to the server then repoint a symlink.
Most of my work is modeling and simulation. Sqlite is almost always my output format ; one case per database is really natural and convenient, both for analysis, and run management.
Anyway. Sqlite is amazing.