Database is 300 GB, but I only see small tables?

Question

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?

Are you connected to the right database? Perhaps another database has all the data. — Laurenz Albe, Jun 15 '21 at 22:02

Erwin Brandstetter · Accepted Answer · 2021-06-16T17:21:28.040

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.

Measure the size of a PostgreSQL table row

Database is 300 GB, but I only see small tables?

1 Answers1