Cleaning up the database

Often, a database can contain several unused objects or very old data. Cleaning up these objects helps administrators perform a backup of images more quickly. From the development point of view, unused objects are similar to quiet noise because they affect the refactoring process.

Getting ready

In database applications, one needs to keep the database clean as database objects might hinder quick development due to the objects' dependencies. To clean the database, one needs to identify the unused database objects, including tables, views, indexes, and functions.

A recipe for bloated tables and indexes will not be introduced here; you can take a look at the bucardo check_postgres Nagios plugin code at https://bucardo.org/wiki/Check_postgres to understand how bloats in tables and indexes can be calculated.

How to do it…

Table statistics, such as the number of live rows, index scans, and sequential scans, can help identify empty and unused tables. Note that the following queries...