The Misleading Simplicity Of OLTP vs. OLAP

OLTP handles many small transactions, while OLAP handles analytical queries. That distinction is correct, but it is not enough for engineering work. A benchmark becomes useful only when the workload, dataset, measurement window, and interpretation boundary are explicit.

The local benchmark lab in labs/oltp-olap-benchmark/ uses PostgreSQL in Docker, HammerDB 5.0 for TPROC-C and TPROC-H workloads, and a PostgreSQL EXPLAIN (ANALYZE, BUFFERS) probe for a targeted analytical query.

The most useful result was not that one side was “faster.” The useful result was this:

A date index changed the analytical query from a parallel sequential scan to a parallel index-only scan, reduced read buffers, and cut execution time from 213.717 ms to 134.184 ms in this local run.

That is the benchmark lesson I want to keep. OLTP and OLAP are not labels to memorize. They are different ways of creating pressure on the database.

Table Of Contents

  • Test environment
  • Local architecture
  • Building the benchmark database
  • OLTP workload with TPROC-C
  • OLAP workload with TPROC-H
  • Analytical query-plan probe
  • What the index result proves
  • What the numbers mean
  • Production rules

Test Environment

This was a local Docker benchmark, not an official TPC result and not a production capacity claim.

ItemValue
Run date2026-07-10
HostmacOS/Darwin on Apple Silicon
Docker29.3.0
Docker Composev5.1.0
PostgreSQL imagepostgres:16-alpine
PostgreSQL version observedPostgreSQL 16.14
HammerDB imagetpcorg/hammerdb:latest
HammerDB CLI version observed5.0
HammerDB platformlinux/amd64 under Docker Desktop
Lab pathlabs/oltp-olap-benchmark/

The HammerDB image ran as linux/amd64 on an ARM64 laptop. That matters. Docker Desktop virtualization and architecture emulation can distort absolute numbers, especially for CPU-bound and I/O-heavy work. The results are still useful for comparing behavior inside the same local environment, but they should not be treated as universal PostgreSQL performance values.

The clean rerun used ID 20260710T025057Z. Its generated summary is committed at /resources/blog/post-54-benchmarking-types-oltp-olap/benchmark-summary.md, and the raw HammerDB and EXPLAIN excerpts are linked below.

The full run command is intentionally boring:

cd labs/oltp-olap-benchmark
./scripts/run-all.sh

Cleanup is also explicit:

./scripts/cleanup.sh

Local Architecture

StageRole
run-all.shStarts from a clean state, runs both workloads, and collects logs and plans
Docker ComposeKeeps PostgreSQL and HammerDB in isolated containers
PostgreSQL 16Hosts the TPROC-C and TPROC-H databases
HammerDB 5.0Runs the transactional and analytical workloads
SQL probesCaptures dataset checks and EXPLAIN (ANALYZE, BUFFERS) evidence

The Compose file keeps the database and benchmark runner separate:

services:
  postgres:
    image: postgres:16-alpine
    ports:
      - "55432:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres

  hammerdb:
    image: tpcorg/hammerdb:latest
    platform: linux/amd64
    working_dir: /home/HammerDB-5.0
    volumes:
      - .:/workspace

The important design choice is that all code shown in this post lives in the lab. The article does not use isolated snippets that cannot be run.

Building The Benchmark Database

The lab builds two separate databases:

DatabaseWorkloadMain dataset evidence
tpccHammerDB TPROC-C2 warehouses, 60,000 customers, 306,070 orders, 3,058,117 order lines
tpchHammerDB TPROC-Hscale factor 1, 150,000 customers, 1,500,000 orders, 5,999,168 line items

The saved dataset summary is available at /resources/blog/post-54-benchmarking-types-oltp-olap/dataset-summary.txt.

The local database sizes after the run were:

DatabaseSize
tpcc654 MB
tpch2,064 MB

HammerDB calls its built-in workloads TPROC-C and TPROC-H. HammerDB documents these as workloads derived from TPC-C and TPC-H, not official audited TPC results. That distinction matters when writing or publishing benchmark claims.

OLTP Workload With TPROC-C

The TPROC-C script uses a small local configuration:

dbset db pg
dbset bm TPC-C

diset connection pg_host postgres
diset connection pg_port 5432

diset tpcc pg_count_ware 2
diset tpcc pg_num_vu 2
diset tpcc pg_rampup 1
diset tpcc pg_duration 1
diset tpcc pg_storedprocs true
diset tpcc pg_allwarehouse true

The benchmark used 2 active virtual users, a 1-minute ramp-up, and a 1-minute measured duration. That is deliberately short. I wanted a reproducible article lab, not a long capacity test.

HammerDB reported:

MetricValue
Active virtual users2
Warehouses2
Ramp-up1 minute
Measured duration1 minute
PostgreSQL TPM219,223
NOPM93,918

The transaction timing breakdown is more useful than the headline number:

TransactionCallsp50p95p99Average
New Order492,1400.336 ms0.453 ms0.717 ms0.368 ms
Payment489,2940.222 ms0.311 ms0.479 ms0.247 ms
Stock Level48,7660.378 ms1.013 ms56.618 ms2.430 ms
Delivery49,1680.673 ms0.990 ms1.505 ms0.729 ms
Order Status49,0640.137 ms0.201 ms0.268 ms0.141 ms

The stock-level transaction had a much fatter p99 than payment or order-status, even though its p95 was close to 1 ms. That is the kind of signal OLTP benchmarking should surface. A single TPM value does not tell me which operation carries the tail-latency risk.

The Docker stats snapshot after TPROC-C showed:

MetricSnapshot
PostgreSQL CPU16.45%
PostgreSQL memory203.2 MiB / 7.653 GiB
Network I/O313 MB / 506 MB
Block I/O45.1 kB / 11.5 GB

Again, this is a snapshot, not a full resource profile. It is still enough to remind me that OLTP pressure is not only query latency. It also creates write volume, lock pressure, WAL activity, and checkpoint/vacuum work.

The raw HammerDB result is saved at /resources/blog/post-54-benchmarking-types-oltp-olap/hammerdb-tprocc-result.txt.

OLAP Workload With TPROC-H

For the analytical side, the lab builds HammerDB’s PostgreSQL TPROC-H schema at scale factor 1:

dbset db pg
dbset bm TPC-H

diset connection pg_host postgres
diset connection pg_port 5432

diset tpch pg_scale_fact 1
diset tpch pg_num_tpch_threads 2
diset tpch pg_total_querysets 1
diset tpch pg_degree_of_parallel 2

The TPROC-H run executed one query set: 22 analytical queries.

MetricValue
Scale factor1
Query sets1
Queries22
Total query-set time9 seconds
Geometric mean of returning query times0.26616 seconds

Some query times from the run:

QueryTime
Query 182.209 s
Query 170.959 s
Query 10.890 s
Query 150.866 s
Query 90.527 s
Query 130.413 s

This workload feels different from TPROC-C even before looking at results. TPROC-C repeatedly mutates small transactional state. TPROC-H loads a larger analytical schema and spends its time scanning, joining, grouping, and aggregating.

The raw HammerDB result is saved at /resources/blog/post-54-benchmarking-types-oltp-olap/hammerdb-tproch-result.txt.

Analytical Query-Plan Probe

HammerDB gives workload-level evidence, but I also wanted one query-plan example I could reason about directly. The lab runs this analytical query against lineitem:

explain (analyze, buffers, timing)
select
  l_shipdate,
  count(*) as line_count,
  sum(l_extendedprice * (1 - l_discount)) as discounted_revenue
from lineitem
where l_shipdate >= date '1995-01-01'
  and l_shipdate < date '1995-04-01'
group by l_shipdate
order by l_shipdate;

Then it creates a plausible covering index:

create index idx_lineitem_shipdate_revenue_probe
  on lineitem (l_shipdate)
  include (l_extendedprice, l_discount);

The expectation was simple: filtering by l_shipdate and reading l_extendedprice plus l_discount should benefit from the index. The actual result was more interesting.

ProbePlan shapeExecution timeBuffer evidence
Before indexparallel sequential scan + partial hash aggregate213.717 msshared hit 3,179 / read 126,193
After indexparallel index-only scan + group aggregate134.184 msshared hit 23,469 / read 11,303 / heap fetches 33,813

The index reduced read buffers and improved this query in the clean run. That result is useful, but it is still not a rule that “uses an index” means “better.” It is one measured outcome for this dataset, predicate, visibility state, and Docker environment.

The full plan output is saved at /resources/blog/post-54-benchmarking-types-oltp-olap/olap-index-probe.txt.

What The Index Result Proves

The indexed plan was not magic. PostgreSQL used a parallel index-only scan:

Parallel Index Only Scan using idx_lineitem_shipdate_revenue_probe on lineitem
  Index Cond: ((l_shipdate >= '1995-01-01'::date)
    AND (l_shipdate < '1995-04-01'::date))
  Heap Fetches: 33813

The baseline plan used a parallel sequential scan:

Parallel Seq Scan on lineitem
  Rows Removed by Filter: 1924843

The query selected about 224,637 rows after filtering. That is not a tiny lookup, but it was selective enough for the targeted index to reduce table reads substantially in this run. The optimized plan still performed 33,813 heap fetches, and creating the index took 2.163 seconds in the lab. The benefit is therefore conditional: it helped this read query, but it also adds write overhead, storage, vacuum interaction, and maintenance cost.

This is why EXPLAIN (ANALYZE, BUFFERS) matters. PostgreSQL’s documentation is explicit that ANALYZE executes the statement and adds actual timing and row statistics, while BUFFERS shows shared/local/temp block activity. Without those actuals, I would only have a plausible explanation.

What The Numbers Actually Mean

The benchmark supports a narrow set of claims:

  • HammerDB 5.0 ran PostgreSQL TPROC-C and TPROC-H workloads locally.
  • The TPROC-C run produced 219,223 PostgreSQL TPM and 93,918 NOPM under a 2-warehouse, 2-VU, 1-minute measured configuration.
  • The TPROC-H scale-factor-1 run completed one 22-query set in 9 seconds with a 0.26616-second geometric mean for returning query times.
  • The custom analytical query improved with the targeted index in this local run: 213.717 ms before index, 134.184 ms after index.
  • The indexed plan reduced read buffers and changed the query to a parallel index-only scan, but still required 33,813 heap fetches.

The benchmark does not support these claims:

  • PostgreSQL can handle 219,223 TPM in production.
  • A laptop Docker result predicts managed database performance.
  • This index will always improve this query in every environment.
  • Indexes are always good for OLAP queries.
  • HammerDB local output is an official TPC result.

Those unsupported claims would be overreach.

Production Implications

My final rule is:

Name the workload before choosing the metric, and inspect the plan before trusting the optimization.

For OLTP, I care about:

  • transaction mix;
  • p95/p99 latency by transaction type;
  • lock waits and blocking;
  • write amplification;
  • error rate;
  • checkpoint, vacuum, and WAL pressure;
  • throughput after warm-up, not just peak samples.

For OLAP, I care about:

  • row counts and dataset shape;
  • scan volume;
  • join and aggregate strategy;
  • memory and temp files;
  • buffer reads and hits;
  • parallelism;
  • whether an index helps the real query, not the imagined query.

The index probe is the lesson I would carry into production code review. A developer might propose lineitem(l_shipdate) include (...) and the idea would sound reasonable. The review should not stop there. The next questions are:

  • How selective is the date range?
  • Does the query still need many heap fetches?
  • Does the index disable a better parallel plan?
  • How much write overhead does the extra index add?
  • Is this query frequent enough to justify the storage and maintenance cost?

Benchmarking is useful only when it makes those questions answerable.

References