OLTP versus OLAP, and why not to run analytics on your OLTP database

-- OLTP: fast single-row transaction
BEGIN;
SELECT balance FROM accounts WHERE account_id = 'SAV2001' FOR UPDATE;
UPDATE accounts SET balance = balance - 25000.00
WHERE account_id = 'SAV2001';
COMMIT;
-- Completes in milliseconds, touches one row

-- OLAP: aggregate across billions of rows
SELECT
    branch_id,
    DATE_TRUNC('month', txn_date) AS month,
    SUM(amount) AS total_deposits,
    COUNT(*) AS txn_count
FROM transactions
WHERE txn_date >= '2019-01-01'
GROUP BY branch_id, DATE_TRUNC('month', txn_date)
ORDER BY month;
-- Scans 5 years of data, reads only amount + txn_date + branch_id
-- Columnar store reads just those 3 columns, not all 50

Row-Oriented (OLTP):
  Row 1: [account_id, holder_name, balance, status, branch_id, ...]
  Row 2: [account_id, holder_name, balance, status, branch_id, ...]
  Row 3: [account_id, holder_name, balance, status, branch_id, ...]

  Good at: read/write one full row (all columns together)
  Bad at:  sum one column across millions of rows (reads all columns)

Column-Oriented (OLAP):
  account_id column: [SAV2001, SAV2002, SAV2003, ...]
  balance column:    [100000, 250000, 75000, ...]
  status column:     [ACTIVE, ACTIVE, FROZEN, ...]

  Good at: sum balance across millions of rows (reads only balance)
  Bad at:  update one row (balance is in one file, status in another)

package com.bank.model;

/**
 * Why each storage layout fails at the other's job.
 */
public class StorageLayoutTradeoffs {

    // Row store scanning billions of rows for SUM(balance):
    // Must read: [account_id, holder_name, balance, status, branch_id, ...]
    // Needs only: balance
    // Wastes I/O reading 49 unwanted columns per row
    // No columnar compression to help

    // Column store updating one account's balance:
    // balance column file: find and update one value
    // status column file: if also changed, find and update there too
    // Each column is a separate file: one logical row = N file seeks
}

What happens when you run analytics on OLTP:

  1. Analytical query scans 500M rows
  2. Hot account pages evicted from buffer cache
  3. Live transactions now hit disk instead of cache
  4. CPU and memory consumed by the analytical scan
  5. Long-running snapshot prevents vacuum from reclaiming space
  6. Connection pool fills up waiting for the slow query
  7. Result: customer-facing transactions slow to a crawl

-- This kills your production database:
SELECT branch_id, SUM(amount)
FROM transactions
WHERE txn_date >= '2019-01-01'
GROUP BY branch_id;
-- Scans 500M rows on the OLTP primary during business hours
-- Every customer transaction slows down

package com.bank.config;

import javax.sql.DataSource;

/**
 * Separate data sources for OLTP and OLAP workloads.
 */
public class OltpOlapConfig {

    private final DataSource oltpDataSource;  // PostgreSQL
    private final DataSource olapDataSource;  // Redshift/BigQuery

    public OltpOlapConfig(DataSource oltp, DataSource olap) {
        this.oltpDataSource = oltp;
        this.olapDataSource = olap;
    }

    public DataSource forTransactions() { return oltpDataSource; }
    public DataSource forAnalytics()    { return olapDataSource; }
}

-- Debezium CDC pipeline:
-- 1. Debezium reads PostgreSQL logical replication
-- 2. Publishes row changes to Kafka topics
-- 3. Kafka consumer loads into Redshift/BigQuery
-- 4. Analytics team queries the warehouse
-- 5. PostgreSQL is never touched by analytics

-- The warehouse query that would kill OLTP:
SELECT branch_id, SUM(amount)
FROM warehouse.transactions
WHERE txn_date >= '2019-01-01'
GROUP BY branch_id;
-- Runs in seconds on columnar storage
-- Zero impact on production PostgreSQL