Lesson Overview

In this lesson we will:

  • Learn about the different types of databases;
  • Compare and contrast OLTP and OLAP Databases;
  • Discuss column orientated databases.

Database Categories

There are many different database vendors and products in the market. These include:

  • Traditional products from commercial vendors including Oracle and SQL Server
  • Open source products including MySQL and Postgres
  • Cloud native databases such as Snowflake and Redshift
  • NoSQL solutiosn such as Cassandra and MongoDB
  • Analytical databases such as Apache Druid and Apache Pinot

Choosing a database is filled with tradeoffs. Some will perform and there is currently no single product which would win for all categories and use cases.

OLTP vs OLAP

One of the main distinctions in the database world is OLTP vs OLAP, with almost all databases falling into one of the two classes.

OLTP stands for Online Transaction Processing. Broadly, these are designed for transactional systems.

OLAP stands for Online Analytical Processing.

OLTP Databases

OLTP databases are designed and optimised for transactional processing.

  • They are optimised for speed;
  • They are optimised for working with single row inserts, updates and deletes as opposed to large batch updates;
  • They are designed for multiple users using the database in parallel, incorporating transactions.

Common OLTP databases include Oracle, SQL Server, MySQL and Postgres. They are

OLAP Databases

OLAP databases are designed and optimised for offline analysis of large datasets.

  • They are optimised for speed when working with very large datasets;

Some of the implications of these decisions include:

  • They are not designed or optimised for inserting or reading single rows or small batches of data;
  • They are not designed or optimised
  • They do not necessarily have support for transactions;
  • They are designed for working with large datasets.
  • They are not optimised for writes. The vast majority of interactions with the OLAP database will be reads and queries, meaning that there is more emphasis on this.

Common OLAP databases include Snowflake, Apache Druid, Apache Pinot.

Column Orientation

One of the key enablers of OLAP databases is the way that.

OLTP databases store the data on disk as...

OLAP databases store the data in columns. This means that the lookup is more efficient.

Next Lesson

Description of next lesson here

Hands-On Training For The Modern Data Stack

Timeflow Academy is an online, hands-on platform for learning about Data Engineering and Modern Cloud-Native Database management using tools such as DBT, Snowflake, Kafka, Spark and Airflow...

Sign Up

Already A Member? Log In

Next Lesson:

Introduction To Snowflake

Prev Lesson:

What Is Data Engineering?

© 2022 Timeflow Academy.