What actually happens
when you INSERT a row?
You write one innocent line: db.Insert("Users", row). On the surface it looks trivial — but under the hood, five layers cooperate in milliseconds to make that row findable, durable, and safe from crashes. Let's trace it.
Why should you care?
When you tell an AI "add a column" or "speed up this query," it needs to know which layer the change belongs to. If you can name the layers — storage, index, transaction, query — you can steer the AI with specifics instead of accepting generic, half-right code.
The five layers, top to bottom
Every insert falls through these. Each row below is one specialist with exactly one job.
1 · Public API
The friendly front door: db.Insert(...). Hides everything below it.
2 · Table & Schema
Validates the row against the column types and extracts the primary key.
3 · Transaction + WAL
Records the change in a durable log before applying it — the durability point.
4 · B+ Tree Index
Stores the row by key in sorted order so it can be found again in O(log n).
5 · Page Storage
Lays the bytes onto fixed-size 4 KB pages — the only thing the disk understands.
Read the real code
This engine is a real C#/.NET embedded database. Here's the public API a developer actually calls — code on the left, plain English on the right.
using var db = new Database("mydata.mde", cacheSize: 100);
var columns = new List<ColumnDefinition>
{
new ColumnDefinition("Id", DataType.Int, false),
new ColumnDefinition("Name", DataType.String),
new ColumnDefinition("Age", DataType.Int)
};
var table = db.CreateTable("Users", columns,
primaryKeyColumn: "Id");
var row = new DataRow(table.Schema);
row["Id"] = 1;
row["Name"] = "Alice";
row["Age"] = 30;
db.Insert("Users", row);
In plain English
Open (or create) a database file on disk. The cacheSize says how many pages to keep hot in memory.
Describe the schema: three columns, with Id as the primary key. The schema is the contract every row must satisfy.
Build one row, fill its cells by column name, and hand it to db.Insert. That single call is the whole iceberg tip — everything else in this course is what happens beneath it.
Watch the row fall through the stack
Press play. The packet is your row; follow it from your code down to durable bytes. The amber step is where durability is guaranteed.
Inside Table.Insert
Here is the actual insert path inside the Table layer. Notice the order of operations — it is the entire story in fifteen lines.
public void Insert(DataRow row, Transaction? transaction = null)
{
_lock.EnterWriteLock();
try
{
var key = GetPrimaryKey(row);
ValidateRowForWrite(row);
var keyExists = _index.Search(key) != null;
if (keyExists || keyPendingInTransaction)
throw new InvalidOperationException(
$"Duplicate primary key value '{key}'.");
var serialized = SerializeRow(row);
if (transaction != null)
{
transaction.LogInsert(_schema.TableName, key, serialized);
return;
}
_index.Insert(key, serialized);
}
finally
{
_lock.ExitWriteLock();
}
}
Line by line
EnterWriteLock — grab an exclusive lock so no other thread can write this table at the same time (Module 6).
GetPrimaryKey / ValidateRowForWrite — pull out the key and check the row obeys the schema.
_index.Search(key) — reject duplicates. A key already in the tree (or buffered in this transaction) is an error.
SerializeRow — flatten the row to bytes.
transaction != null — if inside a transaction, only log the insert now; the index is updated later, on commit (Module 5). Otherwise, write straight to the index.
finally — release the lock no matter what, even if an exception was thrown.
Separation of concerns. Each layer has one job. Durability is the WAL's job, sort order is the index's job, disk I/O is the storage engine's job. Confusing these is the #1 bug in amateur database code.
Zoom in: what "serialize to bytes" really means
Step 4 — "the row becomes a flat byte array" — sounds like hand-waving. It isn't. A disk can't store a tidy { Id: 1, Name: "Alice" } object; it stores bytes. Serialization is the precise rule for turning each field into bytes. Here is exactly what this engine's DataSerializer produces — edit the row and watch every byte update live.
Three rules are visible here. (1) Every field starts with a null-marker byte — 01 means "a value is present" (a null would be a lone 00). (2) An Int is always exactly 4 bytes, written little-endian (least-significant byte first — that's why 1 shows as 01 00 00 00). (3) A String is variable length, so it's prefixed with its byte-count, then the UTF-8 characters. Fixed-width numbers, length-prefixed text — that's the whole grammar.
Try typing a longer name, or a multi-byte character like é or 日. The length prefix and byte count climb — proof that text is genuinely variable-width on disk, which is exactly why the storage layer (Module 2) can't assume every row is the same size.
The seven steps, at your own pace
The same journey as the animation, frozen so you can re-read it.
You call db.Insert
One method call enters the engine carrying your row.
Database finds the table
Looks up "Users" by name and forwards the row to it.
Table validates the row
Checks each cell against the schema; rejects type mismatches and duplicate keys.
Row is serialized to bytes
The structured row becomes a flat byte array — the form disk understands.
Bytes appended to the WAL
Written to the durable log first. If we crash after this, recovery can replay it.
Bytes inserted into the B+ tree
Filed by key in sorted order, ready for fast lookups and range scans.
Success returns to you
The row is findable, durable, and safe. Total time: milliseconds.
Check yourself
Four questions. Pick an answer to reveal the reasoning.
_index.Search(key), the Table checks HasBufferedValueForKey — a key still pending in the current transaction also counts as a duplicate, which can surprise you mid-transaction.Up next: we zoom into the very bottom of the stack — storage. How does the engine physically organize a disk it can only address in fixed-size blocks? Enter pages and extents.