Cloud databases put traditional MPP systems in the area of data warehousing under increasing pressure.
Why this is so and what are the advantages and disadvantages of both types of database systems I show you in this blog post.
The Architecture Of A MPP Database System
MPP database systems are shared-nothing systems. In an MPP database system, each node has its own CPU, main memory and mass storage device.
A typical MPP database system besides Teradata is e.g. Netezza. But also Amazon Redshift or Microsoft Azure Synapse Analytics fall into this category.
The main characteristic of a MPP Systems is that data is distributed evenly across all nodes.
The basic architecture is, therefore, the same for all MPP database systems. The main difference is how the data is stored on the nodes (rows or columns) and how it is localized.
Each manufacturer has developed its own strategy.
Teradata can store data in rows as well as in columns. Meanwhile, it is also possible to find data from a Column Partitioned Table using a Primary Index.
Netezza uses its own hardware called FPGA and zone maps to define where the searched data is not located and limits the queries to the required columns.
Almost all MPP database systems offer the following 3 options for distributing the data (Netezza, Amazon Redshift, Microsoft Azure Synapse):
- Distribute All
Tables are copied completely to all nodes. This is ideal for small tables as they are already available for joining on all nodes without the need to copy data (Teradata does not offer this kind of distribution, but it copies if necessary whole tables when executing a query for join preparation)
- Distribute By Hash
Here the distribution takes place via a key (in Teradata it is the primary index).
- Distribute Randomly
The data of a table is distributed evenly but randomly across all nodes. In Teradata, this is achieved by using so-called NOPI tables.
Advantages Of A MPP Database System
Excellent performance can be achieved by distributing the load across nodes.
- Scalability and Concurrency
In principle, MPP systems can be scaled linearly by adding new nodes (CPU, memory, and mass storage). Doubling the number of nodes doubles the performance.
Disadvantages Of A MPP Database System
Most MPP database systems come with their own hardware that has been specially optimized to achieve the best performance.
These include the BYNET in Teradata which performs special tasks (sorting and merging of answer sets) or the special hardware from Netezza to restrict the data that is read.
This often makes the system complex and expensive.
- Distribution of Data
The big advantage of MPP database systems is also their biggest disadvantage: distribution of data evenly across nodes. The even distribution of the data is essential but the task of choosing a good distribution key is up to the user.
Modern cloud databases like Snowflake do not have this problem, because they are shared data systems and all nodes can access the common database.
If the system is scaled up or down, this is connected with downtime in which the data must be distributed evenly (to the old and the new nodes).
- Lack of elasticity
MPP database systems are not as ideal as cloud databases due to their lack of elasticity.
MPP database systems can scale, but this takes up to weeks as hardware has to be added or at least restructuring of data is needed. Snowflake, for example, can scale in real-time without any downtime. Snowflake is a real cloud database.
Many manufacturers now offer their database in the cloud, but essential features are missing. I don't consider them cloud databases but it's a matter of definition.
Teradata is also available in the cloud. But what remains of Teradata if there is no BYNET anymore? What would Netezza be without dedicated hardware? Running your database on the computer of somebody else is not sufficient in my point of view.