Code to Report Table Index Schema or Database Fragmentation in PostgreSQL

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.

Subscribe to get access

Read more of this content when you subscribe today.

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.