OLTP and OLAP

Introduction

In Postgres, OLTP and OLAP are two completely different database system design philosophies serving distinct purposes.
Postgres itself is an extremely powerful relational database management system (RDBMS). By default, it is highly optimized for OLTP and thanks to its rich ecosystem of extensions, Postgres can also fully support OLAP workloads.

Here is the detailed difference between these two concepts:

OLTP

Online Transaction Processing: focuses on fast, accurate and secure processing of a large number of continuous financial or operational transactions from end users.

Data characteristics: Data changes constantly (Insert, Update, Delete continuously).
Query Pattern: Read/write statements acting on one or a few specific data rows (for example, finding info of a specific customer with WHERE id = 123).
Advantages
- Data integrity (ACID): Postgres guarantees absolute transaction integrity without errors or data loss, thanks to its locking mechanisms and MVCC (Multi-Version Concurrency Control).
- Indexing: Supports multiple index types for rapid data lookups.
Examples: E-commerce platforms, banking systems, Enterprise Resource Planning, etc.

OLAP

Online Analytical Processing: focuses on analyzing and aggregating historical data to generate reports and statistics that help businesses make decisions.

Data characteristics: Historical data that rarely changes (mainly bulk Read and Append/Insert). Extremely large data volumes (millions to billions of rows).
Query Pattern: Heavy read queries that often scan multiple columns across entire tables to calculate aggregates (for example, calculating total revenue over several years using SUM, GROUP BY, JOIN).
Processing Methods
- By default, Postgres uses row-oriented storage, which is not optimal for heavy OLAP because when it needs to sum a single column, it still reads all other columns in that row into RAM.
- However, Postgres can be optimized for OLAP through the following methods:
- Using Partitioning to split tables by time, region, etc.
- Using Materialized View to calculate periodic data statistics.
Real-world examples: Plotting quarterly revenue charts, analyzing customer shopping behavior to deploy targeted advertising campaigns.

Detail

In practice, OLTP and OLAP are two critical pillars in enterprise systems. One serves daily operations, while the other supports analytical insights for strategic decision-making.
Almost every major business domain must use both systems simultaneously, separating them across departments to solve different problems.
Let us look at the Banking and Fintech domain, where the data accuracy (ACID) of OLTP is paramount and the historical data of OLAP is utilized for risk management.
OLTP for transaction systems:
- Handles QR code scans, interbank transfers and ATM withdrawals.
- Updates account balances immediately when transactions occur.
OLAP for risk management & fraud detection:
- The system runs in the background analyzing transaction histories. If it detects a transaction in City A followed by another transaction in City B just 5 minutes later, the system will flag this anomaly using OLAP to alert that the account has unusual activity.
- Compiles transaction amounts by category and timeframe.

Create the tables as follows:

CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cif_number VARCHAR(10) UNIQUE NOT NULL, 
    full_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(15) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE,
    identity_card_num VARCHAR(20) UNIQUE NOT NULL, 
    status VARCHAR(20) DEFAULT 'ACTIVE', 
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE accounts (
    account_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE RESTRICT,
    account_number VARCHAR(20) UNIQUE NOT NULL, 
    balance NUMERIC(15, 2) NOT NULL DEFAULT 0.00 CHECK (balance >= 0), 
    currency VARCHAR(3) DEFAULT 'USD',
    account_type VARCHAR(20) DEFAULT 'PAYMENT', 
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(user_id)
);

CREATE TABLE qr_merchants (
    merchant_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    merchant_name VARCHAR(150) NOT NULL,
    merchant_code VARCHAR(50) UNIQUE NOT NULL, 
    bank_account_number VARCHAR(20) NOT NULL,
    bank_code VARCHAR(20) NOT NULL, 
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE transactions (
    transaction_id UUID NOT NULL DEFAULT gen_random_uuid(),
    reference_number VARCHAR(50) NOT NULL,
    from_account_id UUID REFERENCES accounts(account_id),
    to_account_id UUID REFERENCES accounts(account_id),   
    merchant_id UUID REFERENCES qr_merchants(merchant_id), 
    amount NUMERIC(15, 2) NOT NULL CHECK (amount > 0),
    transaction_type VARCHAR(20) NOT NULL, 
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING', 
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (transaction_id, created_at),
    CONSTRAINT uq_ref_num_partition UNIQUE (reference_number, created_at)
) PARTITION BY RANGE (created_at);

CREATE INDEX idx_accounts_number ON accounts(account_number);
CREATE INDEX idx_transactions_from_acc ON transactions(from_account_id);
CREATE INDEX idx_transactions_to_acc ON transactions(to_account_id);
CREATE INDEX idx_transactions_created_at ON transactions(created_at DESC);

The users table holds bank customer profiles.
- user_id (UUID) is designed for the system and programming logic to link tables, optimize APIs and serve as the primary key.
- cif_number (Customer Information File) is the customer identifier, used by tellers, accountants and core business systems for quick lookups and reconciliations.
The accounts table stores account type information.
The qr_merchants table manages merchants supporting QR code scanning.
The transactions table logs specific details for each transaction.
- The reference_number is a unique transaction code generated for interbank transfers.
- Utilizing PRIMARY KEY (transaction_id, created_at) with PARTITION BY RANGE (created_at) creates table partitions over time.

Next, we create the partition tables split by quarters:

CREATE TABLE transactions_2026_q1 PARTITION OF transactions
    FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2026-04-01 00:00:00+00');

CREATE TABLE transactions_2026_q2 PARTITION OF transactions
    FOR VALUES FROM ('2026-04-01 00:00:00+00') TO ('2026-07-01 00:00:00+00');

CREATE TABLE transactions_2026_q3 PARTITION OF transactions
    FOR VALUES FROM ('2026-07-01 00:00:00+00') TO ('2026-10-01 00:00:00+00');

CREATE TABLE transactions_2026_q4 PARTITION OF transactions
    FOR VALUES FROM ('2026-10-01 00:00:00+00') TO ('2027-01-01 00:00:00+00');

CREATE TABLE transactions_default PARTITION OF transactions DEFAULT;

OLTP

BEGIN;
SELECT balance FROM accounts WHERE account_id = '68d3f4e7-5ccf-4c44-8e31-03cc9285e063' FOR UPDATE;

UPDATE accounts SET balance = balance - 500000 WHERE account_id = '68d3f4e7-5ccf-4c44-8e31-03cc9285e063';
UPDATE accounts SET balance = balance + 500000 WHERE account_id = 'ef7e9581-2387-492c-9955-2a064ba3051e';

INSERT INTO transactions (from_account_id, to_account_id, amount, transaction_type, status, reference_number, merchant_id, description)
VALUES ('68d3f4e7-5ccf-4c44-8e31-03cc9285e063', 'ef7e9581-2387-492c-9955-2a064ba3051e', 500000, 'QR_PAY', 'SUCCESS', 'FT2026' || upper(substring(md5(random()::text) from 1 for 8)), '5bec8b62-72fd-4338-970a-590f8f522fed', 'Scan QR');

COMMIT;

This section processes a single fund transfer transaction.
You can see that we use FOR UPDATE (row-level lock) to prevent other transactions from reading or modifying this row during our processing.
Using a different lock type like FOR SHARE can lead to deadlocks when two transactions depend on one another.
The reference_number is a static string throughout the transaction, generated randomly using these functions:

random()::text: Generates a random decimal number and casts it to text.
md5: Encodes the text into a 32-character MD5 hash containing characters from 0-9 and a-f. This cleans up decimal points and ensures high randomness.
substring(... from 1 for 8): Extracts the first 8 characters of the MD5 hash.
upper: Converts the extracted characters to uppercase.
'FT2026' || ...: To represent a Financial Transaction, the || operator concatenates strings, prepending FT2026 to the 8 uppercase characters.

If you use FOR UPDATE, transactions will run sequentially, blocking subsequent transactions, but they will ultimately complete successfully.

Using FOR SHARE does not block READ operations, but it will lead to a Deadlock when calculating the balance because the transactions hold locks on each other.

OLAP

Next, we create a MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW mv_fraud_and_risk_analytics AS
SELECT 
    t.transaction_id,
    t.reference_number,
    t.amount,
    t.transaction_type,
    t.status,
    t.created_at AS transaction_time,
    EXTRACT(YEAR FROM t.created_at) AS trans_year,
    EXTRACT(QUARTER FROM t.created_at) AS trans_quarter,
    EXTRACT(MONTH FROM t.created_at) AS trans_month,
    EXTRACT(DAY FROM t.created_at) AS trans_day,
    fa.account_number AS source_account,
    fu.full_name,          
    fu.cif_number,          
    ta.account_number AS destination_account,
    tu.full_name AS receiver_name,
    m.merchant_name,
    m.merchant_code
FROM 
    transactions t
LEFT JOIN accounts fa ON t.from_account_id = fa.account_id
LEFT JOIN users fu ON fa.user_id = fu.user_id
LEFT JOIN accounts ta ON t.to_account_id = ta.account_id
LEFT JOIN users tu ON ta.user_id = tu.user_id
LEFT JOIN qr_merchants m ON t.merchant_id = m.merchant_id
WHERE t.status = 'SUCCESS';

CREATE UNIQUE INDEX idx_mv_fraud_unique_id ON mv_fraud_and_risk_analytics (transaction_id);
CREATE INDEX idx_mv_fraud_time_type ON mv_fraud_and_risk_analytics (trans_year, transaction_type, trans_month);
CREATE INDEX idx_mv_fraud_time_real ON mv_fraud_and_risk_analytics (transaction_time);
CREATE INDEX idx_mv_fraud_merchant ON mv_fraud_and_risk_analytics (merchant_code);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_fraud_and_risk_analytics;

To run REFRESH MATERIALIZED VIEW CONCURRENTLY, you must create a unique index like idx_mv_fraud_unique_id.
This allows you to run the refresh command daily to update data and perform analytics without impacting active transactional operations.

Analytical use cases for statistics and risk prevention are as follows:

-- Query 1
SELECT 
    trans_month,
    COUNT(transaction_id) AS total_transactions,
    SUM(amount) AS total_volume
FROM 
    mv_fraud_and_risk_analytics
WHERE 
    trans_year = 2026 
    AND transaction_type = 'QR_PAY'
GROUP BY 
    trans_month
ORDER BY 
    trans_month;

-- Query 2
SELECT 
    cif_number,
    full_name,
    COUNT(transaction_id) AS total_transactions_in_hour,
    SUM(amount) AS total_amount_transferred
FROM 
    mv_fraud_and_risk_analytics
WHERE 
    transaction_time > NOW() - INTERVAL '1 hour'
    AND transaction_type IN ('TRANSFER', 'QR_PAY')
GROUP BY 
    cif_number, full_name
HAVING 
    COUNT(transaction_id) >= 50
ORDER BY total_transactions_in_hour DESC;

-- Query 3
SELECT 
    cif_number,
    full_name,
    SUM(amount) AS total_money_to_blacklist_merchants
FROM 
    mv_fraud_and_risk_analytics
WHERE 
    merchant_code IN ('MERCHANT_GAMBLING_01', 'MERCHANT_FRAUD_SCAM_02')
GROUP BY 
    cif_number, full_name;

Query 1: Analyzes total transaction count and volume by month for the year.
Query 2: Identifies users with 50 or more transactions within a single hour for analysis.
- Useful for detecting Smurfing behavior.
- Bad actors often avoid transferring large sums at once to evade detection, opting to break the funds down and execute numerous small transactions in a short window.
Query 3: Analyzes transactions made by customers to blacklisted merchant accounts, such as fraud or gambling, recorded by the bank.
- Essential for credit risk assessment and flagging anomalous customers.
- The risk analysis system checks history to update credit scoring.