Databases

How Relational Databases Work

CMU 15-445: Database Systems (YouTube): Great course on database internals

Processing workloads

Two main types of database workloads: OLTP (OnLine Transactional Processing) and OLAP (OnLine Analytical Processing). OLTP pattern is write-heavy, oriented around a single row per query, e.g. user logs in, edits profile, places order, etc. OLAP pattern is read-heavy, running queries across many rows to gain insights, e.g. generating business reports. Row-based database typically better for OLTP, columnar store typically better for OLAP. Systems typically architected to offload data from OLTP database to OLAP data warehouse for efficiency and so analytical queries don't interfere with production traffic.

Debugging performance

• EXPLAIN: shows the query plan
• EXPLAIN ANALYZE: EXPLAIN + time each step of query execution