4 ms·

I feel like the scenarios in which it makes sense to use SQLite for multi users apps is growing, for a few reasons: - SSD hard disks are ubiquitious - WAL Mod

I feel like the scenarios in which it makes sense to use SQLite for multi users apps is growing, for a few reasons: - SSD hard disks are ubiquitious - WAL Mode: readers don't block, and multiple processes can access a single database - DB-per-customer is getting more common due to privacy laws - The browser itself is increasingly being used as a client side cache DB in its own right IIRC Nomadlist uses (or used) sqlite. Wonder how many more web sites are using it in anger.

bob1029 7y ago
I am 100% on board with this sentiment. We are already exclusively using SQLite to back our B2B application and it has absolutely zero issues even under heavy workloads. I would say its easily faster (especially in terms of request latency) than the SQL Server instance we previously-utilized for the same purpose. WAL is an absolute must if you are dealing with any sort of concurrency. As long as you can satisfy the following constraints, I see zero reason to use anything other than SQLite: - Only one process will ever own the authoritative copy of the database at a time. - You don't have any near-term plans to cluster or otherwise go wide on your persistence layer across multiple processes. Keeping in mind that even if you do need to do this, it shouldn't be a huge deal to import your SQLite files into something that natively supports clustering like Postgres, or use some partitioning strategy that can distribute SQLite databases across multiple nodes. I feel like a lot of misinformation has been put forth that would make SQLite seem unacceptable for larger-scale concurrent business applications, but every benchmark I've personally ran against SQLite makes every other database engine look like a joke (within the constraints laid out above; obviously a 30 node Postgres cluster is going to destroy a single instance of SQLite). You just need to understand a few magic PRAGMAs to get things up and running (journal mode = WAL), because it certainly isn't capable of any of this directly out of the box. I don't know about the 'anger' aspect of it, but I certainly feel dejected by the current approach that most take regarding business object persistence. SQLite feels like a way to sidestep an entire universe of non-value-add concerns (creating a separate sql instance, networking, security, configuration, policy, etc). In terms of management, SQLite absolutely cannot be beaten. It is literally as easy as copying any other file when performing backups or restores. You need to be a little careful with the .WAL and .SHM files, but nothing that can't be managed with a few lines of script in a cron job.
- lacampbell 7y ago
  I'd be interested to hear as much about your set up as you can divulge. How many concurrent writing users you have, you use a cache, etc. > In terms of management, SQLite absolutely cannot be beaten. It is literally as easy as copying any other file when performing backups or restores. This - I have a lot of respect to DBAs, enough to know that I am not one.
  - tracker1 7y ago
    Can't speak for GP, but it really will vary on workload. How much data is in each DB, how much workload is write vs read. In a mostly read scenario (say a blog), I'd be surprised if you couldn't handle 100k simultaneous users on a fast drive, more with a good caching strategy. For modest read/write, should be able to get 1000-10k users without much issue. A little advice would be to ensure logging is to a separate db/drive from the main data if you are logging changes, etc. The only down side is distribution with High Availability, but so many scenarios don't actually demand the extra overhead for true HA.

bob1029 7y ago

I am 100% on board with this sentiment. We are already exclusively using SQLite to back our B2B application and it has absolutely zero issues even under heavy workloads. I would say its easily faster (especially in terms of request latency) than the SQL Server instance we previously-utilized for the same purpose. WAL is an absolute must if you are dealing with any sort of concurrency. As long as you can satisfy the following constraints, I see zero reason to use anything other than SQLite: - Only one process will ever own the authoritative copy of the database at a time. - You don't have any near-term plans to cluster or otherwise go wide on your persistence layer across multiple processes. Keeping in mind that even if you do need to do this, it shouldn't be a huge deal to import your SQLite files into something that natively supports clustering like Postgres, or use some partitioning strategy that can distribute SQLite databases across multiple nodes. I feel like a lot of misinformation has been put forth that would make SQLite seem unacceptable for larger-scale concurrent business applications, but every benchmark I've personally ran against SQLite makes every other database engine look like a joke (within the constraints laid out above; obviously a 30 node Postgres cluster is going to destroy a single instance of SQLite). You just need to understand a few magic PRAGMAs to get things up and running (journal mode = WAL), because it certainly isn't capable of any of this directly out of the box. I don't know about the 'anger' aspect of it, but I certainly feel dejected by the current approach that most take regarding business object persistence. SQLite feels like a way to sidestep an entire universe of non-value-add concerns (creating a separate sql instance, networking, security, configuration, policy, etc). In terms of management, SQLite absolutely cannot be beaten. It is literally as easy as copying any other file when performing backups or restores. You need to be a little careful with the .WAL and .SHM files, but nothing that can't be managed with a few lines of script in a cron job.

lacampbell 7y ago
I'd be interested to hear as much about your set up as you can divulge. How many concurrent writing users you have, you use a cache, etc. > In terms of management, SQLite absolutely cannot be beaten. It is literally as easy as copying any other file when performing backups or restores. This - I have a lot of respect to DBAs, enough to know that I am not one.
- tracker1 7y ago
  Can't speak for GP, but it really will vary on workload. How much data is in each DB, how much workload is write vs read. In a mostly read scenario (say a blog), I'd be surprised if you couldn't handle 100k simultaneous users on a fast drive, more with a good caching strategy. For modest read/write, should be able to get 1000-10k users without much issue. A little advice would be to ensure logging is to a separate db/drive from the main data if you are logging changes, etc. The only down side is distribution with High Availability, but so many scenarios don't actually demand the extra overhead for true HA.