PostgreSQL looks at sample code for database index table and table space sizes
- 2020-05-06 11:51:48
- OfStack
1. Introduction
PostgreSQL provides several system administration functions to view the size of tables, indexes, table Spaces, and databases.
ii. Database object size function
function name return type describes pg_column_size(any) int The number of bytes (possibly compressed) required to store a specified value bigint Specifies the disk space pg_database_size(name) will use for OID's database bigint Specifies the name of the disk space pg_indexes_size(regclass) used by the database bigint The total used disk space pg_relation_size(relation regclass, fork text) associated with a table index specifying the table OID or table name bigint Specify OID or the name of the table or index by specifying pg_relation_size(relation regclass) disk space to be used by fork('main', 'fsm' or 'vm') bigint pg_relation_size (... , 'main') for pg_size_pretty(bigint) text Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units pg_size_pretty(numeric) text Converts a numeric value in bytes into a human-readable unit of size, pg_table_size(regclass) bigint Specify the disk space to be used by a table OID or table name, removing the index (but containing TOAST, free-space mapping, and visual mapping) pg_tablespace_size(oid) bigint Specify the disk space pg_tablespace_size(name) will use for OID's table space bigint Specifies the disk space pg_total_relation_size(regclass) used by the name of the table space bigint Specifies the total disk space used by the table OID or table name, including all indexes and TOAST data
PostgreSQL provides several system administration functions to view the size of tables, indexes, table Spaces, and databases.
ii. Database object size function
function name return type describes pg_column_size(any) int The number of bytes (possibly compressed) required to store a specified value bigint Specifies the disk space pg_database_size(name) will use for OID's database bigint Specifies the name of the disk space pg_indexes_size(regclass) used by the database bigint The total used disk space pg_relation_size(relation regclass, fork text) associated with a table index specifying the table OID or table name bigint Specify OID or the name of the table or index by specifying pg_relation_size(relation regclass) disk space to be used by fork('main', 'fsm' or 'vm') bigint pg_relation_size (... , 'main') for pg_size_pretty(bigint) text Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units pg_size_pretty(numeric) text Converts a numeric value in bytes into a human-readable unit of size, pg_table_size(regclass) bigint Specify the disk space to be used by a table OID or table name, removing the index (but containing TOAST, free-space mapping, and visual mapping) pg_tablespace_size(oid) bigint Specify the disk space pg_tablespace_size(name) will use for OID's table space bigint Specifies the disk space pg_total_relation_size(regclass) used by the name of the table space bigint Specifies the total disk space used by the table OID or table name, including all indexes and TOAST data
3. Explanation of
with examples3.1 view the number of bytes
required to store a specified value
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 the database size
View the 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
---------
(1 row)
david=#
View david database size
david=# select pg_database_size('david');
pg_database_size
------------------
(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
postgres | 6657144
jboss | 6521348
bugs | 6521348
david | 190534776
BMCV3 | 28147135608
mydb | 10990712
template1 | 6521348
(8 rows)
david=#
The result looks too long to be easy to read.
3.3 display size
in a humanized way
david=# select pg_size_pretty(pg_database_size('david'));
pg_size_pretty
----------------
MB
(1 row)
david=#
3.4 view single index size
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=#
david=# select pg_size_pretty(pg_relation_size('idx_join_date_test'));
pg_size_pretty
----------------
MB
(1 row)
david=#
3.5 view
for all index sizes in the specified table
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 combined index sizes of idx_test and idx_join_date_test are approximately the same as the index sizes of pg_indexes_size() above.
3.6 view all the index sizes specified in schema, in descending 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
countrylanguage_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
------------------
(1 row)
david=# select pg_size_pretty(pg_relation_size('test'));
pg_size_pretty
----------------
MB
(1 row)
david=#
View
using the pg_table_size() function
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
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 specified in schema, in descending 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 table space size
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 it:
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=#