How databases store and retrieve data
Explore how row-oriented and columnar storage work, why databases organize data differently, and how those choices affect query performance.
Row-oriented
Example: InnoDBClustered pages hold whole rows, including unrequested fields.
Column-oriented
Example: ParquetParquet separates columns within row groups. Readers skip unneeded chunks.
Examples: MySQL with InnoDB, and Spark reading Parquet files managed by Iceberg. Iceberg is a table format, not a database engine; Parquet supplies the columnar layout.
Limits: covering indexes and stack roles
A covering secondary index can supply a query without a clustered-row lookup. This diagram models a clustered-row scan with inline fields, not every InnoDB query. Real I/O depends on pages, caching, compression, off-page values, predicates, and the execution plan. Column pruning is not a guaranteed speed multiplier.
Different roles: InnoDB is a MySQL storage engine; Iceberg is a table format. Here, Spark runs queries, Polaris manages the catalog, and MinIO stores files.
Rows together. Columns together.
Same six records: grouped by row in InnoDB, by column within Parquet row groups.
SELECT AVG(close) FROM bars;Row layout / InnoDB
Read a page → obtain complete records.
Page A: AAPL / 100 / 1,000; GOOG / 150 / 1,500; MSFT / 200 / 2,000.
Page B: NVDA / 300 / 3,000; ORCL / 250 / 2,500; TSLA / 350 / 3,500.
Read both pages, including unused fields. Calculate with close only.
Column layout / Parquet
Read selected column chunks → obtain values.
Row group A: symbol [AAPL, GOOG, MSFT]; close [100, 150, 200]; volume [1,000, 1,500, 2,000].
Row group B: symbol [NVDA, ORCL, TSLA]; close [300, 250, 350]; volume [3,000, 2,500, 3,500].
Read the footer and close chunks. Skip symbol and volume.
How the example simplifies real storage
- Sample layout: each stock appears once. Symbol is the InnoDB primary key. The Parquet rows are sorted by symbol, so their group ranges do not overlap.
- InnoDB: the diagram shows data-page access, not the index traversal. It assumes no covering index on close. Cached pages can avoid disk reads.
- Parquet: a row group is a logical batch of rows, not one physical read unit. Each column chunk contains pages. Readers use metadata to find the required byte ranges.
- Lookup: the footer’s symbol bounds let this reader skip group B. The example shows all chunks in group A as read. Readers may use further filtering or page-level optimizations.
- Average: this example decodes all close values. It does not infer an average from minimum and maximum statistics.
The tiny batches keep the example readable. Real InnoDB pages and Parquet row groups usually contain many more records. Compression, encoding, caching, and metadata access are not measured here.
One record
Rows keep an entity’s fields nearby. An index can locate the record.
Many values
Columns let scans read fewer fields and process batches of values.
Compression
Similar values often compress well together. Savings depend on data and encoding.
Locate or eliminate.
An indexed row-store plan versus a file-based columnar plan. These are examples—not universal read paths for every database.
SELECT close, volume FROM bars WHERE symbol = 'AAPL';InnoDB / index route
A secondary index finds primary keys; InnoDB fetches matching clustered records.
Iceberg stack / pruning route
Snapshot metadata and statistics prune manifests, files, and row groups.
What each Iceberg metadata layer contains
- Table metadata JSON: schema, partition specifications, snapshot history, and the current snapshot reference.
- Manifest list: manifest locations and partition summaries. A snapshot points to its manifest list.
- Manifest: entries for data files or delete files. Data-file entries contain paths, partition values, and column metrics when available.
- Parquet footer: row-group and column-chunk locations and statistics. The reader can use these to skip more data.
Statistics reject impossible matches; they do not prove that all remaining rows match. The reader still applies the query filter. Delete-file applicability follows Iceberg rules, not a simple one-to-one link.
Protect readers. Keep history.
Storage layout alone does not determine transactions. In these two examples, update 100 → 105 while existing readers keep their earlier view.
InnoDB
Row versionsIceberg stack
File snapshotsInnoDB: transaction
Locks coordinate writes. Undo preserves old views; redo supports recovery. Durability depends on settings.
Iceberg: snapshot
Replace affected files; reuse unchanged files. Atomically swap the catalog’s metadata pointer.
What if two Iceberg writers race?
Writer B’s stale pointer swap fails. If its assumptions still hold, it can rebuild metadata on the new state and retry. Compatible appends often reuse files.
Validation depends on the operation and isolation level. “Different files” alone is not a universal test. InnoDB’s native locks are separate from the application advisory locks used in the article.
Read-view and cleanup limits
The example uses an existing consistent read view and a separate fresh read view. InnoDB visibility depends on the isolation level and read type. Locking reads do not follow this simple model.
InnoDB purges old undo when it is no longer needed. Iceberg snapshot expiration and orphan-file cleanup are separate operations. Retention must protect active readers and writers. A failed pre-commit write can leave unreferenced files; not every retry is automatically idempotent.
Choose for the work.
Transactions · OLTP
Many short reads and writes: accounts, orders, inventory.
Analytics · OLAP
Scan and aggregate many records: trends, reports, totals.
Mixed workloads
Some systems maintain both layouts. Indexes, caching, and execution matter too.
Reading a few fields across many records often favors columnar storage.
Rows / InnoDB
Row scans include unused fields. A covering index can change the trade-off.
Columns / Iceberg + Parquet
Pruning, compression, and parallel column reads suit analytics.
Layout is not a database category
SQL can query either layout. Columnar engines can support updates and transactions; row stores can run analytics. A “wide-column” database is a different category, not another name for a columnar analytics engine.
Example-specific: partition evolution & maintenance
Change the partition rule
The article physically partitions its table by StockID.
All files currently use the same partition specification.
InnoDB upkeep
Manage index cost, statistics, page flushing, and undo purge. More indexes can increase write work.
Iceberg upkeep
Compact files, expire snapshots, and remove safe-to-delete orphan files. Merge on read also adds delete-processing work.
Index costs & row counts
- Index write cost: inserts add entries to indexes. Updates need to change affected index entries—not necessarily every secondary index.
- Row counts: Iceberg metadata can answer some counts, but deletes, filters, and engine support can require more work. InnoDB generally counts visible records through an index rather than keeping one exact transactional count.