Speed, Scale & Agility Delivered with Distributed Joins

Organizations are driving towards faster decisions and actions across more comprehensive ranging data sources than ever. Broader scope means multiple data sites because of business drivers alone. The distributed join is a query operator that combines two relations stored at different locations. Because the cloud-based distributed database creates many more data storage sites, the trend towards distributed joins is strong. The implication is there will be many more distributed joins in your future. This situation puts a premium on handling larger/broader scales of data and dynamic join capabilities. 

Why the Move to Distributed Databases?

We all know that distributed databases allow local users or bots to manage and access the data in the local databases while providing global data management that provides global users with a global view of the data. Because distributed databases store data across multiple computers, distributed databases may improve performance at end-user worksites by allowing transactions to be processed on many machines instead of limited to one. Increased foresight with tuned distributed databases can be used for business transactions plus analytical-driven business strategy and tactics. The drive to the cloud leveraging incremental relocation and more operations occurring at the edge with intelligent automation all feed the distributed database trend.

Advantages of Distributed Databases

Distributed databases provide some real benefits in the agile world and fall typically into these four categories:

·        Better Transparency: Users have the freedom from the operational details of the network, the replication (multiple copies of the data), or fragmentation issues in the data.

·        Increased Reliability/Availability: Because data can be distributed over many sites, one site can fail, and the data usage can continue.

·        Easier Expansion: The expansion of the system in adding more data sources, increasing data size, or adding more processors is much easier.

·        Improved Performance: A distributed DBMS can achieve interquery and intraquery parallelism by executing multiple queries at different sites by breaking a query into several subqueries that run in parallel.

Distributed Joins 

To make distributed joins scalable for high throughput workloads, it’s best to avoid data movement as much as possible. Some options for doing this are:

·        Make small and rarely updated tables that you regularly join against into reference tables, thus avoiding broadcasting these small tables around.

·        Try to choose shared key columns that are commonly joined upon regularly. This approach will promote using local joins to minimize data movement and promote parallel joins.

·        Try to restrict the number of rows in joins that cause any of the joined tables to reshuffle.

Net; Net:

Most users of SQL databases have a good understanding of the join algorithms in a single process server environment. They understand the trade-offs and uses for nested loop joins, and hash joins. Distributed join algorithms tend not to be understood and require a much different set of trade-offs to account for table data spread amongst a cluster of machines. The data movement trade-offs are key here, so designing them into the user views and the joins they imply is crucial. It was once thought that you could not cost-effectively scale distributed relational databases. Or, in other words, have a scale-out relational database. This is now possible and this type of modern database is table stakes. Modern databases are distributed-native and also combine NoSQL and SQL data access patterns, thus reducing the need for special-purpose datastores.