eRPC PostgreSQL Performance Analysis Report

Date: 2025-12-03 Database: erpc-db (PostgreSQL 18.0) Issue: 100% CPU utilization, write-heavy workload performance degradation

---

Executive Summary

The eRPC database was experiencing severe performance issues caused by inefficient query patterns and incorrect index design. The database appeared CPU-bound but was actually I/O-bound - the CPU was spending most of its time waiting on disk reads.

Root Cause: A single query pattern accounted for 81.3% of total database execution time, with the index column order being backwards for the actual query pattern.

Solution: Create a new index with correct column order and remove redundant/unused indexes.

---

Root Cause Analysis

The Problematic Query

SELECT value FROM rpc_cache
WHERE range_key = $1 AND partition_key LIKE $2
AND (expires_at IS NULL OR expires_at > NOW() AT TIME ZONE $3)
ORDER BY partition_key DESC
LIMIT $4

Before optimization:

• Mean execution time: 561 ms
• Buffers read per query: 18,431 pages (~150 MB I/O)
• Percentage of total DB time: 81.3%
• Concurrent executions: 63+ queries competing for I/O

The Index Problem

Original indexes:

PRIMARY KEY (partition_key, range_key)
CREATE INDEX idx_reverse ON rpc_cache (partition_key, range_key);  -- Duplicate of PK
CREATE INDEX idx_expires_at ON rpc_cache (expires_at) WHERE expires_at IS NOT NULL;  -- Never used

The query filters on:

WHERE range_key = $1 AND partition_key LIKE $2

The problem: The index starts with partition_key, but the query's most selective filter is range_key = exact_value. PostgreSQL had to scan all rows matching the partition_key prefix, then filter by range_key.

---

Solution

1. Create New Index with Correct Column Order

CREATE INDEX CONCURRENTLY idx_range_partition ON rpc_cache (range_key, partition_key);

This allows PostgreSQL to:

1. Find exact range_key match first (very selective)
2. Then filter by partition_key LIKE 'evm:10:%' prefix on a small result set

2. Drop Redundant Indexes

DROP INDEX CONCURRENTLY idx_reverse;     -- Duplicate of primary key
DROP INDEX CONCURRENTLY idx_expires_at;  -- Never used (0 scans)

---

Results

Query Execution Plan - Before

Index Scan Backward using idx_reverse on rpc_cache
  Index Cond: ((partition_key >= 'evm:10:') AND (partition_key < 'evm:10;')
               AND (range_key = 'eth_getBlockByNumber'))
  Buffers: shared hit=21 read=18431
  I/O Timings: shared read=15054.799
Execution Time: 16208.208 ms

Query Execution Plan - After

Index Scan Backward using idx_range_partition on rpc_cache
  Index Cond: ((range_key = 'eth_getBlockByNumber:somehash')
               AND (partition_key >= 'evm:10:') AND (partition_key < 'evm:10;'))
  Buffers: shared hit=3
Execution Time: 0.034 ms

Performance Comparison

Metric	Before	After	Improvement
Execution Time	16,208 ms	0.034 ms	99.9998%
Buffers Read	18,431 pages	3 pages	99.98%
I/O Wait	15,054 ms	~0 ms	~100%

---

Schema Changes for eRPC

The following changes need to be implemented in eRPC's database initialization/migration code:

Add New Index

CREATE INDEX idx_range_partition ON rpc_cache (range_key, partition_key);

Remove Duplicate Index

-- DELETE THIS from schema:
CREATE INDEX IF NOT EXISTS idx_reverse ON rpc_cache (partition_key, range_key);

Keep or Remove idx_expires_at

CREATE INDEX IF NOT EXISTS idx_expires_at ON rpc_cache (expires_at) WHERE expires_at IS NOT NULL;

Note: This index is currently unused (0 scans) because only finalized data is being cached. If unfinalized or realtime data is used in the future, this index will be needed for expiration queries. Keep it if you plan to use non-finalized data; remove it if you only use finalized data.

---

Optional: Schema Improvement

For further optimization, consider adding an explicit chain_id column to eliminate LIKE pattern matching entirely:

Current approach:

-- partition_key = 'evm:10:0xabc...'
SELECT value FROM rpc_cache
WHERE range_key = $1 AND partition_key LIKE 'evm:10:%'

Improved approach:

-- Add chain_id column extracted from partition_key
ALTER TABLE rpc_cache ADD COLUMN chain_id int;

-- New query (equality instead of LIKE)
SELECT value FROM rpc_cache
WHERE chain_id = 10 AND range_key = $1

This would provide:

• Faster equality comparisons vs pattern matching
• Enable partitioning by chain_id in the future
• More efficient index usage

---

Verification Queries

Check Index Usage

SELECT
    indexrelname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE relname = 'rpc_cache'
ORDER BY idx_scan DESC;

Verify Query Plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT value FROM rpc_cache
WHERE range_key = 'eth_getBlockByNumber:somehash'
  AND partition_key LIKE 'evm:10:%'
  AND (expires_at IS NULL OR expires_at > NOW())
ORDER BY partition_key DESC
LIMIT 1;

Expected result:

• Index Scan using idx_range_partition
• Buffers: < 100 pages
• Execution Time: < 10 ms

---

Summary

Change	Impact
Add idx_range_partition	99.9%+ query time reduction
Drop idx_reverse	Reduced write amplification, storage savings
Keep idx_expires_at	Required if using unfinalized/realtime data