Pivotal Knowledge Base

Follow

How to remove fragmentation ( aka bloat ) on a relation using redistribute

Environment

Product Version
Pivotal Greenplum (GPDB) All versions

Purpose

Before beginning, it is highly recommended to have a look at this article to understand what bloats are. The bloats can also be removed using vacuum, but do look at this article on some pros and cons related to it.

The article here would explain the steps to redistribute the data and help remove bloat.

Procedure 

Before redistributing the data you will need to identify the table distribution policy, if you are only redistributing a single table, then the quickest way to look out for the distribution policy is:

\d <table_name>

if you have a series of tables that would need redistribution, then check out the script in the article mentioned here and here to know the distribution policy of the table.

If the table distribution policy is "random" then the command to use is the following:

alter table <table_name> set with (reorganize=true) distributed randomly;

If the table in question is distributed with a set of columns or a single column, then the command would be the following:

alter table <table_name> set with (reorganize=true) distributed by (<column_names1>,<column_names2>....)

Comments

Powered by Zendesk