I found out experimentally that the threshold at which the blob is transferred to the overflow page is ((U-12)*32/255)-23. This is the threshold for the index page. As a result, the base is very swollen, there is a lot of unused space in the leaf pages. What prevents placing blobs as for regular tabs?

Code to reproduce the problem in python:

import sqlite3 as sql

base1 = sql.connect('test.db', isolation_level=None)

cursor1 = base1.cursor()
cursor1.execute("""
    CREATE TABLE test(
        key INT,
        data BLOB,
        PRIMARY KEY (key)
    ) WITHOUT ROWID""")

bl = b'\xFE' * 1200

for i in range(500):
    
    query = 'insert into test(key, data) values (?, ?)'
    params = (i + 1000, bl)
    cursor1.execute(query, params)
    
base1.close()

This script reproduces the problem on a database with a page size of 4096.

A WITHOUT ROWID table is implemented as a stand-alone covering index. So it uses the same storage format as an index. This is explained in the file format documentation.

Tables use B*Trees that put all content on leaves. That means that intermediate pages contain duplicate keys, which isn't a problem since the key is just and integer and is hence small. But for an index, the key can be arbitrarily large, and so you don't want to store it in multiple places, and so indexes use ordinary B-Trees which hold content on intermediate pages as well as in the leaves. To preserve a reasonable amount of fan-out, no more than 1/4th of a page can be used for any single entry before it spills onto an overflow page. This means that the minimum fan-out will be 4. It would be very bad if we allowed arbitrarily large index entries on page resulting in a fan-out of 1.

Why there is such a restriction in the index is understandable. But nothing prevents you from storing one record in the page. It turns out that it is better not to use BLOBS in tables WITHOUT ROWID?

In the first message, there is an example in which the database grows more than 3 times...

In the first message, there is an example in which the database grows more than 3 times...

Is there a distinct reason you require WITHOUT ROWID for that case? It sounds like if you simply add an auto-incrementing/automatic rowid (even if you don't really need it for your software logic), your db size will be reduced by 1/3rd (edit: to 1/3rd of its size).

There is not 1/3, but 3 times it turns out:

• using WITHOUT ROWID is 2347 KB
• the usual option is 692 KB

The situation is clear, thank you. It needs to be very careful about BLOBS and WITHOUT ROWID.

It needs to be very careful about BLOBS and WITHOUT ROWID.

This would be incorrect. It does not matter what the payload is, whether it is TEXT, a BLOB, or a bunch of disparate types that end up having a payload that exceeds 1/4 page.

You would see the same thing if you had a table thus:

create table x
(
  id integer not null primary key,
  v1 real not null default 3.14159,
  v2 real not null default 3.14159,
  v3 real not null default 3.14159,
  v4 real not null default 3.14159,
  .. repeat ...,
  v150 real not null default 3.14159
) without rowid;

and that table does not contain even a single blob.

Example:

import sqlite3 as sql

base2 = sql.connect('test2.db', isolation_level=None)

cursor2 = base2.cursor()
sql = 'CREATE TABLE test(key INT PRIMARY KEY, ' + ', '.join('v{} real default 3.14159'.format(i) for i in range(1,150)) + ') without rowid'
print(sql)
cursor2.execute(sql)

for i in range(500):
    query = 'insert into test(key) values (?)'
    params = (i + 1000,)
    cr = cursor2.execute(query, params)

base2.close()

This would be incorrect. It does not matter what the payload is, whether it is TEXT, a BLOB, or a bunch of disparate types that end up having a payload that exceeds 1/4 page.

Of course, I expressed myself incorrectly.

The most important thing I realized is this: with the most unsuccessful payload size, the size of the table will increase in different ways (due spliting onto overflow pages). With rowid - twice (payload about the page size), WITHOUT ROWID - 4 times (payload about 1/4 of the page).

If a large fraction of rows have a size just above the 1/4 page threshold, you could try using a different page size.

For reference and completeness, you could perhaps post the results of the analyzer tool.
Would illustrate the empty space in overflow pages.

I'm not 100% on this, but I guess the issue is that an overflow page contains bytes only for a single row?

From the file format doc, I don't see any offset anywhere between the row head and the overflow page (its tail).
Unless SQLite can consolidate overflow for all rows on the same B*Tree page, knowing their order and total size,
and thus deduce offsets for same-overflow-page sibling rows? I've been meaning to figure this out for a while,
but I'm taking the opportunity of this thread to ask instead, since it is relevant.

I guess the issue is that an overflow page contains bytes only for a single row?

Yes.

/** Disk-Space Utilization Report For test2.db

Page size in bytes................................ 4096      
Pages in the whole file (measured)................ 573       
Pages in the whole file (calculated).............. 573       
Pages that store data............................. 573        100.0% 
Pages on the freelist (per header)................ 0            0.0% 
Pages on the freelist (calculated)................ 0            0.0% 
Pages of auto-vacuum overhead..................... 0            0.0% 
Number of tables in the database.................. 2         
Number of indices................................. 0         
Number of defined indices......................... 0         
Number of implied indices......................... 0         
Size of the file in bytes......................... 2347008   
Bytes of user payload stored...................... 603000      25.7% 

*** Page counts for all tables with their indices *****************************

TEST.............................................. 572         99.83% 
SQLITE_SCHEMA..................................... 1            0.17% 

*** Page counts for all tables and indices separately *************************

TEST.............................................. 572         99.83% 
SQLITE_SCHEMA..................................... 1            0.17% 

*** All tables ****************************************************************

Percentage of total database...................... 100.0%    
Number of entries................................. 501       
Bytes of storage consumed......................... 2347008   
Bytes of payload.................................. 603121      25.7% 
Bytes of metadata................................. 6972         0.30% 
Average payload per entry......................... 1203.83   
Average unused bytes per entry.................... 3466.90   
Average metadata per entry........................ 13.92     
Average fanout.................................... 7.00      
Maximum payload per entry......................... 1206      
Entries that use overflow......................... 500         99.80% 
Index pages used.................................. 9         
Primary pages used................................ 64        
Overflow pages used............................... 500       
Total pages used.................................. 573       
Unused bytes on index pages....................... 5694        15.4% 
Unused bytes on primary pages..................... 43721       16.7% 
Unused bytes on overflow pages.................... 1687500     82.4% 
Unused bytes on all pages......................... 1736915     74.0% 

*** Table SQLITE_SCHEMA *******************************************************

Percentage of total database......................   0.17%   
Number of entries................................. 1         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 121          3.0% 
Bytes of metadata................................. 112          2.7% 
B-tree depth...................................... 1         
Average payload per entry......................... 121.00    
Average unused bytes per entry.................... 3863.00   
Average metadata per entry........................ 112.00    
Maximum payload per entry......................... 121       
Entries that use overflow......................... 0            0.0% 
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 3863        94.3% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 3863        94.3% 

*** Table TEST ****************************************************************

Percentage of total database......................  99.83%   
Number of entries................................. 500       
Bytes of storage consumed......................... 2342912   
Bytes of payload.................................. 603000      25.7% 
Bytes of metadata................................. 6860         0.29% 
B-tree depth...................................... 3         
Average payload per entry......................... 1206.00   
Average unused bytes per entry.................... 3466.10   
Average metadata per entry........................ 13.72     
Average fanout.................................... 7.00      
Non-sequential pages.............................. 0            0.0% 
Maximum payload per entry......................... 1206      
Entries that use overflow......................... 500        100.0% 
Index pages used.................................. 9         
Primary pages used................................ 63        
Overflow pages used............................... 500       
Total pages used.................................. 572       
Unused bytes on index pages....................... 5694        15.4% 
Unused bytes on primary pages..................... 39858       15.4% 
Unused bytes on overflow pages.................... 1687500     82.4% 
Unused bytes on all pages......................... 1733052     74.0%