PostgreSQL database maintenance guide

Edit online

The PostgreSQL database maintenance guide describes how to properly maintain the PostgreSQL backend database used by the directory server to improve performance and reliability.

You can perform database maintenance operations on the ISVD PostgreSQL database by using the idspgmaint utility. These operations help manage table bloat, maintain index health, and ensure accurate query planning.
  • VACUUM
    • Reclaims dead tuples and updates visibility maps.
    • Allows concurrent reads and writes. However, it does not usually return disk space to the operating system.
    • Use for routine, online maintenance.
  • VACUUM FULL
    • Rewrites tables to compact data and return space to the OS.
    • Slower operation and requires ACCESS EXCLUSIVE locks.
    • Use during maintenance windows when need to reclaim OS disk space or after unusual bloat spikes.
  • ANALYZE
    • Updates optimizer statistics.
    • Run after VACUUM or issue ANALYZE separately when the data has changed substantially
  • REINDEX

    • Corrects index size growth and fixes bloated or corrupted indexes.

Note:

PostgreSQL runs autovacuum. Use the idspgmaint utility for manual and controlled maintenance, such as on hot tables or during maintenance windows. Review autovacuum thresholds and parameters (for example, autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor)

For more information about the idspgmaint utility, see idspgmaint.