How To Find The Size Of A Schema Or Table (Including Toast Tables)

IT Support Forum Forums Databases PostgreSQL General Discussion How To Find The Size Of A Schema Or Table (Including Toast Tables)

This topic contains 0 replies, has 1 voice, and was last updated by  Webmaster 3 weeks, 2 days ago.

  • Author
    Posts
  • #2411

    Webmaster
    Keymaster

    Today I’ve been struggling with Postgres, trying to find out which table(s) is taking up all the hard drive space. My approach was to see which is the largest schema(s), then see which is the largest table (including toast table(s)) and work from there.

    How To Find The Size Of A Schema In Postgres

    The following postgres script shows the size of each schema in a Postgres database:

    SELECT schema_name,
    pg_size_pretty(sum(table_size)::bigint),
    (sum(table_size) / pg_database_size(current_database())) * 100
    FROM (
    SELECT pg_catalog.pg_namespace.nspname as schema_name,
    pg_relation_size(pg_catalog.pg_class.oid) as table_size
    FROM pg_catalog.pg_class
    JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
    ) t
    GROUP BY schema_name
    ORDER BY schema_name

    This gives you a good overview of what’s going on.

    How To Find The Size Of All Tables

    The next script shows the size of all tables, but excludes system and Toast 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 150;

    If your size problem resides in one of these tables, you can fix that. However, if it doesn’t, it may be in a Toast table.

    How To Find The Size Of A Toast Table

    The Toast table is like an overflow table for data that doesn’t fit into other tables allocated space. There is actually a Toast table for each table that requires a Toast table. Here’s how to find the size of all Toast tables in Postgres:

    SELECT nspname || ‘.’ || relname AS “relation”,
    pg_size_pretty(pg_total_relation_size(C.oid)) AS “total_size”,
    C.oid
    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 150;

    How To Find Out What Table A Toast Table Corresponds To

    Once you’ve found the size of the Toast table, you may want to see what table that Toast table corresponds to. To do this, use the value in the Oid column from the script above, to find the name of the corresponding table via this script:

    select oid::regclass from pg_class where reltoastrelid=’1273510250′::regclass;

    You’ll then need to do something with the data to fix the size problem (delete some of it, truncate it, delete the table, etc.), then run a FULL Vacuum to release the space from the disk. Don’t forget that you’ll need at least the same amount of disk space as the table that you’re vacuuming so the new table can be written to disk before the old one is cleaned up (though, if you truncated the data, that’ll be ~0kb).

You must be logged in to reply to this topic.