Seeing database table sizes

To see which database tables are taking up disk space, go to the Settings -> Database → Query tab in the VPOP3 settings, and put the following query in the Query box, and press Do Query. This will show the biggest 20 tables in the database, and how much space they are occupying.

SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind <> 'i' AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC

Some of the tables which are commonly large are:

  • messages.msgdata - this contains message data in mailboxes, including recently deleted messages in VPOP3 Enterprise.

  • archive.archivemain - this contains the main index for archived messages

  • logdb.* - these tables contain data for the 'historical logging & reporting' facility in VPOP3

  • greylisting.* - these tables contain data for the greylisting component in VPOP3, such as which senders have previously attempted to send messages

  • smtpstats.smtpclientfailures - this contains data about previous SMTP attempts - for the intrusion prevention system

  • spamfilter.bayes2 - this contains statistical data for the spamfilter

  • outqueue.outmessages - this contains messages which are currently in, or have recently been in, the VPOP3 outqueue.

Note that, as with most database servers, when you delete data, the database server does not automatically release the disk space back to the operating system, instead it is kept and reused by the database server for future entries in that database table. This is for performance reasons. If you have to free up disk space for some reason, see Freeing disk space used by the database.