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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply