We are always asked by a client or our manager about how fragmented a table, an index, a schema or whole database is in PostgreSQL database. The following code will answer this question with the following information reported for a schema.
Toal 7415.00 MB, Free 1918.87 MB, Fragmentation 25.88 Percent
Please do NOT run the code during busy or peak hours, ideally run the code in after hours or off-peak hours.
Read more of this content when you subscribe today.
1) Database Size in PostgreSQL.
testdb=> SELECT pg_size_pretty ( pg_database_size ('testdb') );
pg_size_pretty
----------------
136 GB
(1 row)
testdb=>select pg_database.datname, pg_size_pretty(pg_database_size(datname)) AS DBSIZE from pg_database; datname | dbsize -----------+--------- template0 | 7873 kB rdsadmin | 8125 kB template1 | 8037 kB postgres | 8037 kB testdb | 136 GB (5 rows)
2) Tablespace size in PostgreSQL.
testdb=> select pg_size_pretty ( pg_tablespace_size ('pg_default'));
pg_size_pretty
----------------
136 GB
(1 row)
testdb=> select pg_tablespace.spcname, pg_size_pretty(pg_tablespace_size(spcname)) AS "TBLSPACESIZE" from pg_tablespace; spcname | TBLSPACESIZE ------------+-------------- pg_default | 136 GB pg_global | 774 kB (2 rows)
3)Table size in PostgreSQL.
— Specify the disk space used by the table OID or table name, excluding the index (but containing TOAST, free space mapping, and visual mapping).
testdb=> select pg_size_pretty( pg_table_size('testuser.test'));
pg_size_pretty
----------------
10 GB
(1 row)
testdb=> select pg_size_pretty( pg_relation_size('testuser.test'));
pg_size_pretty
----------------
10 GB
(1 row)
— Specify the total disk space used by the table OID or table name, including all indexes and TOAST data.
testdb=> select pg_size_pretty( pg_total_relation_size('testuser.test'));
pg_size_pretty
----------------
16 GB
(1 row)
4)Index size in PostgreSQL.
— total disk space associated with table index of specified table OID or table name.
testdb=> select pg_size_pretty( pg_indexes_size('testuser.test'));
pg_size_pretty
----------------
5377 MB
(1 row)
-- get all index names of table testuser.test
testdb=>\d testuser.test
...
..
.
-- get size of index
testdb=> select pg_size_pretty( pg_relation_size('testuser.ix_test_id'));
pg_size_pretty
----------------
942 MB
(1 row)
5) Top 10 biggest tables.
testdb=> select schemaname,tablename, pg_size_pretty( pg_table_size(schemaname||'.'||tablename)) TABSIZE, pg_table_size(schemaname||'.'||tablename) TABSIZEINBYTES from pg_tables order by 4 desc limit +10; schemaname | tablename | tabsize | tabsizeinbytes ------------+-------------------+---------+--------------- testuser | cdr | 44 GB | 47675359232 testuser | test | 10 GB | 11257372672 ... .. . (10 rows)
6) Top 10 tables with biggest indexes.
testdb=> select schemaname,tablename, pg_size_pretty( pg_indexes_size(schemaname||'.'||tablename)) IDXSIZE, pg_indexes_size(schemaname||'.'||tablename) IDXSIZEINBYTES from pg_tables order by 4 desc limit +10; schemaname | tablename | idxsize | idxsizeinbytes -----------+-----------+----------+---------------- testuser | cdr | 29 GB | 31294144512 testuser | test | 5379 MB | 5640585216 ... .. . (10 rows)
7) Top 10 biggest indexes.
testdb=> select schemaname,tablename,indexname, pg_size_pretty(pg_relation_size(schemaname||'.'||indexname)) idxsize, pg_relation_size(schemaname||'.'||indexname) indexsizeinbytes from pg_indexes order by 5 desc limit +10; schemaname | tablename | indexname | idxsize | indexsizeinbytes ------------+-----------+-----------+---------+------------------ testuser | cdr | pk_cdr | 4045 MB | 4241883136 testuser | test | ix_pk | 2777 MB | 2912100352 ... .. . (10 rows)
8) Top 10 biggest tables ( including indexes, TOAST…).
testdb=> select schemaname,tablename, pg_size_pretty( pg_total_relation_size(schemaname||'.'||tablename)) relsize, pg_total_relation_size(schemaname||'.'||tablename) relsizeinbytes from pg_tables order by 4 desc limit +10; schemaname | tablename | relsize | relsizeinbytes ------------+-----------+---------+---------------- testuser | cdr | 74GB | 78969503744 testuser | test | 16 GB | 16900882432 testuser | test1 | 13 GB | 13817872384 ... .. . (10 rows)
| Function name | Return type | describe |
| pg_column_size(any) | int | The number of bytes required to store a specified value (possibly compressed) |
| pg_database_size(oid) | bigint | Specify the disk space used by the OID database |
| pg_database_size(name) | bigint | Disk space for specified name database |
| pg_indexes_size(regclass) | bigint | Use total disk space associated with table index of specified table OID or table name |
| pg_relation_size(relation regclass, fork text) | bigint | Specify the table or index of OID or name by specifying fork(‘main‘, Disk space used by’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 into a human-readable format with size units |
| pg_size_pretty(numeric) | text | Converting a byte-based value into a human readable unit of size |
| pg_table_size(regclass) | bigint | Specify the disk space used by the table OID or table name, excluding the index (but containing TOAST, free space mapping, and visual mapping) |
| pg_tablespace_size(oid) | bigint | Specify the disk space used by the table space of the OID |
| pg_tablespace_size(name) | bigint | Disk space used by the specified name table space |
| pg_total_relation_size(regclass) | bigint | Specify the total disk space used by the table OID or table name, including all indexes and TOAST data |
As we know, a normal index does not include NULL value, so SQL query won’t use this normal index for a SQL query with condition ‘IS NULL’.
Here is a tip for how to create an index on column with NULL value.
SQL> create index IDX_BATCH_ID_NULL on test ( BATCH_ID, 1); Index created.
Actually the index is created by including two columns of BATCH_ID and constant number 1.
SQL> select count(*) from test where BATCH_ID is null;
COUNT(*)
----------
292
SQL> select count(*) from test where BATCH_ID is not null;
COUNT(*)
----------
172
SQL> create index idx_BATCH_ID on test(BATCH_ID);
Index created.
SQL> SET AUTOTRACE ON EXPLAIN
SQL> select MACHINE_ID from test
where BATCH_ID is not null and rownum<=2;
MACHINE_ID
----------------------
MACHINE_1
MACHINE_2
2 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3931117773
--------------------------------------------------------------------
| Id | Operation | Name|Rows| Bytes | Cost(%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2| 130 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | TABLE ACCESS FULL|TEST | 172| 11180 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------
SQL> select MACHINE_ID from test
where BATCH_ID is null and rownum<=2;
MACHINE_ID
---------------------------------------------
MACHINE_3
MACHINE_4
Execution Plan
----------------------------------------------------------
Plan hash value: 3931117773
-----------------------------------------------------------------
|Id| Operation |Name|Rows|Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------
|0 | SELECT STATEMENT | | 2 | 650 | 2 (0) | 00:00:01 |
|*1| COUNT STOPKEY | | | | | |
|*2| TABLE ACCESS FULL|TEST|292 |18980 | 2 (0) | 00:00:01 |
-------------------------------------------------------------------
SQL> create index idx_BATCH_ID_null on test ( BATCH_ID, 1);
Index created.
SQL> select MACHINE_ID from test
where BATCH_ID is null and rownum<=2;
MACHINE_ID
------------------------------------------------------
MACHINE_3
MACHINE_4
Execution Plan
----------------------------------------------------------
Plan hash value: 1426346817
--------------------------------------------------------------------
|Id| Operation |Name|Rows|Bytes|Cost(%CPU)| Time |
---------------------------------------------------------------------
|0 |SELECT STATEMENT | | 2 | 130 | 3 (0)|00:00:01|
|*1| COUNT STOPKEY | | | | | |
| 2| TABLE ACCESS BY INDEX ROWID|TEST|292 |18980| 3 (0)|00:00:01|
|*3| INDEX RANGE SCAN | IDX_BATCH_ID_NULL
| 23 | | 2 (0)|00:00:01|
----------------------------------------------------------------------
This is often used script to kill PostgreSQL session(s).
pg_terminate_backend() AND pg_cancel_backend()
1) kill all connections of a specified database name.
-- List all available databases
postgres=>\l
postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = 'DATABASE NAME'
AND pid <> pg_backend_pid();
2) kill all connections of a specified user name.
postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE usename = 'testuser'
AND pid <> pg_backend_pid();
3) kill all sessions of current database.
postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()
AND pid <> pg_backend_pid();
4) kill a connection of a specified pid.
postgres=> SELECT pg_terminate_backend(pid);
5) kill a running SQL of a session, but does not kill the session.
postgres=> SELECT pg_cancel_backend( pid );
It is useful when a running SQL is taking long time than expected.
Make DBA Life Easy 