Great article! I think the explanation of leaf vs interior pages might have benefitted from a diagram. My understanding after reading it is this:
3───────┐ │pk data│ 1───────┐ ┌─┤A ....│ │rng ptr│ │ │B ....│ ┌─┤A-B 3 ├─┤ 4───────┤ │ │C-D 4 │ │ │pk data│ 0───────┐ │ └───────┘ └─┤C ....│ │rng ptr│ │ │D ....│ │A-D 1 ├─┤ 5───────┤ │E-H 2 │ │ │pk data│ └───────┘ │ 2───────┐ ┌─┤E ....│ │ │rng ptr│ │ │F ....│ └─┤E-F 5 ├─┤ 6───────┤ │G-H 6 │ │ │pk data│ └───────┘ └─┤G ....│ │H ....│ └───────┘
(also reading https://www.sqlite.org/fileformat.html says that the row’s rowid will be used as the B+Tree page key instead of the PK, but I assume that’s a simplification on the article’s part because an integer PK will be used as the rowid. So assume the PKs in the above diagram are octodecimal :-) )
rowid
You are correct by default, however SQLite also has WITHOUT ROWID to use the actual primary key as the page key.
WITHOUT ROWID
Great article! I think the explanation of leaf vs interior pages might have benefitted from a diagram. My understanding after reading it is this:
(also reading https://www.sqlite.org/fileformat.html says that the row’s
rowid
will be used as the B+Tree page key instead of the PK, but I assume that’s a simplification on the article’s part because an integer PK will be used as the rowid. So assume the PKs in the above diagram are octodecimal :-) )You are correct by default, however SQLite also has
WITHOUT ROWID
to use the actual primary key as the page key.