In this lesson we will:
- Learn about the different types of databases;
- Compare and contrast OLTP and OLAP Databases;
- Discuss column orientated databases.
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.
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 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 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.
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.
Description of next lesson here