Introduction to Table Partitions and Storage Groups

Table partitions and storage groups are supported under "unmanaged" databases. Managed database deployment is a depreciated feature from version v3.0. NuoDB databases onwards. Databases must be restarted using a capture file and the restart database command. Use the incremental restart command to restart failed or shut down nodes into a running database. Also see Restart Database.

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 storage managers in the database.

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. The subset stored in the partition is governed by a partitioning policy. A partitioned table must contain at least one table partition and may contain multiple table partitions. 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.

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 as opposed to 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, that is, a storage group can be serviced by one or more SMs. In addition, an SM may service one or more storage groups. Storage groups are created in one of two ways. You can use the add storageGroup command in nuodbmgr, for example:

add storageGroup name sg_west host pid 970

This creates a storage group named sg_west associated with the SM running on host with process ID 970. Alternatively, you can specify the database option, storage-group, when starting an SM, for example:

start process sm host database companydata 
   archive /var/opt/nuodb/production-archives/companydata initialize true options 'storage-group sgeast'

Caution: The --storage-group option is not interchangeable for `add storageGroup`. You can use `add storageGroup` to add a storage group to a running SM. That is, an SM on an archive that is already initialized. You can only use --storage-group to add storage groups to a new archive that is being initialized (when the --initialize option is set). If you attempt to add a new storage group to an archive with the --storage-group option when restarting an SM, you will get this error message:

storage group NEWNAME,cannot be added when loading an existing archive. Please 
   use the addstorageGroup command after the SM is running.

Unpartitioned Tables

An unpartitioned table is a table for which no partitioning rules have been defined.

Note: Once an unpartitioned table has been created, you cannot partition it. If you attempt to do so, you will recive an error message where TABLE is the name of the unpartitioned table you are attempting to alter to add a partitioning rule, and SCHEMA is the name of the schema in which that table is defined:

Table SCHEMA.TABLE has no partitioning information.

There is one predefined storage group named UNPARTITIONED. SMs that service the UNPARTITIONED storage group store data for all unpartitioned tables in the database. All SMs service the UNPARTITIONED storage group.

In addition, there is a special reserved storage group to facilitate mapping all storage groups to a specific SM. However, this storage group can not be used in table partition to storage group mapping. This storage group is named ALL. An SM configured with storage group ALL services all storage groups for the database. The only way to map an SM to service all storage groups is by starting the SM with the storage-group all Database option.

By default, a row in an unpartitioned NuoDB SQL table is stored in the UNPARTITIONED storage group which is serviced by all SMs in a database. Table partitions allow you to store the data being inserted 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 into 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.