Question 258291 in PostgreSQL Question posted in
The official documentation can be found here.

How to list tables of specific database and sort them by size with PostgreSQL?

I have tried to select a list of databases and select all tables from each database, ordering the first ten tables by their size – including their indexes- through PL/pgSQL, but I have had a problem to join the query for the database with the query for the tables and their sizes included in the specific database.

I have started like this:

    DO $$
DECLARE
  database_name pg_database.datname%TYPE;
  total_table_size pg_tables.tablename%TYPE;
    rec record;
BEGIN
    FOR rec IN SELECT datname
  FROM pg_database
LOOP
  database_name := rec.datname;
 
  raise notice 'Database name: %', database_name;
 
  SELECT tablename,
  pg_total_relation_size(table_name) AS total_table_size
  FROM pg_tables
  INTO table_name, total_table_size
  ORDER BY pg_total_relation_size(relid) DESC
  LIMIT 10;
 END LOOP;
END;
$$;

I do not know how to specify that I want the first ten table names and their sizes ordered from the largest to the smallest of the present selected database. Could anyone help me with it, please?

I thought about joining the queries somehow, but I have not found a column that I could use to make the join.

I have searched similar problems in the community, but I did not find something so specific.

Thanks in advance.

Tags:

2

Answers


  1. 0

    If you are connected to 1 database I am not sure you can get information from another database. Possibly it could be done with a dblink.

    Below will give a list of table sizes within the current database

        SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size
FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         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
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;
    Login or Signup to reply.
  2. 0
    1. You’re already limiting the result to just top 10 rows through the addition of LIMIT 10. You’re just duplicating the same 10 rows as many times as there are databases in your catalog, but still those are results for the current database you’re connected to.
    2. You’re probably familiar with all Database Object Size Functions but while it’s good to order by the raw byte size, run it through pg_size_pretty() in your select section to make it readable.
    3. Don’t forget about schemas/namespaces. You can have multiple of those in a single database, and a table of the same name in each one. In pg_tables it’s under schemaname, in information_schema.tables it’s under table_schema. The recommendation is

      Since the information schema is SQL-standard whereas the views described here are PostgreSQL-specific, it’s usually better to use the information schema if it provides all the information you need.

    4. While you can list all databases available on the cluster in pg_database catalog, there’s no cluster-wide catalog for namespaces and tables. You need postgres_fdw or dblink for databases to interact.
    5. CREATE VIEW v_pg_total_relation_sizes on each of your databases (here are examples you can just prepend with create view ... as). To make it present by default in databases created in the future, create one in template1 database as well. Make them visible through postgres_fdw, then create a view that’s a union of the local one and all the linked ones. Depending on how large your databases are, if this gets slow you might want to consider making it a materialized view to cache it.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search