PostgreSQL Administration Commands – (Version 9,10,11,12,13)-Part3 (Monitoring Disk Usage)

    Each table has a primary heap disk file where most of the data is stored. If the table has any columns with potentially-wide values, there also might be a TOAST file associated with the table, which is used to store values too wide to fit comfortably in the main table. There will be one valid index on the TOAST table, if present. There also might be indexes associated with the base table. Each table and index is stored in a separate disk file — possibly more than one file, if the file would exceed one gigabyte.

    Monitoring Methods:

    You can monitor disk space in three ways. Use of SQL functions is the easiest one among three.

    • Using the SQL functions
    • Using the oid2name module
    • Using manual inspection of the system catalogs

    SQL Function

    1. Display the space used by TOAST tables
    postgres=# SELECT relname, relpages
    FROM pg_class,
         (SELECT reltoastrelid
          FROM pg_class
          WHERE relname = 'customer') AS ss
    WHERE oid = ss.reltoastrelid OR
          oid = (SELECT indexrelid FROM pg_index WHERE indrelid = ss.reltoastrelid)
    ORDER BY relname;

    relname | relpages ----------------------+---------- pg_toast_16806 | 0 pg_toast_16806_index | 1

    (2 rows)

    2. Display index sizes as well.

    postgres=# SELECT c2.relname, c2.relpages
    FROM pg_class c, pg_class c2, pg_index i
    WHERE c.relname = 'customer' AND
          c.oid = i.indrelid AND
          c2.oid = i.indexrelid
    ORDER BY c2.relname;
           relname        | relpages
     customer_id_indexdex |       26

    3. Display the table sizes in desc order

    postgres=# SELECT relname, relpages
    FROM pg_class
    ORDER BY relpages DESC;

    relname | relpages
    rental | 150
    payment | 108
    pg_proc | 81
    idx_unq_rental_rental_date_inventory_id_customer_id | 64
    pg_attribute | 60
    pg_toast_2618 | 60

    4. Using some basic sql functions

    select pg_database_size(11913);
    Select pg_database_size(‘postgres’);
    select pg_tablespace_size(1663);
    select pg_tablespace_size('pg_default');



    oid2name is a utility program that helps administrators to examine the file structure used by PostgreSQL. To make use of it, you need to be familiar with the database file structure. Following is the help utility of the same.

    oid2name --help
    oid2name helps examining the file structure used by PostgreSQL.
      oid2name [OPTION]...

      -f, --filenode=FILENODE    show info for table with given file node
      -i, --indexes              show indexes and sequences too
      -o, --oid=OID              show info for table with given OID
      -q, --quiet                quiet (don't show headers)
      -s, --tablespaces          show all tablespaces
      -S, --system-objects       show system objects too
      -t, --table=TABLE          show info for named table
      -V, --version              output version information, then exit
      -x, --extended             extended (show additional columns)
      -?, --help                 show this help, then exit

    Connection options:
      -d, --dbname=DBNAME        database to connect to
      -h, --host=HOSTNAME        database server host or socket directory
      -H                         same as -h, deprecated option
      -p, --port=PORT            database server port number
      -U, --username=USERNAME    connect as specified database user
    The default action is to show all database OIDs.
    1. Find Oid of all the databases as follows.

    oid2name -p 5432 -U “postgres”

    All databases:
        Oid   Database Name  Tablespace
      16384       dvdrental  pg_default
      24585  dvdrental_test  pg_default
      13709        postgres  pg_default
      13708       template0  pg_default
          1       template1  pg_default

    2. Then go the desired old location and get top 10 db objects in the default tablespace ordered by size.

    $ cd $PGDATA/base/<oid> 
    $ ls -lS * | head -10

    Disk Full Failure

    • The most important disk monitoring task of a database administrator is to make sure the disk doesn’t become full. A filled data disk will not result in data corruption, but it might prevent useful activity from occurring. If the disk holding the WAL files grows full, database server panic and consequent shutdown might occur.
    • If you cannot free up additional space on the disk by deleting other things, you can move some of the database files to other file systems by making use of tablespace.
    • Tip: Some file systems perform badly when they are almost full, so do not wait until the disk is completely full to take action.
      If your system supports per-user disk quotas, then the database will naturally be subject to whatever quota is placed on the user the server runs as. Exceeding the quota will have the same bad effects as running out of disk space entirely.

    Recent Articles


    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox