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)

Viewing 0 reply threads
  • 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).

Viewing 0 reply threads
  • You must be logged in to reply to this topic.