TRUNCATE command hangs indefinitely on OAuth2RefreshToken table

Products

VCF Operations/Automation (formerly VMware Aria Suite)

Issue/Introduction

You are attempting to utilize the article vIDM 3.3.x vPostgres DB OAuth2RefreshToken table consumes most space on the appliance leading to service outages
When attempting to reclaim disk space on a highly utilized /db partition (e.g., 84% full), you may experience an issue where the database truncation command hangs indefinitely. Specifically, executing the TRUNCATE TABLE "OAuth2RefreshToken" CASCADE; command fails to complete and blocks the terminal.

Environment

VMware Identity Manager 3.3.7

Cause

A TRUNCATE operation is a Data Definition Language (DDL) command that requires an ACCESS EXCLUSIVE lock on the target table. The hang is caused by lock contention on the "OAuth2RefreshToken" table.

In the environment, the horizon-workspace service runs an automated, scheduled background job to prune expired tokens using a standard Data Manipulation Language (DML) query:

DELETE FROM "OAuth2RefreshToken" WHERE expiration < $1

Because the table has bloated to an excessive size (e.g., 40GB), this automated DELETE operation performs a massive sequential scan that can take hours or days to complete. While running, it holds a lock on the table, forcing the TRUNCATE command to queue indefinitely. Additionally, the dead tuples generated by this massive DELETE scan can trigger the PostgreSQL autovacuum worker, which places another lock on the table and further blocks the truncation.

Resolution

To clear the blocking locks and successfully execute the truncation, perform the following steps:

Stop the horizon-workspace service across all nodes in the cluster. This prevents the application scheduler from immediately spawning a new DELETE thread after the current one is killed:
```
service horizon-workspace stop
```
Check for active, blocking database connections using the pg_stat_activity view to identify the Process IDs (PIDs) running the stuck DELETE query or the autovacuum worker:
```
SELECT pid, query, state, wait_event_type, wait_event FROM pg_stat_activity WHERE state != 'idle';
```
Validate the identified PIDs at the OS level to confirm they belong to the horizon application or postgres daemon:
```
ps -f -p <PID>
```
Forcefully sever the connections holding the locks by terminating the identified PIDs:
```
SELECT pg_terminate_backend(<PID>);
```
With all locks cleared, immediately execute the truncation to instantly drop the underlying data files and reclaim the disk space:
```
TRUNCATE TABLE "OAuth2RefreshToken" CASCADE;
```
Restart the horizon-workspace service across all nodes:
```
service horizon-workspace start
```

Additional Information

After restoring services, the background pruning job will resume on its next schedule, but will complete instantly against the newly emptied table.
If you are encountering secondary symptoms related to lingering usernames during login, please continue testing with an incognito browser session after completing the database truncation.