OLTP

Example

Copy code
-- Typical OLTP schema: normalized, small transactions
CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    balance DECIMAL(12,2)
);

-- A single, fast, ACID transaction
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

This kind of query — touch one or two rows, commit immediately, thousands of times a second from many concurrent users — is what OLTP engines are built and indexed for.

OLTP vs. OLAP

Data warehouses don't replace OLTP systems; they consume from them. A typical pipeline extracts data from OLTP source systems (the app's production database), transforms it, and loads it into an OLAP-oriented warehouse for analytics — because running heavy analytical aggregations directly against a production OLTP database risks slowing down the live application.

DuckDB angle

DuckDB is explicitly not an OLTP engine — it's a columnar, vectorized engine built for OLAP-style analytical scans and aggregations, not for high-concurrency single-row transactional writes. The standard pattern is to extract data from an OLTP system (via a connector, CDC, or periodic export) and load it into DuckDB or MotherDuck for the analytical workloads OLTP databases handle poorly.