OLTP

Online Transaction Processing

Pronunciation: oh-el-tee-PEE

Updated October 3, 20262 min read

In short

OLTP (online transaction processing) describes databases built for many small, fast reads and writes from everyday operations, such as placing orders.

What is OLTP?

Every time someone buys a product, transfers money or changes a password, an OLTP system records it. These workloads consist of huge numbers of short transactions, each touching only a few rows, and they must be fast, correct and safe even when thousands of users act at the same moment.

OLTP databases are usually relational, such as PostgreSQL, MySQL, SQL Server and Oracle, and they rely on ACID transactions, indexes on the columns used for lookups, and normalized schemas that store each fact once so updates stay consistent. Data is typically stored by row, since a transaction usually reads or writes a whole record.

The key measures are latency, throughput in transactions per second, and availability. Techniques such as connection pooling, replication for failover, careful indexing and partitioning keep OLTP systems responsive as they grow.

A common misconception is that the same database should also serve heavy analytics. Long reports that scan millions of rows compete with customer transactions and slow them down. That is why companies copy operational data into OLAP systems, such as data warehouses, for analysis.

Key takeaways

OLTP systems handle many small, fast transactions from daily operations.
They are usually relational databases with ACID transactions.
Schemas are normalized and data is stored by row.
Latency, throughput and availability are the key measures.
Heavy analytics belongs in a separate OLAP system.

Example

A typical OLTP transactionsql

BEGIN;

-- Touches only a few rows, found through indexes
UPDATE accounts SET balance = balance - 50 WHERE id = 17;
UPDATE accounts SET balance = balance + 50 WHERE id = 42;
INSERT INTO transfers (from_id, to_id, amount) VALUES (17, 42, 50);

COMMIT;  -- all three changes happen, or none do

Readers ask

What is the difference between OLTP and OLAP?

OLTP handles many small, real-time transactions, such as orders and payments. OLAP handles fewer but much larger analytical queries over historical data, such as sales by region over five years.

Is MySQL an OLTP database?

Yes. MySQL, PostgreSQL, SQL Server and Oracle are classic OLTP databases, designed for fast transactional reads and writes.

Can one database do both OLTP and OLAP?

Some systems, called HTAP (hybrid transactional and analytical processing), try to do both. Most organizations still keep a transactional database and copy data into a separate analytical one.

Often compared

OLTP vs OLAPOLTP systems handle many small, fast transactions such as orders and payments, while OLAP systems run large analytical queries over historical data.