Relational Persistence: PostgreSQL Ingestion Stress Test

At extreme scales, handling millions of requests per second introduces severe bottlenecks across network interfaces, CPU cycles, and database persistence layers. While stateless endpoints can scale horizontally using distributed architectures, stateful operations—specifically database writes—introduce physical and architectural limits.

This chapter explores the engineering realities of high-throughput ingestion, analyzing the limits of network cards, the performance of different programming languages, and the bottlenecks encountered when executing relational database writes under heavy load.

1. The Economics and Architecture of Extreme Scale

Achieving 1 million requests per second (RPS) on a single machine is theoretically possible but financially and operationally impractical for production environments.

The Cost of Vertical Scaling

To handle massive throughput on a single instance, organizations must provision specialized, high-performance cloud hardware.

• Network Bottlenecks: Standard cloud instances are limited by their Network Interface Card (NIC) capacity. Moving large payloads (e.g., 30 KB per request) at 1 million RPS requires over $240 \text{ Gbps}$ of network bandwidth.
• Financial Overhead: Provisioning a single "supercomputer" instance with $250 \text{ Gbps}$ network interfaces and massive CPU/memory allocations can cost close to **$1,000,000 per month** ($12 Million/year).

Real-World Horizontal Architecture

In production, high-scale enterprises (such as Amazon, Uber, or Netflix) do not rely on a single monolithic supercomputer. Instead, they distribute the load globally using a decentralized architecture.

• Geographic Distribution: Edge servers are deployed close to users (e.g., New York, California, London) to reduce latency.
• Load Balancing: Traffic is routed to regional clusters where pools of smaller, cost-effective commodity servers (each handling 100,000 to 500,000 RPS) process requests in parallel.
• Elasticity: Capacity can be dynamically scaled up in regions experiencing peak traffic without paying for idle global overhead.

2. Overcoming the Network Bottleneck: Payload Reduction

During initial stress testing, network bandwidth often saturates before CPU or memory limits are reached.

Naive vs. Optimized Payload Testing

When testing an endpoint returning a large JSON array (e.g., 100 items, totaling ~30 KB), a 10 Gbps or 50 Gbps network card quickly becomes the absolute bottleneck.

To isolate application performance from physical network limits during testing, we can reduce the payload size:

// Naive Approach: Large payload saturates the NIC
const data = Array.from({ length: 100 }, () => generateMockRecord()); // ~30 KB

// Optimized Approach: Small payload shifts bottleneck to CPU/Runtime
const data = Array.from({ length: 3 }, () => generateMockRecord());   // ~1 KB

Stress Test Results (1 KB Payload)

By reducing the payload size to 1 KB, the network interface is freed from saturation:

• CPU Usage: Drops to near 0% on the load-testing client, indicating it is no longer blocked by network I/O.
• Throughput: The system successfully achieves 3,000,000 requests per second on the stateless route.
• Trade-off: While this proves the runtime can handle the request volume, real-world payloads are rarely this small. To handle full-sized payloads (30 KB) at 1 million RPS, the underlying software stack must be highly optimized.

3. Language Runtime Performance at Scale

When pushing for 1 million RPS with realistic payloads (30 KB), standard high-level runtimes and frameworks fail—even on ultra-high-performance hardware.

The C++ Optimization Stack

To achieve 1 million RPS with 30 KB payloads, the application must be rewritten in C++ using:

1. Drogon: An ultra-high-performance, non-blocking, asynchronous C++ HTTP web framework built on top of epoll (Linux).
2. RapidJSON: A fast JSON parser and generator for C++ that leverages SIMD (Single Instruction, Multiple Data) instructions to parallelize string parsing.

4. Relational Persistence: The PostgreSQL Write Bottleneck

While stateless endpoints can scale to millions of requests, introducing a database write operation changes the performance profile entirely.

The Target Scenario

We test a simple ingestion endpoint (POST /code) that generates a random 500-character string and persists it to a PostgreSQL database.

Database Schema

CREATE TABLE codes (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    code VARCHAR(500) NOT NULL
);

Hardware Profile

The database is hosted on a high-performance, dedicated database instance:

• Cost: ~$5,000 per month.
• Resources: High CPU core count, massive RAM allocation, and fast NVMe storage.

5. The Danger of Connection Overwhelm

A common mistake when stress-testing databases is configuring the load generator with too many concurrent connections.

The Failure Mode: 5,000 Concurrent Connections

Running the load test with 5,000 concurrent connections using autocannon:

# Naive execution that overwhelms the database
autocannon -c 5000 -d 20 -m POST http://localhost:8080/code

• Result: The test fails with a massive volume of timeout errors.
• Root Cause: PostgreSQL uses a process-based connection model (or a thread pool that maps to backend processes). When 5,000 concurrent connections attempt to write simultaneously, the database spends more time on context switching, lock contention, and connection overhead than executing actual queries.

The Optimized Mode: 300 Concurrent Connections

Reducing the concurrency limit allows the database to process queries efficiently without resource starvation:

# Optimized execution with controlled concurrency
autocannon -c 300 -d 20 -m POST http://localhost:8080/code

• Result: 35,000 writes per second with 0 errors.
• Verification: Running a count query in DataGrip confirms that exactly 700,000 records were successfully persisted in 20 seconds:
  SQL

  Copy

  SELECT COUNT(*) FROM codes; -- Returns 700,000
  

6. Analyzing the Database Bottleneck

While 35,000 writes per second is highly performant for a relational database, it is far below our target of 1,000,000 RPS.

Why the Database Limits Throughput

During the 35,000 RPS database write test, system metrics reveal a surprising state:

• App Server CPU: Near idle.
• Database Server CPU: Surprisingly low (not fully saturated).
• Network Bandwidth: Negligible (only ~500 MB transferred over 20 seconds).

The Real Bottleneck: ACID Compliance and Disk I/O

The limit is not CPU, memory, or network bandwidth. The bottleneck is I/O latency driven by relational database guarantees:

1. The WAL (Write-Ahead Log): To guarantee Durability (the "D" in ACID), PostgreSQL must write transaction data to the WAL on non-volatile storage before confirming a successful write to the client.
2. Disk Serialization: Even with high-speed NVMe drives, physical disk write latency (specifically the fsync system call) limits how fast individual, sequential transactions can be committed.
3. Lock Contention: Multiple connections writing to the same table must acquire locks on index structures and page buffers, creating serialization queues.

Summary and Key Takeaways

• Horizontal over Vertical Scaling: Real-world high-throughput systems rely on geographically distributed load balancers and clusters of smaller servers rather than single, expensive supercomputers.
• Network Saturation: Large payloads (e.g., 30 KB) will saturate network interfaces long before CPU limits are reached. Reducing payload sizes during testing helps isolate runtime bottlenecks.
• Runtime Efficiency Matters: Standard runtimes (Node.js, Python, Java, Go) cannot handle 1 million RPS with realistic payloads on a single machine. Achieving this requires low-level languages like C++ utilizing asynchronous frameworks (Drogon) and SIMD-accelerated parsers (RapidJSON).
• Connection Overload: High concurrency (e.g., 5,000 connections) degrades database performance due to context switching and lock contention. Lowering concurrency (e.g., 300 connections) can increase throughput and eliminate timeout errors.
• The Relational Write Limit: Relational databases are bound by ACID compliance. The requirement to flush transactions to the Write-Ahead Log (WAL) on disk limits write throughput, making raw relational inserts unsuitable for direct 1M+ RPS ingestion without caching, batching, or NoSQL buffering layers.