Maintaining system tables

This is probably something you learn as you work more on PostgreSQL server rather than in any user guide out there.

System catalog tables in Postgres maintain information on every stats available for Postgres to device a plan for execution. These tables also need maintenance from time to time as the indexes too get bloats and performance degrades. We often run vacuum and reindex on all our user tables but it is important to keep the system tables in good shape too.

Below is a script that can be put in crontab and is advisable to run it every other month (takes only a couple of seconds to process, still run it during off production hours).

for db in `psql -l -t|awk '{print $1}'|grep -v -e ":" -e "template0"|sed '/^$/d'`; do reindexdb -s -d $db; done
Advertisements

, , ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: