Registry DB Cleanup in WSO2 API Manager

Image credits: Steve Johnson from Pexels

Different flows in WSO2 API Manager such as creating and updating APIs and adding tags and ratings cause different registry properties to be added to the Registry database. This is the expected behavior. But when Registry Versioning is enabled, older properties persist in the database. Over time, this could lead to unnecessary record growth in the Registry tables in the DB. This can directly affect the performance of the product if the number of records exceeds millions in number. Then there will be a time that we need to clean up the fast growing database tables from the Registry.

Due to this limitation, registry versioning is disabled by default in WSO2 API Manager 3.0 onwards to prevent unnecessary DB growth.

In WSO2 API Manager versions before 3.0.0 (2.x.x and 1.x.x), Registry Versioning is enabled by default and hence we have to clean up the Registry DB if there are large tables with millions of records. Out of all the tables available in the Registry DB, we can clean up the following tables without affecting the functionality of the product:

1. REG_LOG
2. REG_PROPERTY
3. REG_RESOURCE_PROPERTY
4. REG_TAG
5. REG_RESOURCE_TAG

You can use the following DB Scripts to clean up these tables.

REG_LOG Table Cleanup

You can use the following script for all the database types - H2, DB2, Mysql, Mssql, Oracle, and Postgresql.

REG_LOG Cleanup Script

REG_PROPERTY and REG_RESOURCE_PROPERTY Table Cleanup

Use the following script for H2, Mssql, and Postgresql database types.

H2, Mssql and Postgresql REG_PROPERTY and REG_RESOURCE_PROPERTY Cleanup Script

Use the following script for the Mysql database type.

Mysql REG_PROPERTY and REG_RESOURCE_PROPERTY Cleanup Script

Use the following script for DB2 and Oracle database types.

DB2 and Oracle REG_PROPERTY and REG_RESOURCE_PROPERTY Cleanup Script

REG_TAG and REG_RESOURCE_TAG Table Cleanup

Use the following script for H2, Mssql, and Postgresql database types.

H2, Mssql and Postgresql REG_TAG and REG_RESOURCE_TAG Cleanup Script

Use the following script for theMysql database type.

Mysql REG_TAG and REG_RESOURCE_TAG Cleanup Script

Use the following script for DB2 and Oracle database types.

DB2 and Oracle REG_TAG and REG_RESOURCE_TAG Cleanup Script

Turn-off Registry Versioning

To prevent having to constantly run the cleanup scripts when the DB grows, you can turn-off registry versioning in previous WSO2 API Manager versions and refer to the documentation to learn how to do this.