Traditional databases store data in a row-oriented approach that is optimized for transactional, single-entity data lookup. But if you need to aggregate data by a specific column, the system has to read all columns from disk, which slows down query performance and increase resource usage.
To solve the issue, columnar databases was introduced.
Columnar database is a type of a database that stores data in columns together on the disk.
Imagine the following sample:
|Account|LastName|FirstName|Purchase,$|
| 0122 | Jones | Jason | 325.5 |
| 0123 | Diamond| Richard | 500 |
| 0124 | Tailor | Alice | 125 |
In row-database it will be stored as following:
0122, Jones, Jason, 325.5;
0123, Diamond, Richard, 500;
0124, Tailor, Alice, 125;
In column-database:
0122, 0123, 0124;
Jones, Diamond, Tailor;
Jason, Richard, Alice;
325.5, 500, 125;
Benefits of the approach:
📍High data compression due to the similarity of data within a column
📍Enhanced querying and aggregation performance for in analytical and reporting tasks
📍Reduced I/O load as there is no need to process irrelevant data
The most popular columnar databases:
1. Amazon Redshift
2. Google Cloud BigTable
3. Microsoft Azure Cosmos DB
4. Apache Druid
5. Vertica
6. ClickHouse
7. Snowflake Data Cloud
Columnar databases are well-suited for building data warehouse, real-time analytics, statistics, storing and aggregating time-series data.
#engineering