3 ms·

I 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.

by hobs 6y ago

bob1029 6y ago
With 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 ago
  Sure, 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 ago
    And 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 ago
Out 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.
- hobs 6y ago
  If you decide you dont want to throw anything away forever you can grow pretty fast :) Some of the huge ones absolutely do binary storage for HA/DR reasons, but payroll companies and realestate companies are the mainstay and they have a tendency to store a lot of XML crap on top of the lovely normalized rows.
combatentropy 6y ago
> I regularly see 50TB total of databases on SQL Server How big is each database? With SQLite, each database is its own file. The theoretical limit for such a file is 140 TB, but the practical limit is probably much lower (https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html).
- hobs 6y ago
  Anywhere from 40GB to 10-15TB.

I 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.

by hobs 6y ago

I 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 ago

With 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 ago
Sure, 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 ago
  And 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 ago

Out 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.

hobs 6y ago
If you decide you dont want to throw anything away forever you can grow pretty fast :) Some of the huge ones absolutely do binary storage for HA/DR reasons, but payroll companies and realestate companies are the mainstay and they have a tendency to store a lot of XML crap on top of the lovely normalized rows.

combatentropy 6y ago

> I regularly see 50TB total of databases on SQL Server How big is each database? With SQLite, each database is its own file. The theoretical limit for such a file is 140 TB, but the practical limit is probably much lower (https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html).

hobs 6y ago
Anywhere from 40GB to 10-15TB.