Benchmarks

pgcraft benchmarks measure the performance of each dimension type’s backing tables and views against a real PostgreSQL instance. Run them with just bench (see Development for setup and CLI options).

What is benchmarked

Each dimension type is exercised with a consistent set of operations:

Simple dimension — a single backing table with a read-only view. Benchmarks cover single-row INSERT, UPDATE, and DELETE on the table, batch INSERTs (100 and 1,000 rows), and SELECT queries on the view over a 10k-row dataset.

Append-only dimension — a root table joined to an attributes table through a view. Updates append a new attribute row and re-point the root foreign key, preserving full history. Benchmarks cover single-row INSERT and UPDATE, batch INSERT, SELECT from the join view, and SELECT after an entity has accumulated 100 historical revisions.

EAV dimension — entity-attribute-value storage with a pivot view that presents attributes as columns. Benchmarks cover single-row INSERT (fans out to one entity row plus one attribute row per column), single-attribute UPDATE, batch INSERT, and SELECT from the pivot view.

Ledger — an append-only, immutable table. Benchmarks cover single-row INSERT, batch INSERTs (100 and 1,000 rows), and a balance aggregation query (SUM(value) GROUP BY category) over 10k entries.

Representative results

The tables below were generated with just bench-docs on a local development machine (PostgreSQL 16, Linux, NVMe SSD). Your numbers will vary — run just bench to measure on your own hardware.

Simple dimension

  • test_insert_single — Insert one row into the backing table.

  • test_update_single — Update one row in the backing table.

  • test_delete_single — Delete one row (includes re-insert each round).

  • test_insert_batch_100 — Insert 100 rows in a single statement.

  • test_insert_batch_1000 — Insert 1,000 rows in a single statement.

  • test_select_all — SELECT * from the view (10k rows seeded).

  • test_select_filtered — SELECT with WHERE clause (10k rows seeded).

Benchmark

Min (us)

Mean (us)

Median (us)

Rounds

test_insert_single

528.69

836.41

654.96

10,000

test_update_single

552.29

715.39

679.27

10,000

test_delete_single

1,473.78

1,758.60

1,714.60

10,000

test_insert_batch_100

827.45

911.26

890.46

100

test_insert_batch_1000

3,692.54

3,927.31

3,888.07

100

test_select_all

3,492.91

6,016.22

4,089.64

1,000

test_select_filtered

580.01

672.10

631.95

1,000

Append-only dimension

  • test_insert_single — Insert one row into the backing table.

  • test_update_single — Update one row in the backing table.

  • test_insert_batch_100 — Insert 100 rows in a single statement.

  • test_select_latest — SELECT * from the join view (10k entities).

  • test_select_after_many_updates — SELECT after 100 revisions of one entity.

Benchmark

Min (us)

Mean (us)

Median (us)

Rounds

test_insert_single

553.65

696.72

669.91

10,000

test_update_single

590.83

823.26

787.96

10,000

test_insert_batch_100

2,357.30

2,584.77

2,510.88

100

test_select_latest

18,107.89

21,392.76

19,626.55

1,000

test_select_after_many_updates

105.69

140.94

132.94

1,000

EAV dimension

  • test_insert_single — Insert one row into the backing table.

  • test_update_single_attribute — Update one attribute (appends a new row).

  • test_insert_batch_100 — Insert 100 rows in a single statement.

  • test_select_pivot — SELECT * from the pivot view (10k entities).

  • test_select_pivot_filtered — Filtered pivot view query (10k entities).

Benchmark

Min (us)

Mean (us)

Median (us)

Rounds

test_insert_single

581.59

738.88

709.48

10,000

test_update_single_attribute

4,700.91

12,114.47

12,017.25

10,000

test_insert_batch_100

4,117.31

4,436.53

4,351.18

100

test_select_pivot

28,262.89

33,627.11

31,448.78

1,000

test_select_pivot_filtered

18,653.54

20,772.97

19,900.04

1,000

Ledger

  • test_insert_single — Insert one row into the backing table.

  • test_insert_batch_100 — Insert 100 rows in a single statement.

  • test_insert_batch_1000 — Insert 1,000 rows in a single statement.

  • test_select_balance — SUM(value) GROUP BY category (10k entries).

Benchmark

Min (us)

Mean (us)

Median (us)

Rounds

test_insert_single

538.18

697.02

670.43

10,000

test_insert_batch_100

985.80

1,193.12

1,134.34

100

test_insert_batch_1000

5,589.34

6,186.24

6,132.88

100

test_select_balance

1,449.59

1,604.02

1,557.24

1,000

Cross-dimension comparison

Single-row insert and primary read query across dimension types:

Dimension type

Insert mean (us)

Insert median (us)

Read mean (us)

Simple dimension

836.41

654.96

6,016.22

Append-only dimension

696.72

669.91

21,392.76

EAV dimension

738.88

709.48

33,627.11

Ledger

697.02

670.43

1,604.02