Partition Tables

No PostgreSQL specific customizations were made for this feature.

When dealing with large data volumes, data is often partitioned to improve performance. Partitioning is the division of a larger table into smaller tables. It can be implemented in a data warehouse to improve query performance by reducing the number of records that queries must scan to retrieve a result set. It can also decrease the amount of time necessary to load data into data warehouse tables and perform batch processing.

For instance, instead of storing detailed sales order records for all months in a single table, a database administrator may break this table down into a set of tables partitioned by month so that any one table contains only orders for a single month. Most databases allow for partitions to be created and the optimizer automatically routes the query to the correct table.

Databases differ dramatically in the size of the data files and physical tables they can manage effectively. Partitioning support varies by database. Most database vendors today provide some support for partitioning at the database level. Regardless, the use of some partitioning strategy is essential in designing a manageable data warehouse. Like all data warehouse tuning techniques, you should periodically re-evaluate your partitioning strategy.

There are two basic types of partitioning:

• Server level
• Application level

Server-level partitioning involves dividing one physical table into logical partitions in the database environment. The database software handles this type of partitioning completely, so these partitions are effectively transparent to MicroStrategy software. Since only one physical table exists, the SQL engine must write SQL against a single table, and the database manages which logical partitions are used to resolve the query.

MicroStrategy Architect supports this partitioning functionality natively.