Database Maintenance - PostgreSQL (Connect 2024)

Reorganize Database Indexes

The automatic clean-up functionality of Connect removes a significant number of records resulting in fragmentation of the corresponding database indexes. To address this issue, the following SQL script should be scheduled for daily database maintenance:

To reorganize all Connect application-specific indexes, use the following SQL script:

Delete Obsolete Data

If there is a need to delete all Contexts and Performance Records created before a specific date, the following script can be employed. Be aware that fragmented indexes may cause poor performance during deletion. If required, defragment these indexes prior to deleting data. Ensure to adjust the date in the first line of the script as needed:

Reducing the size of the database

Deleting a large amount of data does not necessarily reduce the size of the database. In this case, it is advisable to instruct the database to release the no longer needed space. This can be achieved with the following SQL script:

The VACUUM FULL command defragments and compacts the database reclaiming space occupied by deleted rows. After shrinking the database, it may be beneficial to reorganize the indexes.