Compare OLAP and OLTP on AWS

When we discuss Database, in addition to whether the DB type is SQL or NoSQL, you also need to see if the DB type is OLAP or OLTP. These can be seen as two design features that greatly affect the following use. this and related application. On AWS there are more than 15 different types of DBs that support a wide variety of applications where Redshift and Aurora stand out offering a lot of powerful capabilities in terms of scaling and performance, but they have huge differences. Now, let’s take a look at OLAP and OLTP or Redshift vs Aurora in this post.

What is OLTP?

OLTP stands for Online Transaction Processing and is designed to handle persistent state store storage behind a front-end application. OLTP is therefore suitable for transactional procedures such as GET, INSERT, UPDATE or DELETE.

Some common tasks when thinking of OLTP DB are as follows:

  • What is the username of this email_user? (GET)
  • When was the last login time of user_1? (GET)
  • Update billing of user hours (UPDATE)

OLTP’s strength is optimized for writing (WRITE). OLTP workloads can be used for both READ and WRITE applications, but READ tends to be looking for specific metrics (specific value like knowing the user_id in advance and then finding information about this user_id) rather than scanning all values ​​and executing them. does the calculation (scanning all users who log into the system from 8am to 9am and calculates the total number of minutes late…). This is the core point that software architects or solution architects need to clarify to choose the right type of Database on AWS.

Some types of DBs designed in OLTP form in AWS:

  • MySQL
  • PostgreSQL
  • Amazon Aurora
  • Oracle

What is OLAP?

OLAP stands for Online Analytic Processing is a type of DB optimally designed for analyzing large data sets (large datasets) quickly to answer questions about data.

Some common use cases for OLAP:

  • How do I calculate my company’s Customer Lifetime Value (CLV)?
  • What is my average gaming time?
  • What is the conversion rate of recommendation-based landing pages?

OLAP database is designed in the opposite direction of OLTP when focusing on scanning and calculating capacity on many rows (rows) for one or more columns of data (volume). To optimize performance, OLAP organizes data in columnar format.

What is columnar?

Instead of the usual data we organize in tabular form and each row is a data record, OLAP organizes in the opposite way that the data is organized in the form of columns. Columnar-optimized storage types allow for optimal compression performance and execution of sequential read requests, which allows large numbers of datasets to be read.

OLAP is optimally designed for reading or scanning large datasets, so changes related to requests such as Update or Write will be done through batch periodically. Batch running time can vary depending on the business, usually hourly or daily. OLAP will shine in applications such as data analytics, big data, etc. with aggregation requirements.

Amazon Redshift is a well known OLAP DB on AWS. There are also many other types of OLAP such as Terradata, HP Vertical, IBM Netezza, KDB+

Can OLAP be used instead of OLAP?

In terms of capabilities, database management systems in general have basic features but are designed in different ways to optimize for different workloads. OLAP systems are often very expensive, so if you are simply a programmer it will be easy to use OLTP for OLAP requests to still run.

Example: Using Aurora for big data analysis.

Actually this will not be optimal in terms of architecture and as the requirements grow larger you will find that doing this is very painful. Kind of like forcing a fish to climb a tree like a monkey.

Forcing an OLTP DB to behave like an OLAP DB also creates bad performance when it is not optimally designed to read a large set of datasets. There are some workaround solutions is to have an OLAP layer on top of the OLTP database, for example, PostgreSQL can do this, but basically, if you use pure OLAP workloads, choosing a pure OLAP database like Amazon Redshift will be beneficial. on a large scale.

In short, learn more Amazon Redshift ha!

Have fun!

Leave a Reply