Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WFWSs-00068m-Il for pgsql-performance@arkaria.postgresql.org; Mon, 17 Feb 2014 22:15:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WFWSs-0000bJ-1x for pgsql-performance@arkaria.postgresql.org; Mon, 17 Feb 2014 22:15:02 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WFWSp-0000Vy-Mf for pgsql-performance@postgresql.org; Mon, 17 Feb 2014 22:15:00 +0000 Received: from smtp152.ord.emailsrvr.com ([173.203.6.152]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WFWSi-0005fD-6i for pgsql-performance@postgresql.org; Mon, 17 Feb 2014 22:14:55 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp16.relay.ord1a.emailsrvr.com (SMTP Server) with ESMTP id D75DF3D066C for ; Mon, 17 Feb 2014 17:14:48 -0500 (EST) X-Virus-Scanned: OK Received: by smtp16.relay.ord1a.emailsrvr.com (Authenticated sender: david.wall-AT-yozons.com) with ESMTPSA id 7E5543D0462 for ; Mon, 17 Feb 2014 17:14:47 -0500 (EST) Message-ID: <530289D4.20106@computer.org> Date: Mon, 17 Feb 2014 14:14:44 -0800 From: David Wall User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.3.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: DB size and TABLE sizes don't seem to add up Content-Type: multipart/alternative; boundary="------------020700050101000909070803" X-Pg-Spam-Score: -0.7 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------020700050101000909070803 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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). --------------020700050101000909070803 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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).



--------------020700050101000909070803--