How Table Partitioning Works

You specify the partitioning strategy of a table in the PARTITION BY clause of the CREATE TABLE statement. The available strategies include hash-partitioning on each row's primary key value, hash-partitioning on column values other than the primary key, range-partitioning, and list-partitioning.

SQLFire maps each row of a partitioned table to a logical "bucket." The mapping of rows to buckets is based on the partitioning strategy that you specify. For example, with hash-partitioning on the primary key, SQLFire determines the logical bucket by hashing the primary key of the table. Each bucket is assigned to one or more members, depending on the number of copies that you configure for the table. Configuring a partitioned table with one or more redundant copies of data ensures that partitioned data remains available even if a member is lost.

When members are lost or removed, the buckets are reassigned to new members based on load. Losing a member in the cluster never results in re-assigning rows to buckets. You can specify the total number of buckets to use with the BUCKETS clause of the CREATE TABLE statement. The default number of buckets is 113.

In SQLFire, all peer servers in a distributed system know which peers host which buckets, so they can efficiently access a row with at most one network hop to the member that hosts the data. Reads or writes to a partitioned table are transparently routed to the server that hosts the row that is the target of the operation. Each peer maintains persistent communication channels to every peer in the cluster.

Figure 1. Partitioned Table Data

Although each bucket is assigned to one or more specific servers, you can use a procedure to relocate buckets in a running system, in order to improve the utilization of resources across the cluster. See Rebalancing Partitioned Data on SQLFire Members.