Configuring Partitioning

written by vdbdev on August 25, 2013 with no comments

VoltDB distributes data and processing across partitions. Other systems use the terms “shards” or “regions”. There are two steps to configuring partitioning in VoltDB.

  1. Tell the database how to distribute data.
  2. Tell the database how to route transactions to the data.

Distributing Data: Partitioned Tables

Partitioning a table is easy. Use the PARTION TABLE DDL statement to designate the table attribute (column) that is the partitioning attribute. Each row of the table will be automatically distributed by hashing the partitioning attribute’s value to a partition. For example, if your DDL contains a table of towns, you can partition (shard) the the table by state:

CREATE TABLE towns (
   town VARCHAR(64),
   state VARCHAR(2),
   state_num TINYINT NOT NULL,
   county VARCHAR(64),
   county_num SMALLINT NOT NUL,
   elevation INTEGER
);

PARTITION TABLE towns ON COLUMN state_num;

Routing Transactions: Single Partition Procedures

Like declaring the partitioning for tables, you declare the partitioning for procedures in DDL. For example, if you create this procedure in the DDL:

CREATE PROCEDURE leastpopulated AS 
    SELECT TOP 1 county, abbreviation, population
    FROM people, states WHERE people.state_num=?
    AND people.state_num=states.state_num
    ORDER BY population ASC; 

You would partition it on the state_num as follows:

PARTITION PROCEDURE leastpopulated ON TABLE people COLUMN state_num;

Java stored procedures declare their partitioning using a Java annotation. For example, the examples/voter application’s single partition Vote.java stored procedure declares is partitioning with the following annotation.

package voter.procedures;

import org.voltdb.ProcInfo;
import org.voltdb.SQLStmt;
import org.voltdb.VoltProcedure;
import org.voltdb.VoltTable;

@ProcInfo (
    partitionInfo = "votes.phone_number:0",
    singlePartition = true
)
public class Vote extends VoltProcedure {
    ....
}

More Information

Partitioning is a key concept in VoltDB and there is extensive documentation on the topic. Continue by reading the Using VoltDB application design chapter. Or, work through the tuturial for a hands-on explanation.