Data Models and Storage Engines

Rows, documents, and key-values — plus how B-tree and LSM engines store data on disk.

Fresher10 min read

Read these first

Why Databases Beat File Storage

Why this matters

Picking Postgres vs MongoDB is a data-model decision first — the engine choice follows from how VaultCommerce structures products, orders, and sessions.
Write-heavy event logs and read-heavy catalog pages stress different engine designs; blaming "the database is slow" without knowing B-tree vs LSM behaviour leads to wrong fixes.
System design interviews often pair "what model fits?" with "how does the engine store it?" — both answers matter.
Understanding engines explains why some indexes help writes and others hurt them.

SQL (Postgres)

ACID transactions

Complex joins

Fixed schema

NoSQL

Flexible schema

Horizontal scale

Specialised access

Structured relations with joins favour SQL. Flexible schema and horizontal scale favour NoSQL.

Relational rows and tables

The relational model stores data in tables with fixed columns. VaultCommerce's products table treats every SKU as a row with typed fields:

Java

CREATE TABLE products (
    id          BIGSERIAL PRIMARY KEY,
    sku         VARCHAR(32) NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    price_cents INTEGER NOT NULL,
    category_id BIGINT REFERENCES categories(id)
);

Relationships are explicit foreign keys. Joining orders to order_items to products is the default query pattern for checkout and reporting.

Relational model traits

Schema-first — Columns and types are declared before data arrives.
Normalised relations — Each fact lives in one place; joins assemble views.
Strong typing — price_cents as integer avoids floating-point rounding in money fields.

Document and key-value models

Document stores bundle related fields into one JSON blob. VaultCommerce's product marketing team wanted flexible attribute sets per category — specs as nested JSON fits a document model:

Java

{
  "_id": "prod-7721",
  "sku": "VLT-HEADPHONE-X",
  "name": "VaultSound Pro",
  "specs": { "driver_mm": 40, "wireless": true, "battery_hours": 30 }
}

Key-value stores map opaque keys to values — ideal for session tokens (session:abc123 → user_id) where you never join across keys.

How storage engines work

Under every database sits an engine that pages data to disk. Two families dominate:

B-tree engines (Postgres, MySQL InnoDB)

In-place updates — Find the page, modify the row, flush WAL for durability.
Read-optimised — Point lookups and range scans on indexed columns are fast.
Write cost — Random page writes and index maintenance add latency on heavy insert workloads.

LSM engines (RocksDB, Cassandra, LevelDB)

Append-first — Writes go to an in-memory buffer, then flush to sorted SSTable files.
Write-optimised — Sequential disk writes absorb high ingest rates.
Read cost — Multiple sorted runs may need merging; compaction runs in the background.

VaultCommerce runs Postgres (B-tree) for orders because checkout reads a few rows by primary key. Their clickstream pipeline later adopted an LSM-backed store for millions of page-view events per hour.

Pages, WAL, and durability

When VaultCommerce inserts an order, Postgres does not immediately rewrite the data file. It appends a write-ahead log (WAL) entry first, then updates the in-memory page buffer. On crash, replaying the WAL restores committed transactions.

Java

# Postgres WAL lives outside the main data directory
ls $PGDATA/pg_wal/

This is why COMMIT returns quickly — the engine guarantees durability asynchronously while keeping the hot path in memory.

Matching model to access pattern

Aspect	Relational (Postgres)	Document (e.g. MongoDB)
Structure	Normalised tables + joins	Nested documents
Schema	Enforced at write time	Flexible per document
Best for	Orders, payments, inventory	Catalogs with variable attributes
Engine	B-tree (in-place)	Often B-tree or LSM per deployment

Structure
Relational (Postgres)Normalised tables + joins
Document (e.g. MongoDB)Nested documents
Schema
Relational (Postgres)Enforced at write time
Document (e.g. MongoDB)Flexible per document
Best for
Relational (Postgres)Orders, payments, inventory
Document (e.g. MongoDB)Catalogs with variable attributes
Engine
Relational (Postgres)B-tree (in-place)
Document (e.g. MongoDB)Often B-tree or LSM per deployment

VaultCommerce keeps financial data relational and experiments with document storage only where schema variance outweighs join requirements.

Quick recall

Everything you need if you only revisit this box.

Data models define structure; storage engines define on-disk layout and read/write trade-offs.
Relational tables suit VaultCommerce orders with foreign keys and joins.
Document and key-value models fit flexible attributes and session lookups respectively.
B-tree engines favour read-heavy OLTP; LSM engines favour write-heavy ingestion.
WAL-based durability lets Postgres commit quickly while guaranteeing crash recovery.

Test yourself

Answer these before moving on — recall is what makes it stick.