Skip to content

Maintaining the enterprise database

The enterprise database used with a HCL Traveler High Availability pool requires periodic maintenance just like the internal database used by a standalone server.

After many changes to table data, the table and its indexes can become fragmented. Logically sequential data might reside on nonsequential pages, forcing the database manager to perform additional read operations to access data. Enterprise database servers provide utilities for maintaining the databases. These utilities can be configured to run automatically. Whether run manually or automatically, a regularly scheduled maintenance plan is essential to maintain peak performance of your system.

Note: Maintenance should be run on all of the tables in the database. We recommend performing online index maintenance once a week during non-peak hours and offline maintenance about once a month. Consult the database server product documentation for more information on database maintenance.

Note: HCL Traveler includes the ability to run database maintenance. For more information, refer to HCL Traveler database maintenance for HA deployments.

Online database maintenance

The database allows read and write access during maintenance action and there is minimum performance impact to the application.

  • PostgreSQL

    VACUUM (ANALYZE) <table markdownname>;

    Performs online cleanup and statistics update. Does not block reads/writes. Use VACUUM FULL only during downtime (locks table). For more information, see this article.

    REINDEX CONCURRENTLY TABLE <table markdownname>;

    Rebuilds indexes concurrently (online). Minimal blocking; table remains accessible. Available since PostgreSQL 12. For more information, see this article.

Offline database maintenance

The database only allows read access during maintenance, does not allow any access during maintenance, or may experience excessive locking during maintenance. Because of this, the offline maintenance should be run with Traveler shutdown on all servers or other blocking in place to prevent any of the Traveler servers from trying to connect to the database during maintenance. If the database is highly available or has secondary clusters, the maintenance only needs to be run on the primary database.

  • PostgreSQL

    VACUUM FULL <table markdownname>;

    Fully rewrites the table and all indexes into a new file. Reclaims space but requires an exclusive lock; table is unavailable during the process. For more information, see this article.

    REINDEX TABLE <table markdownname>;

    Fully rebuilds all indexes for the table. Locks the table for writes during operation; use for heavy index fragmentation or corruption. For more information, see this article.

Parent topic:Maintaining the HCL Traveler database