acardona/CATMAID — pull request #723 by tomka (2014-07-29 20:37:40+00:00)
Title: Change data type of columns in many relations

As a result of the discussion in issue #715 I created a set of commits that introduces new data types for many columns of the database. We hope to improve performance a bit, which was in tests the case for at least some queries.

Also, the size of the tables went down a bit. This is the size of the 20 biggest relations *before* the migrations of this pull request:

Relations, indices and etc. separate:

relation | size
--------------------------------------|---------
public.treenode | 1205 MB
public.treenode_location_z_index | 163 MB
public.treenode_location_x_index | 163 MB
public.skeleton_id_treenode_index | 163 MB
public.treenode_parent_id | 163 MB
public.treenode_pkey | 163 MB
public.treenode_id_key | 163 MB
public.treenode_location_y_index | 163 MB
public.log | 119 MB
public.review | 98 MB
public.class_instance | 81 MB
public.treenode_connector | 74 MB
public.class_instance_class_instance | 68 MB
public.treenode_class_instance | 61 MB
public.review_skeleton_id | 37 MB
public.review_reviewer_id | 37 MB
public.review_project_id | 37 MB
public.review_pkey | 37 MB
public.review_treenode_id | 37 MB
public.connector | 20 MB

All data belonging to a certain relation aggregated:

relation | total_size
--------------------------------------|------------
public.treenode | 2346 MB
public.review | 281 MB
public.class_instance_class_instance | 231 MB
public.treenode_connector | 210 MB
public.class_instance | 193 MB
public.treenode_class_instance | 165 MB
public.log | 135 MB
public.connector | 37 MB
public.change_request | 4304 kB
public.django_session | 2744 kB
public.connector_class_instance | 136 kB
public.message | 120 kB
public.auth_permission | 112 kB
public.django_admin_log | 104 kB
public.guardian_userobjectpermission | 88 kB
public.class | 80 kB
public.djcelery_taskstate | 80 kB
public.auth_user | 72 kB
public.auth_user_groups | 72 kB
public.auth_user_user_permissions | 72 kB

And this is the data *after* the migrations have been applied:

Relations, indices and etc. separate:

relation | size
--------------------------------------|---------
public.treenode | 1020 MB
public.treenode_parent_id | 163 MB
public.treenode_location_y_index | 163 MB
public.treenode_location_x_index | 163 MB
public.treenode_id_key | 163 MB
public.treenode_location_z_index | 163 MB
public.skeleton_id_treenode_index | 163 MB
public.treenode_pkey | 163 MB
public.review | 111 MB
public.log | 104 MB
public.class_instance | 67 MB
public.treenode_connector | 57 MB
public.treenode_class_instance | 50 MB
public.class_instance_class_instance | 50 MB
public.review_skeleton_id | 37 MB
public.review_reviewer_id | 37 MB
public.review_project_id | 37 MB
public.review_pkey | 37 MB
public.review_treenode_id | 37 MB
public.class_instance_user_id | 19 MB

All data belonging to a certain relation aggregated:

relation | total_size
--------------------------------------|------------
public.treenode | 2161 MB
public.review | 294 MB
public.class_instance_class_instance | 213 MB
public.treenode_connector | 193 MB
public.class_instance | 179 MB
public.treenode_class_instance | 154 MB
public.log | 119 MB
public.connector | 35 MB
public.change_request | 4040 kB
public.django_session | 2744 kB
public.connector_class_instance | 128 kB
public.message | 120 kB
public.auth_permission | 112 kB
public.django_admin_log | 104 kB
public.guardian_userobjectpermission | 88 kB
public.djcelery_taskstate | 80 kB
public.class | 80 kB
public.auth_user_groups | 72 kB
public.auth_user | 72 kB
public.auth_user_user_permissions | 72 kB