How to vacuum large database tables in VidiCore [VC UG]

A database consists of one or more tables in which all the data in a database is stored. Each table is made up of rows and columns. The different tables can grow in size pretty quick and it can be hard to understand what kind of information a specific table contains. Here we will show you how to list your tables with information, explain the meaning of the most common tables and how to empty the large ones.

To get a list of your five largest tables in size order (Largest to smallest), execute the following SQL query;

CODE

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 5;

You should now see a list with your tables and their sizes.

The most common tables that usually are the bigger ones are;

t_bigtext: Various kinds of text values in the system. (Like metadata field values, job metadata values etc.)

t_metadataentry: Relation of metadata field and groups and their change histories.

t_auditlog: Vidispine API access log/history.

Since the tables grow quickly, you can use the "VACUUM" service;

Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table. VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an exclusive lock on each table while it is being processed.

To perform a vacuum, run the command below;

VACUUM FULL VERBOSE TABLE-NAME;

For more information and parameters on Vacuum;

https://www.postgresql.org/docs/9.5/static/sql-vacuum.html