Vacuum/Reindex in AMP :-

By Sumesh Murali posted Jul 01, 2014 02:22 AM


When we do a vacuum/reindex on AMP, we do full vacuum.

VACUUM FULL does more extensive processing, including moving of tuples across blocks to try to compact the table to the minimum number of disk blocks. This form is much slower and requires an exclusive lock on each table while it is being processed. VACUUM reclaims storage occupied by deleted tuples and makes it available for re-use. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done.

To vacuum/reindex please run the following commands on AMP CLI :

1. Disable AMP:
# amp_disable

2. Prior to running each db command below (reindex and vacuum) stop and restart the postgres database using the commands:
# service postgresql stop
# service postgresql start
This will remove any queued up nightly jobs and allow the reindex and vacuum jobs to proceed with without waiting in the queue.

2a. If you are a super user for postgres then :
airwave=> reindex database airwave
airwave=> vacuum full analyze

2b. For normal root login users, run the following commands:
AMP Version < 7.3
# /opt/airwave/bin/reindexdb -d airwave -Upostgres
# /opt/airwave/bin/vacuumdb -v -f -z -Upostgres airwave
AMP Version >= 7.3
# /opt/airwave/pgsql/bin/reindexdb -d airwave -Upostgres
# /opt/airwave/pgsql/bin/vacuumdb -v -f -z -Upostgres airwave

2c. or you can also run these for normal root login users :

# psql -Upostgres airwave
psql (8.4.4)
Type "help" for help.

airwave=# reindex database airwave;
airwave=# vacuum full analyze;

3. Enable AMP bye :
# amp_enable

1 comment


Jan 10, 2018 09:58 AM



 Thanks for this post.


 Is there any useful command that you may recommend to check the progress of the re-index and vacuum after each command given above is issued? My Airwave is using psql (9.4.5).