Skip to content

Instantly share code, notes, and snippets.

@hvanhonacker

hvanhonacker/pg_disk_usage.sql

Last active November 23, 2018 07:41
Show Gist options
  • Save hvanhonacker/0758e058bd66410e2cdd to your computer and use it in GitHub Desktop.
Save hvanhonacker/0758e058bd66410e2cdd to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Hugo revised this gist May 28, 2015. 1 changed file with 16 additions and 1 deletion.
    17 changes: 16 additions & 1 deletion pg_disk_usage.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,4 @@
    -- Finding the largest databases in your cluster
    SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
    @@ -9,4 +10,18 @@ FROM pg_catalog.pg_database d
    THEN pg_catalog.pg_database_size(d.datname)
    ELSE NULL
    END DESC -- nulls first
    LIMIT 20
    LIMIT 20


    -- Finding the total size of your biggest tables
    SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    ORDER BY pg_total_relation_size(C.oid) DESC
    LIMIT 20;

    -- https://wiki.postgresql.org/wiki/Disk_Usage
  2. Hugo created this gist May 28, 2015.
    12 changes: 12 additions & 0 deletions pg_disk_usage.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,12 @@
    SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
    ELSE 'No Access'
    END AS Size
    FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_database_size(d.datname)
    ELSE NULL
    END DESC -- nulls first
    LIMIT 20