Anyone who has worked with PostgreSQL in production environments knows that testing database performance is rarely straightforward. Synthetic benchmarks like pgbench are useful for stress testing, but they don't reflect how real applications behave. Production workloads have peaks and troughs, complex query patterns, and user behaviour that varies throughout the day. This is why I'm pleased to introduce the pgEdge Load Generator.

The Problem with Traditional Benchmarks

Most database benchmarking tools focus on raw throughput: how many queries per second can the database handle at maximum load? Whilst this is valuable information, it tells us little about how a system will cope with real-world usage patterns.

Consider a typical e-commerce platform. Traffic peaks during lunch breaks and evenings, drops off overnight, and behaves differently at weekends compared to weekdays. A stock trading application has intense activity during market hours and virtually none outside them. These temporal patterns matter enormously for capacity planning, replication testing, and failover validation.

What Is pgEdge Load Generator?

The pgEdge Load Generator (pgedge-loadgen) is a command-line tool that creates realistic PostgreSQL workloads for testing and validation. It's not a benchmarking tool; it's a workload simulator designed to exercise your database in ways that mirror actual application behaviour.

The tool provides seven pre-built applications spanning different use cases:

Transaction Processing (TPC-based):

  • wholesale (TPC-C): Classic OLTP with orders, inventory, and payment processing

  • analytics (TPC-H): Decision support with 22 complex analytical queries

  • brokerage (TPC-E): Mixed read/write stock trading simulation

  • retail (TPC-DS): Multi-channel retail decision support

Semantic Search (pgvector-based):

  • ecommerce: Product search with vector embeddings

  • knowledgebase: FAQ and documentation similarity matching

  • docmgmt: Enterprise document management

Temporal Profiles: The Key Differentiator

What sets this tool apart from traditional benchmarks is its temporal profile system. Rather than hammering the database at a constant rate, the load generator adjusts its activity based on simulated time-of-day patterns.

Four profiles are included:

  • local-office: Single timezone business hours with realistic lunch dips

  • global: 24/7 operation following business hours across multiple timezones

  • store-regional: Evening peak patterns typical of regional e-commerce

  • store-global: Multi-region peaks spanning Asia, Europe, and the Americas

This means you can test how your database handles the transition from quiet periods to peak load, the scenario that catches out many production deployments.

Getting Started

The workflow is straightforward. First, initialise your chosen application:

pgedge-loadgen init --app wholesale --target-size 1GB

This creates the schema and populates it with realistic test data generated using the gofakeit library. You can target specific database sizes from megabytes to terabytes.

Then run the workload:

pgedge-loadgen run --workers 20 --profile global

The tool reports real-time statistics including queries per second, average latency, and p99 latency. A graceful shutdown (Ctrl+C) provides a summary of the entire run.

Use Cases

The load generator proves particularly valuable for:

Replication Testing: Simulate continuous write workloads to validate streaming or logical replication under realistic conditions.

Failover Validation: Generate sustained activity whilst testing automatic failover mechanisms. The temporal profiles help identify whether failover behaves differently during peak versus quiet periods.

Configuration Tuning: Test changes to work_mem, shared_buffers, or connection pooling settings against realistic query patterns rather than artificial stress tests.

Capacity Planning: The temporal profiles provide a more accurate picture of resource utilisation throughout a typical business cycle.

Final Thoughts

The pgEdge Load Generator fills a gap in the PostgreSQL testing ecosystem. Traditional benchmarks measure theoretical maximums; this tool helps you understand how your database will behave when real users are interacting with it.

The source code is available on GitHub, and comprehensive documentation covers installation, configuration, and advanced usage patterns. If you're responsible for PostgreSQL deployments that need to handle realistic production workloads, give it a bash; you won’t regret it!