Tábuas, Part 1: a 1 TB Oracle base and a pipeline that wouldn't finish

First draft — Virgil, grounded in the actual benchmark/pipeline notes, kept intentionally abstract on schema and business logic per the project’s own scope rules. Ruan’s to punch up into his voice, same treatment as everything else this pass.

The setting

The BR-EMS biometric tables are built at LabMA/UFRJ on top of an Oracle base of well over a billion records — submissions from Brazilian insurers covering on the order of 130 million people. The pipeline turns that raw mass into the inputs of a life table: for every person, every year, how long were they exposed, and did they die, get disabled, or leave.

That’s a simpler sentence than the reality. “How long were they exposed” sounds like a lookup; it’s actually a reconciliation problem. Insurers submit their own records, on their own schedules, with their own conventions for how a contract starts, ends, gets cancelled, or quietly carries over into the next year without anyone re-submitting it. None of that arrives clean. The pipeline’s job is to turn a pile of submissions that don’t agree with each other into one canonical timeline per person, per contract, per year — and to do it the same way every time, because a life table is only as trustworthy as its reproducibility.

At the scale involved — hundreds of millions of exposure-years once everything’s unpacked — the honest version of “turn messy submissions into a timeline” is a genuinely large batch job. That’s the part that broke.

Where the naïve pipeline died

The first version of this pipeline was correct. It was also, past a certain table size, not really finishable. Not “slow” in the sense of “grab a coffee” — slow in the sense of “this step alone will not complete inside the batch window we have,” which is a different kind of problem. The kind where you stop asking “how do I make this faster” and start asking “what is this computation actually doing, and does it have to do that much work.”

The proximate cause was one specific kind of query, run at the scale the real data demanded rather than the scale it was tested at. It wasn’t a bug — the query was returning correct results, eventually. It just didn’t scale the way the rest of the pipeline needed it to, and nothing about staring harder at the SQL was going to fix that. The fix turned out to live one level up: in how the problem was framed, not in how the query was written. That reframe is the rest of this series.

What this series covers

• Part 1 (this post) — the problem, the base, and the shape of the slowness.
• Part 2 — reading the execution plan, and the bitmask reframe for the contract logic.
• Part 3 — the CROSS JOIN reframe, the GROUP BY rewrite, and the multi-engine benchmark (PostgreSQL · Hydra · ClickHouse · DuckDB) that followed.

Part 2 starts where the profiling actually starts: reading the execution plan instead of guessing at it.