Taming the "Beast SQL": Moving from Read-Side Hacks to Write-Side Integrity

In Part 1, I detailed how an unprimed Oracle Exadata Flash Cache after a weekend Disaster Recovery (DR) test spiked database latency from 40ms to 20 minutes, triggering connection timeouts and an emergency incident.

Allowing the cache to warm organically over 12 hours stabilised the system. But waiting for hardware to warm was treating a symptom. The incident exposed the fragility of our core data layer: every user login executed a massive, runtime deduplication query across 10 billion records.

Here is how our 10kB "Beast SQL" worked, why it failed without hardware acceleration, how a tactical 10-day window patch bought us months of runway, and how we permanently restructured the ingestion pipeline for write-side integrity.

The Anatomy of the 10kB "Beast SQL"

When a retail or business banking customer logs into their account, the dashboard displays their current balance and their last 40 transactions.

Serving those 40 rows did not involve a simple indexed select. Instead, the application fired a 10kB query reconciling three distinct data sources:

1. The Booked Ledger (booked_transaction): The historical system of record containing over 10 billion settled records spanning 24+ months, partitioned across roughly 8,000 partitions.

2. Internal Markers (marked_booked_transaction): An append-only table of approximately 700 million records. Every status update inserted a new row. The query had to dynamically determine the most recent state for any given transaction reference.

3. Real-Time Feed (v_realtime_transaction): External pending transaction authorisations from card processors and clearing gateways, keyed by a composite primary key spanning five columns.

   
   

Why Runtime Deduplication Collapses

To deduplicate internal markers, the query relied on an analytic window function:

SELECT mbt_filtered.*
FROM (
    SELECT mbt.*,
           ROW_NUMBER() OVER (
               PARTITION BY mbt_account_nr, mbt_account_currency_code, mbt_transaction_ref_id
               ORDER BY mbt_calculation_ts DESC NULLS LAST
           ) AS rn
    FROM marked_booked_transaction mbt
    WHERE mbt_account_nr = :1 
      AND mbt_account_currency_code = :2 
      AND mbt_valid_until > SYSDATE
) mbt_filtered
WHERE mbt_filtered.rn = 1 
  AND mbt_filtered.mbt_transaction_status = 'PENDING';

Next, to verify whether a pending authorisation had already settled, the query joined the candidate pending set back against the primary booked transactions table:

SELECT *
FROM pending_tx
LEFT JOIN booked_transaction tx ON (
    tx.tx_account_nr = :5 
    AND tx.tx_account_currency_code = :6 
    AND pending_tx.pending_tx_sepa_customer_ref = tx.tx_sepa_customer_ref
)
WHERE tx.tx_sepa_customer_ref IS NULL;

The Bottlenecks

• Sort & Hash Overhead: ROW_NUMBER() OVER (PARTITION BY ...) requires grouping and sorting all candidate records in memory before discarding rows where rn > 1.

• Unbounded Ledger Joins: Because pending items were joined against booked_transaction without date boundaries, the engine scanned historical ledger data for accounts with deep transaction histories. For corporate clients with 1–2 million historical records, the join took minutes.

• The Hardware Mask: Under normal operations, Exadata's Smart Scan offloaded column filtering directly to storage cell processors. This capability hid the query's inefficiency for years. When the cluster ran on a cold cache, the missing flash tier forced unbuffered physical disk I/O, causing queries to drag on for up to 20 minutes.

  
  

The Tactical Band-Aid: The 10-Day Lookback Window

Two years earlier, an internal refactoring cycle removed an older date constraint from the join between pending and booked records. The query had since been cross-referencing pending records against the entire two-year historical ledger.

Our next mandatory Disaster Recovery exercise was scheduled in four months. Rewriting the ingestion layer across multiple independent teams in that time window was impossible.

We added a single-line constraint to the pending-to-booked join:

-- The Tactical Date Boundary
AND tx.tx_booking_date >= SYSDATE - 10

We based this decision on these factors. Note that 10 days is a parameter we can change as needed.

┌────────────────────────────────────────────────────────────────────────┐
│                        THE 10-DAY WINDOW METRICS                       │
├───────────────────────────┬────────────────────────────────────────────┤
│ Historical Tail Covered   │ 99.8% of pending items clear in < 5 days   │
│ Boundary Horizon          │ 10 calendar days                           │
│ Known Edge Risk           │ Multi-day bank holiday clusters (Christmas)│
└───────────────────────────┴────────────────────────────────────────────┘

Validating Confidence

In load test environments, the execution plan appeared unchanged. Synthetic test accounts held only ~10,000 transactions, far below the volume needed to expose partition-scanning bottlenecks.

We relied on domain metrics: over 99.8% of pending transactions settled within 3 to 5 business days. A 10-day lookback window provided adequate headroom.

The patch held during the subsequent DR exercise. It bought more time to implement a permanent redesign.

The Permanent Solution: Write-Side Integrity

The root flaw was treating the database query engine as an ad-hoc reconciliation layer on read.

We restructured the architecture around two clear principles:

1. Physical Table Separation: We permanently split Pending and Booked records into separate, purpose-built tables.

2. Write-Side Deduplication: We moved all deduplication, state validation, and settlement checks into our 15 dedicated ingestion importers.

Write Complexity vs. Read Simplification

A common question during architectural review was: Why add logic to 15 importers instead of keeping it centralised in SQL?

The operational economics made the answer more complex:

While write volume was high (40–50M records/day), resolving integrity on ingestion meant paying the computational penalty once per write.

Read queries were reduced to simple, index-backed SELECT operations:

-- The Refactored Read: Zero Analytic Overhead
SELECT tx_id, tx_amount, tx_currency, tx_booking_date, tx_status
FROM booked_transactions
WHERE tx_account_nr = :1
ORDER BY tx_booking_date DESC
FETCH FIRST 40 ROWS ONLY;

Summary & Looking Forward

High-end database engines can compensate for suboptimal query patterns, but they accumulate architectural debt. Moving deduplication from the runtime query layer to write-side ingestion transformed our database from an overloaded calculation engine into a stable, low-latency storage tier.

Eliminating the 10kB "Beast SQL" had a second, strategic benefit: it made our application database-agnostic. We no longer need such a strong database.

In the future, I will cover "The Great Exit": how we built a zero-downtime dual-write bridge to migrate 11TB of banking transactions from on-premises Oracle Exadata to Cloud PostgreSQL without relying on CDC pipelines.