We are using a an Append-optimised(AO) columnar table in Greenplum(v6) which frequently receives both UPDATE and INSERTS. AO tables are not well suited for UPDATES but the columnar orientation make query execution considerably faster in comparison to the row model and also gives column level compression benefits.

However in order to make fast UPDATES I have created an index on the table. Greenplum creates a meta data structure prefixed with pg_ao_blkdir_* during index creation for AO table and this meta data contains mapping info which aids index to find the required row to update.

But on the long run I see this meta data table is growing at an unexpected rate where my data size ≈80 GB but pg_ao_blkdir_ is more than 450GB for the same table. I am trying to find a way to rebuild or recreate this meta data without recreating the table. I have tried below things,

1. Reindex/drop/recreate index- meta data exists even after dropping the index
2. Tried to drop meta data itself after dropping the index- Wasn't successful
3. Lazy vacuum - no change
4. VACUUM FULL - works on testing setup, but on actual server taking so long

We are using a an Append-optimised(AO) columnar table in Greenplum(v6) which frequently receives both UPDATE and INSERTS. AO tables are not well suited for UPDATES but the columnar orientation make query execution considerably faster in comparison to the row model and also gives column level compression benefits.

However in order to make fast UPDATES I have created an index on the table. Greenplum creates a meta data structure prefixed with pg_ao_blkdir_* during index creation for AO table and this meta data contains mapping info which aids index to find the required row to update.

But on the long run I see this meta data table is growing at an unexpected rate where my data size ≈80 GB but pg_ao_blkdir_ is more than 450GB for the same table. I am trying to find a way to rebuild or recreate this meta data without recreating the table. I have tried below things,

1. Reindex/drop/recreate index- meta data exists even after dropping the index
2. Tried to drop meta data itself after dropping the index- Wasn't successful
3. Lazy vacuum - no change
4. VACUUM FULL - works on testing setup, but on actual server taking so long

• postgresql
• greenplum
• vacuum

1 Answer 1

The only way to do this is by recreating the AO table . A vacuum full on base AO table also rebuilds meta data but it's slower in comparison to recreating it.

To keep the size in control frequently VACUUM FULL auxiliary table directly .