Database growth: Managing TBE_AUDIT_LOG and TBE_AUDIT_LOG_PACKET table size in IDE Services

Problem

The IDE Services database exhibits consistent growth of approximately 200 MB per day, primarily attributed to the TBE_AUDIT_LOG and TBE_AUDIT_LOG_PACKET table. This table stores data regarding license activation history, user actions, configuration changes, and similar audit logs. Over time, this pattern of growth can lead to substantial storage requirements, posing a potential issue.

Cause

The consistent accumulation of audit-related data in the TBE_AUDIT_LOG and TBE_AUDIT_LOG_PACKET table is due to the lack of an automated database retention policy within the On-Premise installation.

Resolution

Cleaning the Database Safely

The TBE_AUDIT_LOG and TBE_AUDIT_LOG_PACKET table can be cleaned by removing older records that are no longer required for audit purposes. This action will not impact usage statistics sent to the JetBrains Portal. Execute the following SQL command to delete records older than a specified number of days:

DELETE FROM "<schema_name>"."TBE_AUDIT_LOG"
WHERE "CREATED_DATE" < NOW() - INTERVAL '10 days';

DELETE FROM "<schema_name>"."TBE_AUDIT_LOG_PACKET"
WHERE "CREATED_DATE" < NOW() - INTERVAL '10 days';

<schema_name> - provide the schema specified in your application.yaml under tbe.db.schema parameter. If not set explicitly, public schema is used by default

Replace 10 days with the desired retention period based on your organization's requirements.

Automating Data Cleanup

To automate periodic cleanup, utilize the pg_cron extension within the PostgreSQL database or set it up as a cron job on your server.

Redirecting Data to Object Storage

For compliance or governance purposes, audit logs can be redirected to Object Storage. This allows records to be stored without impacting the size of the primary database. Object Storage is also an option if used for hosting plugins within an internal JetBrains IDE plugin repository.

If assistance is required in setting up Object Storage or configuring automation, contact IDE Services Support.