Use Cases for Table Partitions and Storage Groups

Query Optimization

Table partitioning helps to improve SQL execution plans using the optimizer to process queries against table partitions, i.e. a technique called "partition pruning". For example, if a table is split horizontally into partitions p i by ranges on col1 such that data where col1 < 3 is stored in p1, data where col1 < 7 is stored in p2, and data where col1 < 10 is stored in p3. In this case, the query, “select * from t1 where col1 < 2” can be optimized to access only partition p1.

Aging and Archiving of Historical Data

Data aging is the process of logically grouping, and then physically moving, historical data within a database or between databases to improve the response time of queries to recent data or conserve disk space on lower-capacity, higher-performance storage media. Table partitioning and storage groups improve support for data aging within a NuoDB database by enabling logical grouping by partitioning key, and the storage of historical data in storage groups served by higher-capacity media.

I/O Multiplexing

Partitioning data into multiple storage groups enables the storage of partitions on different SMs. This can effectively increase the I/O bandwidth available when the database is I/O bound and database reads or writes are striped across multiple storage groups. For example, a write-intensive application (such as data ingest) that loads data into multiple partitions may benefit from storing those partitions in storage groups served by different SMs to take advantage of multiplexed I/O bandwidth while committing data load transactions.

Increased Storage Manager (SM) Resource Capacity

The aggregate SM storage and memory capacity can be effectively increased if SMs serve disjoint parts of the database.