Table Partitions and Storage Groups

Table partitioning is the mechanism by which a version of an SQL table row is stored only on a subset of Storage Managers (SMs) running in a NuoDB domain.

Table Partitions

A table partition is an attribute that can be associated with an SQL table. The partition contains a subset of the row versions that are stored on and serviced by a storage group. A partitioning policy governs the subset stored in the partition. A partitioned table must contain at least one table partition. Table partitions are associated with SQL tables by specifying partition criteria. Each partition within a table has a uniquely identified name that maps to one storage group.

NuoDB uses two levels of mapping between a version of a row in a horizontally partitioned table and physical storage. The first mapping level uses table partitioning which provides the ability to map a table partition, which contains a version of a row, to exactly one symbolic storage group name. The second mapping level assigns a storage group, a logical storage unit, to one or more SMs in the database.

Storage Groups

A storage group represents a logical storage unit that can be serviced by multiple SMs in a database.

A storage group is referenced by a symbolic identifier that uniquely identifies a storage group within the database. These symbolic identifiers allow table partitioning policies to reference a symbolic name instead of a static storage location.

The storage group to SM assignments provide the flexibility to dynamically change the mapping to accommodate database operational requirements without a service interruption. The relationship between storage groups and SMs is a many-to-many mapping meaning that a storage group can be serviced by one or more SMs.

In addition, an SM may service one or more storage groups.

For information on adding storage groups, see Managing Storage Groups.

Storage Groups in a Database.
Figure 1. Storage groups in a database

Partitioning an Unpartitioned Table

An unpartitioned table can be partitioned under the following circumstances:

  • Only range-style partitioning is supported.

  • All unique indexes must contain the partitioning column or an autogenerated column.
    The resulting partitioned table will have a first partition that contains all the data from the original table, including existing indexes.

Run ALTER TABLE to partition a table. The syntax for ALTER TABLE is the following:

ALTER TABLE [table] MODIFY PARTITION BY RANGE (column_name)
    [STORE IN default_storage_group]
    PARTITION partition_name VALUES LESS THAN (literal | MAXVALUE)
    [STORE IN storage_group]

Predefined Storage Groups

The following are the two predefined storage groups:

  • UNPARTITIONED (alias DEFAULT)
    The UNPARTITIONED storage group is automatically created with the database and cannot be deleted. Every SM always services the UNPARTITIONED storage group and this cannot be changed. Any table or a row in a table that does not have a partition defined, is stored in the UNPARTITIONED storage group. Thus, if you do not partition your tables, all the data in the database is stored in the UNPARTITIONED storage group. Any other storage groups that are created will have no data in them.

  • ALL
    If an SM serves the ALL storage group, then it automatically and unconditionally serves all storage groups. You cannot store a partition or table in the ALL storage group.

Table partitions can be used to store the inserted data in one or more storage groups in the database based on the value of a column in that row.

For example, a simple table partitioning use case would be a table containing user data that is partitioned based on the user’s zip code column. The inserted data is stored in table partitions that map to storage group names that represent the geographical storage location near the zip code of the row being inserted. Then the storage groups are fulfilled by assigning SMs that are located in those geographical locations. For example, the USWEST storage group is serviced by an SM located in San Diego, CA and the USEAST storage group is serviced by an SM located in Cambridge, MA.