Generally, databases are used to store data. It can be used for other applications or projects. A database management system facilitates the development and execution of database platforms. Users of the system are provided with the facility to perform different kinds of operations, such as create, delete and modify data. Database Management Systems(DBMSs) are classified according to their data structures. There are several types of databases such as hierarchical databases, relational databases, non-relational databases, object oriented databases, network databases, centralized databases, NoSQL databases, end-user databases and so on.
Perhaps, The most widely used relational database management systems. The relational databases are operated and managed by the Relational Database Management Systems (RDBMSs). RDBMSs are used to store data in the form of tables using Structured Query Language(SQL). RDBMS offers a dependable technique for storing and retrieving massive amounts of data while maintaining system performance and ease of deployment. RDBMS can handle plenty of users at a time. Also, It is capable of managing small to huge amounts of data. RDBMS uses a table structure with headers that serve as column names and rows that contain relevant values. RDBMS build data structures using atomicity, consistency, isolation, and durability which ensure trust.The Relational Database Management Systems are becoming more popular in the professional world because of their two hot approaches, Row oriented databases and column oriented databases.
Row-oriented Databases:
Row-oriented databases maintain all the information related to a record next to each other in memory and organize data by record. This is the traditional method of organizing data and still provides significant benefits for fast storage data. They are specially designed to effectively read and write data from rows. In a row-oriented database, data is stored row by row. It means the data from the first column of the row will be set right after the data from the last column of row before it. It will be more understandable with the below metaphor :
| Vikram, Male, India, 27 | Joey, Male, USA, 33 | Rose, Female, Canada, 45 |
Here, the “|” denotes the block’s end, indicating that all of the data contained in the block has been stored together.
This traditional disk storage method is adequate for transactional queries. Hence, Row databases are generally used for Online Transactional Processing (OLTP), Where a single transaction like inserting, removing, or updating can be quickly performed with a minimal amount of data.When a huge amount of data in a row needs to be fetched, row stores like MySQL and Postgres are great way to implement it. In some cases data engineers use indexing methods to prevent full table scans.
However, A row-oriented database requires additional space. Row oriented databases may retrieve a row or a group of rows quickly, but when we execute aggregation, the database loads unwanted data into memory, which takes longer than just picking the columns we want to aggregate on. The row-oriented database may also require access to more disks. It means memory with additional data.
In a nutshell, assume that a certain disk can only store three columns worth of data. The below written schemas, take it as an example with column names, Name:City:Age.
Disk 1: | Manish | India | 27 |
Disk 2: | Nadeem |Turkey | 38 |
Disk 3: | Mike | USA | 45 |
To get the sum of all people’s ages, the computer would have to run this query across all three disks and all three columns on each disk in order to find the total of ages. Therefore, It is clear that adding data to a row-oriented database is fast and easy but getting specific data out of it requires more time and memory.
Column-oriented Databases:
In the column-oriented databases, data is arranged in columns. Column-oriented database is also known as a columnar database or c-store database. In this data organization is done by field. It keeps all the data along with a field next to each other in memory. The use of columnar databases has increased because they improve the performance of data queries. They are optimized for reading and processing columns efficiently. This method is frequently applied in data warehousing , analytics systems and also optimized for extracting particular columns of data. It will be more understandable with below metaphor :
| Vikram Joey Rose | Male Male Female | India USA Canada | 27 33 45 |
Here, the “|” denotes the block’s end, indicating that all of the data contained in the block has been stored together. The data is sorted by its column.
In a nutshell, Here again assume that a certain disk can only store three columns worth of data. Again take it as an example the below written schemas with column names, Name:City:Age.
Disk 1: | Manish | Nadeem | Mike |
Disk 2: | India | Turkey | USA |
Disk 3: | 27 | 33 | 45 |
Here, The computer simply needs to access one disk (Disk 3) and add up all the values on it to obtain the sum of ages. It uses a small number of disks and requires minimum memory. The number of disks that need to be visited and the quantity of additional data that can be stored in memory are both reduced when data is organized per column. Hence, this greatly improves the overall speed of computation.
However, data modification is slower in a column oriented database, but there are a number of pros, such as, It is more suitable for online analytical processing applications(OLAP applications). A column oriented database allows you to process large amounts of data more quickly. Also, data is only accessed if required to compute the query result. Most modern analytical and reporting databases including amazon redshift, SAP HANA, Oracle, Microsoft and vector databases use columnar storage. One of the great advantages of using a column database is faster data aggregation ,Since each block in a column oriented database contains all the values for a certain data field. In this database, every column has a unique data-type, so there can be high rates of compression. Also better communication means data will require less disk space.
By using a column-oriented database any organization can store, aggregate and operate on many data sets without investing in additional storage options. One of the most popular and effective methods for columnar databases is ELT. Data is gathered from resources and loaded into storage using ELT. Data engineers then apply transformations to obtain useful data. ELT makes data access quicker ,enables automated data pipelines and it is less expensive too. Nowadays ELT pipelines are the ideal option for modern companies that depend on quick data collection from multiple resources.
Row and column based databases have their own unique purposes. But the demands of modern companies want quickness because slower analysis can be caused by ineffective storage, which may restrict growth, innovation and new ideas.