1

I wonder why my database is so large. I type:

SELECT pg_size_pretty(pg_database_size('defaultdb'));

and it says defaultdb is 259 GB. That is huge.

So I list my tables in psql with:

\dt

For each table I type:

SELECT pg_size_pretty( pg_total_relation_size('tablename') );

I get:

city_zip 0.048 MB
ntaboundaries 1.9 MB
zipcodeboundaries 2.4 MB
spatial_ref_sys 7.2 MB

What is going on? Is pg_database_size() telling me memory allocated, not the storage memory used? Is there some other hidden location, like a log, that is hogging storage, and if so, how do I query the size of the logs?

1
  • Are you connected to the right database? Perhaps another database has all the data. Jun 15, 2021 at 22:02

1 Answer 1

2

pg_total_relation_size() sums up everything that belongs to a table, including bloat.

But \dt in psql does not list all tables of a database, it only includes schemas listed in the current search_path. Check with:

SHOW search_path;

System tables in pg_catalog are always excluded. And there may be additional schemas. To see all tables in the database:

\dt *.*

The manual:

Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path — this is equivalent to using * as the pattern. (An object is said to be visible if its containing schema is in the search path and no object of the same kind and name appears earlier in the search path. This is equivalent to the statement that the object can be referenced by name without explicit schema qualification.) To see all objects in the database regardless of visibility, use *.* as the pattern.

To see the biggest relations in your current database (make sure you are connected to the right one!):

SELECT pg_size_pretty(pg_total_relation_size(c.oid)), c.oid::regclass, relkind
FROM   pg_class c
ORDER  BY pg_total_relation_size(c.oid) DESC
LIMIT  10;

I used c.oid::regclass because that automatically displays tables with schema-qualification where needed to be visible.

Note that indexes (relkind = 'i') are included in the size of tables (relkind = 'r'), when asking with pg_total_relation_size(). But they are also listed separately in this list. Details about pg_class in the manual.

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.