Fintech Startup: 10x Query Performance

Background

The Client

A Series A fintech startup processing over 800,000 payment transactions daily. Their core product — real-time financial dashboards and end-of-day compliance reports — was built on PostgreSQL managing three years of accumulated transaction history across 40+ million records.

As the dataset grew, reporting queries that once returned in seconds began timing out. By the time DharmOps was engaged, the finance and compliance teams could no longer close the books reliably. Reports were triggering database timeouts, forcing manual exports as a workaround.

The Problem

What Was Slowing Them Down

A DharmOps diagnostic uncovered four compounding issues that had gone undetected because they only surfaced at scale:

• Full sequential scans on multi-year transaction tables. The twelve most-used reporting queries performed sequential scans across the entire transactions table — 40M+ rows — on every execution, with no filtering on indexes.
• Missing composite indexes on high-cardinality filter columns. Queries filtering by account_id, date range, and status had no composite indexes to support that access pattern. PostgreSQL fell back to seq scans instead of index seeks.
• ORM-generated SQL bypassing the query planner. The application's ORM was generating sub-optimal JOIN ordering that prevented the planner from choosing the most efficient execution path.
• No materialized views for aggregated financial summaries. Every report recalculated running totals, daily summaries, and balance snapshots from raw records on each request — work that only needed to happen once per day.

The Solution

What We Did

DharmOps completed the full engagement in 5 working days. All changes were applied live using concurrent index builds and a rolling deployment — zero downtime, no maintenance window required.

• Designed composite indexes on (account_id, created_at DESC, status). Created covering indexes tailored to each reporting access pattern. The primary reporting query dropped from a 40M-row seq scan to an index scan returning 200 rows in under 100ms.
• Rewrote 12 critical reporting queries using CTEs and window functions. Replaced ORM-generated SQL with hand-optimized queries using CTEs for clarity and window functions for running totals — achieving the same results with 95% less data movement.
• Introduced Redis-backed caching for daily and monthly summaries. Aggregated financial summaries — daily totals, account balances, trend data — are now computed once at midnight and cached. Report pages render instantly from cache rather than querying the DB.
• Deployed pg_stat_statements monitoring with regression alerts. Set up query-level performance tracking with automated Slack alerts when any query exceeds a 2-second threshold. The client now catches regressions before users notice them.

The Results

Measurable Outcomes

Beyond the raw numbers, the compliance team regained confidence in their tooling. End-of-day reports that previously timed out now complete in under 4 seconds. The finance team eliminated their manual export workaround entirely.