Can't explain db size

From: Matteo Grolla <matteo(dot)grolla(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Can't explain db size
Date: 2015-12-15 09:52:16
Message-ID: CAEW0--jM75OZPE8U1t=wBt4UvOJUirexQ0Ao5vZz=3oFmUvRwA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,
I hope you can help me understand why the db is so big and if there's
anything I can do.
It's the DB of an Enterprise Content Management application, Alfresco. Here
are some data I collected, after executing a vaccum from pg admin.

A) Largest tables sizes relation total_size
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20; public.alf_node_properties 17 GB
public.alf_node 2331 MB
public.alf_node_aspects 1939 MB
public.alf_child_assoc 1160 MB
public.alf_content_data 338 MB
public.alf_content_url 296 MB
public.alf_transaction 207 MB
public.alf_node_assoc 140 MB
public.alf_activity_feed 6016 kB
public.alf_activity_post 1464 kB
public.alf_acl_member 1056 kB
public.alf_access_control_list 712 kB
public.act_hi_detail 352 kB
public.act_hi_varinst 248 kB
public.alf_prop_value 240 kB
public.act_ru_variable 232 kB
public.alf_access_control_entry 208 kB
public.alf_lock 144 kB
public.alf_authority 144 kB
public.jbpm_log 120 kB

B) size size of alf_node_properties
select pg_catalog.pg_size_pretty(sum(pg_column_size(alf_node_properties.*)))
from alf_node_properties 6322 MB

C) size of all the columns of alf_node_properties
Dimensioni delle singole colonne (MB)node_idactual_type_npersisted_type_n
qname_idlist_indexlocale_idboolean_valuelong_valuefloat_valuedouble_value
string_valueserializable_valuetotale (MB)totale values select
pg_catalog.pg_size_pretty(sum(pg_column_size(node_id))) node_id,
pg_catalog.pg_size_pretty(sum(pg_column_size(actual_type_n))) actual_type_n,
pg_catalog.pg_size_pretty(sum(pg_column_size(persisted_type_n)))
persisted_type_n,
pg_catalog.pg_size_pretty(sum(pg_column_size(qname_id))) qname_id,
pg_catalog.pg_size_pretty(sum(pg_column_size(list_index))) list_index,
pg_catalog.pg_size_pretty(sum(pg_column_size(locale_id))) locale_id,
pg_catalog.pg_size_pretty(sum(pg_column_size(boolean_value))) boolean_value,
pg_catalog.pg_size_pretty(sum(pg_column_size(long_value))) long_value,
pg_catalog.pg_size_pretty(sum(pg_column_size(float_value))) float_value,
pg_catalog.pg_size_pretty(sum(pg_column_size(double_value))) double_value,
pg_catalog.pg_size_pretty(sum(pg_column_size(string_value))) string_value,
pg_catalog.pg_size_pretty(sum(pg_column_size(serializable_value)))
serializable_value
from alf_node_properties419210210419210419524192104196686237171830

-------Questions----------------

1) Can you explain me the big difference between the result in A for table
alf_node_properties: 17GB and the result in B: ~6GB ?

2) Can you explain me the difference between the result in B: ~6GB and the
result in C, the sum of all column sizes, 3717MB ?

Thanks

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andreas Kretschmer 2015-12-15 10:07:58 Re: Can't explain db size
Previous Message Andreas Kretschmer 2015-12-15 09:49:38 Re: Estimation row error