PostgreSQL views database, index, and tablespace size

The code is as follows:

Instance description 3.1 view the number of bytes required to store a specified value David = # select pg_column_size (1 ); Pg_column_size ----------------               4 (1 row) David = # select pg_column_size (10000 ); Pg_column_size ----------------               4 (1 row) David = # select pg_column_size ('David '); Pg_column_size ----------------               6 (1 row) David = # select pg_column_size ('Hello, world '); Pg_column_size ---------------- 12 (1 row) David = # select pg_column_size ('2017-04-18 15:17:21. 2013 + 08 '); Pg_column_size ---------------- 30 (1 row) David = # select pg_column_size ('China '); Pg_column_size ----------------               7 (1 row) David = #3.2 view the database size View raw data David = # d test Table "public. test" Column | Type | Modifiers ----------- + --------------------- + ----------- Id | integer | Name | character varying (20) | Gender | boolean | Join_date | date | Dept | character (4) | Indexes: "Idx_join_date_test" btree (join_date) "Idx_test" btree (id) David = # select count (1) from test; Count --------- 1835008 (1 row) David = # view the size of the david database David = # select pg_database_size ('David '); Pg_database_size ------------------ 190534776 (1 row) David = # View all database sizes David = # select pg_database.datname, pg_database_size (pg_database.datname) AS size from pg_database; Datname | size ----------- + ------------- Template0. | 6513156 IPVS | 6657144 Jboss | 6521348 Bugs | 6521348 David | 190534776 BMCV3/28147135608 Mydb | 10990712 Template1 | 6521348 (8 rows) David = # The result is too long to read. 3.3 display the size in a user-friendly manner David = # select pg_size_pretty (pg_database_size ('David ')); Pg_size_pretty ---------------- 182 MB (1 row) David = #3.4 view the size of a single index David = # select pg_relation_size ('idx _ test '); Pg_relation_size ------------------ 41238528 (1 row) David = # select pg_size_pretty (pg_relation_size ('idx _ test ')); Pg_size_pretty ---------------- 39 MB (1 row) David = # David = # select pg_size_pretty (pg_relation_size ('idx _ join_date_test ')); Pg_size_pretty ---------------- 39 MB (1 row) David = #3.5 view the size of all indexes in a specified table David = # select pg_indexes_size ('test '); Pg_indexes_size ----------------- 82477056 (1 row) David = # select pg_size_pretty (pg_indexes_size ('test ')); Pg_size_pretty ---------------- 79 MB (1 row) The size of the index david = # idx_test and idx_join_date_test equals to the size of the index queried by pg_indexes_size. 3.6 view the size of all indexes in the specified schema in the ascending order. David = # select * from pg_namespace; Nspname | nspowner | nspacl -------------------- + ---------- + ------------------------------------- Pg_toast | 10 | Pg_temp_1 | 10 | Pg_toast_temp_1 | 10 | Pg_catalog | 10 | {postgres = UC/postgres, = U/postgres} Information_schema | 10 | {postgres = UC/postgres, = U/postgres} Public | 10 | {postgres = UC/postgres, = UC/postgres} (6 rows) David = # select indexrelname, pg_size_pretty (pg_relation_size (relid) from pg_stat_user_indexes where schemaname = 'public' order by pg_relation_size (relid) desc; Indexrelname | pg_size_pretty ------------------------------- + ---------------- Idx_join_date_test | 91 MB Idx_test | 91 MB Testtable_idx| 1424 kB City_pkey | 256 kB City11 | 256 kB Country1_age_pkey | 56 kB Sale_pkey | 8192 bytes Track_pkey | 8192 bytes Tbl_partition_201211_joindate | 8192 bytes Tbl_partition_201212_joindate | 8192 bytes Tbl_partition_201301_joindate | 8192 bytes Tbl_partition_201302_joindate | 8192 bytes Tbl_partition_201303_joindate | 8192 bytes Customer_pkey | 8192 bytes Album_pkey | 8192 bytes Item_pkey | 8192 bytes Tbl_partition_201304_joindate | 8192 bytes Tbl_partition_201307_joindate | 8192 bytes Tbl_partition_201305_joindate | 0 bytes Tbl_partition_201306_joindate | 0 bytes (20 rows) David = #3.7 view the specified table size David = # select pg_relation_size ('test '); Pg_relation_size ------------------ 95748096 (1 row) David = # select pg_size_pretty (pg_relation_size ('test ')); Pg_size_pretty ---------------- 91 MB (1 row) David = # Use the pg_table_size () function to view David = # select pg_table_size ('test '); Pg_table_size --------------- 95789056 (1 row) David = # select pg_size_pretty (pg_table_size ('test ')); Pg_size_pretty ---------------- 91 MB (1 row) David = #3.8 view the total size of a specified table David = # select pg_total_relation_size ('test '); Pg_total_relation_size ------------------------ 178266112 (1 row) David = # select pg_size_pretty (pg_total_relation_size ('test ')); Pg_size_pretty ---------------- 170 MB (1 row) David = #3.9 view the sizes of all tables in the specified schema in the ascending order. David = # select relname, pg_size_pretty (pg_relation_size (relid) from pg_stat_user_tables where schemaname = 'public' order by pg_relation_size (relid) desc; Relname | pg_size_pretty ------------------------------- + ---------------- Test | 91 MB Testtable | 1424 kB City | 256 kB Countrylanguage | 56 kB Country | 40 kB Testcount | 8192 bytes Tbl_partition_201302 | 8192 bytes Tbl_partition_201303 | 8192 bytes Person | 8192 bytes Customer | 8192 bytes American_state | 8192 bytes Tbl_david | 8192 bytes Emp | 8192 bytes Tbl_partition_201212 | 8192 bytes Tbl_partition_201304 | 8192 bytes Tbl_partition_error_join_date | 8192 bytes Tbl_partition_201211 | 8192 bytes Album | 8192 bytes Tbl_partition_201307 | 8192 bytes Tbl_xulie | 8192 bytes Tbl_partition_201301 | 8192 bytes Sale | 8192 bytes Item | 8192 bytes Track | 8192 bytes Tbl_partition_201306 | 0 bytes Tbl_partition | 0 bytes Tbl_partition_201305 | 0 bytes Person2 | 0 bytes (28 rows) David = #3.10 view the tablespace size David = # select spcname from pg_tablespace; Spcname ------------ Pg_default Pg_global (2 rows) David = # select pg_tablespace_size ('PG _ default '); Pg_tablespace_size -------------------- 28381579760 (1 row) David = # select pg_size_pretty (pg_tablespace_size ('PG _ default ')); Pg_size_pretty ---------------- 26 GB (1 row) David = # Another method for viewing: David = # select pg_tablespace_size ('PG _ default')/1024/1024 as "size m "; SIZE M -------- 27066 (1 row) David = # select pg_tablespace_size ('PG _ default')/1024/1024/1024 as "size g "; SIZE G -------- 26 (1 row) David = #