Vacuum Column Limit Exceeded Error
If your vacuum fails with the message
ERROR: 1036 DETAIL: Vacuum column limit
exceeded, your table has more columns than VACUUM can process with the
available memory. The vacuum column limit is less than the maximum number of columns for
a table, which is 1600. The actual column limit for a vacuum varies depending on your
cluster's configuration. For example, on clusters with DC1 or DS1 node types, you can
generally VACUUM tables with up to about 500 columns. With the DS2 node type, the limit
could be as high as 750 columns for DS2.xlarge or 1,000 columns for a DS2.8xlarge
You can increase the vacuum column limit by increasing the value of wlm_query_slot_count, which
increases the amount of memory available for the query. The maximum value for
wlm_query_slot_count is limited to the concurrency value for the queue.
For more information, see Best Practices for Designing
If increasing the value of wlm_query_slot_count is not an option, or if it doesn't solve the problem, you can avoid needing to vacuum by performing a deep copy. To perform a deep copy, you create a copy of the table, insert the rows from the original table into the copy, drop the original table, and then rename the copy. A deep copy is often much faster than a vacuum. For more information, see Performing a Deep Copy.
For example, suppose the table
calendardays has 365 columns. After a
load operation, you perform a vacuum and the vacuum fails, as the following example
vacuum calendardays; An error occurred when executing the SQL command: vacuum calendardays; ERROR: 1036 DETAIL: Vacuum column limit exceeded for table calendardays HINT: Increase the value of wlm_query_slot_count or perform a deep copy instead of a vacuum.
The following example sets
wlm_query_slot_count to 10, performs a
vacuum, and then resets
wlm_query_slot_count to 1. With the higher slot
count, the vacuum
set wlm_query_slot_count to 10; vacuum calendardays; set wlm_query_slot_count to 1; vacuum executed successfully
You can perform a deep copy instead of a vacuum. The following example uses CREATE TABLE LIKE to perform a deep copy.
create table likecalendardays (like calendardays); insert into likecalendardays (select * from calendardays); drop table calendardays; alter table likecalendardays rename to calendardays;
Performing a deep copy using CREATE TABLE AS (CTAS) is faster than using CREATE TABLE LIKE, but CTAS does not preserve the sort key, encoding, distkey, and notnull attributes of the parent table. For a comparison of different deep copy methods, see Performing a Deep Copy.