Database Health

  • An orphaned record is a record whose foreign key value references a non-existent primary key value.


  • Every record that has been deleted from a PostgreSQL database but is still taking some space is called a dead tuple. 
  • The space occupied by dead tuples is called bloat. 
  •  PostgreSQL runs VACUUM on such tables to remove bloat and reclaim the storage. 

This page provides CLI commands that will enable you to track the health of the Asset Manager system as measured by the amount of bloat in its database. The goal is to minimize bloat and to keep Asset Manager's database running within a defined threshold for bloat. Asset Manager 3.3.2.2 allows you to monitor bloat, receive an alert when acceptable thresholds for it have been breached, and resolve bloat with VACUUM.

Also, to minimize bloat, the Asset Manager system has been made to delete orphaned attributes and orphaned certificates at each upgrade. This is in addition to deleting orphaned interfaces, which Asset Manager was already doing.

Monitoring Orphans via the Command Center CLI

This CLI command support db orphans shows the number of orphaned interfaces, certificates, and attributes in the Asset Manager database. This command can only be run by a user having the "superuser" flag. 

Sample results:

Command Center before upgrade:

Command Center after upgrade:
admin@demosystem> support orphans 
Orphans: 
  Interfaces: 10 
  Certificates: 1 
  Attributes: 6 		 admin@demosystem> support orphans 
Orphans: 
  Interfaces: 0
  Certificates: 0
  Attributes: 0

Other database tools have been made available at Asset Manager 3.3.2.2 for database monitoring:

support db [ bloat | orphans | psql | tuples | vacuum ]  

  bloat    Report size of database bloat per schema
  orphans  Get count of or delete orphan entries in database
  psql     Start a Postgres interactive session
  tuples   Report the number of dead tuples per table
  vacuum   Perform database maintenance

The system also automatically monitors bloat and vacuum metrics. This runs as a nightly process and posts results a log file that can be sent to syslog.

 A user can run the CLI command db vacuum without any options. This will perform a “vacuum full analyze” on the database by default, reclaim space in the database, and collect full table statistics.

The API /api/rest/management/db/tuplestats