Difference between OLTP and OLAP (and why not run analytics on production)

-- Typical OLTP: small, fast, one-row update
UPDATE accounts
SET    balance = balance - 50000
WHERE  account_id = 'SAV2001';
-- Runs thousands of times per second
-- Touches one row, completes in milliseconds

package com.bank.core;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class OLTPExample {
    public static void main(String[] args) throws Exception {
        String sql = "UPDATE accounts SET balance = balance - ? "
            + "WHERE account_id = ?";

        try (Connection conn = DriverManager.getConnection(
                 "jdbc:postgresql://localhost/bankdb",
                 "app", "secret");
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setBigDecimal(1, new BigDecimal("50000"));
            ps.setString(2, "SAV2001");
            int rows = ps.executeUpdate();
            System.out.println("Updated rows: " + rows);
        }
    }
}

Updated rows: 1

-- Typical OLAP: massive scan-and-aggregate
SELECT d.branch_name,
       SUM(f.amount)   AS total_spend,
       COUNT(*)        AS txn_count
FROM   fact_transactions f
JOIN   dim_branch d ON f.branch_id = d.branch_id
WHERE  f.quarter = 'Q3-2024'
GROUP  BY d.branch_name
ORDER  BY total_spend DESC;

branch_name    | total_spend    | txn_count
Mumbai Central | 45000000.00    | 125000
Delhi Main     | 38000000.00    | 98000
Bangalore IT   | 32000000.00    | 87000

package com.bank.core;

public class OLTPvsOLAP {
    public static void main(String[] args) {
        System.out.println("OLTP: UPDATE accounts SET balance = "
            + "balance - 50000 WHERE id = 'SAV2001'");
        System.out.println("  -> 1 row, 2ms, runs 10000x/sec");
        System.out.println();
        System.out.println("OLAP: SELECT branch, SUM(amount) "
            + "FROM fact_transactions GROUP BY branch");
        System.out.println("  -> 50M rows, 8 sec, runs 10x/day");
    }
}

OLTP: UPDATE accounts SET balance = balance - 50000 WHERE id = 'SAV2001'
  -> 1 row, 2ms, runs 10000x/sec

OLAP: SELECT branch, SUM(amount) FROM fact_transactions GROUP BY branch
  -> 50M rows, 8 sec, runs 10x/day

package com.bank.core;

public class ResourceContention {
    public static void main(String[] args) {
        // Simulating what happens when OLAP runs on OLTP
        System.out.println("Normal OLTP latency: 2 ms");
        System.out.println("During analytics scan: 3500 ms");
        System.out.println();
        System.out.println("Root causes:");
        System.out.println("  1. CPU saturated by scan");
        System.out.println("  2. Buffer cache evicted by scan");
        System.out.println("  3. Locks held by long query");
        System.out.println("  4. Row-store reads ALL columns"
            + " to aggregate ONE column");
        System.out.println();
        System.out.println("Result: Rajesh's payment times out.");
    }
}

Normal OLTP latency: 2 ms
During analytics scan: 3500 ms

Root causes:
  1. CPU saturated by scan
  2. Buffer cache evicted by scan
  3. Locks held by long query
  4. Row-store reads ALL columns to aggregate ONE column

Result: Rajesh's payment times out.

# CDC from production PostgreSQL to OLAP warehouse
# Using Debezium to capture every committed change
# Source: OLTP (PostgreSQL) -> Kafka -> OLAP (Redshift)

# Step 1: Debezium captures changes
# Step 2: Kafka streams changes to consumer
# Step 3: Consumer writes to Redshift

# Result: OLAP is seconds behind OLTP
# Analytics runs on Redshift, not production

-- Fact table: one row per transaction event
CREATE TABLE fact_transactions (
    txn_id      VARCHAR PRIMARY KEY,
    account_id  VARCHAR REFERENCES dim_account(account_id),
    branch_id   INT     REFERENCES dim_branch(branch_id),
    date_id     INT     REFERENCES dim_date(date_id),
    amount      DECIMAL(15,2),
    txn_type    VARCHAR
);

-- Dimension table: branch attributes
CREATE TABLE dim_branch (
    branch_id   INT PRIMARY KEY,
    branch_name VARCHAR,
    city        VARCHAR,
    region      VARCHAR
);

package com.bank.core;

public class HTAPConsideration {
    public static void main(String[] args) {
        System.out.println("HTAP: one engine for both OLTP + OLAP");
        System.out.println("Examples: TiDB, SingleStore");
        System.out.println();
        System.out.println("Bank preference: clear separation");
        System.out.println("  Reason: analyst's report must NEVER");
        System.out.println("  slow down a customer's payment.");
        System.out.println("  Risk tolerance: zero.");
    }
}

HTAP: one engine for both OLTP + OLAP
Examples: TiDB, SingleStore

Bank preference: clear separation
  Reason: analyst's report must NEVER
  slow down a customer's payment.
  Risk tolerance: zero.