Somewhere around 2015, it became fashionable to declare stored procedures dead. Business logic belonged in the application layer. Databases were dumb persistence. ORMs would save us. Microservices would orchestrate everything.
Then I watched a Solvency II reporting pipeline take 14 hours because someone insisted on pulling 80 million policy records into a Python service to compute technical provisions row by row. The previous PL/SQL implementation ran in 22 minutes.
The question is not whether stored procedures are good or bad. The question is where the data lives, how much of it moves, and who signs the regulatory submission.
The Cost of Moving Data
Most architectural debates ignore physics. If you have 50 million transactions in Oracle and you need to aggregate them by counterparty, currency, and risk class, you have two options:
- Compute the aggregation where the data sits, return a few thousand rows
- Stream 50 million rows over the network to an application server, aggregate in memory, write back
The second option is not just slower. It is slower by orders of magnitude, and it introduces failure modes that did not exist before: network timeouts mid-stream, partial reads, memory pressure on the app tier, retry semantics that may or may not be idempotent.
In BDDK and EIOPA reporting, where the same calculation may need to run nightly across years of historical data for back-testing, this difference is not academic. It is the difference between a pipeline that finishes before the regulatory deadline and one that does not.
When Stored Procedures Are the Right Answer
After 13 years of building reporting infrastructure for banks and insurers, the pattern is consistent. Push logic into the database when:
- The operation is set-based and the data is already there. Aggregations, joins across large tables, window functions over time series. Oracle's optimizer has spent 40 years getting good at this. Your Python loop has not.
- The result needs to be auditable against the source. When a regulator asks why a number is what it is, being able to point to a single procedure with versioned DDL and a clear execution log is worth more than any clean architecture diagram.
- Latency between read and write matters. Computing a provision, then writing it back, then computing a derived figure from it. Round-tripping each step through an application server adds milliseconds that compound into hours over a full run.
- The transaction boundary must be tight. If a calculation and its persistence must succeed or fail together, doing it in a single PL/SQL block is cleaner than coordinating distributed transactions across services.
When to Get Out of the Database
The reverse is equally important. Pull logic out when:
- The logic depends on external systems. Calling a rating API, hitting a message queue, reading from a file system. Do not do this from PL/SQL. You will regret it the first time the external system hangs and your database sessions pile up.
- The computation is not set-based. Iterative simulations, Monte Carlo runs, machine learning inference. These belong in Python or Scala, not in cursor loops.
- The logic changes faster than your database release cycle. If business rules shift weekly and your DBA team deploys quarterly, stored procedures become a bottleneck. Move that logic somewhere with a faster deployment cadence.
- You need horizontal scale beyond what the database can give you. If you genuinely need to fan out across 200 workers, the database is not the right place. But be honest about whether you actually need that scale or whether you just want it.
The Hybrid That Actually Works
In practice, the reporting pipelines that survive contact with regulators look like this:
- Raw data lands in Oracle or a comparable warehouse
- Set-based transformations, aggregations, and reconciliations happen in PL/SQL packages with full logging
- Results are exposed through views or materialized views
- An orchestration layer in Python or Airflow handles scheduling, external system integration, and notifications
- Final report generation, formatting, and submission happen in the application layer
The database does what databases are good at. The application layer does what it is good at. Nobody pretends that one tool solves every problem.
The 2am Test
The real decision criterion is this: when the nightly run fails at 2am and someone has to fix it before the 8am regulatory submission, where do they look?
If the answer is "I check the database job log, find the failed procedure, read the error, and know exactly which 50 lines of PL/SQL to investigate," you have a maintainable system.
If the answer is "I trace the request through six microservices, correlate logs across four systems, figure out which container died, and then try to reproduce the data state," you have built something that looks clean on a whiteboard and is hostile to operate.
Regulatory reporting is not a place to optimize for architectural elegance at the expense of operability. The auditor does not care about your hexagonal architecture. The auditor cares whether the number is right and whether you can prove how it was calculated.
The Honest Framework
Stop asking whether stored procedures are modern. Start asking:
- Where does the data actually live right now?
- How much of it has to move to compute the answer?
- Who is accountable when the number is wrong?
- How fast does this logic need to change?
- What does the failure mode look like at 2am?
Answer those, and the right place for the logic usually becomes obvious. Ideology is what you reach for when you have not thought the problem through.