Horizontal Database Partitioning

As defined by wikipedia (https://en.wikipedia.org/wiki/Partition_(database)), "A partition is a division of a logical database or its constituent elements into distinct independent parts. Database partitioning is normally done for manageability, performance or availability reasons, as for load balancing." . Horizontal partitioning, also called sharding, is a method of implementing a distributed database system. The concept of database sharding has been gaining popularity over the past several years due to the enormous growth in transaction volume and size of business application databases for services such as:

Sharding can be simply defined as “shared-nothing” horizontal partitioning of a database into a number of smaller database instances that collectively comprise one logical database. What drives the need for sharding is the fact that as the size and transaction volume of the database incurs linear growth, response times tend to grow logarithmically. Distributed queries allow far faster processing due to performing parallel execution on each shard. The following diagram illustrates how xSQL and a Distributed SQL client application might implement sharding.



It is well known that database performance drops in concert with an increase in database size. This is due in large part to the increasing size (depth) of index structures such as b-trees. When a database exists on a spinning disk, then mechanical artifacts exacerbate the problem (greater “rotational latency” waiting for the platter to spin or the disk head to slew to the proper track as the database occupies more of the disk). Partitioning a logical database into, e.g., five physical databases means that each physical database is only 20% the size of the same logical database if it existed as a single physical database. Consequently, index structures are more shallow and the effects of spinning disk geometry (if any) are mitigated. Furthermore, instead of a single database server providing query execution for a single physical database, we can allow for (e.g., again) five server processes to provide the query execution. This is called distributed query processing, and is transparent to a database client application. The client application merely opens the logical database and the database configuration (described in a JSON document) determines the physical makeup of the database (i.e. whether logical and physical mean the same thing, or whether the logical database consists of 2, 5 or 100 physical partitions, etc). Once connected, the client application begins/commits/aborts its transactions and queries in the normal way. If the database is physically partitioned, the eXtremeSQL query engine takes care of distributing the query on behalf of the client application and gathering (appending) the result sets from each partition to present a single (logical) view to the client application.

Sharding with eXtremeDB High Availability

Sharding and eXtremeDB High Availability can be combined. In this configuration, a single logical database is horizontally partitioned into two or more physical database instances. Each shard (physical database instance) then has a master and replica server process, and the replica can be promoted to master in the event of an unexpected termination of the original master. In this way, we can preserve the availability of the single logical database that would otherwise not be possible if any of the servers processing the shards were to fail. (Please refer to the eXtremeSQL User's Guide for further details.)

The following diagram illustrates how two database shards could use eXtremeDB High Availability while being accessed by a client application using a Distributed SQL engine.