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
- 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
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.
Usage:
oid2name [OPTION]...
Options:
-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.
- 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.
