OLTP

Online Transaction Processing (OLTP) systems handle high volumes of short-lived, routine transactions — INSERTs, UPDATEs, DELETEs, and simple SELECTs. They are the operational backbone of applications: processing orders, updating inventory, recording logins. OLTP is the counterpart to OLAP (analytical processing).

Characteristics

• Normalized schema (3NF) to minimize redundancy and ensure update consistency
• ACID compliant — every transaction is atomic, consistent, isolated, durable
• Many concurrent users — thousands of simultaneous read/write transactions
• Simple queries — Typically touch a few rows via Primary Key or indexed lookups
• Low latency — Responses in milliseconds
• Current data — Reflects the latest state of the business

OLTP vs OLAP

Aspect	OLTP	OLAP
Purpose	Operations (process orders, log events)	Analysis (trends, reports, dashboards)
Queries	Simple, predefined	Complex, ad-hoc aggregations
Schema	Normalized (3NF)	Star Schema / Snowflake Schema
Data	Current, operational	Historical, consolidated
Users	App users, clerks	Analysts, executives
Volume	Many small transactions	Few large scans
Response time	Milliseconds	Seconds to minutes

Typical OLTP Systems

• PostgreSQL, MySQL, Oracle, SQL Server — Traditional relational DBMS
• CockroachDB, YugabyteDB — Distributed SQL for horizontal scale
• Amazon Aurora — Cloud-native relational

OLTP to OLAP Pipeline

OLTP databases feed analytical systems through ETL/ELT:

graph LR
    OLTP[(OLTP Database)] -->|Extract| ETL[ETL Pipeline]
    ETL -->|Transform & Load| DW[("[[Data Warehousing|Data Warehouse]]")]
    DW --> OLAP["[[OLAP]] Queries"]
    DW --> BI[BI Dashboards]