← Back to Blog Index | Documentation

From Nexmark to Production: Benchmarking Stream Processing in PostgreSQL

How pg_trickle performs on the standard streaming benchmark suite

Nexmark is to stream processing what TPC-H is to analytical databases: the standard benchmark everyone uses to compare systems. Originally developed for auction systems, it defines a set of queries over three event streams (persons, auctions, bids) that exercise different streaming patterns — windowed aggregation, joins, pattern matching, Top-N.

Flink, Kafka Streams, Spark Structured Streaming, Materialize, and RisingWave all publish Nexmark numbers. pg_trickle does too. Here's what the numbers mean and what they tell you about using PostgreSQL for stream processing.

The Nexmark Setup

Nexmark simulates an online auction system:

• Persons: New user registrations (low volume).
• Auctions: New auction listings (medium volume).
• Bids: Bids on auctions (high volume — this is the firehose).

The benchmark defines 8 queries, each testing a different streaming pattern:

Source Tables

CREATE TABLE persons (
    id          bigint PRIMARY KEY,
    name        text NOT NULL,
    email       text NOT NULL,
    city        text NOT NULL,
    state       text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE auctions (
    id          bigint PRIMARY KEY,
    seller_id   bigint NOT NULL REFERENCES persons(id),
    category    text NOT NULL,
    initial_bid numeric(12,2) NOT NULL,
    expires_at  timestamptz NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE bids (
    id          bigserial PRIMARY KEY,
    auction_id  bigint NOT NULL REFERENCES auctions(id),
    bidder_id   bigint NOT NULL REFERENCES persons(id),
    amount      numeric(12,2) NOT NULL,
    bid_at      timestamptz NOT NULL DEFAULT now()
);

Stream Tables for Each Query

-- Q1: Currency conversion (stateless map)
SELECT pgtrickle.create_stream_table('nexmark_q1',
    $$SELECT id, auction_id, bidder_id,
            amount * 0.908 AS amount_eur,
            bid_at
      FROM bids$$,
    schedule => '1s', refresh_mode => 'DIFFERENTIAL');

-- Q3: Join persons + auctions by state
SELECT pgtrickle.create_stream_table('nexmark_q3',
    $$SELECT p.name, p.city, p.state, a.id AS auction_id
      FROM persons p
      JOIN auctions a ON a.seller_id = p.id
      WHERE p.state IN ('OR', 'ID', 'CA')$$,
    schedule => '1s', refresh_mode => 'DIFFERENTIAL');

-- Q4: Average closing price per category
SELECT pgtrickle.create_stream_table('nexmark_q4',
    $$SELECT a.category,
            AVG(b.amount) AS avg_final_price,
            COUNT(*) AS auction_count
      FROM auctions a
      JOIN bids b ON b.auction_id = a.id
      GROUP BY a.category$$,
    schedule => '1s', refresh_mode => 'DIFFERENTIAL');

-- Q5: Top-5 auctions by bid count (sliding window)
SELECT pgtrickle.create_stream_table('nexmark_q5',
    $$SELECT auction_id, COUNT(*) AS bid_count
      FROM bids
      WHERE bid_at >= now() - interval '10 minutes'
      GROUP BY auction_id
      ORDER BY bid_count DESC
      LIMIT 5$$,
    schedule => '1s', refresh_mode => 'DIFFERENTIAL',
    temporal_mode => 'sliding_window');

The Numbers

Tested on a single-node PostgreSQL 18 instance (8 vCPU, 32GB RAM, NVMe SSD). Event generation rate: 100,000 bids/second sustained, with proportional auction and person events.

Throughput is the maximum sustained event ingestion rate before the scheduler falls behind (staleness exceeds the schedule interval). At 100K bids/second, all queries keep up with under 1.5 seconds of staleness.

How to Read These Numbers

vs. Flink

Flink on a 4-node cluster handles millions of events per second for Nexmark. pg_trickle on a single node handles ~100K. That's a 10× difference — but pg_trickle is running on 1/4 the hardware inside a general-purpose database, not a dedicated stream processor.

For most PostgreSQL workloads, 100K events/second is more than enough. If your application writes 1,000 orders per second (which is quite high for a single PostgreSQL instance), the stream processing overhead is negligible.

vs. Materialize

Materialize (now Redpanda-owned) is a dedicated IVM system. Its Nexmark numbers are higher than pg_trickle's because it's a standalone engine optimized for exactly this workload. But it's a separate database — your application can't use BEGIN ... INSERT ... SELECT FROM stream_table ... COMMIT in the same transaction.

vs. "Just Use a Cron Job"

The comparison that matters for most teams isn't pg_trickle vs. Flink. It's pg_trickle vs. the cron job that refreshes a materialized view every 5 minutes. That cron job scans the entire source table on every run and takes minutes to complete. pg_trickle processes only the changes and takes milliseconds.

What Nexmark Doesn't Tell You

Nexmark tests throughput under sustained load with a uniform event distribution. Production workloads are spikier and more complex:

• Spike handling. A flash sale produces a burst of 10× normal traffic for 30 seconds. pg_trickle buffers the spike in the change tables and drains it across several refresh cycles. The staleness increases temporarily, then recovers.

• Complex queries. Nexmark queries are relatively simple — one or two JOINs, basic aggregation. Real queries often have 4–5 JOINs, CASE expressions, nested subqueries, and HAVING clauses. More complex queries have higher per-refresh-cycle costs.

• Concurrent reads. Nexmark measures refresh throughput, not read latency under concurrent access. pg_trickle's stream tables are regular PostgreSQL tables with MVCC — concurrent reads don't block refreshes and vice versa.

Running the Benchmark Yourself

The Nexmark benchmark is included in pg_trickle's test suite:

# Build the E2E Docker image (includes pg_trickle)
just build-e2e-image

# Run Nexmark queries
cargo test --test e2e_tpch_tests -- --ignored nexmark --test-threads=1 --nocapture

# Control the event generation rate and duration
NEXMARK_EVENTS_PER_SEC=50000 NEXMARK_DURATION_SEC=60 \
    cargo test --test e2e_tpch_tests -- --ignored nexmark --test-threads=1 --nocapture

The benchmark reports per-query throughput, latency percentiles, and the maximum sustainable event rate.

The Bottom Line

pg_trickle isn't trying to replace Flink or Kafka Streams for large-scale stream processing. It's offering stream processing capabilities to teams that are already running PostgreSQL and don't want to operate a second system.

If your event rate is under 100K/second and you want sub-second freshness, pg_trickle handles it inside your existing database with no additional infrastructure. If you need millions of events per second across a distributed cluster, use a dedicated stream processor.

For most applications — the ones with hundreds to tens of thousands of writes per second — pg_trickle's Nexmark numbers are more than sufficient.