The Anatomy of a Database Insert: Where Does Your Data Go After INSERT?

Meet our two developers:

• 200 (The Explainer): Named after the HTTP “200 OK” status code. They are the reliable senior engineer who always seems to know how things work under the hood.
• 404 (The Questioner): Named after the HTTP “404 Not Found” status code. They are the curious junior developer, constantly searching for answers and trying to make sense of complex systems.

404: Hey 200, if you are free can we go for a tea break?

200: Sure, I’m always ready for Tea buddy…!

(They grab their cups and head to the Cafeteria)

404: Man, I was just running a massive INSERT script and it was taking forever. It got me thinking — when I write an insert statement and hit execute, what is actually happening? Where does that data physically go under the hood?

200: That’s a great question. We use databases every day, but have you ever wondered how the data is physically stored inside them?

404: I honestly just assumed it gets dumped into some giant, hidden text file or a massive spreadsheet on the server. Does it not?

200: Surprisingly, databases don’t store data in a human-readable format at all. Data is typically organized into binary pages, blocks, and other storage structures optimized for efficient random and sequential I/O operations.

404: Whattt! Pages…!!!? Blocks…!!!? What are these?

200: It works like a hierarchy. Table records are stored in Pages (or DB blocks), which are grouped into Extents, which are saved to Data Files, which the operating system finally writes to Physical Disk Sectors.

404: ??? Can you please double-click on that and explain?

200: Sure! Let’s start with the smallest piece.

Data Pages:

A database page (also called a Data Block in some databases like Oracle) is the smallest unit of storage that a database engine reads or writes.

Think of a data page as a database’s storage container. Instead of saving each row separately on the physical disk, the database packs multiple rows into these fixed-size pages (usually 8 KB or 16 KB). Whenever data is read or written, the database moves these entire pages into memory rather than pulling individual rows.

404: Okay, that makes sense. But if it’s just a fixed-size 8 KB container, how does the database organize the data inside it? How does it know where one row ends and the next one begins? Can I see what a page actually looks like?

200: Wait, I’ll show you how a Data Page typically looks…

(200 pulls up a diagram on their phone and shows it to 404)

Data Page

404: Looks cute…!

200: Cute ? (Takes a sip of tea) Spend a minute looking at the above image, sorry “Cute image.”

404: I see some text inside the image like Page Header, Data Records, Free Space, Offset Array, etc. What are those exactly?

200: Those are the fundamental essentials to look “Cute.” (Both start laughing) Jokes apart, I’ll help you understand all the terminologies mentioned in this image.

Get Prashant Chavan’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

There are three major sections:

• Page Header: Contains metadata about the page, such as the Page Number, the amount of Free Space, checksums for data integrity, etc.
• Data Records: This is where the actual row data is physically written.
• Offset Array (or Row Offsets): Think of this as an index for the page itself. It is an array where each index holds a pointer to the exact starting location of the corresponding data record above it.

404: So, we can store all my million records in a single page, right?

200: No! As you can see, the page and its sections have a fixed total size (usually 8 KB). Let’s say the Page Header takes up 96 Bytes, leaving roughly 8000 Bytes for Data Records and the Offset Array.

If a single record is around 200 Bytes, you can store roughly 40 records in one Data Page.

For example:

id (16 Bytes) | name (160 Bytes) | city (20 Bytes) | pincode (4 Bytes) |

404: If I understand correctly, I would need 25,000 Data Pages to store my 1 million records.

(1,000,000 records / 40 records per page = 25,000 pages)

200: Exactly.

404: But I never remember creating Data Pages manually. Who creates them exactly, and who maintains them?

200: Data pages are automatically created and managed by the database engine behind the scenes. Whenever you insert data that exceeds the capacity of existing pages, the database allocates a new one.

Think of a data page like an empty notebook page. You start writing text lines from the top down (Data Records), but you also start writing a table of contents from the bottom margin up (Offset Pointers). The clean, unwritten paper left in the dead center is your Free Space. It sits right between the boundaries of your written data and your pointers.

404: Wait… you mentioned Extents earlier. We spoke a lot about Pages, but what exactly is an Extent?

200: Good catch! Think of an Extent as a collection of consecutive Data Pages. Instead of allocating storage one page at a time, the database often allocates multiple pages together as a single unit. This improves allocation efficiency and helps keep related data physically close together.

404: So the hierarchy is basically:

Records → Pages → Extents → Database Files → Physical Storage

200: Exactly!

404: Ohh, interesting… I understand that the table records are stored in Pages now. But what next? Data pages are stored on disk, aren’t they?

200: Yes. Data pages are stored inside database files on the disk. However, they are not written directly to raw disk sectors. The database stores pages inside its own database files, and the Operating System (OS) maps those files to filesystem blocks and physical disk sectors underneath.

When a query requests a single row, the database usually loads the entire page containing that row into memory. This page-based I/O model is one of the key reasons databases organize data into pages and use structures like B+ Trees for efficient retrieval.

NOTE: For now, we’ll set aside the detailed discussion of B+ Trees and Index types such as Dense, Sparse, Secondary, and Clustered to stay focused on the core concept.

404: I’m getting a bit confused with the “Blocks” terminology. Can you elaborate a bit?

200: Sure! There are actually two different kinds of blocks involved here:

1. Database Pages (called Data Blocks in Oracle): These are managed by the database engine. The DB stores rows inside pages because reading and writing rows one at a time from a physical disk would be incredibly slow. For example, if your user record is 200 Bytes, the database does not perform a tiny 200-Byte disk read. Instead, it grabs the entire 8 KB page containing that row.
2. Storage / Filesystem Blocks: These are managed by the OS and the physical storage device. When the database requests its 8 KB page, the OS might read multiple filesystem blocks (for example, two 4 KB OS blocks) from the hard drive to satisfy the database’s request.

404: Got it! Thanks for the explanation, it was a productive tea break
(for me, at least) (laughs)

200: You are always welcome buddy.

404: Let’s catch up around lunch time! I want to know how Data Retrieval from the database works internally.

200: Sure, see you!