Benchmark

Straum’s benchmark sends Straum and PostgreSQL the same asks about six months of New York taxi trips, checks that the answers are equal, and times both. Memory at the end is what two real tables measured.

The data is the TLC yellow trips of January to June 2024, 20,332,093 trips, and the 265 rows of taxi_zone_lookup.csv.

Results

Ten fixed asks over the benchmark’s two views, each sent to PostgreSQL as SQL over the raw tables and to Straum as an ask over HTTP. One run on an Apple M2 Pro with 16 GB, on 2026-11-15: PostgreSQL 18.6 in Docker Desktop with 10 CPUs, and Straum’s release build on the same machine. Each time is the median over at least a second, from one Python client: asyncpg for PostgreSQL, aiohttp and orjson for Straum. Rows are the answer’s.

The target is under 1 ms for every ask (ADR-0050); the two percentile asks over every trip miss it.

Straum was ready 16.8 s after it started and held 113 MiB, the 265 zone rows’ projection included. In PostgreSQL the table and its indexes take 2,475 MiB.

• Reads run with PostgreSQL’s defaults, two parallel workers per query, except shared_buffers: 2 GB. The rest of the table comes from the OS cache. The other settings in bench/taxi.py only speed the load. yellow_trips has an index on pickup_at, which the week and day asks use, and the gap is smallest there, where PostgreSQL reads least.
• PostgreSQL’s time follows the rows it reads: every trip, but for the week and day asks, which read a week’s or a day’s. Straum’s hardly changes from ask to ask: most of it is the HTTP round trip, reading the query and writing JSON.
• The percentile asks are the exception: Straum gathers every fare of the cells in range, so their time follows the fares the range holds, as Median and percentiles says.
• Straum answers only what the views keep: these keys, on hour edges. PostgreSQL answers any query over the raw rows. Straum keeps its cells current as trips change; a summary table in PostgreSQL would have to be rebuilt or maintained.

Running it yourself

bench/taxi.py

bench/taxi.py shell sets up the same, then leaves you in straum shell at the trips in place of running the asks. Leaving the shell removes PostgreSQL.

It needs Docker, a Rust toolchain and uv, which fetches the script’s Python packages. The first run downloads the six months into bench/data/, which git ignores. Then it builds Straum, starts a throwaway postgres:18, loads the months with bench/load_taxi_trips.py, runs the SQL straum plan prints and starts straum run. Each ask goes to PostgreSQL as SQL over the raw tables and to Straum as an ask over HTTP, and a run stops at the first answer that differs. A run takes about 80 seconds once the data is there.

bench/taxi.py transactions does the same setup, then one transaction of 1,000 to 1,000,000 changed rows while a client asks Straum back to back, under Transactions.

Each mode takes --wal physical to have Straum read the physical WAL in place of logical decoding. It turns full_page_writes back on after the load and checkpoints, since the physical stream needs page images.

The views

bench/trips.sql opens with trips, the quick start’s view with a median and a p99 fare added: what a dashboard over these trips would declare. dropoffs is the other end of the same trips: tips, the average distance and fares per hour and dropoff zone.

CREATE VIEW dropoffs WITH (buckets = '1 hour, 1 day, 7 days') AS
SELECT date_bin('1 hour', dropoffs.pickup_at, timestamptz 'epoch') AS bucket,
       dropoffs.do_location_id AS do_location_id,
       dropoff.borough AS borough,
       count(*) AS trips,
       sum(dropoffs.tip_amount) AS tips,
       avg(dropoffs.trip_distance) AS avg_distance,
       sum(dropoffs.fare_amount) AS fare
FROM public.yellow_trips AS dropoffs
LEFT OUTER JOIN public.taxi_zones AS dropoff ON dropoff.location_id = dropoffs.do_location_id
GROUP BY 1, 2, 3;

Over the 20,332,093 trips, trips keeps 1,157,581 cells at its hour width and dropoffs 713,457. Neither keeps bound cells: each joins on one of its own keys, so they would be as many again.

The asks

PostgreSQL gets the query anyone would write over the raw tables. The session’s time zone is New York’s, as the TLC’s times are.

-- Trips
SELECT count(*) FROM yellow_trips;

-- Card trips
SELECT count(*) FROM yellow_trips WHERE payment_type = 1;

-- Trips by pickup borough
SELECT z.borough, count(*)
  FROM yellow_trips t
  LEFT JOIN taxi_zones z ON z.location_id = t.pu_location_id
 GROUP BY z.borough;

-- Fares by payment type
SELECT payment_type, count(*), sum(fare_amount), avg(fare_amount)
  FROM yellow_trips
 GROUP BY payment_type;

-- Median fare
SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY fare_amount) FROM yellow_trips;

-- p99 fare by payment type
SELECT payment_type,
       percentile_disc(0.99) WITHIN GROUP (ORDER BY fare_amount)
  FROM yellow_trips
 GROUP BY payment_type;

-- Median fare per hour, one day
SELECT date_trunc('hour', pickup_at),
       percentile_disc(0.5) WITHIN GROUP (ORDER BY fare_amount)
  FROM yellow_trips
 WHERE pickup_at >= '2024-01-17' AND pickup_at < '2024-01-18'
 GROUP BY 1;

-- Manhattan pickups, one week
SELECT count(*)
  FROM yellow_trips t
  JOIN taxi_zones z ON z.location_id = t.pu_location_id
 WHERE z.borough = 'Manhattan'
   AND t.pickup_at >= '2024-01-08' AND t.pickup_at < '2024-01-15';

-- Trips per hour, one day
SELECT date_trunc('hour', pickup_at), count(*)
  FROM yellow_trips
 WHERE pickup_at >= '2024-01-17' AND pickup_at < '2024-01-18'
 GROUP BY 1;

-- Tips by dropoff borough
SELECT z.borough, count(*), sum(t.tip_amount), avg(t.trip_distance),
       sum(t.fare_amount)
  FROM yellow_trips t
  LEFT JOIN taxi_zones z ON z.location_id = t.do_location_id
 GROUP BY z.borough;

Straum gets the same asks over the views, with every value a parameter; bench/taxi.py holds both. Where the SQL has no range, the ask’s is 2000 to 2030, which holds every trip:

-- Trips by pickup borough
SELECT borough, trips FROM trips
 WHERE bucket >= $1 AND bucket < $2
 GROUP BY borough

Transactions

Straum holds a transaction until its commit and applies it whole under its write lock, so its rows wait in memory and every ask waits for the apply. bench/taxi.py transactions updates the fare of the first N trips, which both views follow, in one transaction while a client asks for one day of hour buckets as fast as the answers come. It measures three things: the time from the commit until Straum has applied it, the longest ask meanwhile, which is the lock hold as a client feels it, and how much Straum’s resident set grew. One run on 2026-11-14, the machine, build and PostgreSQL of the Results; Straum held 114 MiB after the load and an idle ask took 177 µs:

Most of the time goes to PostgreSQL decoding the transaction and Straum reading it, outside the lock; the lock itself is held for about 1.1 ms per 1,000 rows, half of it moving each changed fare between the percentiles’ value counts. Memory is what grows: the rows of a transaction wait in Straum until its commit, about 630 bytes each, so a million-row transaction holds over five times what both views do. That sets the default of --max-transaction-rows at 100,000: a transaction at the limit holds the lock for 108 ms, and anything larger is cheaper to start over, which takes the rows through the bulk COPY instead.

Physical WAL

cargo test -p straum-pg --release physical_wal_bench -- --ignored --nocapture

It measures how fast Straum reads physical WAL. It loads the taxi month with bench/load_taxi_trips.py into a PostgreSQL under wal_level = replica, so it needs uv. Then it takes a checkpoint and opens a physical slot. Four phases each change 1,000,000 trips of yellow_trips: COPY in transactions of 250,000 rows, then INSERT, UPDATE and DELETE in transactions of 10,000. The slot streams the WAL, and Straum keeps it in memory. Straum then reads it again three times each way (STRAUM_BENCH_RUNS), with no socket in the way, and the fastest read counts. “Records read” splits pages into records only. “Pipeline read” goes all the way to the commits for crates/straum-notation/tests/taxi.sql. Every row changed must come out, and nothing may be unfit. Autovacuum is off and the checkpoint interval is an hour, so each run writes the same records. STRAUM_TEST_POSTGRES picks the major.

One run on 2026-11-15, the machine of the Results, one thread, PostgreSQL 18:

“Against PostgreSQL” divides the time PostgreSQL took to write a phase’s WAL by the time the pipeline took to read it. Below 1x, as for the deletes, a writer that keeps that pace for long leaves Straum behind, and once the lag passes --max-lag, /readyz turns off. Images are the full-page images: the first change to a page after the checkpoint brings one. The pipeline held at most 429.5 MiB of rows, most of them the open COPY transactions’ rows, which wait for their commit.

Memory

Memory follows cells, as How Straum works says. The rollup key, the columns in GROUP BY, decides how many there are. Two runs on 2026-11-11 measure it: the taxi month under a wide key, and a warehouse’s tables under a narrow one whose dims carry the ids. The resident set came to 177 and 104 bytes a cell, measures and projections included, before bound cells. A cell has taken fewer bytes since (ADR-0044): the benchmark’s two views held 65 MiB for their 2,078,586 cells, 33 bytes a cell, before trips kept its fares for the percentiles; with them they hold 113 MiB.

The taxi month under a wide key

cargo test -p straum-pg --release taxi_month -- --ignored --nocapture

It loads the month with bench/load_taxi_trips.py, so it needs uv, and reads it with crates/straum-notation/tests/taxi.sql, a wider view than the benchmark’s: 15 minute buckets with day and week widths, keyed by payment type, vendor, pickup zone, dropoff zone and the pickup zone’s borough, zone and service zone, with two filtered counts. The test compares asks with the view body in PostgreSQL six times: after the load, after 200 streamed commits, after an update, after a zone is renamed, after a zone moves to a new key, and after a trip in the renamed zone changes. Nothing starts over. Release build, Docker on the same laptop:

The ask over every key reads every 15 minute cell, maps each through the zones’ projection and answers nearly as many groups, so it is only 2.0 to 2.4 times faster than the view in PostgreSQL. Cells per width, each a count(DISTINCT ...) of that key and the bucket over the month:

Pickup and dropoff zone together are nearly unique per trip, so this spec collapses little at 15 minutes. Without the dropoff zone it collapses 5.2 times.

A warehouse’s tables

Tasks keyed by organization, whether the task is live, its group’s category and its storage location’s type, over hour buckets with a day width beside them. The category and the type are columns of two dims, so the cells are keyed by the group and location ids and Straum holds each dim’s projection beside them: its key and the one column the view reads. The tables are a copy of a production database and are not in the repository: 13,116,878 tasks, 5,690,651 task groups and 851,964 storage locations, 2,645 MiB of tasks in PostgreSQL. The machine and build of the results.

A group holds one task or two, and every task has its own hour, so both widths hold about a cell a task; the day width collapses nothing, since the group id is in its key. Keyed by the category and the type instead, as a view that never followed a dim change could be, the widths would hold 311,969 and 66,107 cells.

Straum was ready 10 s after it started and held 2,378 MiB: the cells and the two projections, 6,542,615 dim rows. A run right before held 1,998 MiB; 88 to 104 bytes a cell, projections included.

Every ask equalled PostgreSQL after the load and after each streamed change. Each change is one transaction; the time runs from the commit until Straum had applied it:

The last three change dims. Each is applied to the projection, no cell moves, and the next ask reads the new category or type; Straum never started over.

The asks, PostgreSQL run twice on the loaded tables and Straum the better of two:

PostgreSQL has an index on completed here, and these are the plain queries the asks mirror, not a dashboard’s own. Straum reads the cells of one width in the range, about a cell a task, and maps each through the projections, so an ask over a week costs more than one over a day.