Skip to content

Instantly share code, notes, and snippets.

@Bklyn

Bklyn/object-size.sql

Created April 15, 2015 21:24
Show Gist options
  • Save Bklyn/071f78988626d2a5e88d to your computer and use it in GitHub Desktop.
Save Bklyn/071f78988626d2a5e88d to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL Object Size
Raw
object-size.sql
-- From: https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database
-- Check size of tables and objects in PostgreSQL database
-- Created by Rinn Bernd (ID SIS), last modified on Oct 11, 2011 Go to start of metadata
-- To get an overview about how much space is taken by what database, call:
SELECT
pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;
To get more details, call:
SELECT
relname as "Table",
pg_size_pretty(pg_total_relation_size(relid)) As "Size",
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
-- If you want to dig in deeper and see the size of all objects (at least of 64kB):
SELECT
relname AS objectname,
relkind AS objecttype,
reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_class
WHERE relpages >= 8
ORDER BY relpages DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment