Deciding on a table partition strategy in Greenplum

The gadget spec URL could not be found
Note:Not all tables are good candidates for partitioning. If the answer is yes to all or most of the following questions, table partitioning is a viable database design strategy for improving query performance. If the answer is no to most of the following questions, table partitioning is not the right solution for that table. Test your design strategy to 
ensure that query performance improves as expected.
The gadget spec URL could not be found

1. Is the table large enough? Large fact tables are good candidates for table partitioning. If you have millions or billions of records in a table, you will see 
performance benefits from logically breaking that data up into smaller chunks. For smaller tables with only a few thousand rows or less, the administrative overhead of maintaining the partitions will outweigh any performance benefits you might see.

2 Are you experiencing unsatisfactory performance? As with any performance tuning initiative, a table should be partitioned only if queries against that table are 
producing slower response times than desired. 

3 Do your query predicates have identifiable access patterns? Examine the WHERE clauses of your query workload and look for table columns that are 
consistently used to access data. For example, if most of your queries tend to look up records by date, then a monthly or weekly date-partitioning design might be beneficial. Or if you tend to access records by region, consider a list-partitioning design to divide the table by region.
The gadget spec URL could not be found
4. Does your data warehouse maintain a window of historical data? Another 
consideration for partition design is your organization’s business requirements for 
maintaining historical data. For example, your data warehouse may require that 
you keep data for the past twelve months. If the data is partitioned by month, you 
can easily drop the oldest monthly partition from the warehouse and load current 
data into the most recent monthly partition.
The gadget spec URL could not be found
Can the data be divided into somewhat equal parts based on some defining 
criteria? Choose partitioning criteria that will divide your data as evenly as 
possible. If the partitions contain a relatively equal number of records, query 
performance improves based on the number of partitions created. For example, by 
dividing a large table into 10 partitions, a query will execute 10 times faster than it 
would against the unpartitioned table, provided that the partitions are designed to 
support the query’s criteria.

The gadget spec URL could not be found

Comments