Table of contents
What's OLTP?
OLTP stands for Online Transaction Processing, in which a database is used to execute huge amounts of transactions. Transaction means to change data, either by inserting, updating, or deleting small amounts of data. It's usually used at the first stages of any application, it manipulates some data triggering some actions like selling products, banking, and messaging.
As it's the DB used for operations, it should be:-
- ACID (Atomic, Consistent, Isolated, Durable) to support transactions correctness.
- Rapid, to be able to fulfill huge amounts of transactions concurrently without delays
- Highly available, because any failure in the system has a direct impact on the business operations, along with backups at short intervals to reduce data loss.
What's OLAP?
OLAP stands for Online Analytical Processing, in which a database warehouse is used to get information using complex queries that support decision making. It's usually used by data scientists and business analysts.
OLTP Vs OLAP
Execution time: OLTP requires the system to execute small transactions but rapidly, on the other hand, OLAP is less time-sensitive as it asks for complex queries.
Reads/Writes: OLAP workload is read intensive, while OLTP has a good balance of reads/writes.
Space: OLAP requires huge space to be able to perform the complex queries that support decision making, on the other hand, OLTP only needs the data required to perform transactions.
Backups: OLTP requires very short backup intervals to reduce any data loss, on the other side, OLAP can be backed up less frequently.
Which to use?
Assume you are building Amazon, then to support a customer-facing portal, you need to have an OLTP system that supports huge transaction numbers, to be able to fulfill adding products, orders, invoices, and much more. If you decided that you want to have analysis of products and what are the factors that affect sales, you might need to have an OLAP to support those types of queries. So, Depending on your business requirements, you may need to use both of them!
OLTP and OLAP can be implemented on the same DB, in the end, it's a conceptual model, however, doing so is not recommended as it comes with huge overhead, and you compromise some of the characteristics of each model to be able to use the same db, and single responsibility always wins!