I was reading the file format and there is a talk about value of P that can be calculated using U-35 for table b-tree to find how much the cell content can be before it is force to spill and go to another overflow page. The calculation seems simple but how one can know, how much is available?
As U is said to be the size of page minus the reserved bytes. But a page maybe be already filled by a couple of records. And how one know the exact size of those rows? It should be harder because of variant values and no public api to get the size of fields as far as I know.

Now I am wondering, what is the best way to calculate how much available useful storage is in the current page?
For example, if we have a simple rowid table and only one blob column, what will be the maximum size of the blob for next insert to fill this page and not spill?

Bonus question: 
If a value is forced to go to another overflow page, what will happen to the free space of that overflow page? For example considering a 4kb page, if the value is 6kb, and it takes one and half page, is the remaining 2kb never get to be used? Or another overflow can use that?

As I didn't get any answer or comment, I should ask if my question is irrelevant or wrong?

Is there any clear way to calculate it?
I have a lot of blob and I saved them as chunks to limit memory usage and blob limit size.
But my database gets a lot higher than I want as there are many half used pages because of those blobs.
So I prefer to split them dynamically.

That is unfortunate.
So there is no way to better for storing blobs without wasting extra space?

Maybe if there was an official SizeOf() I could, calculate how much data I inserted or deleted, and use that to know what page I am.

Your analysis is on point.
- Beside indexing and keeping data, SQLite gives you ACID too, and I want that.
- You can not do it safe a secure with a file system.
- Also note that for small blobs (64kb) SQLite is faster most of the time.
- And keeping copy, backup and such tasks much easier as you have one source or file for that.

My only problem is to reduce that "space wastage" by inserting chunk of those blobs according to what I have in the page, so I don't trigger SQLite to make half used overflow pages.
I understand SQLite is a generic database, focused on SQL. But it is far from that now, and using it as a blob storage seems quite a useful task.

Yes I think I can make it work. But one big no-no for me is to mess things up with SQLite internals. I like to let it do it's job and me doing mine. 
This post is to find thoughts and ways on the best ways of doing this without making hacked ways.
And you are right, you can make a fork, but I respect the thought process of @drh and if he thinks there is no need for it, I would respect that and find a more vanilla way, with a sad face.

Indeed and I saw the results.

My problems as I posted before are:
- Speed of inserting BLOB is lower than Filesystem. 1.5GB/S in a 2.5 GB/S machine. And I guess it is the cost of processing pages.
- Second, it is the topic of this post. Problem of pages overhead. Looking for a better way to organize BLOB to generate as low as possible of wasted storage.

It would be lovely to have your thought on this, especially as you designed sqlar and suggested storing content as blob through the years.

Greta ideas to explore.
Thank you!

A question for @drh.
Is this topic useful for sqlar? Or was it came to your mind but felt not useful?

I will be happy to hear any opinions about such topic, to be clear, storing blobs preferably chunked (to prevent size limit and memory usage) while keeping the overhead of overflow pages down.

To simplify the problem, I am trying to find the optimal size of a BLOB that:
- Create just one overflow page and use it all
- Allocate as little as possible from the leaf page.


Reading the documentation at https://www.sqlite.org/fileformat2.html#b_tree_pages
Shows that for a database with 4096B pages and no reserved bytes:
U = PageSize - ReservedBytes = 4096 - 0 = 4096
X = U - 35 = 4096 - 35 = 4061
M = ((U-12)*32/255)-23) = 489.5019
K = M+((P-M)%(U-4)) = ? (As we do not know P for now)

It is stated that, each overflow page, has 4 bytes at the beginning, and the rest is the content, so the useful bytes for the overflow page is:
O = U - 4 = 4096 - 4 = 4092

From documentation, I think I want this part:
"If P>X and K>X then the first M bytes of P are stored on the btree page and the remaining P-M bytes are stored on overflow pages."

From what I understand, the optimal size of P must be:
P = O + M = 4092 + 489.5019 = 4581.5019 (What can be in a overflow page + The minimum it must be stored on the leaf)
I can interpret this value as 4581 or 4582 depending on the rounding as I do not know where is this code of this part is.

I tried this to insert just one record with one blob in a table as:
CREATE TABLE IF NOT EXISTS "Value" ("Data" BLOB);

And get the stats like:
sqlite3_analyzer-32.exe --stats "test.db"

The result for P as 4581 is:

BEGIN;
CREATE TABLE stats(
  name       STRING,           /* Name of table or index */
  path       INTEGER,          /* Path to page from root */
  pageno     INTEGER,          /* Page number */
  pagetype   STRING,           /* 'internal', 'leaf' or 'overflow' */
  ncell      INTEGER,          /* Cells on page (0 for overflow) */
  payload    INTEGER,          /* Bytes of payload on this page */
  unused     INTEGER,          /* Bytes of unused space on this page */
  mx_payload INTEGER,          /* Largest payload size of all cells */
  pgoffset   INTEGER,          /* Offset of page in file */
  pgsize     INTEGER           /* Size of the page */
);
INSERT INTO stats VALUES('sqlite_schema','/',1,'leaf',1,56,3928,56,0,4096);
INSERT INTO stats VALUES('Value','/',2,'leaf',1,492,3587,4584,4096,4096);
INSERT INTO stats VALUES('Value','/000+000000',3,'overflow',0,4092,0,0,4096,4096);
COMMIT;

It shows that no unused byte for page 3, the overflow page.

But, because of curiosity, I checked all P from 4577 to 4582 and here are the result for page 2 and 3, table value:

P = 4577 : 
INSERT INTO stats VALUES('Value','/',2,'leaf',1,489,3590,4580,4096,4096);
INSERT INTO stats VALUES('Value','/000+000000',3,'overflow',0,4091,1,0,4096,4096);
There is 1 unused bytes on page 3, and 3590 bytes for page 2.

P = 4578:
INSERT INTO stats VALUES('Value','/',2,'leaf',1,489,3590,4581,4096,4096);
INSERT INTO stats VALUES('Value','/000+000000',3,'overflow',0,4092,0,0,4096,4096);
There is 0 unused bytes on page 3, and 3590 bytes for page 2.

P = 4579:
INSERT INTO stats VALUES('Value','/',2,'leaf',1,490,3589,4582,4096,4096);
INSERT INTO stats VALUES('Value','/000+000000',3,'overflow',0,4092,0,0,4096,4096);
There is 0 unused bytes on page 3, and 3589 bytes for page 2.

P = 4580:
INSERT INTO stats VALUES('Value','/',2,'leaf',1,491,3588,4583,4096,4096);
INSERT INTO stats VALUES('Value','/000+000000',3,'overflow',0,4092,0,0,4096,4096);
There is 0 unused bytes on page 3, and 3588 bytes for page 2.

P = 4581:
INSERT INTO stats VALUES('Value','/',2,'leaf',1,492,3587,4584,4096,4096);
INSERT INTO stats VALUES('Value','/000+000000',3,'overflow',0,4092,0,0,4096,4096);
There is 0 unused bytes on page 3, and 3587 bytes for page 2.

P = 4582:
INSERT INTO stats VALUES('Value','/',2,'leaf',1,493,3586,4585,4096,4096);
INSERT INTO stats VALUES('Value','/000+000000',3,'overflow',0,4092,0,0,4096,4096);
There is 0 unused bytes on page 3, and 3586 bytes for page 2.

The optimal value seems to be 4578 as lower values, leave extra space on page 3 and higher one use from page 2.


Now I am curious, was my math wrong, or did I have a mistake somewhere else, or is there an undocumented behavior in SQLite internals for processing overflow?

Thank you for pointing that out!
Can you help me how did you know you should add 3?

Please correct me if I am wrong but what I understand from documentation, a "record format" is like:
Header:
- Size in bytes, stored as variant.
- Array of "serial type" variant for each column.
Body:
- Array of value of each column.

From my understanding of documentation and your post, actual size is "Serial Type Size" + "Value Size".

For BLOB of length is "(N-12)/2", N being the serial type.
For this sample, length is 4578 bytes, so:
L = (N-12)/2 = 4578
Then we can calculate N as:
N = (L*2)+12 = 9168
This number needs 14 bits, and as far as I understand the variant type, it uses 7 bits for each byte for first 8 bytes. So it will need 16 bits to store this 7 bits number, and it is 2 byte.

Size should be:
4578 + 2 = 4580
But you showed that it is 4581.

What is that missing one? What is the size-of-header?

So the payload is the whole record without the key (in this case ROWID)?
Then if I have multiple columns other than the BLOB one, I should get the size of them and calculate the payload as described to find out the actual size of the payload?

If that is the case, as far as I know there is no SizeOf() function to get the size of each value, correct?
What will be the best way to calculate the size of the payload? Doing custom math, for each value?

Note that the values NULL, 0, 1, empty string and empty blob do not take up any space in the payload. Changing a value between that set and anything else will affect the length of the payload and invalidate the previous BLOB segmentation calculation

Yes.
But I am curious about what is the best way to calculate payload size?

Thank you.

Is there a good way to calculate record or payload size in exact byte size?
I’m afraid that it is not going to be easy to calculate because as far as I know, SQLite converts some values like REAL if it is possible.

I really wish for a simple way to get a value or record size right now.

Exactly.
As SQLite has been and is a very suitable file format, and size is a critical need, especially for network-intensive projects, loss of space will be a problem for such applications and use cases.

One big part and benefit of SQLite is the pager, and finding a way to use it for such needs would be a big win.

dbstat.c has the calculation for unused bytes, so one can use that to know how much space is available to insert the next record and split the BLOB to use that space.
But one question is, how one can calculate the size of a cell before inserting it?
It is possible to create custom functions calculating that, but as SQLite does some internal magic including variants and converting types (REAL to INETEGER), I guess it will not be an easy task.
Is there any internal function that helps with this?


To be clear, I need these:
- A way to know how much free space is available in this current page. So I can split the blob according to that.
- A way to calculate size of row/cell to make a chunk of blob to just fit that free space.

I would appreciate any help.

Yes I assume, SQLite always adds to a page, whatever that is, it know where it wants to insert the cell, knows the size of the cell too.