PostgreSQL is a low maintenance database compared to other mainstream databases, but requires some periodic attention to achieve optimal performance. The article uses examples to describe how to do a regular PostgtreSQL maintenance for better performance.

Full Vacuum

The PostgreSQL database runs auto-vacuum on a daily basis to check for table-space fragmentation. The auto vacuum reclaims the fragmented space unless the fragmentation reaches a particular threshold. It is recommended to run "Full Vacuum" on a monthly basis to reclaim the lost space. The vacuum may take some time and should be run over weekends to reduce any performance impact.

Please follow the following steps for "full vacuum" -

1. Open Pg Admin III interface from Start --> Progams --> PostgreSQL 8.3 --> Pg Admin III
2. In Object Browser select ‘Postgres Database Server 8.3 (Local)’ and right click to select connect
3. At the password prompt provide the password for the PostgreSQL database administrator user
4. Once connected to the Database server browse the tree list of databases
5. Select the "insyncstore_X" databases one by one and right click to select maintenance
6. In Maintain Database store select VACUUM. In options select "Full" and "Analyze"
7. Press "OK" to start the vacuum

Reindex Database

After the vacuum is done further maintenance will include Reindexing the database.

1. Select the insyncstore_X databases one by one and right click to select maintenance
2. In maintain database store select "Reindex" and with "Force" as option
3. Click on OK to Start the Reindex process

Both of these might take some time and its recommended to run them over the weekend

Automating Maintenance Tasks

The administrator can use the vacuumdb.exe to be run vacuum apart from pgAdminIII. To run the vacuumdb.exe kindly follow the below commands: 

1. Open command prompt
2. Navigate to C:\Program Files\PostgreSQL\8.3\bin
3. Run the following command # vacuumdb.exe -a -f -v -U <postgres_db_username> .

For detailed help on the command, please use "vacuumdb.exe --help" .

The administrator can also use the VACUUM sql command to run the vacuum. Kindly refer the following link -  http://www.postgresql.org/docs/8.3/interactive/sql-vacuum.html.

The task can be scheduled by adding a script to use vacuumdb.exe under "cronttab"  in Linux and "Task Scheduler" for Microsoft Windows server.