PostgreSQL pg_database_size different from sum of pg_total_relation_size

Question

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?

jjanes · Accepted Answer · 2015-09-13 21:00:21Z

2

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.

answered Sep 13, 2015 at 21:00

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Danny G · Accepted Answer · 2015-12-14 20:41:54Z

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;

Collectives™ on Stack Overflow

PostgreSQL pg_database_size different from sum of pg_total_relation_size

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related