Virus HQ ← All notes

12 hours to 30 minutes: rebuilding a payout pipeline in PySpark

Sagar Rathod · Solutions & Systems Architect ·

A large fintech platform ran its partner payouts and reconciliation as a nightly batch built on database stored procedures. It took about 12 hours, and as volume grew it was failing to scale. A batch that long leaves no room for a rerun: one bad night delays payouts and creates real operational risk.

  • Runtime cut from 12 hours to 30 minutes (96% faster)
  • Zero missed payouts during the migration
  • 100% payout matching accuracy
  • Full observability and alerting
  • Phase 2 base version delivered in 3 weeks

Why stored procedures hit a wall

Stored procedures are fine until the work outgrows one database. All the processing competes with everything else that database is doing, it scales only as far as the biggest instance you can buy, and the logic is hard to test or observe. Moving the work to a distributed engine fixes all three.

Phase 1: get more out of what's there

The first phase optimized the existing MySQL stored procedures. Tuning what's already in production is the fastest, lowest-risk win, and it buys breathing room while the bigger change is built.

Phase 2: move the heavy lifting off the database

The second phase changed the shape of the pipeline: data is exported from MySQL to S3, processed in PySpark on AWS Glue, and the results are written back to the database. The base version of this phase shipped in three weeks. The database goes back to serving the application instead of doing hours of batch work, and processing that ran serially now runs in parallel across the cluster.

Migrating money logic safely

The hard part of a payout migration isn't speed, it's correctness. The legacy logic encoded years of business rules, and a single wrong number is a real problem for a real partner. The bar was simple: the new pipeline had to match the old one exactly before it took over, and the cutover had to happen with no downtime and no missed payouts. It did both.

Make it observable

The old batch was a black box until it finished or failed. The new pipeline has full observability and alerting, so problems surface quickly instead of the next morning.

The takeaway

If a critical batch job has quietly become your longest-running process, tune it first to buy time, then fix the architecture. Done in phases, that can take weeks rather than quarters.

Questions people ask

Why do stored-procedure batch jobs stop scaling?

All the processing competes with everything else the database is doing, it scales only as far as the biggest instance you can buy, and the logic is hard to test or observe. Moving the work to a distributed engine fixes all three.

How do you speed up a slow nightly batch job?

Tune what's already in production first to buy breathing room, then change the architecture. Here that meant optimizing the MySQL stored procedures, then exporting data to S3, processing it in PySpark on AWS Glue and writing results back, cutting runtime from 12 hours to 30 minutes.

How do you migrate payout logic without errors?

Treat correctness as the bar, not speed. The new pipeline had to match the old one exactly before taking over, and the cutover had to happen with no downtime and no missed payouts. It achieved zero missed payouts and 100% payout matching accuracy.

Facing something similar?

Book a free 30-minute architecture call. Describe what you're building or what's breaking, and you'll leave with a clear diagnosis and next steps.

Talk to me