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
