PostgreSQL view database, index, table, table space Size sample code _postgresql

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=#