What is OLTP (Online Transaction Processing)?

How it works

An OLTP query usually touches one or a handful of rows: insert this order, read this customer, decrease this stock level. Thousands of users may do that at once, and each change must be correct. That access pattern drives the design:

• Row-oriented storage, so a whole record is read or written together.
• B-tree indexes on primary and foreign keys for fast point lookups.
• Normalised schemas (third normal form), so each fact is stored once and an update cannot leave copies disagreeing.
• Strong transactions: ACID guarantees, with row-level locking or multi-version concurrency control (MVCC) so readers and writers do not block each other.

PostgreSQL, MySQL, SQL Server and Oracle are typical OLTP databases. Latency targets are milliseconds.

These choices make OLTP systems poor at analytics. A query such as “revenue by region by month for three years” scans millions of rows across many joined tables, competes with production traffic for resources, and can slow the application down. That is why data engineers copy OLTP data, in batch or with change data capture, into an OLAP system such as a warehouse or lakehouse, and model it there for analysis. Some products (HTAP databases, Snowflake hybrid tables) try to serve both, but the classic split is still the one to explain first.

Example

A transfer is one transaction: either both rows change or neither does. The second transfer violates a constraint, so its first update is rolled back too.

CREATE TABLE accounts (id int PRIMARY KEY, balance numeric CHECK (balance >= 0));
INSERT INTO accounts VALUES (1, 100), (2, 50);

BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
COMMIT;

DO $$
BEGIN
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;   -- fails the CHECK
EXCEPTION WHEN check_violation THEN
  RAISE NOTICE 'transfer rolled back';
END $$;

SELECT * FROM accounts ORDER BY id;

NOTICE:  transfer rolled back
 id | balance
----+---------
  1 |      70
  2 |      80

Common mistakes

• Running heavy analytical queries on the production OLTP database instead of a replica or warehouse.
• Copying a normalised OLTP schema into the warehouse unchanged and expecting analysts to rebuild every join.
• Adding indexes to fix warehouse-style scans, which slows down every write.
• Assuming OLTP rows have reliable updated_at timestamps for incremental loads without checking how they are maintained.