SQLite Forum

What would be the recommended way to hold an array of ~600 double values?
> it did not suggest anything.

It suggested that you defend your claim that 4k is a big problem.

I support Keith's basic design, though I think his "`col`" column is pointless, given the little you've shared of your program's design. That feature of his schema assumes you'll have more than one array of doubles per SQLite row, but I don't see that you've actually said that.

I challenged you on an ROI basis before. Let's say you're not actually a software developer, but you're using SQLite in support of some other job. Or, maybe you're outside the US. Either way, I'm going to say your complete salary and benefits package is $52k to make the math easy. If you spend a week fiddling around with space optimizations this fiscal year, you will have spent $1000 of your organization's money.

The sweet spot of the storage market is about $32/TB these days, so $1000 will currently buy you 10 TB of *triply-redundant* disk storage. DDR4 ECC UDIMM server RAM is going for about $4/GB, so the same $1000 will buy you 250 GiB of RAM. You can split those two values any way you like: 128 GiB of RAM in the server plus a 3-disk RAID-1 of 4 TB disks to back the DB, for example.

Is your salary + benefits package more than $52k? Okay, then your week of time doing micro-optimizations will buy even more storage.

The question then becomes, how many of these sessions are there?

Let's first take your 50 MiB session size. I'm going to guess this is your ~4k number multiplied by 8, being the 1-byte 0/1 value vs. 8-byte doubles. That's probably insupportable, since as you said earlier, you need some kind of delimeter for ASCII storage, but I'll give you the 8x multiplier anyway, because I am kind. :)

50 MB divides into our 4 TB RAID as 800000 sessions. Do you have that many customers? Oh, and realize that you've now got enough RAM to cache the entire DB for your $1000 in hardware cost.

Now let's take your worst-case value of 1GB: we've got room for 4000 of those in the DB. Do you have more than 4000 active customer sessions? Are they all maximally-sized, or is there a mix of max-sized sessions and lesser ones?

You can work it inversely, too: if each customer costs you $0.25, using the same worst-case numbers, are they each paying you at least a quarter-dollar a year? If so, then why are you spending their money fighting for every last byte of storage?

> It does make a different if avg. file-size for a single session will be ~50MB or ~1GB.

Please don't shout. (Mind, this is a moderator speaking to you now.)

> if every modification our customer is doing has to go through text parsing prior to database save it does make a noticeable difference.

Yes, thus my point: give up on ASCII storage of doubles and use the platform's actual binary representation. Now you can update single values, if you want, which will not only be good for I/O time, it'll be good for durability, because fewer bytes of the DB will be changing at any one instant.