Tags:conceptdatabaseworkloadsstorage Status:🟩

---

Database Workloads

Summary

Database workloads define the type of operations a database handles and impact how data is stored, processed, and accessed. There are three main types:

1. OLTP (On-Line Transaction Processing) – Handles small, real-time transactions and simple queries focused on specific entities.
2. OLAP (On-Line Analytical Processing) – Processes large-scale data for complex queries and aggregate analysis.
3. HTAP (Hybrid Transactional/Analytical Processing) – Combines OLTP and OLAP for systems that support both workloads.

Database Workloads

Database Workloads refers to the type of operations a database system is designed to handle. They determine how data is stored, processed and accessed. There are mainly 3 types of database workloads designed to handle different kind of operations; OLTP, OLAP and HTAP. Most primary workloads are OLTP and OLAP.

In the real world companies have a lot of different databases, so they use both OLAP and OLTP.

OLTP

On-Line Transaction Processing. OLTP is about handling small amount of data. It is good at simple queries that only read/update a small amount of data that is related to a single entity in the database.

INSERT INTO revisions VALUE (?,?...,?)
 
UPDATE useracct SET lastLogin = NOW(),
hostname = ? WHERE userID = ?
 
SELECT P.*, R.* FROM pages as P
INNER JOIN revisions as R ON P.latest = R.revID
WHERE P.pageID = ?

The last one is still OLTP because it retrieves specific, real-time data based on user input, focusing on fast and simple transactions like fetching a page’s details.

OLAP

On-Line Analytical Processing It is good at complex queries that read large portions of the database spanning multiple entities to compute aggregates. Some SQL Queries are automatic generated and can be multiple MB in size because they can be very complex.

SELECT COUNT(U.lastLogin), EXTRACT(month FROM U.lastLogin) AS month
FROM useracct AS U WHERE U.hostname LIKE '%.gov'
GROUP BY EXTRACT(month FROM U.lastLogin)

This query goes through all users in the useracct table, filters for those whose hostname ends with .gov, and then groups them by the month of their lastLogin. For each group, it counts the number of users who logged in during that month. We are going over the whole user table. Then we are doing some analysis.

HTAP

Hybrid Transaction + Analytical Processing. It is a mix of OLTP and OLAP.

Examples

• Query A is OLAP because it queries all sales over a whole year, which is a lot of data. Then we are analysis all the data.
• Query B is a OLTP because we are updating a specific item and updating it. (update/write query).

Aspect	OLTP (Online Transaction Processing)	OLAP (Online Analytical Processing)
Purpose	Handle day-to-day transactions (real-time)	Analyze historical data (batch processing)
Data Size	Relatively small	Extremely large
Data Type	Current, operational data	Historical, aggregated data
Operations	Simple read/write operations (insert/update)	Complex queries (e.g., joins, aggregations)
Access Pattern	Random access, small subsets of data	Sequential scans, full dataset reads
Examples	Banking, e-commerce, CRM systems	Data mining, trend analysis, business intelligence