Each table has a main heap disk file in which most of the data is stored. If a table has columns that may be very wide (large in size), there is another TOAST file associated with the table, which is used to store values that are too wide to be stored in the main table. If you have this attachment file, there will be an index available on the TOAST table. Of course, there may also be index and base table associations. Each table and index is stored in a separate disk file. If the file exceeds 1G bytes, there may be more than one file.
You can monitor disk space in three ways: using SQL functions, using the oid2name module, or manually observing the system directory. SQL function is the easiest method to use, and it is usually recommended.
Using psql on a recently cleaned or analyzed database, you can issue a query to view the disk usage of any table:

postgres=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'ctetest';
 pg_relation_filepath | relpages 
----------------------+----------
 base/12330/41502     |     8334
(1 row)

Each page is usually 8K bytes (remember that relpages are only updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX).
Displays the space used by the TOAST table:

postgres=# SELECT relname, relpages                                      
           FROM pg_class,
                (SELECT reltoastrelid
                 FROM pg_class
                 WHERE relname = 'ctetest') AS ss
           WHERE oid = ss.reltoastrelid OR
                 oid = (SELECT indexrelid
                        FROM pg_index
                        WHERE indrelid = ss.reltoastrelid)
           ORDER BY relname;
       relname        | relpages 
----------------------+----------
 pg_toast_41502       |        0
 pg_toast_41502_index |        1
(2 rows)

Display the size of the index:

postgres=# SELECT c2.relname, c2.relpages                       
           FROM pg_class c, pg_class c2, pg_index i
           WHERE c.relname = 'ctetest' AND
                 c.oid = i.indrelid AND
                 c2.oid = i.indexrelid
           ORDER BY c2.relname;
    relname     | relpages 
----------------+----------
 ctetest_id_idx |     2198
(1 row)

Find the largest tables and indexes:

postgres=# SELECT relname, relpages
           FROM pg_class
           ORDER BY relpages DESC;
                  relname                  | relpages 
-------------------------------------------+----------
 test02                                    |    39216
 ctetest                                   |     8334
 foo                                       |     4425

 

By Kalath