Transactional and Analytical Workloads

The Normalization topic determined how the schema would be built. The remaining question is what kind of workload this schema runs under. A system that records individual loan transactions and a system that produces an annual loan statistic want the same data, but not the same access pattern. This lesson’s question is: how are the two workload shapes separated, and what does that separation change in the storage layout?

Two Workload Shapes

A transactional workload is the daily work at the circulation desk: fetch a member’s record by key, insert a loan row, update a return row. Its traits are these: each request touches a small number of rows, the full set of a row’s columns is requested, latency is measured in milliseconds, and a large number of requests run at the same time. The write rate is high.

An analytical workload is report generation: how many loans went out at which branch in which month, what the sum of overdue penalties is, how a given subject has shifted over the years. Its traits are the reverse: each request touches millions of rows, it asks for only a few of a row’s columns, latency is measured in seconds, the number of concurrent requests is low, and writes are almost absent.

These two workloads share the same data but call for opposite design decisions. A transactional workload gains from a normalized schema, narrow rows, and access by key. An analytical workload gains from wide scans, pre-joined structures, and reading only the columns it needs.

Row and Column Orientation

The difference takes concrete shape in the storage layout. Row-oriented storage keeps all of a row’s columns next to each other on disk. Reading a row is a single block read — ideal for a transactional workload. Aggregating a single column, on the other hand, requires reading every column next to it as well.

Column-oriented storage does the reverse: all the values of the same column sit next to each other. Aggregating a column reads only that column; fetching an entire row, on the other hand, requires a separate read from every column.

The difference is measurable. In a row-oriented engine, the way to emulate column orientation is to keep a single column in its own relation:

mkdir -p workload && cd workload && rm -f row.db column.db

sqlite3 row.db <<'SQL'
PRAGMA page_size = 4096;
CREATE TABLE loan (
  loan_no       INTEGER PRIMARY KEY,
  member_no     INTEGER NOT NULL,
  isbn          TEXT    NOT NULL,
  branch_code   TEXT    NOT NULL,
  checkout_date TEXT    NOT NULL,
  return_date   TEXT    NOT NULL,
  overdue_days  INTEGER NOT NULL,
  penalty_cents INTEGER NOT NULL,
  channel       TEXT    NOT NULL,
  note_text     TEXT    NOT NULL
);
WITH RECURSIVE counter(n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM counter WHERE n < 200000
)
INSERT INTO loan (member_no, isbn, branch_code, checkout_date, return_date,
                   overdue_days, penalty_cents, channel, note_text)
SELECT n % 5000, '978-975-' || (n % 900), 'CEN', '2025-03-02', '2025-03-16',
       n % 7, (n % 7) * 250, 'counter', 'record note field ' || n
FROM counter;
SQL

sqlite3 column.db "PRAGMA page_size = 4096;
CREATE TABLE penalty_cents (value INTEGER NOT NULL);"
sqlite3 row.db "ATTACH 'column.db' AS s;
INSERT INTO s.penalty_cents SELECT penalty_cents FROM loan;"

echo "row.db $(wc -c < row.db | tr -d ' ') bytes"
echo "column.db $(wc -c < column.db | tr -d ' ') bytes"
sqlite3 row.db "SELECT SUM(penalty_cents) FROM loan;"
sqlite3 column.db "SELECT SUM(value) FROM penalty_cents;"

row.db 17428480 bytes
column.db 1957888 bytes
149999250
149999250

The two files carry the same two hundred thousand values and yield the same total. Computing the penalty total requires scanning 17.4 million bytes in the row-oriented layout; 2 million bytes in the column layout. Because the page size is set explicitly in the block, the measurement is reproducible; the byte counts depend on the page size and the engine’s row encoding, while the ratio is set by the number of columns and their widths.

The Share of Compression

Column orientation’s second gain is in compression. Because the values of the same column share a type and are often similar to each other, ratios unreachable in a row-oriented layout are captured. Three methods are common: dictionary encoding maps repeated values to small integers — a few bits suffice for a branch code column; run-length encoding stores consecutive identical values in a sorted column as a single pair; bit packing writes narrow-range integers without aligning them to full byte boundaries.

Compression does not save space alone. Because fewer bytes are read, the scan also speeds up, and some operations can run on the encoded form without decoding the values.

The Row-Oriented Engine’s Counterpart

A row-oriented engine’s closest counterpart to this gain is the covering index: an index that contains every column the query needs. The engine produces the answer from the index without ever touching the table.

sqlite3 :memory: <<'SQL'
CREATE TABLE loan (
  loan_no       INTEGER PRIMARY KEY,
  member_no     INTEGER NOT NULL,
  branch_code   TEXT    NOT NULL,
  note_text     TEXT    NOT NULL,
  penalty_cents INTEGER NOT NULL
);
EXPLAIN QUERY PLAN SELECT branch_code, SUM(penalty_cents) FROM loan GROUP BY branch_code;
CREATE INDEX loan_penalty ON loan (branch_code, penalty_cents);
EXPLAIN QUERY PLAN SELECT branch_code, SUM(penalty_cents) FROM loan GROUP BY branch_code;
SQL

QUERY PLAN
|--SCAN loan
`--USE TEMP B-TREE FOR GROUP BY
QUERY PLAN
`--SCAN loan USING COVERING INDEX loan_penalty

Once the index is added, the plan changes in two ways: the scan runs on the index instead of the table — meaning only two columns are read — and no temporary structure is needed for the grouping, because the index is already sorted by branch code. A covering index approaches column orientation for a narrow query; it does not scale to a widening set of queries, since each one would need its own index.

The Analytical Schema

An analytical workload also changes the schema’s shape. A star schema places a fact table at the center and dimension tables around it. In the library example, the fact table is loan events; the dimensions are member, book, branch, and calendar.

A fact table’s row carries the measurable quantities and the keys leading to the dimensions: overdue days, penalty amount, member key, book key, branch key, date key. The dimension tables, by contrast, are deliberately not normalized — the branch dimension keeps the branch’s name, province, and region together. The rationale is the same one behind denormalization in the earlier lesson: dimensions are small, rarely change, and are joined in every query.

The design’s first decision is the grain: what does one row of the fact table represent? “One loan transaction” and “one branch’s daily summary” are different grains, and the choice cannot be changed later — a finer grain can be rolled up to a coarser one, not the reverse.

Separating the Two Workloads

Running both workloads in the same database harms both. A long-running analytical query consumes the transactional workload’s resources; the transactional workload’s writes change the data an analytical query sees. The common answer is to move the analytical workload to a separate copy: data is extracted from the transactional system at regular intervals, transformed, and loaded into the analytical system.

This has a cost: freshness. The analytical copy always lags by some amount, and how much lag is acceptable is an explicit requirement the system must meet.

Summary

• A transactional workload accesses few rows with many columns; an analytical workload accesses many rows with few columns; the two call for opposite design decisions.
• Row-oriented storage keeps a row’s columns next to each other; column-oriented storage keeps a column’s values next to each other and lowers the bytes read in an aggregation.
• Because a column’s values are similar, the column layout compresses better with methods such as dictionary encoding and run-length encoding.
• A covering index gives a similar gain for narrow queries in a row-oriented engine but does not scale to a widening set of queries.
• A star schema surrounds a fact table with dimensions; choosing the grain is an irreversible decision.

Next Step

This lesson relied on the same assumption twice: writing a loan record either happens completely or not at all, and an analytical query must never see a half-written state. These assumptions also appeared in the course’s first lesson — two processes writing to a file at the same time could not provide them. The next lesson defines the names the database management system gives these guarantees: atomicity, consistency, isolation, and durability.