`ci_builds` is by far the largest table (1.9 TB) and it has 31 indexes associated, which add up to 926 GB of additional space taken. Not only is the space usage and size a concern, but also the write overhead incurred by having to maintain and vacuum those indexes over time. The question here is whether we can reduce the number of indexes on `ci_builds`. We can determine index usage on GitLab.com which is a good indicator. Additionally, we may want to review application logic to decide whether an index is truly not needed (for features not active on GitLab.com, if any). ## Statistics From GitLab.com primary (taken 2021-06-02): ``` gitlabhq_production=# select indexrelname, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelname::regclass)) from pg_stat_user_indexes where relname = 'ci_builds' order by pg_relation_size(indexrelname::regclass) desc; indexrelname | idx_scan | idx_tup_read | idx_tup_fetch | pg_size_pretty ----------------------------------------------------------------+------------+--------------+---------------+---------------- index_ci_builds_on_token | 101049006 | 7019 | 7019 | 114 GB index_ci_builds_on_token_encrypted | 297055834 | 196035431 | 196006818 | 95 GB index_ci_builds_on_commit_id_and_type_and_name_and_ref | 2446869 | 407353077 | 230561671 | 84 GB ci_builds_pkey | 2792636169 | 5915675035 | 4499351495 | 71 GB index_ci_builds_on_commit_id_and_type_and_ref | 1929131 | 18036471 | 13645034 | 63 GB index_ci_builds_on_status_and_type_and_runner_id | 681923 | 5620424581 | 263526035 | 52 GB tmp_reindex_997977174 | 1690 | 11051810 | 5592269 | 50 GB index_ci_builds_on_commit_id_and_status_and_type | 52291778 | 494039133 | 346745164 | 48 GB ci_builds_gitlab_monitor_metrics | 482 | 4436940 | 1579234 | 47 GB index_ci_builds_on_project_id_and_name_and_ref | 22384 | 36581802 | 36572709 | 43 GB index_ci_builds_on_commit_id_and_stage_idx_and_created_at | 113761383 | 4738277575 | 3611595039 | 37 GB index_ci_builds_on_user_id_and_created_at_and_type_eq_ci_build | 1026 | 51030 | 49721 | 37 GB index_ci_builds_on_runner_id_and_id_desc | 2834491 | 2949417 | 1638084 | 31 GB index_ci_builds_on_updated_at | 2015 | 2015 | 2015 | 30 GB index_ci_builds_on_user_id | 0 | 0 | 0 | 30 GB index_ci_builds_on_queued_at | 0 | 0 | 0 | 29 GB index_ci_builds_on_project_id_and_id | 35311490 | 37562325 | 18947650 | 27 GB index_ci_builds_on_stage_id | 8367120 | 415066912 | 412114893 | 27 GB index_ci_builds_on_protected | 0 | 0 | 0 | 26 GB index_ci_builds_on_auto_canceled_by_id | 529432 | 2425 | 428 | 26 GB index_for_resource_group | 1011340 | 9817695287 | 0 | 3206 MB index_ci_builds_project_id_and_status_for_live_jobs_partial2 | 111313078 | 208626906 | 30562405 | 2487 MB index_security_ci_builds_on_name_and_id_parser_features | 0 | 0 | 0 | 1498 MB index_ci_builds_on_commit_id_artifacts_expired_at_and_id | 1416 | 0 | 0 | 840 MB index_secure_ci_builds_on_user_id_name_created_at | 0 | 0 | 0 | 792 MB index_ci_builds_on_project_id_for_successfull_pages_deploy | 0 | 0 | 0 | 754 MB index_partial_ci_builds_on_user_id_name_parser_features | 0 | 0 | 0 | 568 MB index_ci_builds_runner_id_pending_covering | 14261 | 8216619214 | 115547491 | 195 MB index_ci_builds_runner_id_running | 2855661 | 1681270095 | 239588241 | 118 MB partial_index_ci_builds_on_scheduled_at_with_scheduled_jobs | 134 | 19755 | 94 | 336 kB index_ci_builds_on_upstream_pipeline_id | 32414242 | 822 | 537 | 168 kB (31 rows) ``` From a GitLab.com replica: ``` gitlabhq_production=# select indexrelname, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelname::regclass)) from pg_stat_user_indexes where relname = 'ci_builds' order by pg_relation_size(indexrelname::regclass) desc; indexrelname | idx_scan | idx_tup_read | idx_tup_fetch | pg_size_pretty ----------------------------------------------------------------+-------------+---------------+---------------+---------------- index_ci_builds_on_token | 28385445 | 0 | 0 | 114 GB index_ci_builds_on_token_encrypted | 75160484 | 46789179 | 46775070 | 95 GB index_ci_builds_on_commit_id_and_type_and_name_and_ref | 772054 | 47893204 | 21520311 | 84 GB ci_builds_pkey | 468197673 | 460551502 | 328527746 | 71 GB index_ci_builds_on_commit_id_and_type_and_ref | 560300 | 88348245 | 64433667 | 63 GB index_ci_builds_on_status_and_type_and_runner_id | 2244854 | 32736823223 | 291331035 | 52 GB tmp_reindex_997977174 | 232 | 12295669 | 390384 | 50 GB index_ci_builds_on_commit_id_and_status_and_type | 158794939 | 430473212 | 214774298 | 48 GB ci_builds_gitlab_monitor_metrics | 96 | 13933965 | 159960 | 47 GB index_ci_builds_on_project_id_and_name_and_ref | 6978 | 3819655 | 3803782 | 43 GB index_ci_builds_on_commit_id_and_stage_idx_and_created_at | 19672563 | 281111829 | 158211188 | 37 GB index_ci_builds_on_user_id_and_created_at_and_type_eq_ci_build | 0 | 0 | 0 | 37 GB index_ci_builds_on_runner_id_and_id_desc | 873028 | 141702943 | 139725062 | 31 GB index_ci_builds_on_updated_at | 4 | 34329976 | 0 | 30 GB index_ci_builds_on_user_id | 782 | 1046 | 757 | 30 GB index_ci_builds_on_queued_at | 0 | 0 | 0 | 29 GB index_ci_builds_on_project_id_and_id | 11829823 | 9522025163 | 9356829808 | 27 GB index_ci_builds_on_stage_id | 25275083 | 204445009 | 64251723 | 27 GB index_ci_builds_on_protected | 0 | 0 | 0 | 26 GB index_ci_builds_on_auto_canceled_by_id | 0 | 0 | 0 | 26 GB index_for_resource_group | 0 | 0 | 0 | 3206 MB index_ci_builds_project_id_and_status_for_live_jobs_partial2 | 33457028431 | 38678160282 | 446188297 | 2487 MB index_security_ci_builds_on_name_and_id_parser_features | 11712 | 839674 | 17135 | 1498 MB index_ci_builds_on_commit_id_artifacts_expired_at_and_id | 70640 | 854 | 52 | 840 MB index_secure_ci_builds_on_user_id_name_created_at | 11677 | 8755073 | 76789 | 792 MB index_ci_builds_on_project_id_for_successfull_pages_deploy | 0 | 0 | 0 | 754 MB index_partial_ci_builds_on_user_id_name_parser_features | 8082 | 8082097 | 73087 | 568 MB index_ci_builds_runner_id_pending_covering | 3176446 | 2047841004156 | 40981685642 | 195 MB index_ci_builds_runner_id_running | 15675652 | 144637428055 | 1314910853 | 118 MB partial_index_ci_builds_on_scheduled_at_with_scheduled_jobs | 0 | 0 | 0 | 336 kB index_ci_builds_on_upstream_pipeline_id | 0 | 0 | 0 | 168 kB (31 rows) ``` This gives a good impression about size and usage over time. Note the time those counters were reset is not known (and they just keep incrementing over time). For further analysis, [Thanos](https://thanos-query.ops.gitlab.net/graph?g0.range_input=2d&g0.max_source_resolution=0s&g0.expr=topk(100%2C%20sum(rate(pg_stat_user_indexes_idx_scan%7Brelname%3D%22ci_builds%22%7D%5B7d%5D))%20by%20(indexrelname))&g0.tab=1) also has these statistics captured over time. ## Links Related read: https://www.cybertec-postgresql.com/en/get-rid-of-your-unused-indexes/