A table has a column that sometimes holds a multi-megabyte value, far larger than a single 8 KB page. How do storage engines physically store such values, and what does that mean for queries that do not select that column?

## The constraint that forces the mechanism A row is stored contiguously within one page, and pages are typically 8 or 16 KB. A 5 MB document therefore cannot be stored inline by definition. Even a 3 KB value is problematic, because putting only two rows per page would wreck scan efficiency for the whole table. Engines resolve this with off-page storage, known by various vendor names, but with a common shape. ## How it works When a row is about to be written and exceeds a threshold (often a fraction of the page, such as a quarter), the engine works through its variable-length columns largest first: 1. **Compress in place.** Many large values are text or JSON and compress well. If compressing brings the row under the threshold, it stays inline and no separate structure is needed. 2. **Move out of line.** Otherwise the value is split into chunks sized to fit pages of a separate overflow structure: a companion table, a dedicated segment, or a chain of overflow pages linked from the row. Each chunk is stored with an identifier and a sequence number so the value can be reassembled in order. 3. **Leave a pointer.** The main row keeps a small descriptor: the value identifier, the total length, and whether it is compressed. That descriptor is a few dozen bytes instead of megabytes. The main row is now small again, and the table's rows-per-page stays high. ## Reading behaviour, and why it matters Off-page values are fetched **lazily**. The engine reads the main row, and only if the query actually needs that column does it follow the pointer, read the chunks and decompress them. Three consequences follow directly. **Not selecting the column is genuinely free.** `SELECT id, created_at FROM docs WHERE ...` scans only the main pages. The multi-megabyte payloads sit untouched in overflow. This is the sharpest practical argument against SELECT star: on a table with large columns, the difference between selecting three small columns and selecting everything can be one or two orders of magnitude in I/O. **Selecting the column costs extra I/O per row, and it is scattered.** Each large value may require several page reads from the overflow structure, and those reads are not sequential with the table scan. Returning 10,000 large values is a very different operation from returning 10,000 small ones. **Predicates on the column are expensive.** A filter such as `WHERE body LIKE '%needle%'` cannot be evaluated without fetching and decompressing each value. The plan may look like a simple scan while actually performing an enormous amount of hidden work. Where such searches are a real requirement, the answer is a purpose-built index (full-text or trigram style) or an extracted, materialised summary column, not a scan over the payload. ## Other consequences to know - **Aggregates over length or presence.** Asking whether the column is NULL is answered from the row header and the pointer, so it is cheap, whereas computing its length may or may not require fetching, depending on whether the length is kept in the descriptor. - **Indexes.** Index entries must fit in an index page, so a multi-megabyte value cannot be an index key. Engines either refuse the index, truncate, or index a hash or an expression of the value. Uniqueness on a large value is usually implemented as uniqueness on a hash, which changes the semantics you must reason about. - **Write amplification.** Inserting a row with a large value writes the main row plus every chunk, all of it logged for durability. Updating any column of that row may or may not rewrite the chunks, depending on whether the engine can leave the untouched off-page value in place; the good implementations do leave it alone, which is why updating a small column of a row with a huge payload is normally cheap. - **Deletion and cleanup.** Removing the row must eventually free the chunks, which is deferred work that shows up in background maintenance. - **Replication and backup volume** track the chunks, so a table of large blobs dominates log traffic and backup size disproportionately to its row count. ## The design question behind the interview question Given all this, the recurring judgment call is whether large payloads belong in the database at all. Keeping them in the database buys transactional consistency with the metadata, unified backup, and access control in one place. Keeping them in an object store and storing a reference buys smaller databases, cheaper storage, faster backups and streaming reads, at the cost of losing atomicity between the two systems, which then has to be recovered with an outbox or a reconciliation job. Either answer is defensible; not knowing the tradeoff is not.