PostgreSQL command to perform a FULL VACUUM hangs or never finishes

Products

• Carbon Black EDR

Issue/Introduction

• Experiencing general UI slowness and empty pages when trying to display sensor details pages
• EDR Server 6.2.3 and earlier can build up very large sensor_comm_failures tables that can cause slowness running certain postgres database queries.

Environment

• Carbon Black EDR Server: All Supported Versions

Cause

This psql command is used to reduce the size of the table, but on occasion, this command has been known to not finish and seemingly hang for many hours:

sudo psql -p 5002 -d cb -c "VACUUM FULL VERBOSE ANALYZE;" 2> /tmp/vacuum_output.txt

Resolution

1. Check to make sure all services are down on the EDR primary server other than the cb-pgsql service:

   # /usr/share/cb/cbservice cb-enterprise status

2. If the above is true, control-c out of the psql FULL VACUUM command, and instead run this command:

# sudo psql -p 5002 -d cb -c "TRUNCATE sensor_comm_failures"
TRUNCATED   <-- expected output

# sudo /usr/share/cb/cbservice cb-pgsql stop

3. Restart Services