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_id | actual_type_n | persisted_type_n | qname_id | list_index | locale_id | boolean_value | long_value | float_value | double_value | string_value | serializable_value | totale (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_properties | 419 | 210 | 210 | 419 | 210 | 419 | 52 | 419 | 210 | 419 | 668 | 62 | 3717 | 1830 |
-------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
