CODEWITHSUNDEEP
djangopostgresqlpsycopg2
Dan K
Dan K

Reputation: 803

PostgreSQL pg_database_size different from sum of pg_total_relation_size

Currently, my development database is taking up way more disk space than it probably should. The project uses Django, Docker-Machine, and PostgreSQL. The weird thing is that when I use pg_database_size(), it says that around 9MB of disk space is used (the uploaded file is only 20KB). However, when I sum up the sizes of all the tables (i.e. summing up the results of doing pg_total_relation_size() on each table name returned by the query SELECT relname FROM pg_class WHERE relkind='r' AND relname !~ '^(pg_|sql_)';), the total size reported is only 1.8MB. I'm using psycopg2 to query my database.

Why exactly is there such a difference in sizes? I saw a similar sort of question where the answer seemed to be to vacuum any LOBs that weren't properly deleted. I did the following:

docker exec -it <name_of_database_container> vacuumlo -U postgres -W -v postgres

and it reported that it successfully removed 0 LOBs, so it seems like LOBs were never my issue. What else could be going on? Am I not correctly getting all the table names using the SELECT relname.... query?

Upvotes: 1

Views: 2135

Answers (2)

Danny G
Danny G

Reputation: 611

The documentation for pg_total_relation_size says that it includes the size of indexes with it, but that wasn't the case with my database. That may have been because I created the indexes with an explicit name rather than anonymous indexes.

Here are some queries to help track down where space is going:

-- total database size on disk
select * from pg_size_pretty(pg_database_size('etlq'));

-- total sizes by schema
select nspname, pg_size_pretty(sum(pg_total_relation_size(C.oid)))
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
GROUP BY nspname;

-- total size by relation
select nspname, relname, pg_size_pretty(pg_total_relation_size(C.oid)), pg_total_relation_size(c.oid)
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE true
and pg_total_relation_size(C.oid) > 10 * 1024 * 1024 -- greater than 10MB
ORDER BY pg_total_relation_size(C.oid) DESC, nspname;

Upvotes: 0

jjanes
jjanes

Reputation: 44192

Your pg_total_relation_size query is excluding system tables that start with pg_. Those tables do still take up space.

The minimum possible (non-broken) database is around 7.2MB in my hands. So the numbers you give work out.

Upvotes: 2

Related Questions