Optimizing High-Volume Financial Ledger Queries in PostgreSQL

Financial ledgers and audit tables grow rapidly. An append-only transactions table in a growing application can expand from hundreds of thousands of records to tens of millions within months.

Without targeted indexing and data retrieval patterns, simple ledger aggregation queries and balance lookups will trigger sequential scans (Seq Scan), degrading API response times from under 20ms to several seconds.

In this guide, we analyze real execution plans using EXPLAIN ANALYZE and apply composite indexing and covering index strategies to optimize high-traffic PostgreSQL ledger queries.

---

The Bottleneck: Unindexed Aggregation

Consider a typical ledger schema tracking debit and credit events for multi-tenant accounts:

CREATE TABLE transaction_records (
    id BIGSERIAL PRIMARY KEY,
    account_id UUID NOT NULL,
    transaction_type VARCHAR(16) NOT NULL, -- 'CREDIT' or 'DEBIT'
    amount_cents BIGINT NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'COMPLETED',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

When fetching an account's recent completed transactions ordered by timestamp, a standard query looks like:

SELECT amount_cents, transaction_type, created_at
FROM transaction_records
WHERE account_id = 'a1b2c3d4-0000-0000-0000-000000000001'
  AND status = 'COMPLETED'
ORDER BY created_at DESC
LIMIT 50;

On a table with 5 million rows, running EXPLAIN ANALYZE on this query reveals:

• Plan: Parallel Seq Scan on transaction_records
• Execution Time: ~420ms
• Filter Cost: Every disk page must be scanned into memory to filter out irrelevant account IDs.

---

1. Creating the Targeted Composite Index

A single-column index on account_id only reduces the search space partially; the database engine still performs an in-memory sort on created_at and evaluates status = 'COMPLETED'.

To resolve this, create a composite B-Tree index structured in the exact order of equality checks followed by range/ordering clauses:

CREATE INDEX idx_transactions_acc_status_created
ON transaction_records (account_id, status, created_at DESC);

Why this works:

• account_id immediately eliminates 99.9% of unrelated rows.
• status matches the equality filter within that account's leaf nodes.
• created_at DESC ensures rows are already sorted on disk in descending order, eliminating the expensive Sort node in the query plan.

---

2. Using Covering Indexes (INDEX ... INCLUDE) to Eliminate Heap Fetches

Even with an index scan, PostgreSQL must visit the primary heap table to retrieve amount_cents and transaction_type.

For extreme throughput APIs, convert the composite index into a Covering Index using the INCLUDE clause:

DROP INDEX idx_transactions_acc_status_created;

CREATE INDEX idx_transactions_covering
ON transaction_records (account_id, status, created_at DESC)
INCLUDE (amount_cents, transaction_type);

The Result: Index-Only Scan

Re-running EXPLAIN ANALYZE now produces:

• Plan: Index Only Scan using idx_transactions_covering
• Execution Time: 0.84ms (down from 420ms)
• Heap Fetches: 0 (all requested columns exist inside the index leaf nodes).

---

Key Takeaway

For append-only ledger tables:

1. Always place high-cardinality equality keys first (account_id).
2. Align index order with ORDER BY to bypass runtime memory sorting.
3. Use INCLUDE to create index-only scans on high-frequency API read paths.