OLTP (Online Transaction Processing)
Also known as: transactional workload, online transaction processing
OLTP describes workloads made of many small, short-lived transactions that read and write a few rows each — placing an order, updating a profile. It is optimised for concurrency, low latency per operation and transactional correctness, which is why OLTP stores use row-oriented storage and B-tree indexes.
Last reviewed · Part of the Architecture Glossary
In practice
The shape of an OLTP query is WHERE id = ? — a handful of rows, located by index, returned in single-digit milliseconds, thousands of times a second. Row storage suits it because the whole row is wanted and it lives in one place on disk.
Characteristics that follow from that shape:
- Indexes are the design. A missing index turns a 1 ms lookup into a sequential scan; every index added slows writes.
- Transaction size matters more than query complexity. Long transactions hold locks and pin the MVCC horizon — see snapshot isolation.
- Working set should fit in memory. OLTP performance falls off a cliff when the hot pages no longer fit in the buffer pool, and the fall is 100x, not 20%.
When it matters
The default for application databases: Postgres, MySQL, SQL Server, Oracle, and the OLTP-shaped NoSQL stores (DynamoDB, MongoDB) when access is by key.
Common mistake
Running the analytics dashboard against the OLTP primary. One GROUP BY over a year of orders scans the table, evicts the hot working set from the buffer pool, and every WHERE id = ? in the application slows down for the next ten minutes. That is what a replica, or OLAP storage, exists to prevent.
See also
- OLAP (Online Analytical Processing)OLAP describes workloads made of a small number of large queries that scan and aggregate many rows over few columns — revenue by region by month.
- Snapshot IsolationSnapshot isolation gives each transaction a consistent view of the database as of its start time, so reads never block writes and writes never block reads.
- Connection PoolA connection pool keeps a fixed set of open database connections and lends them to requests, avoiding per-request handshake cost and bounding concurrency at the database.
- ShardingSharding splits a dataset across independent database instances by a partition key, so each shard holds a disjoint subset.