PostgreSQL Performance Engineering: Reducing CPU on High-Throughput Ledgers

#The Symptoms: Transaction Exhaustion & High CPU

As transaction volume scaled, our primary PostgreSQL instance reached 95%+ CPU utilization. We experienced connection pool starvation (timeouts in HikariCP), query lock-contention on core ledger tables, and streaming replication lag that prevented reliable read-after-write operations.

#Database Topology & Multi-Datasource Routing

// Database cluster routing topology featuring primary write nodes, read replica pools, PgBouncer proxy layers, and dynamic Java data-source splitting.

#Solving the Connection Bottleneck

The first failure mode was database connection bloat. Developers assumed that more connections meant more throughput. In PostgreSQL, each connection is a separate OS process, causing significant context switching overhead under heavy load.

• ▶We set up PgBouncer as a lightweight connection pooler in transaction mode.
• ▶We tuned application-level pools (HikariCP) down to exactly matching core formulas: Connections = ((core_count * 2) + effective_spindle_count).
• ▶This single change dropped CPU utilization by ~18% through the elimination of idle connection overhead.

#Application-Level Read/Write Splitting

To relieve pressure on the primary instance, we decoupled read traffic. We configured a multi-datasource router in Spring Boot to direct all transactions marked with `@Transactional(readOnly = true)` to read-replicas.

// Multi-datasource routing handler based on transaction context
public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
            ? DataSourceType.READ_REPLICA
            : DataSourceType.PRIMARY;
    }
}

#Eliminating Write Lock Contention

Our core transactions were executing pessimistic write locks (`SELECT ... FOR UPDATE`) on account balance records, blocking concurrent payment runs. We modified the design to use an optimistic lock strategy backed by an asynchronous double-entry ledger queue. Balances are calculated through event streams, shifting heavy write locks to fast, append-only row inserts.