Parent: DevOps
What is a database system?
Database systems are programs that help you structure, manage, store, and read data.
They help you achieve the following functions:
- Connect from other systems in a safe and uniform manner
- Store and structure data in partitions, shards, schemas, tables, view, etc
- Replicate data between many systems
- Even create full-fledged functions in the form of stored procedures
The usual database systems that you come accross is for Structured data, but there are also databases for Unstructured data and even semi-structured data.
This is different from the way data is stored in Big Data Systems.
How should you select databases for OLTP vs OLAP needs?
OLAP databases are read heavy, dimensionally modelled and they are happy with hourly updates. For these workloads having a colum-store database is preferable because analytic workloads usually selects for specific columns, this includes:
- Fabric Warehouse/Lakehouse uses Delta format which is column store
- Clickhouse
- Bigquery
- AWS Redshift
OLTP databases are write&update heavy, theyre usually highly denormalized for the fastest performance. These workloads prefer row-store databases which provides faster update operations on singular rows. This includes:
- SQL Server
- PostgreSQL
- MySQL