OLTPとOLAP

OLTPとOLAPとは

OLTP：トランザクション向け

-- OLTP example: inserting a new order
INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (1042, 88, 3, CURRENT_DATE);

-- Immediately update inventory
UPDATE inventory
SET stock = stock - 3
WHERE product_id = 88;

OLAP：分析向け

-- OLAP example: total sales by region for Q1 2024
SELECT
    d.region,
    SUM(f.sales_amount) AS total_sales,
    COUNT(f.order_id)   AS order_count
FROM fact_sales f
JOIN dim_date   dd ON f.date_key   = dd.date_key
JOIN dim_store  d  ON f.store_key  = d.store_key
WHERE dd.year = 2024
  AND dd.quarter = 1
GROUP BY d.region
ORDER BY total_sales DESC;

2つの方式を並べて比較する

-- OLTP: lookup a single customer's latest order (row-level access)
SELECT o.order_id, o.order_date, o.total_amount
FROM orders o
WHERE o.customer_id = 1042
ORDER BY o.order_date DESC
LIMIT 1;

-- OLAP: monthly revenue trend over the past year (aggregate scan)
SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount)               AS revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

スキーマ設計：正規化と非正規化

-- Normalized OLTP design (3NF)
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name        VARCHAR(100),
    email       VARCHAR(150) UNIQUE
);

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date  DATE,
    total       NUMERIC(10,2)
);

-- Denormalized OLAP fact table (star schema)
CREATE TABLE fact_sales (
    sale_id      BIGINT PRIMARY KEY,
    customer_key INT,
    date_key     INT,
    product_key  INT,
    region       VARCHAR(50),
    category     VARCHAR(50),
    amount       NUMERIC(12,2)
);

インデックス戦略の違い

-- OLTP: B-tree index for fast order lookup by customer
CREATE INDEX idx_orders_customer
    ON orders (customer_id);

-- OLTP: compound index for range queries
CREATE INDEX idx_orders_date_customer
    ON orders (order_date, customer_id);

-- OLAP: partition fact table by year to prune scan
CREATE TABLE fact_sales_2024
    PARTITION OF fact_sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

同時実行性とロック

-- OLTP: explicit transaction with row-level lock
BEGIN;

SELECT balance
FROM accounts
WHERE account_id = 7
FOR UPDATE;

UPDATE accounts
SET balance = balance - 200
WHERE account_id = 7;

COMMIT;

ETL：OLTPとOLAPをつなぐ

-- Simplified ETL INSERT from OLTP orders into OLAP fact table
INSERT INTO fact_sales (
    customer_key,
    date_key,
    product_key,
    amount
)
SELECT
    dc.customer_key,
    dd.date_key,
    dp.product_key,
    o.total_amount
FROM orders o
JOIN dim_customer dc ON dc.source_customer_id = o.customer_id
JOIN dim_date     dd ON dd.calendar_date       = o.order_date
JOIN dim_product  dp ON dp.source_product_id   = o.product_id
WHERE o.order_date = CURRENT_DATE - INTERVAL '1 day'
  AND o.order_id NOT IN (SELECT source_order_id FROM fact_sales);

典型的なOLAPクエリパターン

-- Year-over-year revenue comparison using a window function
SELECT
    dd.year,
    dd.quarter,
    SUM(f.amount)                                          AS revenue,
    LAG(SUM(f.amount)) OVER (PARTITION BY dd.quarter
                             ORDER BY dd.year)             AS prev_year_revenue,
    ROUND(
        100.0 * (SUM(f.amount) -
                 LAG(SUM(f.amount)) OVER (PARTITION BY dd.quarter
                                         ORDER BY dd.year))
        / NULLIF(LAG(SUM(f.amount)) OVER (PARTITION BY dd.quarter
                                          ORDER BY dd.year), 0)
    , 2)                                                   AS yoy_pct_change
FROM fact_sales f
JOIN dim_date dd ON f.date_key = dd.date_key
GROUP BY dd.year, dd.quarter
ORDER BY dd.quarter, dd.year;

HTAP：境界をなくす

-- PostgreSQL with cstore_fdw (columnar extension) example
-- Analytical table stored in columnar format
CREATE FOREIGN TABLE fact_sales_columnar (
    date_key     INT,
    product_key  INT,
    region       VARCHAR(50),
    amount       NUMERIC(12,2)
)
SERVER cstore_server
OPTIONS (filename '/data/fact_sales_columnar');

-- Regular OLTP table remains row-based
-- Both can be queried in the same SQL statement
SELECT f.region, SUM(f.amount)
FROM fact_sales_columnar f
GROUP BY f.region;

適切なシステムの選択

-- Quick diagnostic: check table access pattern
-- High seq_scan relative to idx_scan = analytical (OLAP-like) load
SELECT
    relname              AS table_name,
    seq_scan,
    idx_scan,
    n_live_tup           AS live_rows
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 10;

理解度チェック

レッスンのまとめ