We’re using Pega 24.1.3 version in OpenShift On-prem containers using PostgreSQL EDB Version. Need suggestions on Implementing the Performance optimization steps.
Approach-1: Identifying the heavy DB tables and delete the aged records more x number of days manually
Approach-2: Creating a stored Procedure / DB Script to run it from Database directly
Which one is recommended to follow for Pega? Besides, Is there any standard way of implementing Archiving & Purging Mechanism?
I’m not sure about the complexities involved in both apporaches . Deleting the records which are old x days, then you have to delete the references in other tables.
My recommendation
Enable Case Archival Archive resolved cases older than: x days
Move Attachments Out of PostgreSQL
Large attachment tables often become a major contributor to DB growth.
Review:pc_data_workattach
Purge Historical/Technical Data
Review retention for
pr_perf_stats
logs
queue history
old completed assignments
obsolete history records
Database Maintenance
VACUUM
VACUUM FULL (carefully planned)
I agree with what Ramesh has highlighted!
The recommended approach is to use Pega-side purge/archival mechanisms rather than deleting records directly from the database with a stored procedure. SQL tools are fine for development or one-off maintenance, but in production it is best to implement trimming/purging through activities or an agent so that related instances and indexes are handled properly
If the data is case-related, the standard approach is to use the Purge/Archive framework. For custom tables or custom classes, build a controlled purge activity or scheduled job in Pega instead of running direct DB deletes as the normal operating model