Обсуждение: DB size and TABLE sizes don't seem to add up
I am running PG 9.2.4 and I am trying to figure out why my database size shows one value, but the sum of my total relation sizes is so much less.
Basically, I'm told my database is 188MB, but the sum of my total relation sizes adds up to just 8.7MB, which is 1/20th of the reported total. Where is the 19/20th of my data then? We do make significant use of large objects, so I suspect it's in there. Is there a relation size query that would include the large object data associated with any OIDs in those tables?
Here's the data I am working off of:
First, I run a query to get my total DB size (this is after a restore from a backup, so it should not have too many "holes"):
bpn=# SELECT pg_size_pretty(pg_database_size('bpn'));
pg_size_pretty
----------------
188 MB
(1 row)
Second, I run this query (from http://wiki.postgresql.org/wiki/Disk_Usage) to get the total relation sizes for the tables in that database:
bpn=# SELECT nspname || '.' || relname AS "relation",
bpn-# pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
bpn-# FROM pg_class C
bpn-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
bpn-# WHERE nspname NOT IN ('pg_catalog', 'information_schema')
bpn-# AND C.relkind <> 'i'
bpn-# AND nspname !~ '^pg_toast'
bpn-# ORDER BY pg_total_relation_size(C.oid) DESC;
relation | total_size
---------------------------------------------------------+------------
public.esf_outbound_email_message | 1624 kB
public.esf_transaction_activity_log | 968 kB
public.esf_blob | 560 kB
public.esf_outbound_email_message_attachment | 552 kB
public.esf_tran_report_field_string | 232 kB
public.esf_system_activity_log | 192 kB
public.esf_permission_option_group | 184 kB
public.esf_library_document_version_page | 176 kB
public.esf_transaction | 152 kB
public.esf_transaction_party | 136 kB
public.esf_library_dropdown_version_option | 128 kB
public.esf_signature_key | 112 kB
public.esf_transaction_document | 104 kB
public.esf_permission_option | 96 kB
public.esf_library_email_template_version | 96 kB
public.esf_transaction_party_document | 96 kB
public.esf_field_template | 88 kB
public.esf_transaction_party_assignment | 88 kB
public.esf_outbound_email_message_response | 88 kB
public.esf_user_activity_log | 88 kB
public.esf_library_buttonmessage | 80 kB
public.esf_library_email_template | 80 kB
public.esf_library_documentstyle | 80 kB
public.esf_package | 80 kB
public.esf_package_version_party_template | 80 kB
public.esf_permission | 80 kB
public.esf_report_template | 80 kB
public.esf_transaction_template | 80 kB
public.esf_user | 80 kB
public.esf_userlogin | 80 kB
public.esf_group | 80 kB
public.esf_library | 80 kB
public.esf_library_document | 80 kB
public.esf_library_dropdown | 80 kB
public.esf_stats | 80 kB
public.esf_library_image | 80 kB
public.esf_library_propertyset | 80 kB
public.esf_package_version | 72 kB
public.esf_package_version_document | 72 kB
public.esf_report_template_report_field | 72 kB
public.esf_library_document_version | 72 kB
public.esf_library_documentstyle_version | 72 kB
public.esf_group_user | 72 kB
public.esf_report_field_template | 72 kB
public.esf_library_buttonmessage_version | 72 kB
public.esf_library_propertyset_version | 72 kB
public.esf_library_dropdown_version | 72 kB
public.esf_party_template_field_template | 72 kB
public.esf_library_image_version | 72 kB
public.esf_party_template | 72 kB
public.esf_label_template | 56 kB
public.esf_library_document_version_page_field_template | 56 kB
public.esf_package_version_report_field | 56 kB
public.esf_package_version_party_document_party | 56 kB
public.esf_session_key | 56 kB
public.esf_userlogin_history | 56 kB
public.esf_deployment | 56 kB
public.esf_report_template_transaction_template | 56 kB
public.esf_library_serial | 24 kB
public.esf_http_send_request | 24 kB
public.esf_library_file | 24 kB
public.esf_tran_report_field_tranfileid | 16 kB
public.esf_library_file_version | 16 kB
public.esf_tran_report_field_long | 16 kB
public.esf_http_send_response | 16 kB
public.esf_tran_report_field_date | 16 kB
public.esf_tran_report_field_numeric | 16 kB
public.esf_library_serial_version | 16 kB
public.esf_transaction_file | 16 kB
public.esf_transaction_party_renotify | 16 kB
public.esf_library_image_version_overlay_field | 8192 bytes
(71 rows)
But when I add up those 71 rows, it's only 8,728,192 bytes (roughly 8.7MB).
Basically, I'm told my database is 188MB, but the sum of my total relation sizes adds up to just 8.7MB, which is 1/20th of the reported total. Where is the 19/20th of my data then? We do make significant use of large objects, so I suspect it's in there. Is there a relation size query that would include the large object data associated with any OIDs in those tables?
Here's the data I am working off of:
First, I run a query to get my total DB size (this is after a restore from a backup, so it should not have too many "holes"):
bpn=# SELECT pg_size_pretty(pg_database_size('bpn'));
pg_size_pretty
----------------
188 MB
(1 row)
Second, I run this query (from http://wiki.postgresql.org/wiki/Disk_Usage) to get the total relation sizes for the tables in that database:
bpn=# SELECT nspname || '.' || relname AS "relation",
bpn-# pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
bpn-# FROM pg_class C
bpn-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
bpn-# WHERE nspname NOT IN ('pg_catalog', 'information_schema')
bpn-# AND C.relkind <> 'i'
bpn-# AND nspname !~ '^pg_toast'
bpn-# ORDER BY pg_total_relation_size(C.oid) DESC;
relation | total_size
---------------------------------------------------------+------------
public.esf_outbound_email_message | 1624 kB
public.esf_transaction_activity_log | 968 kB
public.esf_blob | 560 kB
public.esf_outbound_email_message_attachment | 552 kB
public.esf_tran_report_field_string | 232 kB
public.esf_system_activity_log | 192 kB
public.esf_permission_option_group | 184 kB
public.esf_library_document_version_page | 176 kB
public.esf_transaction | 152 kB
public.esf_transaction_party | 136 kB
public.esf_library_dropdown_version_option | 128 kB
public.esf_signature_key | 112 kB
public.esf_transaction_document | 104 kB
public.esf_permission_option | 96 kB
public.esf_library_email_template_version | 96 kB
public.esf_transaction_party_document | 96 kB
public.esf_field_template | 88 kB
public.esf_transaction_party_assignment | 88 kB
public.esf_outbound_email_message_response | 88 kB
public.esf_user_activity_log | 88 kB
public.esf_library_buttonmessage | 80 kB
public.esf_library_email_template | 80 kB
public.esf_library_documentstyle | 80 kB
public.esf_package | 80 kB
public.esf_package_version_party_template | 80 kB
public.esf_permission | 80 kB
public.esf_report_template | 80 kB
public.esf_transaction_template | 80 kB
public.esf_user | 80 kB
public.esf_userlogin | 80 kB
public.esf_group | 80 kB
public.esf_library | 80 kB
public.esf_library_document | 80 kB
public.esf_library_dropdown | 80 kB
public.esf_stats | 80 kB
public.esf_library_image | 80 kB
public.esf_library_propertyset | 80 kB
public.esf_package_version | 72 kB
public.esf_package_version_document | 72 kB
public.esf_report_template_report_field | 72 kB
public.esf_library_document_version | 72 kB
public.esf_library_documentstyle_version | 72 kB
public.esf_group_user | 72 kB
public.esf_report_field_template | 72 kB
public.esf_library_buttonmessage_version | 72 kB
public.esf_library_propertyset_version | 72 kB
public.esf_library_dropdown_version | 72 kB
public.esf_party_template_field_template | 72 kB
public.esf_library_image_version | 72 kB
public.esf_party_template | 72 kB
public.esf_label_template | 56 kB
public.esf_library_document_version_page_field_template | 56 kB
public.esf_package_version_report_field | 56 kB
public.esf_package_version_party_document_party | 56 kB
public.esf_session_key | 56 kB
public.esf_userlogin_history | 56 kB
public.esf_deployment | 56 kB
public.esf_report_template_transaction_template | 56 kB
public.esf_library_serial | 24 kB
public.esf_http_send_request | 24 kB
public.esf_library_file | 24 kB
public.esf_tran_report_field_tranfileid | 16 kB
public.esf_library_file_version | 16 kB
public.esf_tran_report_field_long | 16 kB
public.esf_http_send_response | 16 kB
public.esf_tran_report_field_date | 16 kB
public.esf_tran_report_field_numeric | 16 kB
public.esf_library_serial_version | 16 kB
public.esf_transaction_file | 16 kB
public.esf_transaction_party_renotify | 16 kB
public.esf_library_image_version_overlay_field | 8192 bytes
(71 rows)
But when I add up those 71 rows, it's only 8,728,192 bytes (roughly 8.7MB).
On 02/18/2014 12:14 AM, David Wall wrote:
> I am running PG 9.2.4 and I am trying to figure out why my database size
> shows one value, but the sum of my total relation sizes is so much less.
>
> Basically, I'm told my database is 188MB, but the sum of my total
> relation sizes adds up to just 8.7MB, which is 1/20th of the reported
> total. Where is the 19/20th of my data then? We do make significant
> use of large objects, so I suspect it's in there. Is there a relation
> size query that would include the large object data associated with any
> OIDs in those tables?
You can use "select pg_total_relation_size('pg_largeobject')" to get the
total size of the large objects. Attributing large objects to the tables
that refer them is more difficult. For a single table, something like this:
select sum(pg_column_size(lo.data))
from lotest_stash_values t, pg_largeobject lo
where lo.loid = t.loid;
Replace "lotest_stash_values" with the table's name and lo.loid with the
name of the OID column.
- Heikki
On 2/18/2014 12:34 AM, Heikki Linnakangas wrote:
> On 02/18/2014 12:14 AM, David Wall wrote:
>> I am running PG 9.2.4 and I am trying to figure out why my database size
>> shows one value, but the sum of my total relation sizes is so much less.
>>
>> Basically, I'm told my database is 188MB, but the sum of my total
>> relation sizes adds up to just 8.7MB, which is 1/20th of the reported
>> total. Where is the 19/20th of my data then? We do make significant
>> use of large objects, so I suspect it's in there. Is there a relation
>> size query that would include the large object data associated with any
>> OIDs in those tables?
>
> You can use "select pg_total_relation_size('pg_largeobject')" to get
> the total size of the large objects. Attributing large objects to the
> tables that refer them is more difficult. For a single table,
> something like this:
>
> select sum(pg_column_size(lo.data))
> from lotest_stash_values t, pg_largeobject lo
> where lo.loid = t.loid;
>
> Replace "lotest_stash_values" with the table's name and lo.loid with
> the name of the OID column.
Thanks, Heikki. It's generally even trickier for us because we have a
blob table that other components use for storing
large/binary/unstructured objects (the code handles
compression/decompression and encryption/decryption options for us). So
those tables have an UUID that points to a row in that table that
contains the actual LOID. I'll use your technique to at least tell me
the size for specific tables where I can build the query like you've
described.