Reduce number of indexes on ci_builds
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 also has these statistics captured over time.
Links
Related read: https://www.cybertec-postgresql.com/en/get-rid-of-your-unused-indexes/