DB size and TABLE sizes don't seem to add up

From: David Wall <d(dot)wall(at)computer(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: DB size and TABLE sizes don't seem to add up
Date: 2014-02-17 22:14:44
Message-ID: 530289D4.20106@computer.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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).

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeff Janes 2014-02-17 22:48:44 Re: Why is the optimiser choosing the slower query, or, understanding explain analyze output
Previous Message Alistair Bayley 2014-02-17 21:54:32 Why is the optimiser choosing the slower query, or, understanding explain analyze output