Use Cases of Table Partitions and Storage Groups

When table partitioning is applied, TEs can eliminate processing entire partitions that do not satisfy a filter condition using queries that include filter on a partitioning column. This elimination is called partition pruning. Partition pruning results in a faster query execution. When table partitioning is used, the database can process multiple partitions concurrently. This is called parallel processing. Parallel processing speeds up query execution.

Table partitioning improves query performance due to partition pruning and parallel processing. 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 3 ≤ col1 < 7 is stored in p2, and data where 7 ≤ col1 < 10 is stored in p3. In this case, the query SELECT * FROM t1 WHERE col1 <= 2 is optimized to access only partition p1.

Data aging is the process of managing the data from creation to eventual deletion or archival within a database or between databases. This process improves the response time of queries to recent data and conserves 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.

Partitioning data into multiple storage groups enables the storage of partitions on different SMs. This increases 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.

An SM’s aggregate storage and memory capacity is increased when the SMs serve disjoint parts of the database. This improves the performance of the database.