When is db size an issue?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

When is db size an issue?

Jason T. Slack-Moehrle
Hello All,

Off and on for the last few years I have been writing an e-mail client to
scratch a personal itch. I store the mail in SQLite and attachments on the
file system. However, I recently brought in all of my mail for the last 15
years from mbox format. Now, my database size is over 10gb. I'm not seeing
any real performance issues and my queries are executing nice and fast
during search.

However, does anyone have any thoughts about the size? Should I be
concerned? Is there a theoretical limit I should keep in the back of my
mind?

Jason
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: When is db size an issue?

Simon Slavin-3


On 27 Sep 2017, at 1:41pm, Jason T. Slack-Moehrle <[hidden email]> wrote:

> However, does anyone have any thoughts about the size? Should I be
> concerned? Is there a theoretical limit I should keep in the back of my
> mind?

Not, theoretical, practical:

<https://sqlite.org/limits.html>

Sorry, but your email database will be limited to 140 Terabytes in size, and if your users want to keep more than half a quadrillion messages every day for a hundred years they’re going to have to archive the older ones.  Such a shame.

I used to have a 43Gb database, most of it being one long thing table of hundreds of billions of rows.  Ran without problems and was extremely fast to search.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: When is db size an issue?

Eduardo Morras-2
In reply to this post by Jason T. Slack-Moehrle
On Wed, 27 Sep 2017 08:41:25 -0400
"Jason T. Slack-Moehrle" <[hidden email]> wrote:

> Hello All,
>
> Off and on for the last few years I have been writing an e-mail
> client to scratch a personal itch. I store the mail in SQLite and
> attachments on the file system. However, I recently brought in all of
> my mail for the last 15 years from mbox format. Now, my database size
> is over 10gb. I'm not seeing any real performance issues and my
> queries are executing nice and fast during search.
>
> However, does anyone have any thoughts about the size? Should I be
> concerned? Is there a theoretical limit I should keep in the back of
> my mind?

I developed and use a filesystem over sqlite db, focused on filesizes
<1MB and worm (write-once-read-many) access pattern. Some sqlite dbs
have 500GB-1TB (few even bigger) on mechanical disks without speed
problems.

Easy to backup, use fts on someones and can attach-deattach filesystems
db. One advice, increase sqlite internal cache, don't use its default
value.

>
> Jason



---   ---
Eduardo Morras <[hidden email]>

.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: When is db size an issue?

Roberts, Barry (FINTL)
In reply to this post by Jason T. Slack-Moehrle
We have logged live data into databases that are over 200GB without any issues. I don't think SQLite will be the issue itself, as it is merely limited by the file system. The more likely issue is when you need to transfer or backup the file, it can get unwieldy if it is too large.

Kind Regards,
Barry Roberts.

[hidden email]<mailto:[hidden email]> | www.fugro.com


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users