How to Get Database, Tablespace,Table or Index Size in PostgreSQL

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)

Functions of database object

Function nameReturn typedescribe
pg_column_size(any)intThe number of bytes required to store a specified value (possibly compressed)
pg_database_size(oid)bigintSpecify the disk space used by the OID database
pg_database_size(name)bigintDisk space for specified name database
pg_indexes_size(regclass)bigintUse total disk space associated with table index of specified table OID or table name
pg_relation_size(relation regclass, fork text)bigintSpecify the table or index of OID or name by specifying fork(‘main‘, Disk space used by’fsm’or’vm’
pg_relation_size(relation regclass)bigintAbbreviation for pg_relation_size(…,’main’)
pg_size_pretty(bigint)textConverts a size in bytes expressed as a 64-bit integer into a human-readable format with size units
pg_size_pretty(numeric)textConverting a byte-based value into a human readable unit of size
pg_table_size(regclass)bigintSpecify 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)bigintSpecify the disk space used by the table space of the OID
pg_tablespace_size(name)bigintDisk space used by the specified name table space
pg_total_relation_size(regclass)bigintSpecify the total disk space used by the table OID or table name, including all indexes and TOAST data

Indexing NULL Value in a Table Column

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

PostgreSQL: Script to Kill Sessions

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.