Index a Slow 90-Million-Row FinTech Reporting Database
Overview
What this challenge is about.
Index a Slow 90-Million-Row FinTech Reporting Database. Intermediate challenge in analysis. Analyzing real datasets and building models that drive decisions,...
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.
This is not a data exercise. It is the work an analyst does when stakeholders need answers from messy data. That distinction matters to every hiring manager who has seen candidates describe statistical methods and none who have extracted insight from messy, real-world data.
When you finish, you will have something most graduates do not: a real-world deliverable, verified by Ewance, that you can show to a hiring manager and say "I did this. Here is the proof."
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