Postgres Database size and Table size sum do not match up

Question

I experienced a storage issue and then found that my database was taking up 300GB using:

SELECT pg_size_pretty(pg_database_size(current_database()))

Looking at the sum size of my schemas for this database, it only takes up 126 GB, which is 42%:

SELECT
   pg_size_pretty(sum(total_size)::bigint) as "total",
   pg_size_pretty(sum(relation_size_main)::bigint) as "main",
   pg_size_pretty(sum(relation_size_fsm)::bigint) as "fsm",
   pg_size_pretty(sum(relation_size_vm)::bigint) as "vm",
   pg_size_pretty(sum(relation_size_init)::bigint) as "init",
   pg_size_pretty(sum(table_size)::bigint) as "table",
   pg_size_pretty(sum(external_size)::bigint) as "external_size",
   (sum(total_size) / pg_database_size(current_database())) * 100 as percentage
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
     pg_total_relation_size(pg_catalog.pg_class.oid) as total_size,
     pg_relation_size(pg_catalog.pg_class.oid, 'main') as relation_size_main,
     pg_relation_size(pg_catalog.pg_class.oid, 'fsm') as relation_size_fsm,
     pg_relation_size(pg_catalog.pg_class.oid, 'vm') as relation_size_vm,
     pg_relation_size(pg_catalog.pg_class.oid, 'init') as relation_size_init,
     pg_table_size(pg_catalog.pg_class.oid) as table_size,
     pg_total_relation_size(pg_catalog.pg_class.oid) - pg_relation_size(pg_catalog.pg_class.oid) as external_size
  FROM pg_catalog.pg_class
  JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t;

Which returns:

"total","main","fsm","vm","init","table","external_size","percentage"
"126 GB","97 GB","63 MB","17 MB","0 bytes","99 GB","29 GB",41.91348045761441753100

So what takes up the other 58%?

NOTE: Doing a pg_dump, clearing the /var/lib/postgresql/data/ directory, restarting the server and reloading the SQL dump does solve this problem. But this is not a valid production solution.

Additional Info: I am using the Postgres Docker container and have stopped it multiple times in the past few months since it was crippling my laptop.

Credit to this post and this site for helping with table size extraction.

@LaurenzAlbe like what though, I'm hitting a blank this Monday morning — Werner Raath, Apr 12 '21 at 09:00
Hmm, yes, they must all be in `pg_class`. What may be left is orphaned temporary files or orphaned data files left behind from a crash during something like `VACUUM (FULL)`. Have there been any crashes? — Laurenz Albe, Apr 12 '21 at 10:21
I use the postgres docker and have often stopped it manually, so it could have happened then. Running a `VACUUM FULL` now to see if it makes a difference — Werner Raath, Apr 12 '21 at 10:32
If it is really orphaned files, that won't help. These files are hard to find. — Laurenz Albe, Apr 12 '21 at 10:37
Do you think I should do a dump, clean and restore? Sucks though, since this is the 3rd time this is happening — Werner Raath, Apr 12 '21 at 12:33
Yes, that would get rid of any orphaned files that remain from a crash. If I were you, I would make sure that PostgreSQL stops cleanly when you stop the container. — Laurenz Albe, Apr 12 '21 at 13:23
I think you can find your answers here https://dba.stackexchange.com/questions/121804/what-parts-make-up-pg-database-size or here https://dba.stackexchange.com/questions/109092/how-to-reclaim-space-taken-by-an-index-that-partially-built-and-was-terminated-b — tukan, Apr 16 '21 at 08:02
Hi @tukan, great! Could you please mark this question as a duplicate of https://dba.stackexchange.com/q/109092/207343 — Werner Raath, Apr 16 '21 at 09:11
You are welcome, I thought that the main topic here is the dirty shutdown. It is not possible to mark it as duplicate when it is not directly on SO, but on SE. I don't know how to deal with such situation... — tukan, Apr 16 '21 at 09:38

score 0 · Accepted Answer · answered Apr 16 '21 at 11:49

The issue appears to be connected to the dirty shutdown.

The solution is posted on dba stackexchange: How to reclaim space taken by an index that partially built and was terminated by a power outage

Should you suffer the same issue, check it for solution.

Postgres Database size and Table size sum do not match up

1 Answers1