PostgreSQL Disk usage Size monitoring

Source: Internet
Author: User
Tags disk usage

Table size Information

postgres=# SELECT *, Pg_size_pretty (total_bytes) as Total
postgres-#, Pg_size_pretty (index_bytes) as Index
postgres-#, Pg_size_pretty (toast_bytes) as Toast
postgres-#, Pg_size_pretty (table_bytes) as table
postgres-# from (
Postgres (# SELECT *, Total_bytes-index_bytes-coalesce (toast_bytes,0) as Table_bytes from (
Postgres (# SELECT C.oid,nspname as TABLE_SCHEMA, relname as table_name
Postgres (#, C.reltuples as Row_estimate
Postgres (#, Pg_total_relation_size (c.oid) as Total_bytes
Postgres (#, Pg_indexes_size (c.oid) as Index_bytes
Postgres (#, Pg_total_relation_size (reltoastrelid) as Toast_bytes
Postgres (# from Pg_class C
Postgres (# Left JOIN pg_namespace n on n.oid = C.relnamespace
Postgres (# WHERE relkind = ' R '
Postgres (#) A
Postgres (#) A;
OID | Table_schema | table_name | Row_estimate | Total_bytes | Index_bytes | Toast_bytes | Table_bytes | Total
| Index | Toast | Table
-------+--------------------+-------------------------+--------------+-------------+-------------+------------- +-------------+--------
----+------------+------------+------------
24933 | Public | Pgbench_tellers | 1280 | 237568 | 73728 | | 163840 | 232 KB
| KB | | KB
24939 | Public | pgbench_branches | 128 | 65536 | 16384 | | 49152 | KB
| KB | | KB
24936 | Public | pgbench_accounts | 1.28e+07 | 2009858048 | 287531008 | | 1722327040 | 1917 MB
| 274 MB | | 1643 MB
24930 | Public | Pgbench_history | 20826 | 1261568 | 0 | | 1261568 | 1232 KB
| 0 bytes | | 1232 KB
2619 | Pg_catalog | pg_statistic | 402 | 286720 | 32768 | 73728 | 180224 | 280 KB
| KB | KB | 176 KB
1247 | Pg_catalog | Pg_type | 354 | 163840 | 57344 | | 106496 | KB
| KB | | 104 KB
1260 | Pg_catalog | Pg_authid | 1 | 73728 | 32768 | | 40960 | KB
| KB | | KB
1418 | Pg_catalog | pg_user_mapping | 0 | 16384 | 16384 | | 0 | KB
| KB | | 0 bytes
2613 | Pg_catalog | Pg_largeobject | 0 | 8192 | 8192 | | 0 | 8192 by
TES | 8192 bytes | | 0 bytes
1249 | Pg_catalog | Pg_attribute | 2399 | 598016 | 188416 | | 409600 | 584 KB
| 184 KB | | KB
1255 | Pg_catalog | Pg_proc | 2821 | 958464 | 327680 | 8192 | 622592 | 936 KB

The largest database

postgres=# SELECT D.datname as Name, Pg_catalog.pg_get_userbyid (D.DATDBA) as Owner,
postgres-# case when Pg_catalog.has_database_privilege (D.datname, ' CONNECT ')
postgres-# then Pg_catalog.pg_size_pretty (pg_catalog.pg_database_size (d.datname))
postgres-# ELSE ' No Access '
postgres-# END as SIZE
postgres-# from Pg_catalog.pg_database D
postgres-# ORDER by
postgres-# case when Pg_catalog.has_database_privilege (D.datname, ' CONNECT ')
postgres-# then Pg_catalog.pg_database_size (d.datname)
postgres-# ELSE NULL
postgres-# END DESC--nulls first
postgres-# LIMIT 20
postgres-#;
name | Owner | Size
--------------+--------------+---------
Benchmarksql | Benchmarksql | Ten GB
Postgres | Postgres | 1925 MB
Pg_monitor | Postgres | 7816 KB
Scott | Scott | 7632 KB
Cott | Cott | 7496 KB
template1 | Postgres | 7496 KB
Mgt | Mgt | 7496 KB
Template0 | Postgres | 7129 KB
(8 rows)

The largest table

postgres=# SELECT Nspname | | '. ' | | Relname as "relation",
postgres-# Pg_size_pretty (Pg_relation_size (c.oid)) as "size"
postgres-# from Pg_class C
postgres-# left JOIN pg_namespace N on (n.oid = c.relnamespace)
postgres-# WHERE Nspname not in (' Pg_catalog ', ' information_schema ')
postgres-# ORDER by Pg_relation_size (c.oid) DESC
postgres-# LIMIT 20;
Relation | Size
------------------------------+------------
public.pgbench_accounts | 1642 MB
Public.pgbench_accounts_pkey | 274 MB
Public.pgbench_history | 1208 KB
pg_toast.pg_toast_2618 | 376 KB
Public.pgbench_tellers | KB
Public.pgbench_tellers_pkey | KB
pg_toast.pg_toast_2619 | KB
Pg_toast.pg_toast_2618_index | KB
Public.pgbench_branches_pkey | KB
public.pgbench_branches | KB
Pg_toast.pg_toast_2619_index | KB
Pg_toast.pg_toast_2396_index | 8192 bytes
Pg_toast.pg_toast_2606_index | 8192 bytes
Pg_toast.pg_toast_2609_index | 8192 bytes
Pg_toast.pg_toast_2964_index | 8192 bytes
Pg_toast.pg_toast_2620_index | 8192 bytes
Pg_toast.pg_toast_2604_index | 8192 bytes
Pg_toast.pg_toast_1255_index | 8192 bytes
Pg_toast.pg_toast_3596_index | 8192 bytes
Pg_toast.pg_toast_3592_index | 8192 bytes
(rows)

Total size of table

postgres=# SELECT Nspname | | '. ' | | Relname as "relation",
postgres-# Pg_size_pretty (Pg_total_relation_size (c.oid)) as "Total_size"
postgres-# from Pg_class C
postgres-# left JOIN pg_namespace N on (n.oid = c.relnamespace)
postgres-# WHERE Nspname not in (' Pg_catalog ', ' information_schema ')
postgres-# and C.relkind <> ' i '
postgres-# and Nspname!~ ' ^pg_toast '
postgres-# ORDER by Pg_total_relation_size (c.oid) DESC
postgres-# LIMIT 20;
Relation | Total_size
---------------------------+------------
public.pgbench_accounts | 1917 MB
Public.pgbench_history | 1232 KB
Public.pgbench_tellers | 232 KB
public.pgbench_branches | KB
public.pg_stat_statements | 0 bytes
Public.pg_buffercache | 0 bytes
(6 rows)

Ref:https://wiki.postgresql.org/wiki/disk_usage

PostgreSQL disk use size monitoring

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.