Difference in the size of database size and relations

Поиск
Список
Период
Сортировка
От Luis
Тема Difference in the size of database size and relations
Дата
Msg-id CAM-gcbSfTp6co+vo5s6UMy4b9oGf9yLFZX9WJKrBR1=P8BoXzw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Difference in the size of database size and relations
Re: Difference in the size of database size and relations
Список pgsql-admin
Hi,

I have noticed a weird thing in different databases.
When I run pg_database_size on some databases they report a pretty
high value and if I take a look to all relations the sum of all of
them is not even the half of the one reported by pg_database_size.

Although I've seen this in different databases, this is a specific example.


This are the sizes reported by this query:

SELECT pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;


  size
---------
 99 MB
 45 MB
 10 MB
 8528 kB
 5280 kB
 4352 kB
 3184 kB
 1304 kB
 1152 kB
 896 kB
 544 kB
 528 kB
 336 kB
 336 kB
 336 kB
 304 kB
 296 kB
 288 kB
 272 kB
 248 kB

And this is the database size reported by this query:
SELECT pg_size_pretty(pg_database_size('mydb'));

 pg_size_pretty
----------------
 3539 MB

I've read about LOBs but I don't seem to have any.

Any idea where the space is being used?

Thanks


В списке pgsql-admin по дате отправления:

Предыдущее
От: Payal Singh
Дата:
Сообщение: Re: Cold backup with rsync -- WAL files?
Следующее
От: Ian Lawrence Barwick
Дата:
Сообщение: Re: Difference in the size of database size and relations