Re: Unexpectedly high disk space usage

On 11/7/12 3:58 PM, Jeff Janes wrote:
>>                  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
>
> I question the wisdom of that where clause (from the wiki)
>
> If the pg_catalog relations are big, then they are big and why
> shouldn't they get reported as such?  And if they are not big, that is
> why the limit is there on the wiki page.

The idea was that in a new database with a relatively small number of
tables, your own tables will be lost among the catalog data unless you
filter them out.  Testing against an install with a single real table,
the query there will show something like this right now:

     relation    | total_size
----------------+------------
  public.t       | 3568 kB
  public.t_k_seq | 8192 bytes

But if the filter on pg_catalog is removed, you get this instead:

           relation           | total_size
-----------------------------+------------
  public.t                    | 3568 kB
  pg_catalog.pg_depend        | 808 kB
  pg_catalog.pg_proc          | 752 kB
  pg_catalog.pg_attribute     | 568 kB
  pg_catalog.pg_rewrite       | 464 kB
  pg_catalog.pg_description   | 392 kB
  pg_catalog.pg_statistic     | 328 kB
  pg_catalog.pg_operator      | 208 kB
  pg_catalog.pg_collation     | 152 kB
  pg_catalog.pg_type          | 152 kB
  pg_catalog.pg_amop          | 136 kB
  pg_catalog.pg_class         | 136 kB
  pg_catalog.pg_constraint    | 112 kB
  pg_catalog.pg_conversion    | 104 kB
  pg_catalog.pg_index         | 88 kB
  pg_catalog.pg_amproc        | 80 kB
  pg_catalog.pg_opclass       | 80 kB
  pg_catalog.pg_ts_config_map | 80 kB
  pg_catalog.pg_cast          | 80 kB
  pg_catalog.pg_authid        | 72 kB

That is overload for a lot of people, and confusing to new users.
That's why I opted for the shorter version.

There's no perfect answer to all use cases here.  This sort of thing is
why there's three sets of queries for pg_stat_user_tables,
pg_stat_sys_tables, and pg_stat_all_tables.  The wiki disk space queries
aim to be like the user tables version from that trio.

Adding a note pointing out that you might want to remove pg_catalog and
see the size of those relations would be appropriate.  I wouldn't make
that the default case though, due to the issue highlighted above.  I'd
rather optimize the initially suggested query so that new users get
simple output, even if it means that might hide problems on larger
installs, where the catalog data became big.

The other way I sometimes balance these two requirements--want to show
all the big data, but not clutter small installs with the catalog--is to
make the filter size-based instead:

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
AND relpages > 100
ORDER BY pg_total_relation_size(C.oid) DESC
                                                        LIMIT 20;

On my trivial test install that gives me just the one user table:

  relation | total_size
----------+------------
  public.t | 3568 kB

While still showing larger catalog tables if they grow to be noticeable.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com