Index a Slow 90-Million-Row FinTech Reporting Database
Overview
What this challenge is about.
Design indexes to speed six FinTech reporting queries 5x on 90 million rows, validate plans, and earn a verifiable certificate.
The scenario
The file is a de-identified engineering record from a Warsaw-based personal-finance app (about 95 staff, 220,000 monthly active users) whose nightly and on-demand reporting now blocks the finance team. It contains one backend engineer's ticket, the offending six queries, the transactions table definition, and the query plans captured during a staging run that mirrors production at roughly 90 million rows.
The Brief
What you'll do, and what you'll demonstrate.
Choose the smallest set of PostgreSQL indexes that makes six reporting queries at least five times faster on a 90-million-row transactions table without unacceptably regressing writes or locking the table during deployment.
Earning criteria — what you'll demonstrate
- Read and interpret EXPLAIN ANALYZE output to identify the true cost driver behind a slow query rather than guessing.
- Select the minimum high-leverage index set, using partial and covering indexes only where the query and data justify them.
- Quantify the write-side trade-off of every index instead of treating index addition as free.
- Design a deployment that adds indexes to a large live table without locking it or exceeding a tight maintenance window.
- Communicate a database decision as an evidence-backed recommendation a single engineer can review and trust.
Program Fit
Where this fits in your program.
Sharpens the same skills your degree expects you to demonstrate.
Aligned coursework coming soon.
Skills
Skills you'll demonstrate.
Each one shows up on your verified credential.
- Indexing
Apply indexing to solve real industry problems and demonstrate production-level capability.
- Query Optimization
Apply query optimization to solve real industry problems and demonstrate production-level capability.
- Explain Analyze
Apply explain analyze to solve real industry problems and demonstrate production-level capability.
- Postgresql
Apply postgresql to solve real industry problems and demonstrate production-level capability.
- Partial Indexes
Apply partial indexes to solve real industry problems and demonstrate production-level capability.
- Deployment Safety
Apply deployment safety to solve real industry problems and demonstrate production-level capability.
Careers
Career paths this challenge builds toward
Completing this challenge demonstrates skills that transfer directly to these roles:
Database Administrator
Mirrors core DBA work: diagnosing slow queries from execution plans, choosing indexes that pay for themselves, and shipping schema changes to a busy production database without downtime — the exact judgement a fintech relies on a DBA to exercise safely.
This challenge sharpens
- indexing
- explain-analyze
- deployment-safety
Performance Engineer
Builds the habit of measuring before and after, isolating the true bottleneck, and quantifying trade-offs rather than optimizing on intuition — the foundation of performance work across data-intensive systems.
This challenge sharpens
- query-optimization
- explain-analyze
- indexing
Backend Engineer (Data-Intensive Systems)
Backend engineers at small teams own the database too; this challenge develops the ability to keep reporting fast and writes healthy on a large table while deploying changes safely, a daily reality when you are the only one on call.
This challenge sharpens
- postgresql
- partial-indexes
- deployment-safety