PostgreSQL Technologies

Find top 10 large tables in the current PostgreSQL database

SELECT
table_schema || '.' || table_name AS TableName,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSize
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 10;


Output:

+------------------------------------+-----------+
| tablename | tablesize |
+------------------------------------+-----------+
| schema1.access | 904 MB |
| schema1.activity | 511 MB |
| schema1.sync | 479 MB |
| schema1.recon_back | 143 MB |
+------------------------------------+-----------+

Total size of the table Including Index(s):

SELECT pg_size_pretty(pg_total_relation_size('schema.table_name'));

+----------------+
| pg_size_pretty |
+----------------+
| 904 MB |
+----------------+

Total size of the table without Index(s):

SELECT pg_size_pretty(pg_relation_size('schema.table_name'));

+----------------+
| pg_size_pretty |
+----------------+
| 662 MB |
+----------------+

Find the total size of the index

SELECT pg_size_pretty(pg_indexes_size('schema.table_name'));

+----------------+
| pg_size_pretty |
+----------------+
| 238 MB |
+----------------+

Find all the table(s) and index(s) size in the current database :

SELECT
TableName
,pg_size_pretty(pg_table_size(TableName)) AS TableSize
,pg_size_pretty(pg_indexes_size(TableName)) AS IndexSize
,pg_size_pretty(pg_total_relation_size(TableName)) AS TotalSize
FROM
(
SELECT ('"' || table_schema || '"."' || table_name || '"') AS TableName
FROM information_schema.tables
) AS Tables
ORDER BY 4 DESC;


+------------------------------------------------+-----------+------------+------------+
| tablename | tablesize | indexsize | totalsize |
+------------------------------------------------+-----------+------------+------------+
| "schema"."managedobjectproperties" | 1528 kB | 8320 kB | 9848 kB |
| "schema"."sql_features" | 96 kB | 0 bytes | 96 kB |
+------------------------------------------------+-----------+------------+------------+


Want to check the size of a database (or) all the databases on PostgreSQL server, click here

Refer pg_database_sizepg_indexes_size functions documentation here


Comments Rating 0 (0 reviews)

About the author

BestTechReads

Add Comment

Click here to post a comment

Sending

Categories

Categories