From Weeks to 0.8 Seconds

• Industry: Global financial services (risk & analytics)
• Organization: Publicly traded, ~$6B annual revenue, operations on three continents
• Environment: AWS RDS PostgreSQL, multi-region (US/EU/APAC), read replicas + logical replication
• Engagement: Proactive DBA managed services, ongoing since 2022
• Services: Performance engineering · Major-version upgrades · Partitioning & data lifecycle · Planner tuning

The client is a global financial services firm specializing in risk and analytics. Publicly traded, roughly $6B in annual revenue, running multi-region AWS RDS PostgreSQL across the US, EU, and APAC. Their PostgreSQL estate backs the entity-screening and risk-analytics platforms their customers query around the clock: every match decision, inquiry, and monthly financial roll-up flows through these databases. At this scale, with individual tables past two billion rows, query plans are not an implementation detail. They are the product's response time.

The firm had hit a scaling wall on three fronts at once.

First, raw volume. The largest entity-match table had grown to 2.2 billion rows, with a companion table holding another 677 million, and a monitoring-history table had swollen to 1.2 TB: too large to vacuum, index, or alter comfortably, and expensive to keep entirely live.

Second, a marquee report in the firm's risk-analytics reporting platform, the monthly finance-cost roll-up, had degraded until it literally took weeks to execute. Month-end reporting was effectively unusable.

We attacked the wall in the order the plans demanded.

• Rewrote the pathological report. Working from EXPLAIN output on production-scale data, we restructured the monthly finance-cost query so the planner could drive it through indexes instead of repeated scans of billion-row tables. The original test query fell from 8 minutes 50 seconds to 0.8 seconds. A full production month completed in under five minutes, nearly all of it cold-cache I/O. Down from weeks.
• Executed the major-version migration as phased cutovers across the EU and US regions, on schedule.
• Diagnosed and fixed the CTE-inlining regressions the upgrade surfaced. Reports ran up to 4× slower post-upgrade, and a monthly data feed and a heavily-joined match query stopped using their indexes. We traced each one to CTE inlining and plan changes, restored the intended plans with targeted AS MATERIALIZED clauses, and separated true regressions from data-distribution false alarms so application teams weren't chasing ghosts.
• Applied surgical planner tuning: partial indexes matched to the queries' actual predicates, random_page_cost = 1 to reflect SSD-backed RDS storage and favor index scans, and column-type alignment across environments that took one drifted query from 30 seconds back to interactive speed.
• Built the data-lifecycle machinery: an online table-trim script and a repeatable archiving process for the 1.2 TB monitoring-history table, plus a date-based partitioning strategy, proven first in a proof of concept, for the 2.2 billion-row and 677 million-row match tables. Future maintenance, archiving, and index builds now get partition-sized units of work.

The headline number speaks for itself. A business-critical monthly report went from weeks of runtime to 0.8 seconds, an improvement of roughly six orders of magnitude, and we did it with planner expertise, not hardware. The migration landed across regions with every surfaced plan regression diagnosed and fixed, not worked around blindly. Terabyte-scale tables now have an archiving and partitioning path instead of an unbounded growth curve. The tuning work also left durable assets behind: reusable scripts, partial-index patterns, and planner-configuration standards now applied across the estate. The engagement, continues to grow year over year, and we are now the firm's standing PostgreSQL engineering partner across all three regions.