First, Introduction
PostgreSQL provides a number of system management functions to view tables, indexes, table spaces, and the size of the database, described in detail below.
second, the Database object dimension function
| The name of the function |
return type |
Describe |
| Pg_column_size (Any) |
Int |
The number of bytes required to store a specified number (possibly compressed) |
| Pg_database_size (OID) |
bigint |
Disk space used by the database specifying the OID |
| Pg_database_size (name) |
bigint |
Disk space used by the database with the specified name |
| Pg_indexes_size (Regclass) |
bigint |
Total disk space used to correlate table indexes for the specified table OID or table name |
| Pg_relation_size (Relation regclass, fork text) |
bigint |
Specifies an OID or named table or index, specifying thedisk space used by the fork ('main ', ' FSM ', or ' VM ') |
| Pg_relation_size (Relation Regclass) |
bigint |
Abbreviation for pg_relation_size (..., ' main ') |
| Pg_size_pretty (bigint) |
Text |
Converts a size in bytes expressed as a 64-bit integer to a human-readable format with size units |
| Pg_size_pretty (Numeric) |
Text |
Converts a byte-calculated value into a human-readable dimension unit |
| Pg_table_size (Regclass) |
bigint |
Specifies the disk space used by tables of the table OID or table name, excluding indexes (but includes toast, free space mappings, and visual mappings) |
| Pg_tablespace_size (OID) |
bigint |
Disk space used by the table space of the specified OID |
| Pg_tablespace_size (name) |
bigint |
Disk space used by the specified table space for the name |
| Pg_total_relation_size (Regclass) |
bigint |
Specifies the total disk space used by the table OID or table name, including all indexes and toast Data |
Third, the example explanation
3.1 View the number of bytes required to store a specified number
Copy Code code as follows:
david=# Select Pg_column_size (1);
Pg_column_size
----------------
(1 row)
david=# Select Pg_column_size (10000);
Pg_column_size
----------------
(1 row)
david=# Select Pg_column_size (' David ');
Pg_column_size
----------------
(1 row)
david=# Select Pg_column_size (' Hello,world ');
Pg_column_size
----------------
(1 row)
david=# Select Pg_column_size (' 2013-04-18 15:17:21.622885+08 ');
Pg_column_size
----------------
(1 row)
david=# Select Pg_column_size (' China ');
Pg_column_size
----------------
(1 row)
david=#
3.2 View Database size
View Raw Data
Copy Code code as follows:
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
---------
(1 row)
david=#
View the David database size
Copy Code code as follows:
david=# Select Pg_database_size (' David ');
Pg_database_size
------------------
(1 row)
david=#
View all database sizes
Copy Code code as follows:
david=# Select Pg_database.datname, Pg_database_size (pg_database.datname) as size from pg_database;
Datname | Size
-----------+-------------
Template0 | 6513156
Postgres | 6657144
JBoss | 6521348
Bugs | 6521348
David | 190534776
BMCV3 | 28147135608
MyDB | 10990712
template1 | 6521348
(8 rows)
david=#
The results looked too long to be easy to read.
3.3 Display size in a humane way
Copy Code code as follows:
david=# Select Pg_size_pretty (pg_database_size (' David '));
Pg_size_pretty
----------------
MB
(1 row)
david=#
3.4 View Single Index size
Copy Code code as follows:
david=# Select Pg_relation_size (' idx_test ');
Pg_relation_size
------------------
(1 row)
david=# Select Pg_size_pretty (pg_relation_size (' idx_test '));
Pg_size_pretty
----------------
MB
(1 row)
david=#
Copy Code code as follows:
david=# Select Pg_size_pretty (pg_relation_size (' idx_join_date_test '));
Pg_size_pretty
----------------
MB
(1 row)
david=#
3.5 View all index sizes in the specified table
Copy Code code as follows:
david=# Select Pg_indexes_size (' Test ');
Pg_indexes_size
-----------------
(1 row)
david=# Select Pg_size_pretty (pg_indexes_size (' Test '));
Pg_size_pretty
----------------
MB
(1 row)
david=#
The Idx_test and idx_join_date_test two index sizes add up to almost equal the index size of the pg_indexes_size () query above.
3.6 View all the index sizes in the specified schema, in order from large to small.
Copy Code code as follows:
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 | MB
Idx_test | MB
Testtable_idx | 1424 KB
City_pkey | 256 KB
City11 | 256 KB
Countrylanguage_pkey | Mb
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
(rows)
david=#
3.7 View the specified table size
Copy Code code as follows:
david=# Select Pg_relation_size (' Test ');
Pg_relation_size
------------------
(1 row)
david=# Select Pg_size_pretty (pg_relation_size (' Test '));
Pg_size_pretty
----------------
MB
(1 row)
david=#
Use the Pg_table_size () function to view
Copy Code code as follows:
david=# Select Pg_table_size (' Test ');
Pg_table_size
---------------
(1 row)
david=# Select Pg_size_pretty (pg_table_size (' Test '));
Pg_size_pretty
----------------
MB
(1 row)
david=#
3.8 View the total size of the specified table
Copy Code code as follows:
david=# Select Pg_total_relation_size (' Test ');
Pg_total_relation_size
------------------------
(1 row)
david=# Select Pg_size_pretty (pg_total_relation_size (' Test '));
Pg_size_pretty
----------------
MB
(1 row)
david=#
3.9 View all the table sizes in the specified schema, in order from large to small.
Copy Code code as follows:
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 | MB
TestTable | 1424 KB
City | 256 KB
Countrylanguage | Mb
Country | 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
(rows)
david=#
3.10 View Table Space size
Copy Code code as follows:
david=# select Spcname from Pg_tablespace;
Spcname
------------
Pg_default
Pg_global
(2 rows)
david=# Select Pg_tablespace_size (' Pg_default ');
Pg_tablespace_size
--------------------
(1 row)
david=# Select Pg_size_pretty (pg_tablespace_size (' Pg_default '));
Pg_size_pretty
----------------
GB
(1 row)
david=#
Another way to view:
Copy Code code as follows:
david=# Select Pg_tablespace_size (' Pg_default ')/1024/1024 as "size M";
SIZE M
--------
(1 row)
david=# Select Pg_tablespace_size (' Pg_default ')/1024/1024/1024 as "size G";
SIZE G
--------
(1 row)
david=#