Force partition attachment to happen during the weekend
What does this MR do and why?
gitlab:db:reindex
is executed during the weekends and we can hook into it to execute our changes for finishing the partitioning of ci_builds
.
How to set up and validate locally
This can be tested using a thin clone:
GITLAB_SIMULATE_SAAS=1 CI_DATABASE_URL='postgresql://user:password@127.0.0.1:port/gitlabhq_dblab' bin/rails runner "Feature.enable(:attach_ci_builds_partition); Gitlab::Database::CiBuildsPartitioning.new.execute"
Running \d+ p_ci_builds
will show ci_builds
as a partition to p_ci_builds
:
Partitions: ci_builds FOR VALUES IN ('100')
the CI_DATABASE_URL
can be generated using pgai cli
[1] pry(main)> Gitlab::Database::CiBuildsPartitioning.new.execute
Gitlab::Database::PostgresPartition Exists? (192.5ms) SELECT 1 AS one FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = 'public.p_ci_builds' AND "postgres_partitions"."identifier" = 'public.ci_builds' LIMIT 1 /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/ci_builds_partitioning.rb:66:in `block in already_attached?'*/
Gitlab::Database::PostgresAutovacuumActivity Exists? (184.3ms) SELECT 1 AS one FROM "postgres_autovacuum_activity" WHERE (schema = current_schema()) AND "postgres_autovacuum_activity"."table" IN ('ci_pipelines', 'ci_stages', 'ci_builds', 'ci_resource_groups') LIMIT 1 /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/ci_builds_partitioning.rb:74:in `block in vacuum_running?'*/
TRANSACTION (174.8ms) BEGIN /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
(176.3ms) SET LOCAL lock_timeout TO '10000ms' /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
(361.3ms) SET LOCAL statement_timeout TO '11s'; LOCK ci_pipelines, ci_stages, ci_builds, ci_resource_groups IN ACCESS EXCLUSIVE MODE; DROP TRIGGER IF EXISTS ci_builds_loose_fk_trigger ON ci_builds; ALTER TABLE p_ci_builds ATTACH PARTITION ci_builds FOR VALUES IN (100); ALTER SEQUENCE ci_builds_id_seq OWNED BY p_ci_builds.id; ALTER TABLE p_ci_builds DROP CONSTRAINT partitioning_constraint; CREATE TRIGGER ci_builds_loose_fk_trigger AFTER DELETE ON ci_builds REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records(); CREATE TRIGGER p_ci_builds_loose_fk_trigger AFTER DELETE ON p_ci_builds REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records(); /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/ci_builds_partitioning.rb:23:in `block (2 levels) in execute'*/
TRANSACTION (174.8ms) COMMIT /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database.rb:401:in `commit'*/
(175.5ms) RESET idle_in_transaction_session_timeout; RESET lock_timeout /*application:console,db_config_name:ci,console_hostname:rocket-sled.local,console_username:marius,line:/lib/gitlab/database/with_lock_retries.rb:172:in `execute'*/
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #382033 (closed)
Edited by Marius Bobin