3 ms·
For our B2B application, we've been using SQLite as the exclusive means for reading and writing important bytes to/from disk for over 3 years now. We still hav
by bob1029 6y ago
For our B2B application, we've been using SQLite as the exclusive means for reading and writing important bytes to/from disk for over 3 years now.
We still have not encountered a scenario that has caused us to consider switching to a different solution. Every discussion that has come up regarding high availability or horizontal scaling ended at "build a business-level abstraction for coordination between nodes, with each node owning an independent SQLite datastore". We have yet to go down this path, but we have a really good picture of how it will work for our application now.
For the single-node-only case, there is literally zero reason to use anything but SQLite if you have full autonomy over your data and do not have near term plans to move to a massive netflix-scale architecture. Performance is absolutely not an argument, as properly implemented SQLite will make localhost calls to Postgres, SQL Server, Oracle, et. al. look like a joke. You cannot get much faster than an in-process database engine without losing certain durability guarantees (and you can even turn these off with SQLite if you dare to go faster).
- Kaze404 6y agoI often connect to production databases in read only users to do various data analysis. Is this something you can do with SQLite (besides maybe SSHing into the machine)? If not, how do you get around it (if it ever even comes up)?
- bob1029 6y agoWe have a few paths for this type of thing. One is to simply zip up the entire database and send it across the wire. This is most applicable for local development and QA testing scenarios. Another is to have something in the business application and relevant tooling that allows for programmatic querying of the data we need to look into. We also have some techniques where we do ETL of the data range we care about from 1 SQLite db to another, then pull down the consolidated db for analysis.
- hobs 6y agoI love sqlite, but just a wonder on how big you are going? I regularly see 50TB total of databases on SQL Server, and scaling up to thousands of clients.
- bob1029 6y agoWith current volume, our largest transactional datastore is ~50GB. That said, if we had a situation where we were pushing such volume that the transactional store was 50 TB, and we were still within the scope on the overall project, I see no reason why this would be a problem for us. As I mentioned prior, the context of our applications is single process, single server. Obviously, this is not suitable for all edge cases and you will potentially be better off with a hosted solution in such cases. At the end of the day, there is no arbitrary number of bytes where B-Tree indicies just stop working because you didn't shell out for a SQL Server license.
- hobs 6y agoSure, but things like horizontally partitioning your data across multiple hard drives via splitting files, supporting partition elimination in your queries, etc are all things that I am pretty sure sqlite doesnt even want to bring to to the table.
- bob1029 6y agoAnd I would hope that they do not bother to. The current offering is arguably perfect as-is. I would prefer to implement things like horizontal partitioning myself using business keys and logic that make sense for my problem. I do not want a database to manage this sort of thing for me, because I want perfect visibility into exception cases so business logic can be developed around them. I.e. if one of the drives fails, but I was only storing a lower-priority business entity on that particular partition (e.g. user preferences), I could decide to just continue processing without it (e.g. reset to defaults). With a hosted database solution where you do not have this degree of granularity, a catastrophic loss of part of the storage array would mean the entire database is down. Even if the important data isn't directly impacted.
- CJefferson 6y agoOut of interest, what circles are you moving in? Are people filling their databses with videos or something? I'm sure I just lack the imagination, but i cant imagine how there can be thwt many companies who can make TB of data they then want to shove in a database. The complete sales history of even a medium sized company should still easily fit in a GB or two.
- rakoo 6y ago> build a business-level abstraction for coordination between nodes, with each node owning an independent SQLite datastore" There even are solutions that do this already: - rqlite (https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite) - actordb (https://www.actordb.com/ https://www.actordb.com/)
- seektable 6y ago+1 we use SQLite in our BI tool to keep information about user accounts, data sources (cubes) / reports configs, access control rules etc. SQLite works like a charm even in cloud version where we have > 6,000 registered user accounts.
- HelloNurse 6y agoA successful business, but not a demanding database: 6000 users accessing the database hundreds of times per day each, for small reads and small writes, are unlikely to need more than a few GB of space (the parts of the database in use should fit in the operating system's disk cache) and unlikely to exceed 20-30 small I/O operations per second.
- untog 6y agoBut it accurately describes a lot of use cases where something like Postgres (or MSSQL or even Oracle!) have been deployed because it’s what people assume is needed. It’s great to see accounts detailing how simple things can be.
- manquer 6y agoPerhaps the needs in enterprise are slightly different. Handling HA , backup and security! in a typical deployment entire DB is a single file on the application runtime, in a multi-user context this is significant risk, basically you are trusting each app developers and admins to made the environment and code secure for each as opposed to centralized DB admins doing it. It is easier for a existing DB service stack when you are just adding one more DB, than handling DBs distributed across hundreds of applications typical in any organization. Managed SQL services for SQL variants are readily available and fairly affordable for applications which do not have access to such shared service layers . It does not mean that SQLite has no place I love using it in native applications and mobile apps, however multi-user web applications is perhaps not one of them, even concurrency is not a problem
- lenkite 6y agoHow do you sync db data across nodes ? Custom built solution or use something off the shelf ?