OLTP vs OLAP comparison
OLTP vs OLAP — Two Lives of Data
operational systems create data · analytical systems aggregate copies of it
OLTP · OPERATIONAL
point queries · latest state · e.g. is it allowed?
OLAP · ANALYTICAL
scans + aggregates · history · e.g. fraud patterns
look up rows by key
READ
scan millions + aggregate
write one row at a time
WRITE
bulk load or event stream
end user, web or mobile
USER
analyst, internal reports
fixed queries in app code
QUERIES
ad-hoc, exploratory SQL
many tiny queries
VOLUME
few heavy queries
latest current state
MEANING
history of events
gigabytes to terabytes
SIZE
terabytes to petabytes
Why not analyze on the OLTP database? Four answers:
1 · SILOS
data lives in dozens
of separate systems
2 · SCHEMA
good OLTP schema is
a bad analytics schema
3 · COST
heavy scans would starve
live users
4 · ACCESS
direct access is often
banned for security
takeaway: OLTP serves the present · OLAP explains the past