Background
Rebuilding indexes is a common maintenance recommendation for PostgreSQL databases. However, rebuilding an index is a blocking activity so customers that require little to no downtime don't like to rebuild indexes and are likely giving up performance because of it.
This is one of the main reasons that pg_repack exists in addition to it's ability to reduce table bloat.
PuppetDB suggestion
Since rebuilding indexes is a good idea but we don't want downtime the internet generally recommends creating a new index with the CONCURRENTLY flag which is non-blocking then dropping the existing index and renaming the new one to match the existing name.
We should have a facility for accomplishing the above task and I have two queries that produce that for the indexes in any postgresql database.
Assuming you place the attachments in /tmp you can run them and execute their output as follows.
su - postgres -s /bin/bash -c "psql -d pe-puppetdb -f /tmp/online_rebuild_index_script -qt" > /tmp/rebuild_indexes.sql
|
su - postgres -s /bin/bash -c "psql -d pe-puppetdb -f /tmp/rebuild_indexes.sql -q"
|
su - postgres -s /bin/bash -c "psql -d pe-puppetdb -f /tmp/online_rebuild_unique_constraints.sql -qt" > /tmp/rebuild_unique_constraints.sql
|
su - postgres -s /bin/bash -c "psql -d pe-puppetdb -f /tmp/rebuild_unique_constraints.sql -q"
|
Caveats
The first script rebuilds all indexes that don't enforce constraints.
The second script rebuilds all unique constraints that don't have further dependencies.
In my counting that currently leaves the certnames_transform_certname_key unique index out because numerous FKs depend on it to make sure that certname is unique. Otherwise all indexes are rebuilt by the combination of the two scripts.
|
