How to Calculate PostgreSQL Table Data Size

Use Case

Customer enabled Audit and Monitoring feature on a high-load production system and would like to monitor size of PostgreSQL files which store audit data, to avoid lack of disk space.

Resolution

There are different ways to get data file size which stores Audit data.

1. Use a query with one of PostgreSQL database object management functions:

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE

TIBCO JasperReports® Server stores audit data in the following tables:

jiauditevent

jiauditeventarchive

jiauditeventproperty

jiauditeventpropertyarchive

The SQL below returns size information for audit tables, including size of tables, indexes and TOAST , in both raw bytes and 'pretyy' form:

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a where table_name in ('jiauditevent', 'jiauditeventarchive','jiauditeventproperty','jiauditeventpropertyarchive')
) a;

2. If customer prefer monitoring file size at OS level, he can rertieve information

which file stores a table data using a database object location function:

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBLOCATION

Function pg_relation_filepath() returns the entire file path name (relative to the database 'data' directory) of a table:

SELECT pg_relation_filepath('jiauditevent');

Once files that store a table data idenitified, their size can be monitored with a command line script.

Solution tested with TIBCO JasperReports® Server v.7.1.0

AS-20190503, case #01685820