title: Better practices for choosing partitioning strategies on Greenplum DB [Big Data]
body: <p>I need to know if anyone has any general guidelines (beyond trial and error), for defining a good strategy for optimal partitioning / indexing for a range of query types in Greenplum?</p>
  
  <p>Greenplum has some advice on their admin guide... But truth is, it's almost a copy paste from the postgres docs, and while some of it's advice seems obvious (IE: partition when a table is too big to fit in memory), it's just not enough to define a good strategy to achieve this.</p>
  
  <p>Usually Greenplum databases have very large tables (ranging over hundreds of GBs), and although hardware is specifically chosen for this kind of use, most of the times I've encountered trouble when it comes to really large databases (IE: once had a database with a 60 field table and over 2 thousand million rows that kept increasing it's size by 4-8 million registries a day).</p>
  
  <p>I know there are some techniques on choosing the proper partition, like selecting predictable ranges that will be separated in almost equal sizes (Like date ranges). But there's also the fact that while with any other database I try to rely on indexes, Greenplum completely discourages them by giving a bigger weight to some settings, like its random page cost so that indexes are not used at all.</p>
  
  <p>But I've read some situations where this is completely counter productive: Imagine you have three nodes each of 64GB ram, according to GP you shouldn't partition until the tables are over 192, but since indexes are not used you'll end up seq scanning up to 64gb per node! --- Although this can still be fast, if you enforce index use you can go down from over 20 seconds to just milliseconds.</p>
  
  <p>Another known case is that, when partitioning, the overhead makes the query a lot slower than it should be.</p>
  
  <p><strong>So, back to the original question:</strong><br>
  Does anyone have any good, firm advice on how to define your partitioning/indexing strategy?<br>
  With some of our ETL's the test queries from source can take up from half to a full hour, so trail and error really pushes back productivity.</p>
  
  <p>Thanks.</p>