From cd97deb0197d0afe26e92b8fae1094e7410e6097 Mon Sep 17 00:00:00 2001 From: GitLab Duo Date: Fri, 12 Sep 2025 10:44:42 +0000 Subject: [PATCH 01/17] Adds gitlab_shared_org and gitlab_shared_cell_local schemas MR: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/204840 --- db/database_connections/ci.yaml | 2 ++ db/database_connections/main.yaml | 2 ++ db/database_connections/sec.yaml | 2 ++ db/gitlab_schemas/gitlab_shared.yaml | 2 +- db/gitlab_schemas/gitlab_shared_cell_local.yaml | 8 ++++++++ db/gitlab_schemas/gitlab_shared_org.yaml | 10 ++++++++++ doc/development/cells/_index.md | 2 ++ doc/development/database/multiple_databases.md | 4 +++- lib/gitlab/database/gitlab_schema.rb | 2 ++ spec/lib/gitlab/database/gitlab_schema_spec.rb | 5 ++++- 10 files changed, 36 insertions(+), 3 deletions(-) create mode 100644 db/gitlab_schemas/gitlab_shared_cell_local.yaml create mode 100644 db/gitlab_schemas/gitlab_shared_org.yaml diff --git a/db/database_connections/ci.yaml b/db/database_connections/ci.yaml index 73fbef8d258fa2..f99ea9e3285e30 100644 --- a/db/database_connections/ci.yaml +++ b/db/database_connections/ci.yaml @@ -3,6 +3,8 @@ description: Cell-local GitLab database holding all CI pipelines, builds, etc. gitlab_schemas: - gitlab_internal - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_ci - gitlab_ci_cell_local lock_gitlab_schemas: diff --git a/db/database_connections/main.yaml b/db/database_connections/main.yaml index bf5e214d659fb1..3b924665a11279 100644 --- a/db/database_connections/main.yaml +++ b/db/database_connections/main.yaml @@ -3,6 +3,8 @@ description: Main GitLab database holding all projects, issues, etc. gitlab_schemas: - gitlab_internal - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_main - gitlab_main_org - gitlab_main_cell diff --git a/db/database_connections/sec.yaml b/db/database_connections/sec.yaml index 6e9e790201f92b..3ebe1cc97e68e5 100644 --- a/db/database_connections/sec.yaml +++ b/db/database_connections/sec.yaml @@ -3,6 +3,8 @@ description: Cell-local GitLab database holding Security feature related tables. gitlab_schemas: - gitlab_internal - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_sec lock_gitlab_schemas: - gitlab_main diff --git a/db/gitlab_schemas/gitlab_shared.yaml b/db/gitlab_schemas/gitlab_shared.yaml index c2ae7733dfe446..02cf2d8bbe7ad9 100644 --- a/db/gitlab_schemas/gitlab_shared.yaml +++ b/db/gitlab_schemas/gitlab_shared.yaml @@ -1,6 +1,6 @@ name: gitlab_shared description: - Schema for all tables implementing shared features, + (Deprecated - use gitlab_shared_cell_local or gitlab_shared_org instead) Schema for all tables implementing shared features, ex. loose foreign keys, re-indexing, etc. allow_cross_joins: - gitlab_internal diff --git a/db/gitlab_schemas/gitlab_shared_cell_local.yaml b/db/gitlab_schemas/gitlab_shared_cell_local.yaml new file mode 100644 index 00000000000000..c70c7c9d4b031b --- /dev/null +++ b/db/gitlab_schemas/gitlab_shared_cell_local.yaml @@ -0,0 +1,8 @@ +name: gitlab_shared_cell_local +description: + Schema for all cell local shared tables that do not require sharding. +allow_cross_joins: + - gitlab_internal +allow_cross_transactions: + - gitlab_internal +require_sharding_key: false diff --git a/db/gitlab_schemas/gitlab_shared_org.yaml b/db/gitlab_schemas/gitlab_shared_org.yaml new file mode 100644 index 00000000000000..9adc25ad6add8f --- /dev/null +++ b/db/gitlab_schemas/gitlab_shared_org.yaml @@ -0,0 +1,10 @@ +name: gitlab_shared_org +description: + Schema for all shared tables that require sharding. +allow_cross_joins: + - gitlab_internal +allow_cross_transactions: + - gitlab_internal +require_sharding_key: true +sharding_root_tables: + - organizations diff --git a/doc/development/cells/_index.md b/doc/development/cells/_index.md index 1c4df59625f3cf..d360e42545874c 100644 --- a/doc/development/cells/_index.md +++ b/doc/development/cells/_index.md @@ -22,6 +22,8 @@ Below are available schemas related to Cells and Organizations: | `gitlab_ci` | Use for all tables in the `ci:` database that are for an Organization. For example, `ci_pipelines` and `ci_builds` | | `gitlab_ci_cell_local` | For tables in the `ci:` database that are related to features that is distinct for each cell. For example, `instance_type_ci_runners`, or `ci_cost_settings`. These cell-local tables should not have any foreign key references from/to organization tables. | | `gitlab_main_user` | Schema for all User-related tables, ex. `users`, `emails`, etc. Most user functionality is organizational level so should use `gitlab_main_org` instead (e.g. commenting on an issue). For user functionality that is not organizational level, use this schema. Tables on this schema must strictly belong to a user. | +| `gitlab_shared_org` | Schema for tables with data across multiple databases and has `organization_id` for sharding. These tables inherit from `Gitlab::Database::SharedModel`. | +| `gitlab_shared_cell_local` | Schema for cell local shared tables that do not require sharding and exist across multiple databases. For example, `loose_foreign_keys_deleted_records`. These tables also inherit from `Gitlab::Database::SharedModel`. | Most tables will require a [sharding key](../organization/_index.md#defining-a-sharding-key-for-all-organizational-tables) to be defined. diff --git a/doc/development/database/multiple_databases.md b/doc/development/database/multiple_databases.md index 91eb235931113e..8e9c11e87b2a9f 100644 --- a/doc/development/database/multiple_databases.md +++ b/doc/development/database/multiple_databases.md @@ -33,10 +33,12 @@ Each table of GitLab needs to have a `gitlab_schema` assigned: | `gitlab_ci` | All CI tables that are being stored in the `ci:` database (for example, `ci_pipelines`, `ci_builds`) | | | `gitlab_ci_cell_local` | See [Cells / Organizations schemas](../cells/_index.md#available-cells--organization-schemas) | | | `gitlab_geo` | All Geo tables that are being stored in the `geo:` database (for example, like `project_registry`, `secondary_usage_data`) | | -| `gitlab_shared` | All application tables that contain data across all decomposed databases (for example, `loose_foreign_keys_deleted_records`) for models that inherit from `Gitlab::Database::SharedModel`. | | | `gitlab_internal` | All internal tables of Rails and PostgreSQL (for example, `ar_internal_metadata`, `schema_migrations`, `pg_*`) | | | `gitlab_pm` | All tables that store `package_metadata`| It is an alias for `gitlab_main`, to be replaced with `gitlab_sec` | | `gitlab_sec` | All Security and Vulnerability feature tables to be stored in the `sec:` database | [Decomposition in progress](https://gitlab.com/groups/gitlab-org/-/epics/13043) | +| `gitlab_shared` | Deprecated, refer `gitlab_shared_cell_local` or `gitlab_shared_org` | | +| `gitlab_shared_cell_local` | See [Cells / Organizations schemas](../cells/_index.md#available-cells--organization-schemas) | | +| `gitlab_shared_org` | See [Cells / Organizations schemas](../cells/_index.md#available-cells--organization-schemas) | | More schemas to be introduced with additional decomposed databases diff --git a/lib/gitlab/database/gitlab_schema.rb b/lib/gitlab/database/gitlab_schema.rb index 796db20fc08e2c..a61518feb7bfba 100644 --- a/lib/gitlab/database/gitlab_schema.rb +++ b/lib/gitlab/database/gitlab_schema.rb @@ -37,6 +37,8 @@ def self.table_schemas!(tables) '_test_gitlab_embedding_' => :gitlab_embedding, '_test_gitlab_geo_' => :gitlab_geo, '_test_gitlab_pm_' => :gitlab_pm, + '_test_gitlab_shared_org_' => :gitlab_shared_org, + '_test_gitlab_shared_cell_local_' => :gitlab_shared_cell_local, '_test_' => :gitlab_shared, 'pg_' => :gitlab_internal }.freeze diff --git a/spec/lib/gitlab/database/gitlab_schema_spec.rb b/spec/lib/gitlab/database/gitlab_schema_spec.rb index ae5859066b7939..f1634e943b42eb 100644 --- a/spec/lib/gitlab/database/gitlab_schema_spec.rb +++ b/spec/lib/gitlab/database/gitlab_schema_spec.rb @@ -17,7 +17,8 @@ .values database_connections.permutation(2) do |db, other_db| - gitlab_schemas = db.gitlab_schemas - [:gitlab_shared, :gitlab_internal] + gitlab_schemas = db.gitlab_schemas - + [:gitlab_shared, :gitlab_internal, :gitlab_shared_org, :gitlab_shared_cell_local] expect(other_db.lock_gitlab_schemas).to include(*gitlab_schemas), "Expected `#{other_db.name}` lock_gitlab_schemas to include `#{db.name}` gitlab_schemas:" @@ -42,6 +43,8 @@ '_test_gitlab_main_org_table' | :gitlab_main_org '_test_gitlab_pm_table' | :gitlab_pm '_test_gitlab_sec_table' | :gitlab_sec + '_test_gitlab_shared_org_table' | :gitlab_shared_org + '_test_gitlab_shared_cell_local_table' | :gitlab_shared_cell_local '_test_my_table' | :gitlab_shared 'pg_attribute' | :gitlab_internal end -- GitLab From d6049cce4ee8c32033daf814d2e33f90a463e153 Mon Sep 17 00:00:00 2001 From: GitLab Duo Date: Tue, 16 Sep 2025 11:34:32 +0200 Subject: [PATCH 02/17] Makes changes to Gitlab::Database::SharedModel To include gitlab_shared_org and gitlab_shared_cell_local --- lib/gitlab/database/shared_model.rb | 6 ++-- spec/lib/gitlab/database/shared_model_spec.rb | 34 ++++++++++++++++--- 2 files changed, 33 insertions(+), 7 deletions(-) diff --git a/lib/gitlab/database/shared_model.rb b/lib/gitlab/database/shared_model.rb index 238df2d93e4dbb..a7c5def1ee2210 100644 --- a/lib/gitlab/database/shared_model.rb +++ b/lib/gitlab/database/shared_model.rb @@ -8,6 +8,8 @@ class SharedModel < ActiveRecord::Base self.abstract_class = true + SHARED_SCHEMAS = %i[gitlab_shared gitlab_shared_org gitlab_shared_cell_local].freeze + # if shared model is used, this allows to limit connections # on which this model is being shared class_attribute :limit_connection_names, default: nil @@ -26,9 +28,9 @@ def using_connection(connection) # in such cases it is fine to ignore such connections gitlab_schemas = Gitlab::Database.gitlab_schemas_for_connection(connection) - unless gitlab_schemas.nil? || gitlab_schemas.include?(:gitlab_shared) + unless gitlab_schemas.nil? || (gitlab_schemas & SHARED_SCHEMAS).present? raise "Cannot set `SharedModel` to connection from `#{Gitlab::Database.db_config_name(connection)}` " \ - "since this connection does not include `:gitlab_shared` schema." + "since this connection does not include any of the shared gitlab_schema." end self.overriding_connection = connection diff --git a/spec/lib/gitlab/database/shared_model_spec.rb b/spec/lib/gitlab/database/shared_model_spec.rb index 2ae6ccf6c6ab79..af509a806407a0 100644 --- a/spec/lib/gitlab/database/shared_model_spec.rb +++ b/spec/lib/gitlab/database/shared_model_spec.rb @@ -3,10 +3,7 @@ require 'spec_helper' RSpec.describe Gitlab::Database::SharedModel, feature_category: :database do - describe 'using an external connection' do - let!(:original_connection) { described_class.connection } - let(:new_connection) { double('connection') } - + shared_examples 'shared model using the correct connection' do it 'overrides the connection for the duration of the block', :aggregate_failures do expect_original_connection_around do described_class.using_connection(new_connection) do @@ -14,6 +11,13 @@ end end end + end + + describe 'using an external connection' do + let!(:original_connection) { described_class.connection } + let(:new_connection) { double('connection') } + + it_behaves_like 'shared model using the correct connection' it 'does not affect connections in other threads', :aggregate_failures do expect_original_connection_around do @@ -27,7 +31,7 @@ end end - it 'raises an error if the connection does not include `:gitlab_shared` schema' do + it 'raises an error if the connection does not include shared gitlab_schema' do allow(Gitlab::Database) .to receive(:gitlab_schemas_for_connection) .with(new_connection) @@ -40,6 +44,26 @@ end end + context 'with connection including gitlab_shared_org' do + before do + allow(Gitlab::Database).to receive(:gitlab_schemas_for_connection) + .with(new_connection) + .and_return([:gitlab_shared_org]) + end + + it_behaves_like 'shared model using the correct connection' + end + + context 'with connection including gitlab_shared_cell_local' do + before do + allow(Gitlab::Database).to receive(:gitlab_schemas_for_connection) + .with(new_connection) + .and_return([:gitlab_shared_cell_local]) + end + + it_behaves_like 'shared model using the correct connection' + end + context 'when multiple connection overrides are nested', :aggregate_failures do let(:second_connection) { double('connection') } -- GitLab From 1b7cae3993a3454805b8a074cf133fb207c62b0f Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Wed, 17 Sep 2025 12:14:56 +0200 Subject: [PATCH 03/17] Include new schemas in allow cross join and transactions --- db/gitlab_schemas/gitlab_ci.yaml | 4 ++++ db/gitlab_schemas/gitlab_ci_cell_local.yaml | 4 ++++ db/gitlab_schemas/gitlab_main_cell_local.yaml | 4 ++++ db/gitlab_schemas/gitlab_main_cell_setting.yaml | 4 ++++ db/gitlab_schemas/gitlab_main_jh.yaml | 4 ++++ db/gitlab_schemas/gitlab_main_org.yaml | 4 ++++ db/gitlab_schemas/gitlab_main_user.yaml | 4 ++++ db/gitlab_schemas/gitlab_pm.yaml | 4 ++++ db/gitlab_schemas/gitlab_sec.yaml | 4 ++++ 9 files changed, 36 insertions(+) diff --git a/db/gitlab_schemas/gitlab_ci.yaml b/db/gitlab_schemas/gitlab_ci.yaml index e448d85b182745..5d35f898684f23 100644 --- a/db/gitlab_schemas/gitlab_ci.yaml +++ b/db/gitlab_schemas/gitlab_ci.yaml @@ -2,9 +2,13 @@ name: gitlab_ci description: Schema for all Organizational CI tables, ex. ci_builds, etc. allow_cross_joins: - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local allow_cross_transactions: - gitlab_internal - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local require_sharding_key: true sharding_root_tables: - projects diff --git a/db/gitlab_schemas/gitlab_ci_cell_local.yaml b/db/gitlab_schemas/gitlab_ci_cell_local.yaml index da59ab52adab53..4e2234da0f686f 100644 --- a/db/gitlab_schemas/gitlab_ci_cell_local.yaml +++ b/db/gitlab_schemas/gitlab_ci_cell_local.yaml @@ -2,9 +2,13 @@ name: gitlab_ci_cell_local description: Schema for all Cell-local CI tables, ex. ci_cost_settings, etc. allow_cross_joins: - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_ci allow_cross_transactions: - gitlab_internal - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_ci require_sharding_key: false diff --git a/db/gitlab_schemas/gitlab_main_cell_local.yaml b/db/gitlab_schemas/gitlab_main_cell_local.yaml index 8ca1ecf8d4d7f1..5ade3c5fe154ca 100644 --- a/db/gitlab_schemas/gitlab_main_cell_local.yaml +++ b/db/gitlab_schemas/gitlab_main_cell_local.yaml @@ -2,6 +2,8 @@ name: gitlab_main_cell_local description: Schema for all Cell-local tables, ex. zoekt_nodes, geo_nodes. allow_cross_joins: - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_main - gitlab_main_clusterwide - gitlab_main_cell @@ -9,6 +11,8 @@ allow_cross_joins: allow_cross_transactions: - gitlab_internal - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_main - gitlab_main_clusterwide - gitlab_main_cell diff --git a/db/gitlab_schemas/gitlab_main_cell_setting.yaml b/db/gitlab_schemas/gitlab_main_cell_setting.yaml index 08495a2fd3f9a8..dc071cfbf12c54 100644 --- a/db/gitlab_schemas/gitlab_main_cell_setting.yaml +++ b/db/gitlab_schemas/gitlab_main_cell_setting.yaml @@ -2,12 +2,16 @@ name: gitlab_main_cell_setting description: Schema for all cell setting tables, ex. application_settings, etc. allow_cross_joins: - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_main - gitlab_main_cell - gitlab_main_org allow_cross_transactions: - gitlab_internal - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_main - gitlab_main_cell - gitlab_main_org diff --git a/db/gitlab_schemas/gitlab_main_jh.yaml b/db/gitlab_schemas/gitlab_main_jh.yaml index b9f374f7b43960..491f8b10ab174a 100644 --- a/db/gitlab_schemas/gitlab_main_jh.yaml +++ b/db/gitlab_schemas/gitlab_main_jh.yaml @@ -2,6 +2,8 @@ name: gitlab_main_jh description: Schema for all Jihu-specific tables. allow_cross_joins: - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_main - gitlab_main_cell - gitlab_main_clusterwide @@ -9,6 +11,8 @@ allow_cross_joins: allow_cross_transactions: - gitlab_internal - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_main - gitlab_main_cell - gitlab_main_clusterwide diff --git a/db/gitlab_schemas/gitlab_main_org.yaml b/db/gitlab_schemas/gitlab_main_org.yaml index bc892c0642d78e..04daae08de7886 100644 --- a/db/gitlab_schemas/gitlab_main_org.yaml +++ b/db/gitlab_schemas/gitlab_main_org.yaml @@ -2,6 +2,8 @@ name: gitlab_main_org description: Schema for all Organization Main tables, ex. namespaces, projects, etc. allow_cross_joins: - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_main - gitlab_main_cell - gitlab_main_clusterwide @@ -10,6 +12,8 @@ allow_cross_joins: allow_cross_transactions: - gitlab_internal - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_main - gitlab_main_cell - gitlab_main_clusterwide diff --git a/db/gitlab_schemas/gitlab_main_user.yaml b/db/gitlab_schemas/gitlab_main_user.yaml index 5f7daf6247efd8..1d901b8b31d039 100644 --- a/db/gitlab_schemas/gitlab_main_user.yaml +++ b/db/gitlab_schemas/gitlab_main_user.yaml @@ -5,6 +5,8 @@ description: >- but some functionality (e.g. login) is cluster-wide. allow_cross_joins: - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_main - gitlab_main_cell - gitlab_main_org @@ -13,6 +15,8 @@ allow_cross_joins: allow_cross_transactions: - gitlab_internal - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local - gitlab_main - gitlab_main_cell - gitlab_main_org diff --git a/db/gitlab_schemas/gitlab_pm.yaml b/db/gitlab_schemas/gitlab_pm.yaml index 383bae41ffd104..b431371e61e0c5 100644 --- a/db/gitlab_schemas/gitlab_pm.yaml +++ b/db/gitlab_schemas/gitlab_pm.yaml @@ -2,7 +2,11 @@ name: gitlab_pm description: Schema for all Cell-local package management features. allow_cross_joins: - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local allow_cross_transactions: - gitlab_internal - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local require_sharding_key: false diff --git a/db/gitlab_schemas/gitlab_sec.yaml b/db/gitlab_schemas/gitlab_sec.yaml index c39e9a66da4b52..03ef8ffb392186 100644 --- a/db/gitlab_schemas/gitlab_sec.yaml +++ b/db/gitlab_schemas/gitlab_sec.yaml @@ -2,9 +2,13 @@ name: gitlab_sec description: Schema for all Organizational Security features. allow_cross_joins: - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local allow_cross_transactions: - gitlab_internal - gitlab_shared + - gitlab_shared_org + - gitlab_shared_cell_local require_sharding_key: true sharding_root_tables: - projects -- GitLab From b66e310307458e68b016b68a76e5d99e08660d5e Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Mon, 1 Sep 2025 14:20:25 +0200 Subject: [PATCH 04/17] Adds BBO tables and their models MR: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/203234 Changelog: added # Conflicts: # config/initializers/postgres_partitioning.rb --- db/docs/batched_background_operation_jobs.yml | 12 ++ db/docs/batched_background_operations.yml | 14 +++ ...49_create_batched_background_operations.rb | 102 ++++++++++++++++ ...reate_batched_background_operation_jobs.rb | 113 ++++++++++++++++++ db/schema_migrations/20250829132949 | 1 + db/schema_migrations/20250901090254 | 1 + db/structure.sql | 111 +++++++++++++++++ .../batched_background_operation.rb | 53 ++++++++ .../batched_background_operation_job.rb | 59 +++++++++ 9 files changed, 466 insertions(+) create mode 100644 db/docs/batched_background_operation_jobs.yml create mode 100644 db/docs/batched_background_operations.yml create mode 100644 db/migrate/20250829132949_create_batched_background_operations.rb create mode 100644 db/migrate/20250901090254_create_batched_background_operation_jobs.rb create mode 100644 db/schema_migrations/20250829132949 create mode 100644 db/schema_migrations/20250901090254 create mode 100644 lib/gitlab/database/background_operation/batched_background_operation.rb create mode 100644 lib/gitlab/database/background_operation/batched_background_operation_job.rb diff --git a/db/docs/batched_background_operation_jobs.yml b/db/docs/batched_background_operation_jobs.yml new file mode 100644 index 00000000000000..61ff91ce983331 --- /dev/null +++ b/db/docs/batched_background_operation_jobs.yml @@ -0,0 +1,12 @@ +--- +table_name: batched_background_operation_jobs +classes: +- Gitlab::Database::BackgroundOperation::BatchedBackgroundOperationJob +- Gitlab::Database::BatchedBackgroundOperationJob +feature_categories: +- database +description: Store batched jobs info for each batched_background_operations. +introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/203234 +milestone: '18.4' +gitlab_schema: gitlab_shared +table_size: small diff --git a/db/docs/batched_background_operations.yml b/db/docs/batched_background_operations.yml new file mode 100644 index 00000000000000..378041144cb299 --- /dev/null +++ b/db/docs/batched_background_operations.yml @@ -0,0 +1,14 @@ +--- +table_name: batched_background_operations +classes: +- Gitlab::Database::BackgroundOperation::BatchedBackgroundOperation +- Gitlab::Database::BatchedBackgroundOperation +feature_categories: +- database +description: Stores information about the large data operations performed async. See + https://handbook.gitlab.com/handbook/engineering/architecture/design-documents/batched_background_operations/ + for more details. +introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/203234 +milestone: '18.4' +gitlab_schema: gitlab_shared +table_size: small diff --git a/db/migrate/20250829132949_create_batched_background_operations.rb b/db/migrate/20250829132949_create_batched_background_operations.rb new file mode 100644 index 00000000000000..c188461394ea34 --- /dev/null +++ b/db/migrate/20250829132949_create_batched_background_operations.rb @@ -0,0 +1,102 @@ +# frozen_string_literal: true + +class CreateBatchedBackgroundOperations < Gitlab::Database::Migration[2.3] + include Gitlab::Database::PartitioningMigrationHelpers + + milestone '18.4' + + disable_ddl_transaction! + + def up + opts = { + if_not_exists: true, + primary_key: [:partition, :id], + options: 'PARTITION BY LIST (partition)' + } + + create_table(:batched_background_operations, **opts) do |t| + t.bigserial :id + t.bigint :organization_id + t.bigint :user_id + t.bigint :total_tuple_count + t.timestamptz :started_at + t.timestamptz :on_hold_until + t.timestamptz :created_at, null: false + t.timestamptz :finished_at + t.integer :batch_size, null: false + t.integer :sub_batch_size, null: false + t.integer :pause_ms, null: false, default: 100 + t.integer :max_batch_size + t.integer :partition, null: false, default: 1 + t.integer :priority, null: false, limit: 2, default: 0 + t.integer :status, null: false, limit: 2, default: 0 + t.integer :interval, null: false, limit: 2 + t.text :job_class_name, null: false, limit: 100 + t.text :batch_class_name, null: false, default: 'PrimaryKeyBatchingStrategy', limit: 100 + t.text :table_name, null: false, limit: 63 + t.text :column_name, null: false, limit: 63 + t.text :gitlab_schema, null: false, limit: 255 + t.jsonb :job_arguments, null: false, default: '[]' + t.jsonb :min_cursor + t.jsonb :max_cursor + t.jsonb :next_min_cursor + end + + add_indexes + add_constraints + end + + def down + drop_table(:batched_background_operations) + end + + private + + def add_indexes + add_concurrent_partitioned_index( + :batched_background_operations, + :status, + name: 'index_bbo_by_status' + ) + add_concurrent_partitioned_index( + :batched_background_operations, + :priority, + name: 'index_bbo_by_priority' + ) + add_concurrent_partitioned_index( + :batched_background_operations, + :organization_id, + name: 'index_bbo_by_organization' + ) + + add_concurrent_partitioned_index( + :batched_background_operations, + [:partition, :job_class_name, :table_name, :column_name, :job_arguments], + unique: true, + name: 'index_bbo_on_unique_configuration' + ) + end + + def add_constraints + add_check_constraint( + :batched_background_operations, + '(batch_size >= sub_batch_size)', + check_constraint_name(:batched_background_operations, 'batch_size', 'greater_than_sub_batch_size') + ) + add_check_constraint( + :batched_background_operations, + '(sub_batch_size > 0)', + check_constraint_name(:batched_background_operations, 'sub_batch_size', 'greater_than_zero') + ) + add_check_constraint( + :batched_background_operations, + "jsonb_typeof(min_cursor) = 'array' AND jsonb_typeof(max_cursor) = 'array'", + check_constraint_name(:batched_background_operations, 'cursors', 'jsonb_array') + ) + add_check_constraint( + :batched_background_operations, + 'num_nonnulls(min_cursor, max_cursor) = 2', + check_constraint_name(:batched_background_operations, 'cursors', 'not_null') + ) + end +end diff --git a/db/migrate/20250901090254_create_batched_background_operation_jobs.rb b/db/migrate/20250901090254_create_batched_background_operation_jobs.rb new file mode 100644 index 00000000000000..2e0147396f5cfd --- /dev/null +++ b/db/migrate/20250901090254_create_batched_background_operation_jobs.rb @@ -0,0 +1,113 @@ +# frozen_string_literal: true + +class CreateBatchedBackgroundOperationJobs < Gitlab::Database::Migration[2.3] + include Gitlab::Database::PartitioningMigrationHelpers + + milestone '18.4' + + disable_ddl_transaction! + + def up + opts = { + if_not_exists: true, + primary_key: [:id, :partition], + options: 'PARTITION BY LIST (partition)' + } + + create_table(:batched_background_operation_jobs, **opts) do |t| + t.bigserial :id + t.timestamptz :created_at, null: false + t.timestamptz :started_at + t.timestamptz :finished_at + t.bigint :batched_background_operation_id + t.integer :batch_size, null: false + t.integer :sub_batch_size, null: false + t.integer :pause_ms, null: false, default: 100 + t.integer :partition, null: false, default: 1 + t.integer :batched_background_operation_partition, null: false + t.integer :status, null: false, default: 0, limit: 2 + t.integer :attempts, null: false, default: 0, limit: 2 + t.jsonb :metrics, null: false, default: {} + t.jsonb :min_cursor + t.jsonb :max_cursor + end + + add_indexes + add_constraints + + add_concurrent_partitioned_foreign_key( + :batched_background_operation_jobs, + :batched_background_operations, + column: [:batched_background_operation_partition, :batched_background_operation_id], + target_column: [:partition, :id], + reverse_lock_order: true, + validate: true, + on_update: :cascade, + on_delete: :cascade + ) + end + + def down + drop_table(:batched_background_operation_jobs) + end + + private + + def add_indexes + add_concurrent_partitioned_index( + :batched_background_operation_jobs, + :status, + name: 'index_bbo_jobs_by_status' + ) + + add_concurrent_partitioned_index( + :batched_background_operation_jobs, + :batched_background_operation_id, + name: 'index_bbo_jobs_by_batched_operation_id' + ) + + add_concurrent_partitioned_index( + :batched_background_operation_jobs, + [:batched_background_operation_id, :id], + name: 'index_bbo_jobs_by_batched_operation_id_and_id' + ) + + add_concurrent_partitioned_index( + :batched_background_operation_jobs, + [:batched_background_operation_id, :status], + name: 'index_bbo_jobs_by_batched_operation_id_and_status' + ) + + add_concurrent_partitioned_index( + :batched_background_operation_jobs, + [:batched_background_operation_id, :max_cursor], + name: 'index_bbo_jobs_on_operation_id_and_cursor_max_value', where: 'max_cursor IS NOT NULL' + ) + + add_concurrent_partitioned_index( + :batched_background_operation_jobs, + [:batched_background_operation_id, :finished_at], + name: 'index_bbo_jobs_on_operation_id_and_finished_at' + ) + end + + def add_constraints + add_check_constraint( + :batched_background_operation_jobs, + "jsonb_typeof(min_cursor) = 'array' AND jsonb_typeof(max_cursor) = 'array'", + check_constraint_name(:batched_background_operation_jobs, 'cursors', 'jsonb_array') + ) + + add_check_constraint( + :batched_background_operation_jobs, + "pause_ms >= 100", + check_constraint_name(:batched_background_operation_jobs, 'pause_ms', 'minimum_hundred') + ) + + add_check_constraint( + :batched_background_operation_jobs, + 'num_nonnulls(min_cursor, max_cursor) = 2', + check_constraint_name(:batched_background_operation_jobs, 'cursors', 'not_null') + ) + end +end diff --git a/db/schema_migrations/20250829132949 b/db/schema_migrations/20250829132949 new file mode 100644 index 00000000000000..578bbf9e8b7a9a --- /dev/null +++ b/db/schema_migrations/20250829132949 @@ -0,0 +1 @@ +05d28404096be27f6776e80ee94cb559973ab74a0625e73a5c713c19739c3d30 \ No newline at end of file diff --git a/db/schema_migrations/20250901090254 b/db/schema_migrations/20250901090254 new file mode 100644 index 00000000000000..03ccd65266b17e --- /dev/null +++ b/db/schema_migrations/20250901090254 @@ -0,0 +1 @@ +387e6aa5a2be82dbafc6cf42f60d8995592dc5791a2c69d185450d8d20f69a82 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 73340652aedb47..5235cbd37b31c8 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -4877,6 +4877,66 @@ CREATE TABLE batched_background_migration_job_transition_logs ( ) PARTITION BY RANGE (created_at); +CREATE TABLE batched_background_operation_jobs ( + id bigint NOT NULL, + created_at timestamp with time zone NOT NULL, + started_at timestamp with time zone, + finished_at timestamp with time zone, + batched_background_operation_id bigint, + batch_size integer NOT NULL, + sub_batch_size integer NOT NULL, + pause_ms integer DEFAULT 100 NOT NULL, + partition integer DEFAULT 1 NOT NULL, + batched_background_operation_partition integer NOT NULL, + status smallint DEFAULT 0 NOT NULL, + attempts smallint DEFAULT 0 NOT NULL, + metrics jsonb DEFAULT '{}'::jsonb NOT NULL, + min_cursor jsonb, + max_cursor jsonb, + CONSTRAINT check_55b1377c17 CHECK ((num_nonnulls(min_cursor, max_cursor) = 2)), + CONSTRAINT check_8bdd023c8a CHECK (((jsonb_typeof(min_cursor) = 'array'::text) AND (jsonb_typeof(max_cursor) = 'array'::text))), + CONSTRAINT check_8d511627a2 CHECK ((pause_ms >= 100)) +) +PARTITION BY LIST (partition); + +CREATE TABLE batched_background_operations ( + id bigint NOT NULL, + organization_id bigint, + user_id bigint, + total_tuple_count bigint, + started_at timestamp with time zone, + on_hold_until timestamp with time zone, + created_at timestamp with time zone NOT NULL, + finished_at timestamp with time zone, + batch_size integer NOT NULL, + sub_batch_size integer NOT NULL, + pause_ms integer DEFAULT 100 NOT NULL, + max_batch_size integer, + partition integer DEFAULT 1 NOT NULL, + priority smallint DEFAULT 0 NOT NULL, + status smallint DEFAULT 0 NOT NULL, + "interval" smallint NOT NULL, + job_class_name text NOT NULL, + batch_class_name text DEFAULT 'PrimaryKeyBatchingStrategy'::text NOT NULL, + table_name text NOT NULL, + column_name text NOT NULL, + gitlab_schema text NOT NULL, + job_arguments jsonb DEFAULT '"[]"'::jsonb NOT NULL, + min_cursor jsonb, + max_cursor jsonb, + next_min_cursor jsonb, + CONSTRAINT check_15ba69be48 CHECK ((char_length(job_class_name) <= 100)), + CONSTRAINT check_2fec5330f8 CHECK (((jsonb_typeof(min_cursor) = 'array'::text) AND (jsonb_typeof(max_cursor) = 'array'::text))), + CONSTRAINT check_5c31edc326 CHECK ((batch_size >= sub_batch_size)), + CONSTRAINT check_74d81ce341 CHECK ((char_length(table_name) <= 63)), + CONSTRAINT check_a19c84ab29 CHECK ((char_length(column_name) <= 63)), + CONSTRAINT check_ab3241cf50 CHECK ((sub_batch_size > 0)), + CONSTRAINT check_d5d4eb82dc CHECK ((num_nonnulls(min_cursor, max_cursor) = 2)), + CONSTRAINT check_e79737a142 CHECK ((char_length(gitlab_schema) <= 255)), + CONSTRAINT check_ea209e4176 CHECK ((char_length(batch_class_name) <= 100)) +) +PARTITION BY LIST (partition); + CREATE TABLE p_ci_build_names ( build_id bigint NOT NULL, partition_id bigint NOT NULL, @@ -12445,6 +12505,24 @@ CREATE SEQUENCE batched_background_migrations_id_seq ALTER SEQUENCE batched_background_migrations_id_seq OWNED BY batched_background_migrations.id; +CREATE SEQUENCE batched_background_operation_jobs_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE batched_background_operation_jobs_id_seq OWNED BY batched_background_operation_jobs.id; + +CREATE SEQUENCE batched_background_operations_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE batched_background_operations_id_seq OWNED BY batched_background_operations.id; + CREATE TABLE board_assignees ( id bigint NOT NULL, board_id bigint NOT NULL, @@ -30186,6 +30264,10 @@ ALTER TABLE ONLY batched_background_migration_jobs ALTER COLUMN id SET DEFAULT n ALTER TABLE ONLY batched_background_migrations ALTER COLUMN id SET DEFAULT nextval('batched_background_migrations_id_seq'::regclass); +ALTER TABLE ONLY batched_background_operation_jobs ALTER COLUMN id SET DEFAULT nextval('batched_background_operation_jobs_id_seq'::regclass); + +ALTER TABLE ONLY batched_background_operations ALTER COLUMN id SET DEFAULT nextval('batched_background_operations_id_seq'::regclass); + ALTER TABLE ONLY board_assignees ALTER COLUMN id SET DEFAULT nextval('board_assignees_id_seq'::regclass); ALTER TABLE ONLY board_group_recent_visits ALTER COLUMN id SET DEFAULT nextval('board_group_recent_visits_id_seq'::regclass); @@ -32686,6 +32768,12 @@ ALTER TABLE ONLY batched_background_migration_jobs ALTER TABLE ONLY batched_background_migrations ADD CONSTRAINT batched_background_migrations_pkey PRIMARY KEY (id); +ALTER TABLE ONLY batched_background_operation_jobs + ADD CONSTRAINT batched_background_operation_jobs_pkey PRIMARY KEY (id, partition); + +ALTER TABLE ONLY batched_background_operations + ADD CONSTRAINT batched_background_operations_pkey PRIMARY KEY (partition, id); + ALTER TABLE ONLY board_assignees ADD CONSTRAINT board_assignees_pkey PRIMARY KEY (id); @@ -38381,6 +38469,26 @@ CREATE INDEX index_batched_jobs_on_batched_migration_id_and_status ON batched_ba CREATE UNIQUE INDEX index_batched_migrations_on_gl_schema_and_unique_configuration ON batched_background_migrations USING btree (gitlab_schema, job_class_name, table_name, column_name, job_arguments); +CREATE INDEX index_bbo_by_organization ON ONLY batched_background_operations USING btree (organization_id); + +CREATE INDEX index_bbo_by_priority ON ONLY batched_background_operations USING btree (priority); + +CREATE INDEX index_bbo_by_status ON ONLY batched_background_operations USING btree (status); + +CREATE INDEX index_bbo_jobs_by_batched_operation_id ON ONLY batched_background_operation_jobs USING btree (batched_background_operation_id); + +CREATE INDEX index_bbo_jobs_by_batched_operation_id_and_id ON ONLY batched_background_operation_jobs USING btree (batched_background_operation_id, id); + +CREATE INDEX index_bbo_jobs_by_batched_operation_id_and_status ON ONLY batched_background_operation_jobs USING btree (batched_background_operation_id, status); + +CREATE INDEX index_bbo_jobs_by_status ON ONLY batched_background_operation_jobs USING btree (status); + +CREATE INDEX index_bbo_jobs_on_operation_id_and_cursor_max_value ON ONLY batched_background_operation_jobs USING btree (batched_background_operation_id, max_cursor) WHERE (max_cursor IS NOT NULL); + +CREATE INDEX index_bbo_jobs_on_operation_id_and_finished_at ON ONLY batched_background_operation_jobs USING btree (batched_background_operation_id, finished_at); + +CREATE UNIQUE INDEX index_bbo_on_unique_configuration ON ONLY batched_background_operations USING btree (partition, job_class_name, table_name, column_name, job_arguments); + CREATE INDEX index_board_assignees_on_assignee_id ON board_assignees USING btree (assignee_id); CREATE UNIQUE INDEX index_board_assignees_on_board_id_and_assignee_id ON board_assignees USING btree (board_id, assignee_id); @@ -51415,6 +51523,9 @@ ALTER TABLE ONLY cluster_groups ALTER TABLE ONLY project_control_compliance_statuses ADD CONSTRAINT fk_rails_fe05913910 FOREIGN KEY (compliance_requirements_control_id) REFERENCES compliance_requirements_controls(id) ON DELETE CASCADE; +ALTER TABLE batched_background_operation_jobs + ADD CONSTRAINT fk_rails_fe231ffd46 FOREIGN KEY (batched_background_operation_partition, batched_background_operation_id) REFERENCES batched_background_operations(partition, id) ON UPDATE CASCADE ON DELETE CASCADE; + ALTER TABLE ONLY resource_label_events ADD CONSTRAINT fk_rails_fe91ece594 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL; diff --git a/lib/gitlab/database/background_operation/batched_background_operation.rb b/lib/gitlab/database/background_operation/batched_background_operation.rb new file mode 100644 index 00000000000000..8982053494ff26 --- /dev/null +++ b/lib/gitlab/database/background_operation/batched_background_operation.rb @@ -0,0 +1,53 @@ +# frozen_string_literal: true + +module Gitlab + module Database + module BackgroundOperation + class BatchedBackgroundOperation < SharedModel + include PartitionedTable + + MINIMUM_PAUSE_MS = 100 + PARTITION_DURATION = 7.days + + self.table_name = :batched_background_operations + + # This column must be ignored otherwise Rails will cache the default value and `bulk_insert!` will start saving + # incorrect partition. + ignore_column :partition, remove_never: true + + has_many :batched_jobs, + ->(operation) { in_partition(operation) }, + foreign_key: :batched_background_operation_id, + inverse_of: :batched_operation, + partition_foreign_key: :partition + + validates :pause_ms, numericality: { greater_than_or_equal_to: MINIMUM_PAUSE_MS } + validates :job_arguments, uniqueness: { + scope: [:job_class_name, :table_name, :column_name] + } + + scope :for_partition, ->(partition) { where(partition: partition) } + scope :executable, -> { where(status: [:active, :paused]) } + + partitioned_by :partition, strategy: :sliding_list, + next_partition_if: ->(active_partition) do + oldest_record_in_partition = BatchedBackgroundOperation + .select(:id, :created_at) + .for_partition(active_partition.value) + .order(:id) + .limit(1) + .take + + oldest_record_in_partition.present? && + oldest_record_in_partition.created_at < PARTITION_DURATION.ago + end, + detach_partition_if: ->(partition) do + !BatchedBackgroundOperation + .for_partition(partition.value) + .executable + .exists? + end + end + end + end +end diff --git a/lib/gitlab/database/background_operation/batched_background_operation_job.rb b/lib/gitlab/database/background_operation/batched_background_operation_job.rb new file mode 100644 index 00000000000000..e92acaa36f636f --- /dev/null +++ b/lib/gitlab/database/background_operation/batched_background_operation_job.rb @@ -0,0 +1,59 @@ +# frozen_string_literal: true + +module Gitlab + module Database + module BackgroundOperation + class BatchedBackgroundOperationJob < SharedModel + include PartitionedTable + + MINIMUM_PAUSE_MS = 100 + + self.table_name = :batched_background_operation_jobs + + belongs_to :batched_operation, + ->(job) { in_partition(job) }, + class_name: 'Gitlab::Database::BackgroundOperation::BatchedBackgroundOperation', + foreign_key: :batched_background_operation_id, + partition_foreign_key: :batched_background_operation_partition, + inverse_of: :batched_jobs + + validates :pause_ms, numericality: { greater_than_or_equal_to: MINIMUM_PAUSE_MS } + + delegate :job_class, :table_name, :column_name, :job_arguments, :job_class_name, + to: :batched_operation, prefix: :operation + + scope :for_partition, ->(partition) { where(partition: partition) } + scope :executable, -> { where(status: [:pending, :running]) } + + # This column must be ignored otherwise Rails will cache the default value and `bulk_insert!` will start saving + # incorrect partition. + ignore_column :partition, remove_never: true + + partitioned_by :partition, strategy: :sliding_list, + next_partition_if: ->(active_partition) do + oldest_record_in_partition = BatchedBackgroundOperationJob + .select(:id, :created_at) + .for_partition(active_partition.value) + .order(:id) + .limit(1) + .take + + oldest_record_in_partition.present? && oldest_record_in_partition.created_at < PARTITION_DURATION.ago + end, + detach_partition_if: ->(partition) do + !BatchedBackgroundOperationJob + .for_partition(partition.value) + .executable + .exists? + end + + state_machine :status, initial: :pending do + state :pending, value: 0 + state :running, value: 1 + state :failed, value: 2 + state :succeeded, value: 3 + end + end + end + end +end -- GitLab From 54c9fd3ebb34f8012ce648766cc09e18fd3536b8 Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Sun, 7 Sep 2025 10:57:33 +0200 Subject: [PATCH 05/17] Changes BBO model table names - batched_background_operations -> background_operation_workers - batched_background_operation_jobs -> background_operation_jobs # Conflicts: # db/structure.sql --- config/initializers/postgres_partitioning.rb | 4 +- db/docs/background_operation_jobs.yml | 11 ++ ...s.yml => background_operation_workers.yml} | 5 +- db/docs/batched_background_operation_jobs.yml | 12 -- ...49_create_background_operation_workers.rb} | 38 +++--- ...090254_create_background_operation_jobs.rb | 113 ++++++++++++++++++ ...reate_batched_background_operation_jobs.rb | 113 ------------------ db/structure.sql | 111 +++++++++++++++++ ...hed_background_operation_job.rb => job.rb} | 20 ++-- ...ched_background_operation.rb => worker.rb} | 14 +-- 10 files changed, 276 insertions(+), 165 deletions(-) create mode 100644 db/docs/background_operation_jobs.yml rename db/docs/{batched_background_operations.yml => background_operation_workers.yml} (72%) delete mode 100644 db/docs/batched_background_operation_jobs.yml rename db/migrate/{20250829132949_create_batched_background_operations.rb => 20250829132949_create_background_operation_workers.rb} (65%) create mode 100644 db/migrate/20250901090254_create_background_operation_jobs.rb delete mode 100644 db/migrate/20250901090254_create_batched_background_operation_jobs.rb rename lib/gitlab/database/background_operation/{batched_background_operation_job.rb => job.rb} (76%) rename lib/gitlab/database/background_operation/{batched_background_operation.rb => worker.rb} (82%) diff --git a/config/initializers/postgres_partitioning.rb b/config/initializers/postgres_partitioning.rb index ea987ba2ec7d9f..91bf4d14f46c15 100644 --- a/config/initializers/postgres_partitioning.rb +++ b/config/initializers/postgres_partitioning.rb @@ -44,7 +44,9 @@ MergeRequest::CommitsMetadata, WebHookLog, MergeRequests::GeneratedRefCommit, - MergeRequests::MergeData + MergeRequests::MergeData, + Gitlab::Database::BackgroundOperation::Worker, + Gitlab::Database::BackgroundOperation::Job ]) if Gitlab.ee? diff --git a/db/docs/background_operation_jobs.yml b/db/docs/background_operation_jobs.yml new file mode 100644 index 00000000000000..a71fafaf3a8f9e --- /dev/null +++ b/db/docs/background_operation_jobs.yml @@ -0,0 +1,11 @@ +--- +table_name: background_operation_jobs +classes: +- Gitlab::Database::BackgroundOperation::Job +feature_categories: +- database +description: Store jobs info for each background_operation_workers. +introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/203234 +milestone: '18.4' +gitlab_schema: gitlab_shared +table_size: small diff --git a/db/docs/batched_background_operations.yml b/db/docs/background_operation_workers.yml similarity index 72% rename from db/docs/batched_background_operations.yml rename to db/docs/background_operation_workers.yml index 378041144cb299..a57f6d36a00125 100644 --- a/db/docs/batched_background_operations.yml +++ b/db/docs/background_operation_workers.yml @@ -1,8 +1,7 @@ --- -table_name: batched_background_operations +table_name: background_operation_workers classes: -- Gitlab::Database::BackgroundOperation::BatchedBackgroundOperation -- Gitlab::Database::BatchedBackgroundOperation +- Gitlab::Database::BackgroundOperation::Worker feature_categories: - database description: Stores information about the large data operations performed async. See diff --git a/db/docs/batched_background_operation_jobs.yml b/db/docs/batched_background_operation_jobs.yml deleted file mode 100644 index 61ff91ce983331..00000000000000 --- a/db/docs/batched_background_operation_jobs.yml +++ /dev/null @@ -1,12 +0,0 @@ ---- -table_name: batched_background_operation_jobs -classes: -- Gitlab::Database::BackgroundOperation::BatchedBackgroundOperationJob -- Gitlab::Database::BatchedBackgroundOperationJob -feature_categories: -- database -description: Store batched jobs info for each batched_background_operations. -introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/203234 -milestone: '18.4' -gitlab_schema: gitlab_shared -table_size: small diff --git a/db/migrate/20250829132949_create_batched_background_operations.rb b/db/migrate/20250829132949_create_background_operation_workers.rb similarity index 65% rename from db/migrate/20250829132949_create_batched_background_operations.rb rename to db/migrate/20250829132949_create_background_operation_workers.rb index c188461394ea34..82b4d9634e50f7 100644 --- a/db/migrate/20250829132949_create_batched_background_operations.rb +++ b/db/migrate/20250829132949_create_background_operation_workers.rb @@ -1,6 +1,6 @@ # frozen_string_literal: true -class CreateBatchedBackgroundOperations < Gitlab::Database::Migration[2.3] +class CreateBackgroundOperationWorkers < Gitlab::Database::Migration[2.3] include Gitlab::Database::PartitioningMigrationHelpers milestone '18.4' @@ -14,7 +14,7 @@ def up options: 'PARTITION BY LIST (partition)' } - create_table(:batched_background_operations, **opts) do |t| + create_table(:background_operation_workers, **opts) do |t| t.bigserial :id t.bigint :organization_id t.bigint :user_id @@ -47,56 +47,56 @@ def up end def down - drop_table(:batched_background_operations) + drop_table(:background_operation_workers, if_exists: true) end private def add_indexes add_concurrent_partitioned_index( - :batched_background_operations, + :background_operation_workers, :status, - name: 'index_bbo_by_status' + name: 'index_background_operation_workers_by_status' ) add_concurrent_partitioned_index( - :batched_background_operations, + :background_operation_workers, :priority, - name: 'index_bbo_by_priority' + name: 'index_background_operation_workers_by_priority' ) add_concurrent_partitioned_index( - :batched_background_operations, + :background_operation_workers, :organization_id, - name: 'index_bbo_by_organization' + name: 'index_background_operation_workers_by_organization' ) add_concurrent_partitioned_index( - :batched_background_operations, + :background_operation_workers, [:partition, :job_class_name, :table_name, :column_name, :job_arguments], unique: true, - name: 'index_bbo_on_unique_configuration' + name: 'index_background_operation_workers_on_unique_configuration' ) end def add_constraints add_check_constraint( - :batched_background_operations, + :background_operation_workers, '(batch_size >= sub_batch_size)', - check_constraint_name(:batched_background_operations, 'batch_size', 'greater_than_sub_batch_size') + check_constraint_name(:background_operation_workers, 'batch_size', 'greater_than_sub_batch_size') ) add_check_constraint( - :batched_background_operations, + :background_operation_workers, '(sub_batch_size > 0)', - check_constraint_name(:batched_background_operations, 'sub_batch_size', 'greater_than_zero') + check_constraint_name(:background_operation_workers, 'sub_batch_size', 'greater_than_zero') ) add_check_constraint( - :batched_background_operations, + :background_operation_workers, "jsonb_typeof(min_cursor) = 'array' AND jsonb_typeof(max_cursor) = 'array'", - check_constraint_name(:batched_background_operations, 'cursors', 'jsonb_array') + check_constraint_name(:background_operation_workers, 'cursors', 'jsonb_array') ) add_check_constraint( - :batched_background_operations, + :background_operation_workers, 'num_nonnulls(min_cursor, max_cursor) = 2', - check_constraint_name(:batched_background_operations, 'cursors', 'not_null') + check_constraint_name(:background_operation_workers, 'cursors', 'not_null') ) end end diff --git a/db/migrate/20250901090254_create_background_operation_jobs.rb b/db/migrate/20250901090254_create_background_operation_jobs.rb new file mode 100644 index 00000000000000..d86793f97b6991 --- /dev/null +++ b/db/migrate/20250901090254_create_background_operation_jobs.rb @@ -0,0 +1,113 @@ +# frozen_string_literal: true + +class CreateBackgroundOperationJobs < Gitlab::Database::Migration[2.3] + include Gitlab::Database::PartitioningMigrationHelpers + + milestone '18.4' + + disable_ddl_transaction! + + def up + opts = { + if_not_exists: true, + primary_key: [:id, :partition], + options: 'PARTITION BY LIST (partition)' + } + + create_table(:background_operation_jobs, **opts) do |t| + t.bigserial :id + t.timestamptz :created_at, null: false + t.timestamptz :started_at + t.timestamptz :finished_at + t.bigint :background_operation_worker_id + t.integer :batch_size, null: false + t.integer :sub_batch_size, null: false + t.integer :pause_ms, null: false, default: 100 + t.integer :partition, null: false, default: 1 + t.integer :background_operation_worker_partition, null: false + t.integer :status, null: false, default: 0, limit: 2 + t.integer :attempts, null: false, default: 0, limit: 2 + t.jsonb :metrics, null: false, default: {} + t.jsonb :min_cursor + t.jsonb :max_cursor + end + + add_indexes + add_constraints + + add_concurrent_partitioned_foreign_key( + :background_operation_jobs, + :background_operation_workers, + column: [:background_operation_worker_partition, :background_operation_worker_id], + target_column: [:partition, :id], + reverse_lock_order: true, + validate: true, + on_update: :cascade, + on_delete: :cascade + ) + end + + def down + drop_table(:background_operation_jobs, if_exists: true) + end + + private + + def add_indexes + add_concurrent_partitioned_index( + :background_operation_jobs, + :status, + name: 'index_background_jobs_by_status' + ) + + add_concurrent_partitioned_index( + :background_operation_jobs, + :background_operation_worker_id, + name: 'index_background_jobs_by_worker_id' + ) + + add_concurrent_partitioned_index( + :background_operation_jobs, + [:background_operation_worker_id, :id], + name: 'index_background_jobs_by_worker_id_and_id' + ) + + add_concurrent_partitioned_index( + :background_operation_jobs, + [:background_operation_worker_id, :status], + name: 'index_background_jobs_by_worker_id_and_status' + ) + + add_concurrent_partitioned_index( + :background_operation_jobs, + [:background_operation_worker_id, :max_cursor], + name: 'index_background_jobs_on_worker_id_and_cursor_max_value', where: 'max_cursor IS NOT NULL' + ) + + add_concurrent_partitioned_index( + :background_operation_jobs, + [:background_operation_worker_id, :finished_at], + name: 'index_background_jobs_on_worker_id_and_finished_at' + ) + end + + def add_constraints + add_check_constraint( + :background_operation_jobs, + "jsonb_typeof(min_cursor) = 'array' AND jsonb_typeof(max_cursor) = 'array'", + check_constraint_name(:background_operation_jobs, 'cursors', 'jsonb_array') + ) + + add_check_constraint( + :background_operation_jobs, + "pause_ms >= 100", + check_constraint_name(:background_operation_jobs, 'pause_ms', 'minimum_hundred') + ) + + add_check_constraint( + :background_operation_jobs, + 'num_nonnulls(min_cursor, max_cursor) = 2', + check_constraint_name(:background_operation_jobs, 'cursors', 'not_null') + ) + end +end diff --git a/db/migrate/20250901090254_create_batched_background_operation_jobs.rb b/db/migrate/20250901090254_create_batched_background_operation_jobs.rb deleted file mode 100644 index 2e0147396f5cfd..00000000000000 --- a/db/migrate/20250901090254_create_batched_background_operation_jobs.rb +++ /dev/null @@ -1,113 +0,0 @@ -# frozen_string_literal: true - -class CreateBatchedBackgroundOperationJobs < Gitlab::Database::Migration[2.3] - include Gitlab::Database::PartitioningMigrationHelpers - - milestone '18.4' - - disable_ddl_transaction! - - def up - opts = { - if_not_exists: true, - primary_key: [:id, :partition], - options: 'PARTITION BY LIST (partition)' - } - - create_table(:batched_background_operation_jobs, **opts) do |t| - t.bigserial :id - t.timestamptz :created_at, null: false - t.timestamptz :started_at - t.timestamptz :finished_at - t.bigint :batched_background_operation_id - t.integer :batch_size, null: false - t.integer :sub_batch_size, null: false - t.integer :pause_ms, null: false, default: 100 - t.integer :partition, null: false, default: 1 - t.integer :batched_background_operation_partition, null: false - t.integer :status, null: false, default: 0, limit: 2 - t.integer :attempts, null: false, default: 0, limit: 2 - t.jsonb :metrics, null: false, default: {} - t.jsonb :min_cursor - t.jsonb :max_cursor - end - - add_indexes - add_constraints - - add_concurrent_partitioned_foreign_key( - :batched_background_operation_jobs, - :batched_background_operations, - column: [:batched_background_operation_partition, :batched_background_operation_id], - target_column: [:partition, :id], - reverse_lock_order: true, - validate: true, - on_update: :cascade, - on_delete: :cascade - ) - end - - def down - drop_table(:batched_background_operation_jobs) - end - - private - - def add_indexes - add_concurrent_partitioned_index( - :batched_background_operation_jobs, - :status, - name: 'index_bbo_jobs_by_status' - ) - - add_concurrent_partitioned_index( - :batched_background_operation_jobs, - :batched_background_operation_id, - name: 'index_bbo_jobs_by_batched_operation_id' - ) - - add_concurrent_partitioned_index( - :batched_background_operation_jobs, - [:batched_background_operation_id, :id], - name: 'index_bbo_jobs_by_batched_operation_id_and_id' - ) - - add_concurrent_partitioned_index( - :batched_background_operation_jobs, - [:batched_background_operation_id, :status], - name: 'index_bbo_jobs_by_batched_operation_id_and_status' - ) - - add_concurrent_partitioned_index( - :batched_background_operation_jobs, - [:batched_background_operation_id, :max_cursor], - name: 'index_bbo_jobs_on_operation_id_and_cursor_max_value', where: 'max_cursor IS NOT NULL' - ) - - add_concurrent_partitioned_index( - :batched_background_operation_jobs, - [:batched_background_operation_id, :finished_at], - name: 'index_bbo_jobs_on_operation_id_and_finished_at' - ) - end - - def add_constraints - add_check_constraint( - :batched_background_operation_jobs, - "jsonb_typeof(min_cursor) = 'array' AND jsonb_typeof(max_cursor) = 'array'", - check_constraint_name(:batched_background_operation_jobs, 'cursors', 'jsonb_array') - ) - - add_check_constraint( - :batched_background_operation_jobs, - "pause_ms >= 100", - check_constraint_name(:batched_background_operation_jobs, 'pause_ms', 'minimum_hundred') - ) - - add_check_constraint( - :batched_background_operation_jobs, - 'num_nonnulls(min_cursor, max_cursor) = 2', - check_constraint_name(:batched_background_operation_jobs, 'cursors', 'not_null') - ) - end -end diff --git a/db/structure.sql b/db/structure.sql index 5235cbd37b31c8..9e6ae6ce98dda9 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -4699,6 +4699,66 @@ CREATE TABLE audit_events ( ) PARTITION BY RANGE (created_at); +CREATE TABLE background_operation_jobs ( + id bigint NOT NULL, + created_at timestamp with time zone NOT NULL, + started_at timestamp with time zone, + finished_at timestamp with time zone, + background_operation_worker_id bigint, + batch_size integer NOT NULL, + sub_batch_size integer NOT NULL, + pause_ms integer DEFAULT 100 NOT NULL, + partition integer DEFAULT 1 NOT NULL, + background_operation_worker_partition integer NOT NULL, + status smallint DEFAULT 0 NOT NULL, + attempts smallint DEFAULT 0 NOT NULL, + metrics jsonb DEFAULT '{}'::jsonb NOT NULL, + min_cursor jsonb, + max_cursor jsonb, + CONSTRAINT check_22e75767e4 CHECK (((jsonb_typeof(min_cursor) = 'array'::text) AND (jsonb_typeof(max_cursor) = 'array'::text))), + CONSTRAINT check_b922a72749 CHECK ((pause_ms >= 100)), + CONSTRAINT check_fc1d4517f5 CHECK ((num_nonnulls(min_cursor, max_cursor) = 2)) +) +PARTITION BY LIST (partition); + +CREATE TABLE background_operation_workers ( + id bigint NOT NULL, + organization_id bigint, + user_id bigint, + total_tuple_count bigint, + started_at timestamp with time zone, + on_hold_until timestamp with time zone, + created_at timestamp with time zone NOT NULL, + finished_at timestamp with time zone, + batch_size integer NOT NULL, + sub_batch_size integer NOT NULL, + pause_ms integer DEFAULT 100 NOT NULL, + max_batch_size integer, + partition integer DEFAULT 1 NOT NULL, + priority smallint DEFAULT 0 NOT NULL, + status smallint DEFAULT 0 NOT NULL, + "interval" smallint NOT NULL, + job_class_name text NOT NULL, + batch_class_name text DEFAULT 'PrimaryKeyBatchingStrategy'::text NOT NULL, + table_name text NOT NULL, + column_name text NOT NULL, + gitlab_schema text NOT NULL, + job_arguments jsonb DEFAULT '"[]"'::jsonb NOT NULL, + min_cursor jsonb, + max_cursor jsonb, + next_min_cursor jsonb, + CONSTRAINT check_10f672741a CHECK ((char_length(column_name) <= 63)), + CONSTRAINT check_510f6260d5 CHECK ((char_length(gitlab_schema) <= 255)), + CONSTRAINT check_63fe8b8121 CHECK ((sub_batch_size > 0)), + CONSTRAINT check_7f88b7751b CHECK ((char_length(job_class_name) <= 100)), + CONSTRAINT check_91cc32fc67 CHECK ((char_length(batch_class_name) <= 100)), + CONSTRAINT check_c316362d95 CHECK ((char_length(table_name) <= 63)), + CONSTRAINT check_c74b62c410 CHECK ((batch_size >= sub_batch_size)), + CONSTRAINT check_e91dfde154 CHECK ((num_nonnulls(min_cursor, max_cursor) = 2)), + CONSTRAINT check_f1affe613c CHECK (((jsonb_typeof(min_cursor) = 'array'::text) AND (jsonb_typeof(max_cursor) = 'array'::text))) +) +PARTITION BY LIST (partition); + CREATE TABLE backup_finding_evidences ( created_at timestamp with time zone NOT NULL, updated_at timestamp with time zone NOT NULL, @@ -12386,6 +12446,24 @@ CREATE TABLE aws_roles ( CONSTRAINT check_57adedab55 CHECK ((char_length(region) <= 255)) ); +CREATE SEQUENCE background_operation_jobs_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE background_operation_jobs_id_seq OWNED BY background_operation_jobs.id; + +CREATE SEQUENCE background_operation_workers_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE background_operation_workers_id_seq OWNED BY background_operation_workers.id; + CREATE TABLE badges ( id bigint NOT NULL, link_url character varying NOT NULL, @@ -30256,6 +30334,10 @@ ALTER TABLE ONLY automation_rules ALTER COLUMN id SET DEFAULT nextval('automatio ALTER TABLE ONLY award_emoji ALTER COLUMN id SET DEFAULT nextval('award_emoji_id_seq'::regclass); +ALTER TABLE ONLY background_operation_jobs ALTER COLUMN id SET DEFAULT nextval('background_operation_jobs_id_seq'::regclass); + +ALTER TABLE ONLY background_operation_workers ALTER COLUMN id SET DEFAULT nextval('background_operation_workers_id_seq'::regclass); + ALTER TABLE ONLY badges ALTER COLUMN id SET DEFAULT nextval('badges_id_seq'::regclass); ALTER TABLE ONLY batched_background_migration_job_transition_logs ALTER COLUMN id SET DEFAULT nextval('batched_background_migration_job_transition_logs_id_seq'::regclass); @@ -32708,6 +32790,12 @@ ALTER TABLE ONLY award_emoji ALTER TABLE ONLY aws_roles ADD CONSTRAINT aws_roles_pkey PRIMARY KEY (user_id); +ALTER TABLE ONLY background_operation_jobs + ADD CONSTRAINT background_operation_jobs_pkey PRIMARY KEY (id, partition); + +ALTER TABLE ONLY background_operation_workers + ADD CONSTRAINT background_operation_workers_pkey PRIMARY KEY (partition, id); + ALTER TABLE ONLY backup_finding_evidences ADD CONSTRAINT backup_finding_evidences_pkey PRIMARY KEY (original_record_identifier, date); @@ -38395,6 +38483,26 @@ CREATE UNIQUE INDEX index_aws_roles_on_role_external_id ON aws_roles USING btree CREATE UNIQUE INDEX index_aws_roles_on_user_id ON aws_roles USING btree (user_id); +CREATE INDEX index_background_jobs_by_status ON ONLY background_operation_jobs USING btree (status); + +CREATE INDEX index_background_jobs_by_worker_id ON ONLY background_operation_jobs USING btree (background_operation_worker_id); + +CREATE INDEX index_background_jobs_by_worker_id_and_id ON ONLY background_operation_jobs USING btree (background_operation_worker_id, id); + +CREATE INDEX index_background_jobs_by_worker_id_and_status ON ONLY background_operation_jobs USING btree (background_operation_worker_id, status); + +CREATE INDEX index_background_jobs_on_worker_id_and_cursor_max_value ON ONLY background_operation_jobs USING btree (background_operation_worker_id, max_cursor) WHERE (max_cursor IS NOT NULL); + +CREATE INDEX index_background_jobs_on_worker_id_and_finished_at ON ONLY background_operation_jobs USING btree (background_operation_worker_id, finished_at); + +CREATE INDEX index_background_operation_workers_by_organization ON ONLY background_operation_workers USING btree (organization_id); + +CREATE INDEX index_background_operation_workers_by_priority ON ONLY background_operation_workers USING btree (priority); + +CREATE INDEX index_background_operation_workers_by_status ON ONLY background_operation_workers USING btree (status); + +CREATE UNIQUE INDEX index_background_operation_workers_on_unique_configuration ON ONLY background_operation_workers USING btree (partition, job_class_name, table_name, column_name, job_arguments); + CREATE INDEX index_backup_finding_evidences_on_fk ON ONLY backup_finding_evidences USING btree (finding_id); CREATE INDEX index_backup_finding_evidences_on_project_id ON ONLY backup_finding_evidences USING btree (project_id); @@ -50374,6 +50482,9 @@ ALTER TABLE ONLY users_security_dashboard_projects ALTER TABLE ONLY analytics_dashboards_pointers ADD CONSTRAINT fk_rails_7027b7eaa9 FOREIGN KEY (namespace_id) REFERENCES namespaces(id) ON DELETE CASCADE; +ALTER TABLE background_operation_jobs + ADD CONSTRAINT fk_rails_702e138bd0 FOREIGN KEY (background_operation_worker_partition, background_operation_worker_id) REFERENCES background_operation_workers(partition, id) ON UPDATE CASCADE ON DELETE CASCADE; + ALTER TABLE ONLY ci_builds_runner_session ADD CONSTRAINT fk_rails_70707857d3_p FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE; diff --git a/lib/gitlab/database/background_operation/batched_background_operation_job.rb b/lib/gitlab/database/background_operation/job.rb similarity index 76% rename from lib/gitlab/database/background_operation/batched_background_operation_job.rb rename to lib/gitlab/database/background_operation/job.rb index e92acaa36f636f..4425fa59bfba2d 100644 --- a/lib/gitlab/database/background_operation/batched_background_operation_job.rb +++ b/lib/gitlab/database/background_operation/job.rb @@ -3,24 +3,24 @@ module Gitlab module Database module BackgroundOperation - class BatchedBackgroundOperationJob < SharedModel + class Job < SharedModel include PartitionedTable MINIMUM_PAUSE_MS = 100 - self.table_name = :batched_background_operation_jobs + self.table_name = :background_operation_jobs - belongs_to :batched_operation, + belongs_to :worker, ->(job) { in_partition(job) }, - class_name: 'Gitlab::Database::BackgroundOperation::BatchedBackgroundOperation', - foreign_key: :batched_background_operation_id, - partition_foreign_key: :batched_background_operation_partition, - inverse_of: :batched_jobs + class_name: 'Gitlab::Database::BackgroundOperation::Worker', + foreign_key: :background_operation_worker_id, + partition_foreign_key: :background_operation_worker_partition, + inverse_of: :jobs validates :pause_ms, numericality: { greater_than_or_equal_to: MINIMUM_PAUSE_MS } delegate :job_class, :table_name, :column_name, :job_arguments, :job_class_name, - to: :batched_operation, prefix: :operation + to: :operation, prefix: :operation scope :for_partition, ->(partition) { where(partition: partition) } scope :executable, -> { where(status: [:pending, :running]) } @@ -31,7 +31,7 @@ class BatchedBackgroundOperationJob < SharedModel partitioned_by :partition, strategy: :sliding_list, next_partition_if: ->(active_partition) do - oldest_record_in_partition = BatchedBackgroundOperationJob + oldest_record_in_partition = Job .select(:id, :created_at) .for_partition(active_partition.value) .order(:id) @@ -41,7 +41,7 @@ class BatchedBackgroundOperationJob < SharedModel oldest_record_in_partition.present? && oldest_record_in_partition.created_at < PARTITION_DURATION.ago end, detach_partition_if: ->(partition) do - !BatchedBackgroundOperationJob + !Job .for_partition(partition.value) .executable .exists? diff --git a/lib/gitlab/database/background_operation/batched_background_operation.rb b/lib/gitlab/database/background_operation/worker.rb similarity index 82% rename from lib/gitlab/database/background_operation/batched_background_operation.rb rename to lib/gitlab/database/background_operation/worker.rb index 8982053494ff26..b11b07a7cd941d 100644 --- a/lib/gitlab/database/background_operation/batched_background_operation.rb +++ b/lib/gitlab/database/background_operation/worker.rb @@ -3,22 +3,22 @@ module Gitlab module Database module BackgroundOperation - class BatchedBackgroundOperation < SharedModel + class Worker < SharedModel include PartitionedTable MINIMUM_PAUSE_MS = 100 PARTITION_DURATION = 7.days - self.table_name = :batched_background_operations + self.table_name = :background_operation_workers # This column must be ignored otherwise Rails will cache the default value and `bulk_insert!` will start saving # incorrect partition. ignore_column :partition, remove_never: true - has_many :batched_jobs, + has_many :jobs, ->(operation) { in_partition(operation) }, - foreign_key: :batched_background_operation_id, - inverse_of: :batched_operation, + foreign_key: :background_operation_id, + inverse_of: :operation, partition_foreign_key: :partition validates :pause_ms, numericality: { greater_than_or_equal_to: MINIMUM_PAUSE_MS } @@ -31,7 +31,7 @@ class BatchedBackgroundOperation < SharedModel partitioned_by :partition, strategy: :sliding_list, next_partition_if: ->(active_partition) do - oldest_record_in_partition = BatchedBackgroundOperation + oldest_record_in_partition = Worker .select(:id, :created_at) .for_partition(active_partition.value) .order(:id) @@ -42,7 +42,7 @@ class BatchedBackgroundOperation < SharedModel oldest_record_in_partition.created_at < PARTITION_DURATION.ago end, detach_partition_if: ->(partition) do - !BatchedBackgroundOperation + !Worker .for_partition(partition.value) .executable .exists? -- GitLab From 0e89db7d7d14a8dbc92da99d33260d9590588171 Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Tue, 9 Sep 2025 13:56:44 +0200 Subject: [PATCH 06/17] Adds specs for BBO models --- ...949_create_background_operation_workers.rb | 2 +- ...090254_create_background_operation_jobs.rb | 15 +- db/structure.sql | 7 +- .../database/background_operation/job.rb | 65 ++++---- .../database/background_operation/worker.rb | 78 ++++++---- .../database/background_operation/jobs.rb | 34 +++++ .../database/background_operation/workers.rb | 37 +++++ .../database/background_operation/job_spec.rb | 144 ++++++++++++++++++ .../background_operation/worker_spec.rb | 134 ++++++++++++++++ 9 files changed, 443 insertions(+), 73 deletions(-) create mode 100644 spec/factories/gitlab/database/background_operation/jobs.rb create mode 100644 spec/factories/gitlab/database/background_operation/workers.rb create mode 100644 spec/lib/gitlab/database/background_operation/job_spec.rb create mode 100644 spec/lib/gitlab/database/background_operation/worker_spec.rb diff --git a/db/migrate/20250829132949_create_background_operation_workers.rb b/db/migrate/20250829132949_create_background_operation_workers.rb index 82b4d9634e50f7..6c861297cda5cb 100644 --- a/db/migrate/20250829132949_create_background_operation_workers.rb +++ b/db/migrate/20250829132949_create_background_operation_workers.rb @@ -36,7 +36,7 @@ def up t.text :table_name, null: false, limit: 63 t.text :column_name, null: false, limit: 63 t.text :gitlab_schema, null: false, limit: 255 - t.jsonb :job_arguments, null: false, default: '[]' + t.jsonb :job_arguments, default: '[]' t.jsonb :min_cursor t.jsonb :max_cursor t.jsonb :next_min_cursor diff --git a/db/migrate/20250901090254_create_background_operation_jobs.rb b/db/migrate/20250901090254_create_background_operation_jobs.rb index d86793f97b6991..53d8962bd504c3 100644 --- a/db/migrate/20250901090254_create_background_operation_jobs.rb +++ b/db/migrate/20250901090254_create_background_operation_jobs.rb @@ -10,7 +10,7 @@ class CreateBackgroundOperationJobs < Gitlab::Database::Migration[2.3] def up opts = { if_not_exists: true, - primary_key: [:id, :partition], + primary_key: [:partition, :id], options: 'PARTITION BY LIST (partition)' } @@ -19,7 +19,7 @@ def up t.timestamptz :created_at, null: false t.timestamptz :started_at t.timestamptz :finished_at - t.bigint :background_operation_worker_id + t.bigint :background_operation_worker_id, null: false t.integer :batch_size, null: false t.integer :sub_batch_size, null: false t.integer :pause_ms, null: false, default: 100 @@ -34,17 +34,6 @@ def up add_indexes add_constraints - - add_concurrent_partitioned_foreign_key( - :background_operation_jobs, - :background_operation_workers, - column: [:background_operation_worker_partition, :background_operation_worker_id], - target_column: [:partition, :id], - reverse_lock_order: true, - validate: true, - on_update: :cascade, - on_delete: :cascade - ) end def down diff --git a/db/structure.sql b/db/structure.sql index 9e6ae6ce98dda9..152511a2a83f05 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -4704,7 +4704,7 @@ CREATE TABLE background_operation_jobs ( created_at timestamp with time zone NOT NULL, started_at timestamp with time zone, finished_at timestamp with time zone, - background_operation_worker_id bigint, + background_operation_worker_id bigint NOT NULL, batch_size integer NOT NULL, sub_batch_size integer NOT NULL, pause_ms integer DEFAULT 100 NOT NULL, @@ -32791,7 +32791,7 @@ ALTER TABLE ONLY aws_roles ADD CONSTRAINT aws_roles_pkey PRIMARY KEY (user_id); ALTER TABLE ONLY background_operation_jobs - ADD CONSTRAINT background_operation_jobs_pkey PRIMARY KEY (id, partition); + ADD CONSTRAINT background_operation_jobs_pkey PRIMARY KEY (partition, id); ALTER TABLE ONLY background_operation_workers ADD CONSTRAINT background_operation_workers_pkey PRIMARY KEY (partition, id); @@ -50482,9 +50482,6 @@ ALTER TABLE ONLY users_security_dashboard_projects ALTER TABLE ONLY analytics_dashboards_pointers ADD CONSTRAINT fk_rails_7027b7eaa9 FOREIGN KEY (namespace_id) REFERENCES namespaces(id) ON DELETE CASCADE; -ALTER TABLE background_operation_jobs - ADD CONSTRAINT fk_rails_702e138bd0 FOREIGN KEY (background_operation_worker_partition, background_operation_worker_id) REFERENCES background_operation_workers(partition, id) ON UPDATE CASCADE ON DELETE CASCADE; - ALTER TABLE ONLY ci_builds_runner_session ADD CONSTRAINT fk_rails_70707857d3_p FOREIGN KEY (partition_id, build_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE; diff --git a/lib/gitlab/database/background_operation/job.rb b/lib/gitlab/database/background_operation/job.rb index 4425fa59bfba2d..5725faee991316 100644 --- a/lib/gitlab/database/background_operation/job.rb +++ b/lib/gitlab/database/background_operation/job.rb @@ -6,46 +6,57 @@ module BackgroundOperation class Job < SharedModel include PartitionedTable + self.table_name = :background_operation_jobs + MINIMUM_PAUSE_MS = 100 + PARTITION_DURATION = 14.days - self.table_name = :background_operation_jobs + REQUIRED_COLUMNS = %i[ + batch_size + sub_batch_size + background_operation_worker_id + background_operation_worker_partition + ].freeze belongs_to :worker, - ->(job) { in_partition(job) }, - class_name: 'Gitlab::Database::BackgroundOperation::Worker', - foreign_key: :background_operation_worker_id, - partition_foreign_key: :background_operation_worker_partition, - inverse_of: :jobs + ->(job) { where(partition: job.background_operation_worker_partition) }, + class_name: 'Gitlab::Database::BackgroundOperation::Worker', + foreign_key: :background_operation_worker_id, + partition_foreign_key: :background_operation_worker_partition, + inverse_of: :jobs + + REQUIRED_COLUMNS.each do |column| + validates column, presence: true + end validates :pause_ms, numericality: { greater_than_or_equal_to: MINIMUM_PAUSE_MS } delegate :job_class, :table_name, :column_name, :job_arguments, :job_class_name, - to: :operation, prefix: :operation + to: :worker, prefix: :worker scope :for_partition, ->(partition) { where(partition: partition) } - scope :executable, -> { where(status: [:pending, :running]) } + scope :executable, -> { with_statuses(:pending, :running) } - # This column must be ignored otherwise Rails will cache the default value and `bulk_insert!` will start saving - # incorrect partition. - ignore_column :partition, remove_never: true + # Partition should not be changed once the record is created + attr_readonly :partition partitioned_by :partition, strategy: :sliding_list, - next_partition_if: ->(active_partition) do - oldest_record_in_partition = Job - .select(:id, :created_at) - .for_partition(active_partition.value) - .order(:id) - .limit(1) - .take - - oldest_record_in_partition.present? && oldest_record_in_partition.created_at < PARTITION_DURATION.ago - end, - detach_partition_if: ->(partition) do - !Job - .for_partition(partition.value) - .executable - .exists? - end + next_partition_if: ->(active_partition) do + oldest_record_in_partition = Job + .select(:id, :created_at) + .for_partition(active_partition.value) + .order(:id) + .limit(1) + .take + + oldest_record_in_partition.present? && oldest_record_in_partition.created_at < PARTITION_DURATION.ago + end, + detach_partition_if: ->(partition) do + !Job + .for_partition(partition.value) + .executable + .exists? + end state_machine :status, initial: :pending do state :pending, value: 0 diff --git a/lib/gitlab/database/background_operation/worker.rb b/lib/gitlab/database/background_operation/worker.rb index b11b07a7cd941d..d21a6f53779da9 100644 --- a/lib/gitlab/database/background_operation/worker.rb +++ b/lib/gitlab/database/background_operation/worker.rb @@ -6,47 +6,71 @@ module BackgroundOperation class Worker < SharedModel include PartitionedTable + self.table_name = :background_operation_workers + MINIMUM_PAUSE_MS = 100 - PARTITION_DURATION = 7.days + PARTITION_DURATION = 14.days - self.table_name = :background_operation_workers + REQUIRED_COLUMNS = %i[ + batch_size + sub_batch_size + priority + interval + job_class_name + batch_class_name + table_name + column_name + gitlab_schema + ].freeze - # This column must be ignored otherwise Rails will cache the default value and `bulk_insert!` will start saving - # incorrect partition. - ignore_column :partition, remove_never: true + # Partition should not be changed once the record is created + attr_readonly :partition has_many :jobs, - ->(operation) { in_partition(operation) }, - foreign_key: :background_operation_id, - inverse_of: :operation, - partition_foreign_key: :partition + ->(worker) { where(background_operation_worker_partition: worker.partition) }, + class_name: 'Gitlab::Database::BackgroundOperation::Job', + foreign_key: :background_operation_worker_id, + inverse_of: :worker, + partition_foreign_key: :background_operation_worker_partition + + REQUIRED_COLUMNS.each do |column| + validates column, presence: true + end validates :pause_ms, numericality: { greater_than_or_equal_to: MINIMUM_PAUSE_MS } + validates :job_arguments, uniqueness: { scope: [:job_class_name, :table_name, :column_name] } scope :for_partition, ->(partition) { where(partition: partition) } - scope :executable, -> { where(status: [:active, :paused]) } + scope :executable, -> { with_statuses(:active, :paused) } partitioned_by :partition, strategy: :sliding_list, - next_partition_if: ->(active_partition) do - oldest_record_in_partition = Worker - .select(:id, :created_at) - .for_partition(active_partition.value) - .order(:id) - .limit(1) - .take - - oldest_record_in_partition.present? && - oldest_record_in_partition.created_at < PARTITION_DURATION.ago - end, - detach_partition_if: ->(partition) do - !Worker - .for_partition(partition.value) - .executable - .exists? - end + next_partition_if: ->(active_partition) do + oldest_record_in_partition = Worker + .select(:id, :created_at) + .for_partition(active_partition.value) + .order(:id) + .limit(1) + .take + + oldest_record_in_partition.present? && + oldest_record_in_partition.created_at < PARTITION_DURATION.ago + end, + detach_partition_if: ->(partition) do + !Worker + .for_partition(partition.value) + .executable + .exists? + end + + state_machine :status, initial: :paused do + state :paused, value: 0 + state :active, value: 1 + state :finished, value: 2 + state :failed, value: 3 + end end end end diff --git a/spec/factories/gitlab/database/background_operation/jobs.rb b/spec/factories/gitlab/database/background_operation/jobs.rb new file mode 100644 index 00000000000000..98d3928fd8d3e8 --- /dev/null +++ b/spec/factories/gitlab/database/background_operation/jobs.rb @@ -0,0 +1,34 @@ +# frozen_string_literal: true + +FactoryBot.define do + factory :background_operation_job, class: 'Gitlab::Database::BackgroundOperation::Job' do + association :worker, factory: :background_operation_worker + + batch_size { 100 } + sub_batch_size { 10 } + min_cursor { [1] } + max_cursor { [1000] } + background_operation_worker_id { 1 } + background_operation_worker_partition { 1 } + + trait :pending do + status { 0 } + end + + trait :running do + status { 1 } + started_at { Time.current } + end + + trait :failed do + status { 2 } + attempts { 1 } + end + + trait :succeeded do + status { 3 } + started_at { 1.hour.ago } + finished_at { Time.current } + end + end +end diff --git a/spec/factories/gitlab/database/background_operation/workers.rb b/spec/factories/gitlab/database/background_operation/workers.rb new file mode 100644 index 00000000000000..4075ae9c908cab --- /dev/null +++ b/spec/factories/gitlab/database/background_operation/workers.rb @@ -0,0 +1,37 @@ +# frozen_string_literal: true + +FactoryBot.define do + factory :background_operation_worker, class: 'Gitlab::Database::BackgroundOperation::Worker' do + organization_id { 1 } + user_id { 1 } + job_class_name { 'CopyColumnUsingBackgroundMigrationJob' } + batch_class_name { 'PrimaryKeyBatchingStrategy' } + table_name { :users } + column_name { :id } + gitlab_schema { :gitlab_main_org } + batch_size { 1000 } + sub_batch_size { 100 } + pause_ms { 100 } + priority { 0 } + interval { 2.minutes } + sequence(:job_arguments) { |n| [["column_#{n}"], ["column_#{n}_convert_to_bigint"]] } + min_cursor { [1] } + max_cursor { [1000] } + + trait :paused do + status { 0 } + end + + trait :active do + status { 1 } + end + + trait :finished do + status { 2 } + end + + trait :failed do + status { 3 } + end + end +end diff --git a/spec/lib/gitlab/database/background_operation/job_spec.rb b/spec/lib/gitlab/database/background_operation/job_spec.rb new file mode 100644 index 00000000000000..b26bcf6eafc017 --- /dev/null +++ b/spec/lib/gitlab/database/background_operation/job_spec.rb @@ -0,0 +1,144 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Database::BackgroundOperation::Job, type: :model, feature_category: :database do + using RSpec::Parameterized::TableSyntax + + it { is_expected.to be_a Gitlab::Database::SharedModel } + + describe 'associations' do + it { is_expected.to belong_to(:worker).inverse_of(:jobs) } + end + + describe 'validations' do + subject { build(:background_operation_job) } + + described_class::REQUIRED_COLUMNS.each do |column| + it { is_expected.to validate_presence_of(column) } + end + + it { is_expected.to validate_numericality_of(:pause_ms).is_greater_than_or_equal_to(100) } + end + + describe 'scopes' do + let_it_be(:job_1) { create(:background_operation_job, :pending) } + let_it_be(:job_2) { create(:background_operation_job, :running) } + let_it_be(:job_3) { create(:background_operation_job, :failed) } + let_it_be(:job_4) { create(:background_operation_job, :succeeded) } + + describe '.executable' do + it 'returns jobs with only with pending or running status' do + expect(described_class.executable).to contain_exactly(job_1, job_2) + end + end + end + + describe 'sliding_list partitioning' do + let(:connection) { described_class.connection } + let(:partition_manager) { Gitlab::Database::Partitioning::PartitionManager.new(described_class) } + + describe 'next_partition_if callback' do + let(:active_partition) { described_class.partitioning_strategy.active_partition } + + subject(:value) { described_class.partitioning_strategy.next_partition_if.call(active_partition) } + + context 'when the partition is empty' do + it { is_expected.to be(false) } + end + + context 'when the partition has recent records' do + before do + create(:background_operation_job, created_at: 1.day.ago) + end + + it { is_expected.to be(false) } + end + + context 'when the first record of the partition is older than PARTITION_DURATION' do + before do + create(:background_operation_job, created_at: (described_class::PARTITION_DURATION + 1.day).ago) + create(:background_operation_job, created_at: 1.day.ago) + end + + it { is_expected.to be(true) } + end + end + + describe 'detach_partition_if callback' do + let(:active_partition) { described_class.partitioning_strategy.active_partition } + + subject(:value) { described_class.partitioning_strategy.detach_partition_if.call(active_partition) } + + context 'when the partition contains executable jobs' do + before do + create(:background_operation_job, :pending) + create(:background_operation_job, :running) + create(:background_operation_job, :succeeded) + end + + it { is_expected.to be(false) } + end + + context 'when the partition contains only non-executable jobs' do + before do + create(:background_operation_job, :succeeded) + create(:background_operation_job, :failed) + end + + it { is_expected.to be(true) } + end + + context 'when the partition is empty' do + it { is_expected.to be(true) } + end + end + + describe 'the behavior of the strategy' do + it 'moves records to new partitions as time passes', :freeze_time do + # We start with partition 1 + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) + + # it's not a day old yet so no new partitions are created + partition_manager.sync_partitions + + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) + + # add one record so the next partition will be created + create(:background_operation_job) + + # after traveling forward past PARTITION_DURATION + travel(Gitlab::Database::BackgroundOperation::Worker::PARTITION_DURATION + 1.second) + + # a new partition is created + partition_manager.sync_partitions + + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1, 2]) + + # and we can insert to the new partition + expect { create(:background_operation_job) }.not_to raise_error + + # after marking old records as non-executable + described_class.for_partition(1).update_all(status: 3) + + partition_manager.sync_partitions + + # the old one is removed + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([2]) + + # and we only have the newly created partition left. + expect(described_class.count).to eq(1) + end + end + end + + # describe 'partition consistency' do + # it 'ensures job and worker are in the same partition' do + # worker = create(:background_operation_worker, partition: 2) + # job = create(:background_operation_job, worker: worker) + # + # expect(job.partition).to eq(worker.partition) + # expect(job.background_operation_worker_partition).to eq(worker.partition) + # end + # end +end diff --git a/spec/lib/gitlab/database/background_operation/worker_spec.rb b/spec/lib/gitlab/database/background_operation/worker_spec.rb new file mode 100644 index 00000000000000..52326c79d25db8 --- /dev/null +++ b/spec/lib/gitlab/database/background_operation/worker_spec.rb @@ -0,0 +1,134 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Database::BackgroundOperation::Worker, type: :model, feature_category: :database do + using RSpec::Parameterized::TableSyntax + + it { is_expected.to be_a Gitlab::Database::SharedModel } + + describe 'associations' do + it { is_expected.to have_many(:jobs).inverse_of(:worker) } + end + + describe 'validations' do + subject { build(:background_operation_worker) } + + described_class::REQUIRED_COLUMNS.each do |column| + it { is_expected.to validate_presence_of(column) } + end + + it { is_expected.to validate_numericality_of(:pause_ms).is_greater_than_or_equal_to(100) } + it { is_expected.to validate_uniqueness_of(:job_arguments).scoped_to(:job_class_name, :table_name, :column_name) } + end + + describe 'scopes' do + let_it_be(:worker_1) { create(:background_operation_worker, :active) } + let_it_be(:worker_2) { create(:background_operation_worker, :paused) } + let_it_be(:worker_3) { create(:background_operation_worker, :finished) } + + describe '.executable' do + it 'returns workers with active or paused status' do + expect(described_class.executable).to contain_exactly(worker_1, worker_2) + end + end + end + + describe 'sliding_list partitioning' do + let(:connection) { described_class.connection } + let(:partition_manager) { Gitlab::Database::Partitioning::PartitionManager.new(described_class) } + + describe 'next_partition_if callback' do + let(:active_partition) { described_class.partitioning_strategy.active_partition } + + subject(:value) { described_class.partitioning_strategy.next_partition_if.call(active_partition) } + + context 'when the partition is empty' do + it { is_expected.to be(false) } + end + + context 'when the partition has recent records' do + before do + create(:background_operation_worker, created_at: 1.day.ago) + end + + it { is_expected.to be(false) } + end + + context 'when the first record of the partition is older than PARTITION_DURATION' do + before do + create(:background_operation_worker, created_at: (described_class::PARTITION_DURATION + 1.day).ago) + create(:background_operation_worker, created_at: 1.day.ago) + end + + it { is_expected.to be(true) } + end + end + + describe 'detach_partition_if callback' do + let(:active_partition) { described_class.partitioning_strategy.active_partition } + + subject(:value) { described_class.partitioning_strategy.detach_partition_if.call(active_partition) } + + context 'when the partition contains executable workers' do + before do + create(:background_operation_worker, :active) + create(:background_operation_worker, :paused) + create(:background_operation_worker, :finished) + end + + it { is_expected.to be(false) } + end + + context 'when the partition contains only non-executable workers' do + before do + create(:background_operation_worker, :finished) + create(:background_operation_worker, :failed) + end + + it { is_expected.to be(true) } + end + + context 'when the partition is empty' do + it { is_expected.to be(true) } + end + end + + describe 'the behavior of the strategy' do + it 'moves records to new partitions as time passes', :freeze_time do + # We start with partition 1 + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) + + # it's not 14 days old yet so no new partitions are created + partition_manager.sync_partitions + + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) + + # add one record so the next partition will be created + create(:background_operation_worker) + + # after traveling forward past PARTITION_DURATION + travel(described_class::PARTITION_DURATION + 1.minute) + + # a new partition is created + partition_manager.sync_partitions + + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1, 2]) + + # and we can insert to the new partition + expect { create(:background_operation_worker) }.not_to raise_error + + # after marking old records as non-executable + described_class.for_partition(1).update_all(status: 2) + + partition_manager.sync_partitions + + # the old one is removed + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([2]) + + # and we only have the newly created partition left. + expect(described_class.count).to eq(1) + end + end + end +end -- GitLab From b911574f2b8119a45da87ef36e4f4073c393b19b Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Wed, 10 Sep 2025 10:32:48 +0200 Subject: [PATCH 07/17] Cleanup batched_background_operations related schema --- ...949_create_background_operation_workers.rb | 26 +++- ...090254_create_background_operation_jobs.rb | 36 ++++- db/structure.sql | 135 +++--------------- spec/db/schema_spec.rb | 3 +- .../database/background_operation/jobs.rb | 4 + .../database/background_operation/workers.rb | 6 +- spec/lib/gitlab/database/sharding_key_spec.rb | 1 + 7 files changed, 86 insertions(+), 125 deletions(-) diff --git a/db/migrate/20250829132949_create_background_operation_workers.rb b/db/migrate/20250829132949_create_background_operation_workers.rb index 6c861297cda5cb..4b5d158a558c50 100644 --- a/db/migrate/20250829132949_create_background_operation_workers.rb +++ b/db/migrate/20250829132949_create_background_operation_workers.rb @@ -42,6 +42,26 @@ def up t.jsonb :next_min_cursor end + add_concurrent_partitioned_foreign_key( + :background_operation_workers, + :organizations, + column: :organization_id, + target_column: :id, + reverse_lock_order: true, + on_delete: :cascade, + validate: true + ) + + add_concurrent_partitioned_foreign_key( + :background_operation_workers, + :users, + column: :user_id, + target_column: :id, + reverse_lock_order: true, + on_delete: :cascade, + validate: true + ) + add_indexes add_constraints end @@ -68,7 +88,11 @@ def add_indexes :organization_id, name: 'index_background_operation_workers_by_organization' ) - + add_concurrent_partitioned_index( + :background_operation_workers, + :user_id, + name: 'index_background_operation_workers_by_user' + ) add_concurrent_partitioned_index( :background_operation_workers, [:partition, :job_class_name, :table_name, :column_name, :job_arguments], diff --git a/db/migrate/20250901090254_create_background_operation_jobs.rb b/db/migrate/20250901090254_create_background_operation_jobs.rb index 53d8962bd504c3..3c44d02e1b0241 100644 --- a/db/migrate/20250901090254_create_background_operation_jobs.rb +++ b/db/migrate/20250901090254_create_background_operation_jobs.rb @@ -16,6 +16,8 @@ def up create_table(:background_operation_jobs, **opts) do |t| t.bigserial :id + t.bigint :organization_id + t.bigint :user_id t.timestamptz :created_at, null: false t.timestamptz :started_at t.timestamptz :finished_at @@ -32,6 +34,26 @@ def up t.jsonb :max_cursor end + add_concurrent_partitioned_foreign_key( + :background_operation_jobs, + :organizations, + column: :organization_id, + target_column: :id, + reverse_lock_order: true, + on_delete: :cascade, + validate: true + ) + + add_concurrent_partitioned_foreign_key( + :background_operation_jobs, + :users, + column: :user_id, + target_column: :id, + reverse_lock_order: true, + on_delete: :cascade, + validate: true + ) + add_indexes add_constraints end @@ -48,31 +70,31 @@ def add_indexes :status, name: 'index_background_jobs_by_status' ) - add_concurrent_partitioned_index( :background_operation_jobs, - :background_operation_worker_id, - name: 'index_background_jobs_by_worker_id' + :organization_id, + name: 'index_background_operation_jobs_by_organization' + ) + add_concurrent_partitioned_index( + :background_operation_jobs, + :user_id, + name: 'index_background_operation_jobs_by_user' ) - add_concurrent_partitioned_index( :background_operation_jobs, [:background_operation_worker_id, :id], name: 'index_background_jobs_by_worker_id_and_id' ) - add_concurrent_partitioned_index( :background_operation_jobs, [:background_operation_worker_id, :status], name: 'index_background_jobs_by_worker_id_and_status' ) - add_concurrent_partitioned_index( :background_operation_jobs, [:background_operation_worker_id, :max_cursor], name: 'index_background_jobs_on_worker_id_and_cursor_max_value', where: 'max_cursor IS NOT NULL' ) - add_concurrent_partitioned_index( :background_operation_jobs, [:background_operation_worker_id, :finished_at], diff --git a/db/structure.sql b/db/structure.sql index 152511a2a83f05..5b73fa26f23575 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -4701,6 +4701,8 @@ PARTITION BY RANGE (created_at); CREATE TABLE background_operation_jobs ( id bigint NOT NULL, + organization_id bigint, + user_id bigint, created_at timestamp with time zone NOT NULL, started_at timestamp with time zone, finished_at timestamp with time zone, @@ -4743,7 +4745,7 @@ CREATE TABLE background_operation_workers ( table_name text NOT NULL, column_name text NOT NULL, gitlab_schema text NOT NULL, - job_arguments jsonb DEFAULT '"[]"'::jsonb NOT NULL, + job_arguments jsonb DEFAULT '"[]"'::jsonb, min_cursor jsonb, max_cursor jsonb, next_min_cursor jsonb, @@ -4937,66 +4939,6 @@ CREATE TABLE batched_background_migration_job_transition_logs ( ) PARTITION BY RANGE (created_at); -CREATE TABLE batched_background_operation_jobs ( - id bigint NOT NULL, - created_at timestamp with time zone NOT NULL, - started_at timestamp with time zone, - finished_at timestamp with time zone, - batched_background_operation_id bigint, - batch_size integer NOT NULL, - sub_batch_size integer NOT NULL, - pause_ms integer DEFAULT 100 NOT NULL, - partition integer DEFAULT 1 NOT NULL, - batched_background_operation_partition integer NOT NULL, - status smallint DEFAULT 0 NOT NULL, - attempts smallint DEFAULT 0 NOT NULL, - metrics jsonb DEFAULT '{}'::jsonb NOT NULL, - min_cursor jsonb, - max_cursor jsonb, - CONSTRAINT check_55b1377c17 CHECK ((num_nonnulls(min_cursor, max_cursor) = 2)), - CONSTRAINT check_8bdd023c8a CHECK (((jsonb_typeof(min_cursor) = 'array'::text) AND (jsonb_typeof(max_cursor) = 'array'::text))), - CONSTRAINT check_8d511627a2 CHECK ((pause_ms >= 100)) -) -PARTITION BY LIST (partition); - -CREATE TABLE batched_background_operations ( - id bigint NOT NULL, - organization_id bigint, - user_id bigint, - total_tuple_count bigint, - started_at timestamp with time zone, - on_hold_until timestamp with time zone, - created_at timestamp with time zone NOT NULL, - finished_at timestamp with time zone, - batch_size integer NOT NULL, - sub_batch_size integer NOT NULL, - pause_ms integer DEFAULT 100 NOT NULL, - max_batch_size integer, - partition integer DEFAULT 1 NOT NULL, - priority smallint DEFAULT 0 NOT NULL, - status smallint DEFAULT 0 NOT NULL, - "interval" smallint NOT NULL, - job_class_name text NOT NULL, - batch_class_name text DEFAULT 'PrimaryKeyBatchingStrategy'::text NOT NULL, - table_name text NOT NULL, - column_name text NOT NULL, - gitlab_schema text NOT NULL, - job_arguments jsonb DEFAULT '"[]"'::jsonb NOT NULL, - min_cursor jsonb, - max_cursor jsonb, - next_min_cursor jsonb, - CONSTRAINT check_15ba69be48 CHECK ((char_length(job_class_name) <= 100)), - CONSTRAINT check_2fec5330f8 CHECK (((jsonb_typeof(min_cursor) = 'array'::text) AND (jsonb_typeof(max_cursor) = 'array'::text))), - CONSTRAINT check_5c31edc326 CHECK ((batch_size >= sub_batch_size)), - CONSTRAINT check_74d81ce341 CHECK ((char_length(table_name) <= 63)), - CONSTRAINT check_a19c84ab29 CHECK ((char_length(column_name) <= 63)), - CONSTRAINT check_ab3241cf50 CHECK ((sub_batch_size > 0)), - CONSTRAINT check_d5d4eb82dc CHECK ((num_nonnulls(min_cursor, max_cursor) = 2)), - CONSTRAINT check_e79737a142 CHECK ((char_length(gitlab_schema) <= 255)), - CONSTRAINT check_ea209e4176 CHECK ((char_length(batch_class_name) <= 100)) -) -PARTITION BY LIST (partition); - CREATE TABLE p_ci_build_names ( build_id bigint NOT NULL, partition_id bigint NOT NULL, @@ -12583,24 +12525,6 @@ CREATE SEQUENCE batched_background_migrations_id_seq ALTER SEQUENCE batched_background_migrations_id_seq OWNED BY batched_background_migrations.id; -CREATE SEQUENCE batched_background_operation_jobs_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - -ALTER SEQUENCE batched_background_operation_jobs_id_seq OWNED BY batched_background_operation_jobs.id; - -CREATE SEQUENCE batched_background_operations_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - -ALTER SEQUENCE batched_background_operations_id_seq OWNED BY batched_background_operations.id; - CREATE TABLE board_assignees ( id bigint NOT NULL, board_id bigint NOT NULL, @@ -30346,10 +30270,6 @@ ALTER TABLE ONLY batched_background_migration_jobs ALTER COLUMN id SET DEFAULT n ALTER TABLE ONLY batched_background_migrations ALTER COLUMN id SET DEFAULT nextval('batched_background_migrations_id_seq'::regclass); -ALTER TABLE ONLY batched_background_operation_jobs ALTER COLUMN id SET DEFAULT nextval('batched_background_operation_jobs_id_seq'::regclass); - -ALTER TABLE ONLY batched_background_operations ALTER COLUMN id SET DEFAULT nextval('batched_background_operations_id_seq'::regclass); - ALTER TABLE ONLY board_assignees ALTER COLUMN id SET DEFAULT nextval('board_assignees_id_seq'::regclass); ALTER TABLE ONLY board_group_recent_visits ALTER COLUMN id SET DEFAULT nextval('board_group_recent_visits_id_seq'::regclass); @@ -32856,12 +32776,6 @@ ALTER TABLE ONLY batched_background_migration_jobs ALTER TABLE ONLY batched_background_migrations ADD CONSTRAINT batched_background_migrations_pkey PRIMARY KEY (id); -ALTER TABLE ONLY batched_background_operation_jobs - ADD CONSTRAINT batched_background_operation_jobs_pkey PRIMARY KEY (id, partition); - -ALTER TABLE ONLY batched_background_operations - ADD CONSTRAINT batched_background_operations_pkey PRIMARY KEY (partition, id); - ALTER TABLE ONLY board_assignees ADD CONSTRAINT board_assignees_pkey PRIMARY KEY (id); @@ -38485,8 +38399,6 @@ CREATE UNIQUE INDEX index_aws_roles_on_user_id ON aws_roles USING btree (user_id CREATE INDEX index_background_jobs_by_status ON ONLY background_operation_jobs USING btree (status); -CREATE INDEX index_background_jobs_by_worker_id ON ONLY background_operation_jobs USING btree (background_operation_worker_id); - CREATE INDEX index_background_jobs_by_worker_id_and_id ON ONLY background_operation_jobs USING btree (background_operation_worker_id, id); CREATE INDEX index_background_jobs_by_worker_id_and_status ON ONLY background_operation_jobs USING btree (background_operation_worker_id, status); @@ -38495,12 +38407,18 @@ CREATE INDEX index_background_jobs_on_worker_id_and_cursor_max_value ON ONLY bac CREATE INDEX index_background_jobs_on_worker_id_and_finished_at ON ONLY background_operation_jobs USING btree (background_operation_worker_id, finished_at); +CREATE INDEX index_background_operation_jobs_by_organization ON ONLY background_operation_jobs USING btree (organization_id); + +CREATE INDEX index_background_operation_jobs_by_user ON ONLY background_operation_jobs USING btree (user_id); + CREATE INDEX index_background_operation_workers_by_organization ON ONLY background_operation_workers USING btree (organization_id); CREATE INDEX index_background_operation_workers_by_priority ON ONLY background_operation_workers USING btree (priority); CREATE INDEX index_background_operation_workers_by_status ON ONLY background_operation_workers USING btree (status); +CREATE INDEX index_background_operation_workers_by_user ON ONLY background_operation_workers USING btree (user_id); + CREATE UNIQUE INDEX index_background_operation_workers_on_unique_configuration ON ONLY background_operation_workers USING btree (partition, job_class_name, table_name, column_name, job_arguments); CREATE INDEX index_backup_finding_evidences_on_fk ON ONLY backup_finding_evidences USING btree (finding_id); @@ -38577,26 +38495,6 @@ CREATE INDEX index_batched_jobs_on_batched_migration_id_and_status ON batched_ba CREATE UNIQUE INDEX index_batched_migrations_on_gl_schema_and_unique_configuration ON batched_background_migrations USING btree (gitlab_schema, job_class_name, table_name, column_name, job_arguments); -CREATE INDEX index_bbo_by_organization ON ONLY batched_background_operations USING btree (organization_id); - -CREATE INDEX index_bbo_by_priority ON ONLY batched_background_operations USING btree (priority); - -CREATE INDEX index_bbo_by_status ON ONLY batched_background_operations USING btree (status); - -CREATE INDEX index_bbo_jobs_by_batched_operation_id ON ONLY batched_background_operation_jobs USING btree (batched_background_operation_id); - -CREATE INDEX index_bbo_jobs_by_batched_operation_id_and_id ON ONLY batched_background_operation_jobs USING btree (batched_background_operation_id, id); - -CREATE INDEX index_bbo_jobs_by_batched_operation_id_and_status ON ONLY batched_background_operation_jobs USING btree (batched_background_operation_id, status); - -CREATE INDEX index_bbo_jobs_by_status ON ONLY batched_background_operation_jobs USING btree (status); - -CREATE INDEX index_bbo_jobs_on_operation_id_and_cursor_max_value ON ONLY batched_background_operation_jobs USING btree (batched_background_operation_id, max_cursor) WHERE (max_cursor IS NOT NULL); - -CREATE INDEX index_bbo_jobs_on_operation_id_and_finished_at ON ONLY batched_background_operation_jobs USING btree (batched_background_operation_id, finished_at); - -CREATE UNIQUE INDEX index_bbo_on_unique_configuration ON ONLY batched_background_operations USING btree (partition, job_class_name, table_name, column_name, job_arguments); - CREATE INDEX index_board_assignees_on_assignee_id ON board_assignees USING btree (assignee_id); CREATE UNIQUE INDEX index_board_assignees_on_board_id_and_assignee_id ON board_assignees USING btree (board_id, assignee_id); @@ -49873,6 +49771,9 @@ ALTER TABLE ONLY compliance_requirements_controls ALTER TABLE ONLY user_credit_card_validations ADD CONSTRAINT fk_rails_27ebc03cbf FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; +ALTER TABLE background_operation_workers + ADD CONSTRAINT fk_rails_281a813c59 FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE; + ALTER TABLE ONLY dast_site_validations ADD CONSTRAINT fk_rails_285c617324 FOREIGN KEY (dast_site_token_id) REFERENCES dast_site_tokens(id) ON DELETE CASCADE; @@ -50071,6 +49972,9 @@ ALTER TABLE ONLY project_type_ci_runner_machines ALTER TABLE ONLY description_versions ADD CONSTRAINT fk_rails_3ff658220b FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE; +ALTER TABLE background_operation_jobs + ADD CONSTRAINT fk_rails_40383bd920 FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE; + ALTER TABLE ONLY clusters_kubernetes_namespaces ADD CONSTRAINT fk_rails_40cc7ccbc3 FOREIGN KEY (cluster_project_id) REFERENCES cluster_projects(id) ON DELETE SET NULL; @@ -51229,6 +51133,9 @@ ALTER TABLE ONLY ai_user_metrics ALTER TABLE ONLY boards_epic_board_positions ADD CONSTRAINT fk_rails_cb4563dd6e FOREIGN KEY (epic_board_id) REFERENCES boards_epic_boards(id) ON DELETE CASCADE; +ALTER TABLE background_operation_jobs + ADD CONSTRAINT fk_rails_cb794cb79f FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; + ALTER TABLE ONLY vulnerability_finding_links ADD CONSTRAINT fk_rails_cbdfde27ce FOREIGN KEY (vulnerability_occurrence_id) REFERENCES vulnerability_occurrences(id) ON DELETE CASCADE; @@ -51244,6 +51151,9 @@ ALTER TABLE ONLY observability_group_o11y_settings ALTER TABLE ONLY members_deletion_schedules ADD CONSTRAINT fk_rails_ce06d97eb2 FOREIGN KEY (namespace_id) REFERENCES namespaces(id) ON DELETE CASCADE; +ALTER TABLE background_operation_workers + ADD CONSTRAINT fk_rails_ce5418a44f FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; + ALTER TABLE ONLY resource_milestone_events ADD CONSTRAINT fk_rails_cedf8cce4d FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL; @@ -51631,9 +51541,6 @@ ALTER TABLE ONLY cluster_groups ALTER TABLE ONLY project_control_compliance_statuses ADD CONSTRAINT fk_rails_fe05913910 FOREIGN KEY (compliance_requirements_control_id) REFERENCES compliance_requirements_controls(id) ON DELETE CASCADE; -ALTER TABLE batched_background_operation_jobs - ADD CONSTRAINT fk_rails_fe231ffd46 FOREIGN KEY (batched_background_operation_partition, batched_background_operation_id) REFERENCES batched_background_operations(partition, id) ON UPDATE CASCADE ON DELETE CASCADE; - ALTER TABLE ONLY resource_label_events ADD CONSTRAINT fk_rails_fe91ece594 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL; diff --git a/spec/db/schema_spec.rb b/spec/db/schema_spec.rb index 5be5aeb0327695..c5b944da21ae9e 100644 --- a/spec/db/schema_spec.rb +++ b/spec/db/schema_spec.rb @@ -295,7 +295,8 @@ # temp entry, removing FK on source_type_id and target_type_id until table is dropped in follow up MR work_item_related_link_restrictions: %w[source_type_id target_type_id], sbom_vulnerability_scans: %w[project_id build_id], # referenced records are in different DB and no LFK as the table contains references to object storage - security_trainings: %w[training_provider_id] # training_provider_id is a fixed items model reference. + security_trainings: %w[training_provider_id], # training_provider_id is a fixed items model reference. + background_operation_jobs: %w[background_operation_worker_id] # background_operation_workers partitions have to dropped independently and background_operation_jobs will be dropped after execution (with a retention period). }.with_indifferent_access.freeze end diff --git a/spec/factories/gitlab/database/background_operation/jobs.rb b/spec/factories/gitlab/database/background_operation/jobs.rb index 98d3928fd8d3e8..9a669370c50804 100644 --- a/spec/factories/gitlab/database/background_operation/jobs.rb +++ b/spec/factories/gitlab/database/background_operation/jobs.rb @@ -30,5 +30,9 @@ started_at { 1.hour.ago } finished_at { Time.current } end + + trait :organization_specific do + organization_id { create(:common_organization).id } + end end end diff --git a/spec/factories/gitlab/database/background_operation/workers.rb b/spec/factories/gitlab/database/background_operation/workers.rb index 4075ae9c908cab..1e640a9aab64c1 100644 --- a/spec/factories/gitlab/database/background_operation/workers.rb +++ b/spec/factories/gitlab/database/background_operation/workers.rb @@ -2,8 +2,6 @@ FactoryBot.define do factory :background_operation_worker, class: 'Gitlab::Database::BackgroundOperation::Worker' do - organization_id { 1 } - user_id { 1 } job_class_name { 'CopyColumnUsingBackgroundMigrationJob' } batch_class_name { 'PrimaryKeyBatchingStrategy' } table_name { :users } @@ -33,5 +31,9 @@ trait :failed do status { 3 } end + + trait :organization_specific do + organization_id { create(:common_organization).id } + end end end diff --git a/spec/lib/gitlab/database/sharding_key_spec.rb b/spec/lib/gitlab/database/sharding_key_spec.rb index 2828063eb8e0e5..01e26572b2b0eb 100644 --- a/spec/lib/gitlab/database/sharding_key_spec.rb +++ b/spec/lib/gitlab/database/sharding_key_spec.rb @@ -325,6 +325,7 @@ "jira_connect_installations" => "https://gitlab.com/gitlab-org/gitlab/-/issues/524682", "system_note_metadata" => "https://gitlab.com/gitlab-org/gitlab/-/issues/571215" } + has_lfk = ->(lfks) { lfks.any? { |k| k.options[:column] == 'organization_id' && k.to_table == 'organizations' } } columns_to_check = organization_id_columns.reject { |column| work_in_progress[column[0]] } -- GitLab From a1dadc58de83eca8a3b41d0eb4d0efba39d16724 Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Tue, 16 Sep 2025 00:25:26 +0200 Subject: [PATCH 08/17] Splits tables into org specific and cell local Based on the outcome of https://gitlab.com/gitlab-org/gitlab/-/issues/562947#note_2743808869 --- db/docs/background_operation_jobs.yml | 1 + .../background_operation_jobs_cell_local.yml | 12 ++ db/docs/background_operation_workers.yml | 1 + ...ackground_operation_workers_cell_local.yml | 14 +++ ...background_operation_workers_cell_local.rb | 94 +++++++++++++++ ...te_background_operation_jobs_cell_local.rb | 92 ++++++++++++++ ...41_create_background_operation_workers.rb} | 0 ...64941_create_background_operation_jobs.rb} | 12 +- db/schema_migrations/20250829132949 | 1 - db/schema_migrations/20250901090254 | 1 - db/schema_migrations/20250915164710 | 1 + db/schema_migrations/20250915164741 | 1 + db/schema_migrations/20250915164841 | 1 + db/schema_migrations/20250915164941 | 1 + db/structure.sql | 114 +++++++++++++++++- .../background_operation/common_job.rb | 65 ++++++++++ .../background_operation/common_worker.rb | 72 +++++++++++ .../database/background_operation/job.rb | 58 +-------- .../background_operation/job_cell_local.rb | 20 +++ .../database/background_operation/worker.rb | 65 +--------- .../background_operation/worker_cell_local.rb | 20 +++ .../database/background_operation/jobs.rb | 4 +- .../background_operation/jobs_cell_local.rb | 34 ++++++ .../workers_cell_local.rb | 39 ++++++ 24 files changed, 592 insertions(+), 131 deletions(-) create mode 100644 db/docs/background_operation_jobs_cell_local.yml create mode 100644 db/docs/background_operation_workers_cell_local.yml create mode 100644 db/migrate/20250915164710_create_background_operation_workers_cell_local.rb create mode 100644 db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb rename db/migrate/{20250829132949_create_background_operation_workers.rb => 20250915164841_create_background_operation_workers.rb} (100%) rename db/migrate/{20250901090254_create_background_operation_jobs.rb => 20250915164941_create_background_operation_jobs.rb} (90%) delete mode 100644 db/schema_migrations/20250829132949 delete mode 100644 db/schema_migrations/20250901090254 create mode 100644 db/schema_migrations/20250915164710 create mode 100644 db/schema_migrations/20250915164741 create mode 100644 db/schema_migrations/20250915164841 create mode 100644 db/schema_migrations/20250915164941 create mode 100644 lib/gitlab/database/background_operation/common_job.rb create mode 100644 lib/gitlab/database/background_operation/common_worker.rb create mode 100644 lib/gitlab/database/background_operation/job_cell_local.rb create mode 100644 lib/gitlab/database/background_operation/worker_cell_local.rb create mode 100644 spec/factories/gitlab/database/background_operation/jobs_cell_local.rb create mode 100644 spec/factories/gitlab/database/background_operation/workers_cell_local.rb diff --git a/db/docs/background_operation_jobs.yml b/db/docs/background_operation_jobs.yml index a71fafaf3a8f9e..a4232921f22f94 100644 --- a/db/docs/background_operation_jobs.yml +++ b/db/docs/background_operation_jobs.yml @@ -7,5 +7,6 @@ feature_categories: description: Store jobs info for each background_operation_workers. introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/203234 milestone: '18.4' +#gitlab_schema: gitlab_shared_org gitlab_schema: gitlab_shared table_size: small diff --git a/db/docs/background_operation_jobs_cell_local.yml b/db/docs/background_operation_jobs_cell_local.yml new file mode 100644 index 00000000000000..a14a820311c50f --- /dev/null +++ b/db/docs/background_operation_jobs_cell_local.yml @@ -0,0 +1,12 @@ +--- +table_name: background_operation_jobs_cell_local +classes: + - Gitlab::Database::BackgroundOperation::JobCellLocal +feature_categories: + - database +description: Store jobs info for each background_operation_workers_cell_local. +introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/203234 +milestone: '18.4' +#gitlab_schema: gitlab_shared_cell_local +gitlab_schema: gitlab_shared +table_size: small diff --git a/db/docs/background_operation_workers.yml b/db/docs/background_operation_workers.yml index a57f6d36a00125..4798bc7d556e58 100644 --- a/db/docs/background_operation_workers.yml +++ b/db/docs/background_operation_workers.yml @@ -9,5 +9,6 @@ description: Stores information about the large data operations performed async. for more details. introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/203234 milestone: '18.4' +#gitlab_schema: gitlab_shared_org gitlab_schema: gitlab_shared table_size: small diff --git a/db/docs/background_operation_workers_cell_local.yml b/db/docs/background_operation_workers_cell_local.yml new file mode 100644 index 00000000000000..c41f486333bbb5 --- /dev/null +++ b/db/docs/background_operation_workers_cell_local.yml @@ -0,0 +1,14 @@ +--- +table_name: background_operation_workers_cell_local +classes: + - Gitlab::Database::BackgroundOperation::WorkerCellLocal +feature_categories: + - database +description: Stores information about the cell local large data operations performed async. See + https://handbook.gitlab.com/handbook/engineering/architecture/design-documents/batched_background_operations/ + for more details. +introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/203234 +milestone: '18.4' +#gitlab_schema: gitlab_shared_org +gitlab_schema: gitlab_shared +table_size: small diff --git a/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb b/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb new file mode 100644 index 00000000000000..dbfcdf93c40b12 --- /dev/null +++ b/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb @@ -0,0 +1,94 @@ +# frozen_string_literal: true + +class CreateBackgroundOperationWorkersCellLocal < Gitlab::Database::Migration[2.3] + include Gitlab::Database::PartitioningMigrationHelpers + + milestone '18.4' + + disable_ddl_transaction! + + def up + opts = { + if_not_exists: true, + primary_key: [:partition, :id], + options: 'PARTITION BY LIST (partition)' + } + + create_table(:background_operation_workers_cell_local, **opts) do |t| + t.bigserial :id + t.bigint :total_tuple_count + t.timestamptz :started_at + t.timestamptz :on_hold_until + t.timestamptz :created_at, null: false + t.timestamptz :finished_at + t.integer :batch_size, null: false + t.integer :sub_batch_size, null: false + t.integer :pause_ms, null: false, default: 100 + t.integer :max_batch_size + t.integer :partition, null: false, default: 1 + t.integer :priority, null: false, limit: 2, default: 0 + t.integer :status, null: false, limit: 2, default: 0 + t.integer :interval, null: false, limit: 2 + t.text :job_class_name, null: false, limit: 100 + t.text :batch_class_name, null: false, default: 'PrimaryKeyBatchingStrategy', limit: 100 + t.text :table_name, null: false, limit: 63 + t.text :column_name, null: false, limit: 63 + t.text :gitlab_schema, null: false, limit: 255 + t.jsonb :job_arguments, default: '[]' + t.jsonb :min_cursor + t.jsonb :max_cursor + t.jsonb :next_min_cursor + end + + add_indexes + add_constraints + end + + def down + drop_table(:background_operation_workers_cell_local, if_exists: true) + end + + private + + def add_indexes + add_concurrent_partitioned_index( + :background_operation_workers_cell_local, + :status, + name: 'index_bow_cell_local_by_status' + ) + add_concurrent_partitioned_index( + :background_operation_workers_cell_local, + :priority, + name: 'index_bow_cell_local_by_priority' + ) + add_concurrent_partitioned_index( + :background_operation_workers_cell_local, + [:partition, :job_class_name, :table_name, :column_name, :job_arguments], + unique: true, + name: 'index_bow_cell_local_on_unique_configuration' + ) + end + + def add_constraints + add_check_constraint( + :background_operation_workers_cell_local, + '(batch_size >= sub_batch_size)', + check_constraint_name(:background_operation_workers_cell_local, 'batch_size', 'greater_than_sub_batch_size') + ) + add_check_constraint( + :background_operation_workers_cell_local, + '(sub_batch_size > 0)', + check_constraint_name(:background_operation_workers_cell_local, 'sub_batch_size', 'greater_than_zero') + ) + add_check_constraint( + :background_operation_workers_cell_local, + "jsonb_typeof(min_cursor) = 'array' AND jsonb_typeof(max_cursor) = 'array'", + check_constraint_name(:background_operation_workers_cell_local, 'cursors', 'jsonb_array') + ) + add_check_constraint( + :background_operation_workers_cell_local, + 'num_nonnulls(min_cursor, max_cursor) = 2', + check_constraint_name(:background_operation_workers_cell_local, 'cursors', 'not_null') + ) + end +end diff --git a/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb b/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb new file mode 100644 index 00000000000000..6f95dee8e11e1c --- /dev/null +++ b/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb @@ -0,0 +1,92 @@ +# frozen_string_literal: true + +class CreateBackgroundOperationJobsCellLocal < Gitlab::Database::Migration[2.3] + include Gitlab::Database::PartitioningMigrationHelpers + + milestone '18.4' + + disable_ddl_transaction! + + def up + opts = { + if_not_exists: true, + primary_key: [:partition, :id], + options: 'PARTITION BY LIST (partition)' + } + + create_table(:background_operation_jobs_cell_local, **opts) do |t| + t.bigserial :id + t.timestamptz :created_at, null: false + t.timestamptz :started_at + t.timestamptz :finished_at + t.bigint :worker_id, null: false + t.integer :batch_size, null: false + t.integer :sub_batch_size, null: false + t.integer :pause_ms, null: false, default: 100 + t.integer :partition, null: false, default: 1 + t.integer :worker_partition, null: false + t.integer :status, null: false, default: 0, limit: 2 + t.integer :attempts, null: false, default: 0, limit: 2 + t.jsonb :metrics, null: false, default: {} + t.jsonb :min_cursor + t.jsonb :max_cursor + end + + add_indexes + add_constraints + end + + def down + drop_table(:background_operation_jobs_cell_local, if_exists: true) + end + + private + + def add_indexes + add_concurrent_partitioned_index( + :background_operation_jobs_cell_local, + :status, + name: 'index_bj_cell_local_by_status' + ) + add_concurrent_partitioned_index( + :background_operation_jobs_cell_local, + [:worker_id, :id], + name: 'index_bj_cell_local_by_worker_id_and_id' + ) + add_concurrent_partitioned_index( + :background_operation_jobs_cell_local, + [:worker_id, :status], + name: 'index_bj_cell_local_by_worker_id_and_status' + ) + add_concurrent_partitioned_index( + :background_operation_jobs_cell_local, + [:worker_id, :max_cursor], + name: 'index_bj_cell_local_on_worker_id_and_cursor_max_value', where: 'max_cursor IS NOT NULL' + ) + add_concurrent_partitioned_index( + :background_operation_jobs_cell_local, + [:worker_id, :finished_at], + name: 'index_bj_cell_local_on_worker_id_and_finished_at' + ) + end + + def add_constraints + add_check_constraint( + :background_operation_jobs_cell_local, + "jsonb_typeof(min_cursor) = 'array' AND jsonb_typeof(max_cursor) = 'array'", + check_constraint_name(:background_operation_jobs_cell_local, 'cursors', 'jsonb_array') + ) + + add_check_constraint( + :background_operation_jobs_cell_local, + "pause_ms >= 100", + check_constraint_name(:background_operation_jobs_cell_local, 'pause_ms', 'minimum_hundred') + ) + + add_check_constraint( + :background_operation_jobs_cell_local, + 'num_nonnulls(min_cursor, max_cursor) = 2', + check_constraint_name(:background_operation_jobs_cell_local, 'cursors', 'not_null') + ) + end +end diff --git a/db/migrate/20250829132949_create_background_operation_workers.rb b/db/migrate/20250915164841_create_background_operation_workers.rb similarity index 100% rename from db/migrate/20250829132949_create_background_operation_workers.rb rename to db/migrate/20250915164841_create_background_operation_workers.rb diff --git a/db/migrate/20250901090254_create_background_operation_jobs.rb b/db/migrate/20250915164941_create_background_operation_jobs.rb similarity index 90% rename from db/migrate/20250901090254_create_background_operation_jobs.rb rename to db/migrate/20250915164941_create_background_operation_jobs.rb index 3c44d02e1b0241..430d0465a2d641 100644 --- a/db/migrate/20250901090254_create_background_operation_jobs.rb +++ b/db/migrate/20250915164941_create_background_operation_jobs.rb @@ -21,12 +21,12 @@ def up t.timestamptz :created_at, null: false t.timestamptz :started_at t.timestamptz :finished_at - t.bigint :background_operation_worker_id, null: false + t.bigint :worker_id, null: false t.integer :batch_size, null: false t.integer :sub_batch_size, null: false t.integer :pause_ms, null: false, default: 100 t.integer :partition, null: false, default: 1 - t.integer :background_operation_worker_partition, null: false + t.integer :worker_partition, null: false t.integer :status, null: false, default: 0, limit: 2 t.integer :attempts, null: false, default: 0, limit: 2 t.jsonb :metrics, null: false, default: {} @@ -82,22 +82,22 @@ def add_indexes ) add_concurrent_partitioned_index( :background_operation_jobs, - [:background_operation_worker_id, :id], + [:worker_id, :id], name: 'index_background_jobs_by_worker_id_and_id' ) add_concurrent_partitioned_index( :background_operation_jobs, - [:background_operation_worker_id, :status], + [:worker_id, :status], name: 'index_background_jobs_by_worker_id_and_status' ) add_concurrent_partitioned_index( :background_operation_jobs, - [:background_operation_worker_id, :max_cursor], + [:worker_id, :max_cursor], name: 'index_background_jobs_on_worker_id_and_cursor_max_value', where: 'max_cursor IS NOT NULL' ) add_concurrent_partitioned_index( :background_operation_jobs, - [:background_operation_worker_id, :finished_at], + [:worker_id, :finished_at], name: 'index_background_jobs_on_worker_id_and_finished_at' ) end diff --git a/db/schema_migrations/20250829132949 b/db/schema_migrations/20250829132949 deleted file mode 100644 index 578bbf9e8b7a9a..00000000000000 --- a/db/schema_migrations/20250829132949 +++ /dev/null @@ -1 +0,0 @@ -05d28404096be27f6776e80ee94cb559973ab74a0625e73a5c713c19739c3d30 \ No newline at end of file diff --git a/db/schema_migrations/20250901090254 b/db/schema_migrations/20250901090254 deleted file mode 100644 index 03ccd65266b17e..00000000000000 --- a/db/schema_migrations/20250901090254 +++ /dev/null @@ -1 +0,0 @@ -387e6aa5a2be82dbafc6cf42f60d8995592dc5791a2c69d185450d8d20f69a82 \ No newline at end of file diff --git a/db/schema_migrations/20250915164710 b/db/schema_migrations/20250915164710 new file mode 100644 index 00000000000000..12b662e85ffbf4 --- /dev/null +++ b/db/schema_migrations/20250915164710 @@ -0,0 +1 @@ +0eceffc4bc04a3f827dd5b2bc8e6c15096890d307c4df8f11ebfa3f8fea5442f \ No newline at end of file diff --git a/db/schema_migrations/20250915164741 b/db/schema_migrations/20250915164741 new file mode 100644 index 00000000000000..5e8e60f64983f0 --- /dev/null +++ b/db/schema_migrations/20250915164741 @@ -0,0 +1 @@ +a1a06b3ecae01c65514e92773dc438633f4c6f38d6bc80dd10b7e11ca7f36293 \ No newline at end of file diff --git a/db/schema_migrations/20250915164841 b/db/schema_migrations/20250915164841 new file mode 100644 index 00000000000000..8d4d64526eee39 --- /dev/null +++ b/db/schema_migrations/20250915164841 @@ -0,0 +1 @@ +f0bf60e933ed6adb4aca74ba1149784e1fc386ad4711c1d55570c14462659995 \ No newline at end of file diff --git a/db/schema_migrations/20250915164941 b/db/schema_migrations/20250915164941 new file mode 100644 index 00000000000000..333ee199eaff9a --- /dev/null +++ b/db/schema_migrations/20250915164941 @@ -0,0 +1 @@ +95a1e57803edcfb3c9bdf862cf5a51980bfb6684ff09fb42371e8d786e5301df \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 5b73fa26f23575..39758e653317fc 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -4706,12 +4706,12 @@ CREATE TABLE background_operation_jobs ( created_at timestamp with time zone NOT NULL, started_at timestamp with time zone, finished_at timestamp with time zone, - background_operation_worker_id bigint NOT NULL, + worker_id bigint NOT NULL, batch_size integer NOT NULL, sub_batch_size integer NOT NULL, pause_ms integer DEFAULT 100 NOT NULL, partition integer DEFAULT 1 NOT NULL, - background_operation_worker_partition integer NOT NULL, + worker_partition integer NOT NULL, status smallint DEFAULT 0 NOT NULL, attempts smallint DEFAULT 0 NOT NULL, metrics jsonb DEFAULT '{}'::jsonb NOT NULL, @@ -12388,6 +12388,37 @@ CREATE TABLE aws_roles ( CONSTRAINT check_57adedab55 CHECK ((char_length(region) <= 255)) ); +CREATE TABLE background_operation_jobs_cell_local ( + id bigint NOT NULL, + created_at timestamp with time zone NOT NULL, + started_at timestamp with time zone, + finished_at timestamp with time zone, + worker_id bigint NOT NULL, + batch_size integer NOT NULL, + sub_batch_size integer NOT NULL, + pause_ms integer DEFAULT 100 NOT NULL, + partition integer DEFAULT 1 NOT NULL, + worker_partition integer NOT NULL, + status smallint DEFAULT 0 NOT NULL, + attempts smallint DEFAULT 0 NOT NULL, + metrics jsonb DEFAULT '{}'::jsonb NOT NULL, + min_cursor jsonb, + max_cursor jsonb, + CONSTRAINT check_00bb39bb33 CHECK ((pause_ms >= 100)), + CONSTRAINT check_5b84acc749 CHECK ((num_nonnulls(min_cursor, max_cursor) = 2)), + CONSTRAINT check_ebc3302442 CHECK (((jsonb_typeof(min_cursor) = 'array'::text) AND (jsonb_typeof(max_cursor) = 'array'::text))) +) +PARTITION BY LIST (partition); + +CREATE SEQUENCE background_operation_jobs_cell_local_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE background_operation_jobs_cell_local_id_seq OWNED BY background_operation_jobs_cell_local.id; + CREATE SEQUENCE background_operation_jobs_id_seq START WITH 1 INCREMENT BY 1 @@ -12397,6 +12428,51 @@ CREATE SEQUENCE background_operation_jobs_id_seq ALTER SEQUENCE background_operation_jobs_id_seq OWNED BY background_operation_jobs.id; +CREATE TABLE background_operation_workers_cell_local ( + id bigint NOT NULL, + total_tuple_count bigint, + started_at timestamp with time zone, + on_hold_until timestamp with time zone, + created_at timestamp with time zone NOT NULL, + finished_at timestamp with time zone, + batch_size integer NOT NULL, + sub_batch_size integer NOT NULL, + pause_ms integer DEFAULT 100 NOT NULL, + max_batch_size integer, + partition integer DEFAULT 1 NOT NULL, + priority smallint DEFAULT 0 NOT NULL, + status smallint DEFAULT 0 NOT NULL, + "interval" smallint NOT NULL, + job_class_name text NOT NULL, + batch_class_name text DEFAULT 'PrimaryKeyBatchingStrategy'::text NOT NULL, + table_name text NOT NULL, + column_name text NOT NULL, + gitlab_schema text NOT NULL, + job_arguments jsonb DEFAULT '"[]"'::jsonb, + min_cursor jsonb, + max_cursor jsonb, + next_min_cursor jsonb, + CONSTRAINT check_1da63db6a8 CHECK ((char_length(table_name) <= 63)), + CONSTRAINT check_4cc5ecb4f2 CHECK ((char_length(column_name) <= 63)), + CONSTRAINT check_5f184cd88f CHECK ((char_length(gitlab_schema) <= 255)), + CONSTRAINT check_9d0c37a905 CHECK ((char_length(batch_class_name) <= 100)), + CONSTRAINT check_be878382ae CHECK ((batch_size >= sub_batch_size)), + CONSTRAINT check_d94474cbf2 CHECK ((char_length(job_class_name) <= 100)), + CONSTRAINT check_e40b641a88 CHECK ((num_nonnulls(min_cursor, max_cursor) = 2)), + CONSTRAINT check_f9383a3f2e CHECK ((sub_batch_size > 0)), + CONSTRAINT check_f9caba0499 CHECK (((jsonb_typeof(min_cursor) = 'array'::text) AND (jsonb_typeof(max_cursor) = 'array'::text))) +) +PARTITION BY LIST (partition); + +CREATE SEQUENCE background_operation_workers_cell_local_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE background_operation_workers_cell_local_id_seq OWNED BY background_operation_workers_cell_local.id; + CREATE SEQUENCE background_operation_workers_id_seq START WITH 1 INCREMENT BY 1 @@ -30260,8 +30336,12 @@ ALTER TABLE ONLY award_emoji ALTER COLUMN id SET DEFAULT nextval('award_emoji_id ALTER TABLE ONLY background_operation_jobs ALTER COLUMN id SET DEFAULT nextval('background_operation_jobs_id_seq'::regclass); +ALTER TABLE ONLY background_operation_jobs_cell_local ALTER COLUMN id SET DEFAULT nextval('background_operation_jobs_cell_local_id_seq'::regclass); + ALTER TABLE ONLY background_operation_workers ALTER COLUMN id SET DEFAULT nextval('background_operation_workers_id_seq'::regclass); +ALTER TABLE ONLY background_operation_workers_cell_local ALTER COLUMN id SET DEFAULT nextval('background_operation_workers_cell_local_id_seq'::regclass); + ALTER TABLE ONLY badges ALTER COLUMN id SET DEFAULT nextval('badges_id_seq'::regclass); ALTER TABLE ONLY batched_background_migration_job_transition_logs ALTER COLUMN id SET DEFAULT nextval('batched_background_migration_job_transition_logs_id_seq'::regclass); @@ -32710,9 +32790,15 @@ ALTER TABLE ONLY award_emoji ALTER TABLE ONLY aws_roles ADD CONSTRAINT aws_roles_pkey PRIMARY KEY (user_id); +ALTER TABLE ONLY background_operation_jobs_cell_local + ADD CONSTRAINT background_operation_jobs_cell_local_pkey PRIMARY KEY (partition, id); + ALTER TABLE ONLY background_operation_jobs ADD CONSTRAINT background_operation_jobs_pkey PRIMARY KEY (partition, id); +ALTER TABLE ONLY background_operation_workers_cell_local + ADD CONSTRAINT background_operation_workers_cell_local_pkey PRIMARY KEY (partition, id); + ALTER TABLE ONLY background_operation_workers ADD CONSTRAINT background_operation_workers_pkey PRIMARY KEY (partition, id); @@ -38399,13 +38485,13 @@ CREATE UNIQUE INDEX index_aws_roles_on_user_id ON aws_roles USING btree (user_id CREATE INDEX index_background_jobs_by_status ON ONLY background_operation_jobs USING btree (status); -CREATE INDEX index_background_jobs_by_worker_id_and_id ON ONLY background_operation_jobs USING btree (background_operation_worker_id, id); +CREATE INDEX index_background_jobs_by_worker_id_and_id ON ONLY background_operation_jobs USING btree (worker_id, id); -CREATE INDEX index_background_jobs_by_worker_id_and_status ON ONLY background_operation_jobs USING btree (background_operation_worker_id, status); +CREATE INDEX index_background_jobs_by_worker_id_and_status ON ONLY background_operation_jobs USING btree (worker_id, status); -CREATE INDEX index_background_jobs_on_worker_id_and_cursor_max_value ON ONLY background_operation_jobs USING btree (background_operation_worker_id, max_cursor) WHERE (max_cursor IS NOT NULL); +CREATE INDEX index_background_jobs_on_worker_id_and_cursor_max_value ON ONLY background_operation_jobs USING btree (worker_id, max_cursor) WHERE (max_cursor IS NOT NULL); -CREATE INDEX index_background_jobs_on_worker_id_and_finished_at ON ONLY background_operation_jobs USING btree (background_operation_worker_id, finished_at); +CREATE INDEX index_background_jobs_on_worker_id_and_finished_at ON ONLY background_operation_jobs USING btree (worker_id, finished_at); CREATE INDEX index_background_operation_jobs_by_organization ON ONLY background_operation_jobs USING btree (organization_id); @@ -38495,6 +38581,16 @@ CREATE INDEX index_batched_jobs_on_batched_migration_id_and_status ON batched_ba CREATE UNIQUE INDEX index_batched_migrations_on_gl_schema_and_unique_configuration ON batched_background_migrations USING btree (gitlab_schema, job_class_name, table_name, column_name, job_arguments); +CREATE INDEX index_bj_cell_local_by_status ON ONLY background_operation_jobs_cell_local USING btree (status); + +CREATE INDEX index_bj_cell_local_by_worker_id_and_id ON ONLY background_operation_jobs_cell_local USING btree (worker_id, id); + +CREATE INDEX index_bj_cell_local_by_worker_id_and_status ON ONLY background_operation_jobs_cell_local USING btree (worker_id, status); + +CREATE INDEX index_bj_cell_local_on_worker_id_and_cursor_max_value ON ONLY background_operation_jobs_cell_local USING btree (worker_id, max_cursor) WHERE (max_cursor IS NOT NULL); + +CREATE INDEX index_bj_cell_local_on_worker_id_and_finished_at ON ONLY background_operation_jobs_cell_local USING btree (worker_id, finished_at); + CREATE INDEX index_board_assignees_on_assignee_id ON board_assignees USING btree (assignee_id); CREATE UNIQUE INDEX index_board_assignees_on_board_id_and_assignee_id ON board_assignees USING btree (board_id, assignee_id); @@ -38581,6 +38677,12 @@ CREATE INDEX index_boards_on_milestone_id ON boards USING btree (milestone_id); CREATE INDEX index_boards_on_project_id ON boards USING btree (project_id); +CREATE INDEX index_bow_cell_local_by_priority ON ONLY background_operation_workers_cell_local USING btree (priority); + +CREATE INDEX index_bow_cell_local_by_status ON ONLY background_operation_workers_cell_local USING btree (status); + +CREATE UNIQUE INDEX index_bow_cell_local_on_unique_configuration ON ONLY background_operation_workers_cell_local USING btree (partition, job_class_name, table_name, column_name, job_arguments); + CREATE UNIQUE INDEX index_branch_rule_squash_options_on_protected_branch_id ON projects_branch_rules_squash_options USING btree (protected_branch_id); CREATE UNIQUE INDEX index_broadcast_dismissals_on_user_id_and_broadcast_message_id ON user_broadcast_message_dismissals USING btree (user_id, broadcast_message_id); diff --git a/lib/gitlab/database/background_operation/common_job.rb b/lib/gitlab/database/background_operation/common_job.rb new file mode 100644 index 00000000000000..cead5145f42958 --- /dev/null +++ b/lib/gitlab/database/background_operation/common_job.rb @@ -0,0 +1,65 @@ +# frozen_string_literal: true + +module Gitlab + module Database + module BackgroundOperation + module CommonJob + extend ActiveSupport::Concern + + include PartitionedTable + + MINIMUM_PAUSE_MS = 100 + PARTITION_DURATION = 14.days + + REQUIRED_COLUMNS = %i[ + batch_size + sub_batch_size + worker_id + worker_partition + ].freeze + + included do |job_class| + REQUIRED_COLUMNS.each do |column| + validates column, presence: true + end + + validates :pause_ms, numericality: { greater_than_or_equal_to: MINIMUM_PAUSE_MS } + + delegate :job_class, :table_name, :column_name, :job_arguments, :job_class_name, + to: :worker, prefix: :worker + + scope :for_partition, ->(partition) { where(partition: partition) } + scope :executable, -> { with_statuses(:pending, :running) } + + # Partition should not be changed once the record is created + attr_readonly :partition + + partitioned_by :partition, strategy: :sliding_list, + next_partition_if: ->(active_partition) do + oldest_record_in_partition = job_class + .select(:id, :created_at) + .for_partition(active_partition.value) + .order(:id) + .limit(1) + .take + + oldest_record_in_partition.present? && oldest_record_in_partition.created_at < PARTITION_DURATION.ago + end, + detach_partition_if: ->(partition) do + !job_class + .for_partition(partition.value) + .executable + .exists? + end + + state_machine :status, initial: :pending do + state :pending, value: 0 + state :running, value: 1 + state :failed, value: 2 + state :succeeded, value: 3 + end + end + end + end + end +end diff --git a/lib/gitlab/database/background_operation/common_worker.rb b/lib/gitlab/database/background_operation/common_worker.rb new file mode 100644 index 00000000000000..cf00ebf615e7eb --- /dev/null +++ b/lib/gitlab/database/background_operation/common_worker.rb @@ -0,0 +1,72 @@ +# frozen_string_literal: true + +module Gitlab + module Database + module BackgroundOperation + module CommonWorker + extend ActiveSupport::Concern + + include PartitionedTable + + MINIMUM_PAUSE_MS = 100 + PARTITION_DURATION = 14.days + + REQUIRED_COLUMNS = %i[ + batch_size + sub_batch_size + priority + interval + job_class_name + batch_class_name + table_name + column_name + gitlab_schema + ].freeze + + included do |worker_class| + # Partition should not be changed once the record is created + attr_readonly :partition + + REQUIRED_COLUMNS.each do |column| + validates column, presence: true + end + + validates :pause_ms, numericality: { greater_than_or_equal_to: MINIMUM_PAUSE_MS } + + validates :job_arguments, uniqueness: { + scope: [:job_class_name, :table_name, :column_name] + } + + scope :for_partition, ->(partition) { where(partition: partition) } + scope :executable, -> { with_statuses(:active, :paused) } + + partitioned_by :partition, strategy: :sliding_list, + next_partition_if: ->(active_partition) do + oldest_record_in_partition = worker_class + .select(:id, :created_at) + .for_partition(active_partition.value) + .order(:id) + .limit(1) + .take + + oldest_record_in_partition.present? && + oldest_record_in_partition.created_at < PARTITION_DURATION.ago + end, + detach_partition_if: ->(partition) do + !worker_class + .for_partition(partition.value) + .executable + .exists? + end + + state_machine :status, initial: :paused do + state :paused, value: 0 + state :active, value: 1 + state :finished, value: 2 + state :failed, value: 3 + end + end + end + end + end +end diff --git a/lib/gitlab/database/background_operation/job.rb b/lib/gitlab/database/background_operation/job.rb index 5725faee991316..d7d14e6a46e65e 100644 --- a/lib/gitlab/database/background_operation/job.rb +++ b/lib/gitlab/database/background_operation/job.rb @@ -4,66 +4,16 @@ module Gitlab module Database module BackgroundOperation class Job < SharedModel - include PartitionedTable + include CommonJob self.table_name = :background_operation_jobs - MINIMUM_PAUSE_MS = 100 - PARTITION_DURATION = 14.days - - REQUIRED_COLUMNS = %i[ - batch_size - sub_batch_size - background_operation_worker_id - background_operation_worker_partition - ].freeze - belongs_to :worker, - ->(job) { where(partition: job.background_operation_worker_partition) }, + ->(job) { where(partition: job.worker_partition) }, class_name: 'Gitlab::Database::BackgroundOperation::Worker', - foreign_key: :background_operation_worker_id, - partition_foreign_key: :background_operation_worker_partition, + foreign_key: :worker_id, + partition_foreign_key: :worker_partition, inverse_of: :jobs - - REQUIRED_COLUMNS.each do |column| - validates column, presence: true - end - - validates :pause_ms, numericality: { greater_than_or_equal_to: MINIMUM_PAUSE_MS } - - delegate :job_class, :table_name, :column_name, :job_arguments, :job_class_name, - to: :worker, prefix: :worker - - scope :for_partition, ->(partition) { where(partition: partition) } - scope :executable, -> { with_statuses(:pending, :running) } - - # Partition should not be changed once the record is created - attr_readonly :partition - - partitioned_by :partition, strategy: :sliding_list, - next_partition_if: ->(active_partition) do - oldest_record_in_partition = Job - .select(:id, :created_at) - .for_partition(active_partition.value) - .order(:id) - .limit(1) - .take - - oldest_record_in_partition.present? && oldest_record_in_partition.created_at < PARTITION_DURATION.ago - end, - detach_partition_if: ->(partition) do - !Job - .for_partition(partition.value) - .executable - .exists? - end - - state_machine :status, initial: :pending do - state :pending, value: 0 - state :running, value: 1 - state :failed, value: 2 - state :succeeded, value: 3 - end end end end diff --git a/lib/gitlab/database/background_operation/job_cell_local.rb b/lib/gitlab/database/background_operation/job_cell_local.rb new file mode 100644 index 00000000000000..6b987839c995af --- /dev/null +++ b/lib/gitlab/database/background_operation/job_cell_local.rb @@ -0,0 +1,20 @@ +# frozen_string_literal: true + +module Gitlab + module Database + module BackgroundOperation + class JobCellLocal < SharedModel + include CommonJob + + self.table_name = :background_operation_jobs_cell_local + + belongs_to :worker, + ->(job) { where(partition: job.worker_partition) }, + class_name: 'Gitlab::Database::BackgroundOperation::WorkerCellLocal', + foreign_key: :worker_id, + partition_foreign_key: :worker_partition, + inverse_of: :jobs + end + end + end +end diff --git a/lib/gitlab/database/background_operation/worker.rb b/lib/gitlab/database/background_operation/worker.rb index d21a6f53779da9..464f63f7189b07 100644 --- a/lib/gitlab/database/background_operation/worker.rb +++ b/lib/gitlab/database/background_operation/worker.rb @@ -4,73 +4,16 @@ module Gitlab module Database module BackgroundOperation class Worker < SharedModel - include PartitionedTable + include CommonWorker self.table_name = :background_operation_workers - MINIMUM_PAUSE_MS = 100 - PARTITION_DURATION = 14.days - - REQUIRED_COLUMNS = %i[ - batch_size - sub_batch_size - priority - interval - job_class_name - batch_class_name - table_name - column_name - gitlab_schema - ].freeze - - # Partition should not be changed once the record is created - attr_readonly :partition - has_many :jobs, - ->(worker) { where(background_operation_worker_partition: worker.partition) }, + ->(worker) { where(worker_partition: worker.partition) }, class_name: 'Gitlab::Database::BackgroundOperation::Job', - foreign_key: :background_operation_worker_id, + foreign_key: :worker_id, inverse_of: :worker, - partition_foreign_key: :background_operation_worker_partition - - REQUIRED_COLUMNS.each do |column| - validates column, presence: true - end - - validates :pause_ms, numericality: { greater_than_or_equal_to: MINIMUM_PAUSE_MS } - - validates :job_arguments, uniqueness: { - scope: [:job_class_name, :table_name, :column_name] - } - - scope :for_partition, ->(partition) { where(partition: partition) } - scope :executable, -> { with_statuses(:active, :paused) } - - partitioned_by :partition, strategy: :sliding_list, - next_partition_if: ->(active_partition) do - oldest_record_in_partition = Worker - .select(:id, :created_at) - .for_partition(active_partition.value) - .order(:id) - .limit(1) - .take - - oldest_record_in_partition.present? && - oldest_record_in_partition.created_at < PARTITION_DURATION.ago - end, - detach_partition_if: ->(partition) do - !Worker - .for_partition(partition.value) - .executable - .exists? - end - - state_machine :status, initial: :paused do - state :paused, value: 0 - state :active, value: 1 - state :finished, value: 2 - state :failed, value: 3 - end + partition_foreign_key: :worker_partition end end end diff --git a/lib/gitlab/database/background_operation/worker_cell_local.rb b/lib/gitlab/database/background_operation/worker_cell_local.rb new file mode 100644 index 00000000000000..f55fb5234192d3 --- /dev/null +++ b/lib/gitlab/database/background_operation/worker_cell_local.rb @@ -0,0 +1,20 @@ +# frozen_string_literal: true + +module Gitlab + module Database + module BackgroundOperation + class WorkerCellLocal < SharedModel + include CommonWorker + + self.table_name = :background_operation_workers_cell_local + + has_many :jobs, + ->(worker) { where(worker_partition: worker.partition) }, + class_name: 'Gitlab::Database::BackgroundOperation::JobCellLocal', + foreign_key: :worker_id, + inverse_of: :worker, + partition_foreign_key: :worker_partition + end + end + end +end diff --git a/spec/factories/gitlab/database/background_operation/jobs.rb b/spec/factories/gitlab/database/background_operation/jobs.rb index 9a669370c50804..8e0701cb525844 100644 --- a/spec/factories/gitlab/database/background_operation/jobs.rb +++ b/spec/factories/gitlab/database/background_operation/jobs.rb @@ -8,8 +8,8 @@ sub_batch_size { 10 } min_cursor { [1] } max_cursor { [1000] } - background_operation_worker_id { 1 } - background_operation_worker_partition { 1 } + worker_id { 1 } + worker_partition { 1 } trait :pending do status { 0 } diff --git a/spec/factories/gitlab/database/background_operation/jobs_cell_local.rb b/spec/factories/gitlab/database/background_operation/jobs_cell_local.rb new file mode 100644 index 00000000000000..8c2aa62bfd7755 --- /dev/null +++ b/spec/factories/gitlab/database/background_operation/jobs_cell_local.rb @@ -0,0 +1,34 @@ +# frozen_string_literal: true + +FactoryBot.define do + factory :background_operation_job_cell_local, class: 'Gitlab::Database::BackgroundOperation::JobCellLocal' do + association :worker, factory: :background_operation_worker + + batch_size { 100 } + sub_batch_size { 10 } + min_cursor { [1] } + max_cursor { [1000] } + background_operation_worker_id { 1 } + background_operation_worker_partition { 1 } + + trait :pending do + status { 0 } + end + + trait :running do + status { 1 } + started_at { Time.current } + end + + trait :failed do + status { 2 } + attempts { 1 } + end + + trait :succeeded do + status { 3 } + started_at { 1.hour.ago } + finished_at { Time.current } + end + end +end diff --git a/spec/factories/gitlab/database/background_operation/workers_cell_local.rb b/spec/factories/gitlab/database/background_operation/workers_cell_local.rb new file mode 100644 index 00000000000000..5b2300c403a988 --- /dev/null +++ b/spec/factories/gitlab/database/background_operation/workers_cell_local.rb @@ -0,0 +1,39 @@ +# frozen_string_literal: true + +FactoryBot.define do + factory :background_operation_worker_cell_local, class: 'Gitlab::Database::BackgroundOperation::WorkerCellLocal' do + job_class_name { 'CopyColumnUsingBackgroundMigrationJob' } + batch_class_name { 'PrimaryKeyBatchingStrategy' } + table_name { :users } + column_name { :id } + gitlab_schema { :gitlab_main_org } + batch_size { 1000 } + sub_batch_size { 100 } + pause_ms { 100 } + priority { 0 } + interval { 2.minutes } + sequence(:job_arguments) { |n| [["column_#{n}"], ["column_#{n}_convert_to_bigint"]] } + min_cursor { [1] } + max_cursor { [1000] } + + trait :paused do + status { 0 } + end + + trait :active do + status { 1 } + end + + trait :finished do + status { 2 } + end + + trait :failed do + status { 3 } + end + + trait :organization_specific do + organization_id { create(:common_organization).id } + end + end +end -- GitLab From 24886b7e4c88170750d3008a95c1b26739da33d3 Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Tue, 16 Sep 2025 22:17:10 +0200 Subject: [PATCH 09/17] Adds uuid ID for org specific bbo tables --- config/initializers/postgres_partitioning.rb | 4 +- db/docs/background_operation_jobs.yml | 5 +- .../background_operation_jobs_cell_local.yml | 3 +- db/docs/background_operation_workers.yml | 5 +- ...ackground_operation_workers_cell_local.yml | 3 +- ...841_create_background_operation_workers.rb | 11 +- ...164941_create_background_operation_jobs.rb | 11 +- db/structure.sql | 38 ++--- .../background_operation/common_job.rb | 2 +- .../background_operation/common_worker.rb | 2 +- .../database/background_operation/job.rb | 3 + .../database/background_operation/worker.rb | 3 + spec/db/schema_spec.rb | 6 +- .../database/background_operation/jobs.rb | 6 +- .../background_operation/jobs_cell_local.rb | 6 +- .../database/background_operation/workers.rb | 6 +- .../workers_cell_local.rb | 4 - .../job_cell_local_spec.rb | 8 + .../job_shared_examples.rb | 132 +++++++++++++++++ .../database/background_operation/job_spec.rb | 140 +----------------- .../worker_cell_local_spec.rb | 8 + .../worker_shared_examples.rb | 132 +++++++++++++++++ .../background_operation/worker_spec.rb | 130 +--------------- 23 files changed, 340 insertions(+), 328 deletions(-) create mode 100644 spec/lib/gitlab/database/background_operation/job_cell_local_spec.rb create mode 100644 spec/lib/gitlab/database/background_operation/job_shared_examples.rb create mode 100644 spec/lib/gitlab/database/background_operation/worker_cell_local_spec.rb create mode 100644 spec/lib/gitlab/database/background_operation/worker_shared_examples.rb diff --git a/config/initializers/postgres_partitioning.rb b/config/initializers/postgres_partitioning.rb index 91bf4d14f46c15..eb6fdc51c31b28 100644 --- a/config/initializers/postgres_partitioning.rb +++ b/config/initializers/postgres_partitioning.rb @@ -46,7 +46,9 @@ MergeRequests::GeneratedRefCommit, MergeRequests::MergeData, Gitlab::Database::BackgroundOperation::Worker, - Gitlab::Database::BackgroundOperation::Job + Gitlab::Database::BackgroundOperation::Job, + Gitlab::Database::BackgroundOperation::WorkerCellLocal, + Gitlab::Database::BackgroundOperation::JobCellLocal ]) if Gitlab.ee? diff --git a/db/docs/background_operation_jobs.yml b/db/docs/background_operation_jobs.yml index a4232921f22f94..082ed373fcd895 100644 --- a/db/docs/background_operation_jobs.yml +++ b/db/docs/background_operation_jobs.yml @@ -7,6 +7,7 @@ feature_categories: description: Store jobs info for each background_operation_workers. introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/203234 milestone: '18.4' -#gitlab_schema: gitlab_shared_org -gitlab_schema: gitlab_shared +gitlab_schema: gitlab_shared_org +sharding_key: + organization_id: organizations table_size: small diff --git a/db/docs/background_operation_jobs_cell_local.yml b/db/docs/background_operation_jobs_cell_local.yml index a14a820311c50f..e8a4f1bcae2979 100644 --- a/db/docs/background_operation_jobs_cell_local.yml +++ b/db/docs/background_operation_jobs_cell_local.yml @@ -7,6 +7,5 @@ feature_categories: description: Store jobs info for each background_operation_workers_cell_local. introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/203234 milestone: '18.4' -#gitlab_schema: gitlab_shared_cell_local -gitlab_schema: gitlab_shared +gitlab_schema: gitlab_shared_cell_local table_size: small diff --git a/db/docs/background_operation_workers.yml b/db/docs/background_operation_workers.yml index 4798bc7d556e58..1ed1901dba4746 100644 --- a/db/docs/background_operation_workers.yml +++ b/db/docs/background_operation_workers.yml @@ -9,6 +9,7 @@ description: Stores information about the large data operations performed async. for more details. introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/203234 milestone: '18.4' -#gitlab_schema: gitlab_shared_org -gitlab_schema: gitlab_shared +gitlab_schema: gitlab_shared_org +sharding_key: + organization_id: organizations table_size: small diff --git a/db/docs/background_operation_workers_cell_local.yml b/db/docs/background_operation_workers_cell_local.yml index c41f486333bbb5..d93296776fa8c8 100644 --- a/db/docs/background_operation_workers_cell_local.yml +++ b/db/docs/background_operation_workers_cell_local.yml @@ -9,6 +9,5 @@ description: Stores information about the cell local large data operations perfo for more details. introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/203234 milestone: '18.4' -#gitlab_schema: gitlab_shared_org -gitlab_schema: gitlab_shared +gitlab_schema: gitlab_shared_cell_local table_size: small diff --git a/db/migrate/20250915164841_create_background_operation_workers.rb b/db/migrate/20250915164841_create_background_operation_workers.rb index 4b5d158a558c50..0918f2de08fdd9 100644 --- a/db/migrate/20250915164841_create_background_operation_workers.rb +++ b/db/migrate/20250915164841_create_background_operation_workers.rb @@ -15,9 +15,9 @@ def up } create_table(:background_operation_workers, **opts) do |t| - t.bigserial :id - t.bigint :organization_id - t.bigint :user_id + t.uuid :id, default: -> { "gen_random_uuid()" }, null: false + t.bigint :organization_id, null: false + t.bigint :user_id, null: false t.bigint :total_tuple_count t.timestamptz :started_at t.timestamptz :on_hold_until @@ -93,6 +93,11 @@ def add_indexes :user_id, name: 'index_background_operation_workers_by_user' ) + add_concurrent_partitioned_index( + :background_operation_workers, + :created_at, + name: 'index_background_operation_workers_by_created_at' + ) add_concurrent_partitioned_index( :background_operation_workers, [:partition, :job_class_name, :table_name, :column_name, :job_arguments], diff --git a/db/migrate/20250915164941_create_background_operation_jobs.rb b/db/migrate/20250915164941_create_background_operation_jobs.rb index 430d0465a2d641..c9a00cdb92e604 100644 --- a/db/migrate/20250915164941_create_background_operation_jobs.rb +++ b/db/migrate/20250915164941_create_background_operation_jobs.rb @@ -15,9 +15,9 @@ def up } create_table(:background_operation_jobs, **opts) do |t| - t.bigserial :id - t.bigint :organization_id - t.bigint :user_id + t.uuid :id, default: -> { "gen_random_uuid()" }, null: false + t.bigint :organization_id, null: false + t.bigint :user_id, null: false t.timestamptz :created_at, null: false t.timestamptz :started_at t.timestamptz :finished_at @@ -80,6 +80,11 @@ def add_indexes :user_id, name: 'index_background_operation_jobs_by_user' ) + add_concurrent_partitioned_index( + :background_operation_jobs, + :created_at, + name: 'index_background_operation_jobs_by_created_at' + ) add_concurrent_partitioned_index( :background_operation_jobs, [:worker_id, :id], diff --git a/db/structure.sql b/db/structure.sql index 39758e653317fc..b73145295c4e84 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -4700,9 +4700,9 @@ CREATE TABLE audit_events ( PARTITION BY RANGE (created_at); CREATE TABLE background_operation_jobs ( - id bigint NOT NULL, - organization_id bigint, - user_id bigint, + id uuid DEFAULT gen_random_uuid() NOT NULL, + organization_id bigint NOT NULL, + user_id bigint NOT NULL, created_at timestamp with time zone NOT NULL, started_at timestamp with time zone, finished_at timestamp with time zone, @@ -4724,9 +4724,9 @@ CREATE TABLE background_operation_jobs ( PARTITION BY LIST (partition); CREATE TABLE background_operation_workers ( - id bigint NOT NULL, - organization_id bigint, - user_id bigint, + id uuid DEFAULT gen_random_uuid() NOT NULL, + organization_id bigint NOT NULL, + user_id bigint NOT NULL, total_tuple_count bigint, started_at timestamp with time zone, on_hold_until timestamp with time zone, @@ -12419,15 +12419,6 @@ CREATE SEQUENCE background_operation_jobs_cell_local_id_seq ALTER SEQUENCE background_operation_jobs_cell_local_id_seq OWNED BY background_operation_jobs_cell_local.id; -CREATE SEQUENCE background_operation_jobs_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - -ALTER SEQUENCE background_operation_jobs_id_seq OWNED BY background_operation_jobs.id; - CREATE TABLE background_operation_workers_cell_local ( id bigint NOT NULL, total_tuple_count bigint, @@ -12473,15 +12464,6 @@ CREATE SEQUENCE background_operation_workers_cell_local_id_seq ALTER SEQUENCE background_operation_workers_cell_local_id_seq OWNED BY background_operation_workers_cell_local.id; -CREATE SEQUENCE background_operation_workers_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - -ALTER SEQUENCE background_operation_workers_id_seq OWNED BY background_operation_workers.id; - CREATE TABLE badges ( id bigint NOT NULL, link_url character varying NOT NULL, @@ -30334,12 +30316,8 @@ ALTER TABLE ONLY automation_rules ALTER COLUMN id SET DEFAULT nextval('automatio ALTER TABLE ONLY award_emoji ALTER COLUMN id SET DEFAULT nextval('award_emoji_id_seq'::regclass); -ALTER TABLE ONLY background_operation_jobs ALTER COLUMN id SET DEFAULT nextval('background_operation_jobs_id_seq'::regclass); - ALTER TABLE ONLY background_operation_jobs_cell_local ALTER COLUMN id SET DEFAULT nextval('background_operation_jobs_cell_local_id_seq'::regclass); -ALTER TABLE ONLY background_operation_workers ALTER COLUMN id SET DEFAULT nextval('background_operation_workers_id_seq'::regclass); - ALTER TABLE ONLY background_operation_workers_cell_local ALTER COLUMN id SET DEFAULT nextval('background_operation_workers_cell_local_id_seq'::regclass); ALTER TABLE ONLY badges ALTER COLUMN id SET DEFAULT nextval('badges_id_seq'::regclass); @@ -38493,10 +38471,14 @@ CREATE INDEX index_background_jobs_on_worker_id_and_cursor_max_value ON ONLY bac CREATE INDEX index_background_jobs_on_worker_id_and_finished_at ON ONLY background_operation_jobs USING btree (worker_id, finished_at); +CREATE INDEX index_background_operation_jobs_by_created_at ON ONLY background_operation_jobs USING btree (created_at); + CREATE INDEX index_background_operation_jobs_by_organization ON ONLY background_operation_jobs USING btree (organization_id); CREATE INDEX index_background_operation_jobs_by_user ON ONLY background_operation_jobs USING btree (user_id); +CREATE INDEX index_background_operation_workers_by_created_at ON ONLY background_operation_workers USING btree (created_at); + CREATE INDEX index_background_operation_workers_by_organization ON ONLY background_operation_workers USING btree (organization_id); CREATE INDEX index_background_operation_workers_by_priority ON ONLY background_operation_workers USING btree (priority); diff --git a/lib/gitlab/database/background_operation/common_job.rb b/lib/gitlab/database/background_operation/common_job.rb index cead5145f42958..f16ba7fb9655d0 100644 --- a/lib/gitlab/database/background_operation/common_job.rb +++ b/lib/gitlab/database/background_operation/common_job.rb @@ -39,7 +39,7 @@ module CommonJob oldest_record_in_partition = job_class .select(:id, :created_at) .for_partition(active_partition.value) - .order(:id) + .order(:created_at) .limit(1) .take diff --git a/lib/gitlab/database/background_operation/common_worker.rb b/lib/gitlab/database/background_operation/common_worker.rb index cf00ebf615e7eb..27307d1fef1de8 100644 --- a/lib/gitlab/database/background_operation/common_worker.rb +++ b/lib/gitlab/database/background_operation/common_worker.rb @@ -45,7 +45,7 @@ module CommonWorker oldest_record_in_partition = worker_class .select(:id, :created_at) .for_partition(active_partition.value) - .order(:id) + .order(:created_at) .limit(1) .take diff --git a/lib/gitlab/database/background_operation/job.rb b/lib/gitlab/database/background_operation/job.rb index d7d14e6a46e65e..e69fc3a489cf09 100644 --- a/lib/gitlab/database/background_operation/job.rb +++ b/lib/gitlab/database/background_operation/job.rb @@ -14,6 +14,9 @@ class Job < SharedModel foreign_key: :worker_id, partition_foreign_key: :worker_partition, inverse_of: :jobs + + belongs_to :organization + belongs_to :user end end end diff --git a/lib/gitlab/database/background_operation/worker.rb b/lib/gitlab/database/background_operation/worker.rb index 464f63f7189b07..c1f31fc4558fbb 100644 --- a/lib/gitlab/database/background_operation/worker.rb +++ b/lib/gitlab/database/background_operation/worker.rb @@ -14,6 +14,9 @@ class Worker < SharedModel foreign_key: :worker_id, inverse_of: :worker, partition_foreign_key: :worker_partition + + belongs_to :organization + belongs_to :user end end end diff --git a/spec/db/schema_spec.rb b/spec/db/schema_spec.rb index c5b944da21ae9e..80532866427afd 100644 --- a/spec/db/schema_spec.rb +++ b/spec/db/schema_spec.rb @@ -296,7 +296,8 @@ work_item_related_link_restrictions: %w[source_type_id target_type_id], sbom_vulnerability_scans: %w[project_id build_id], # referenced records are in different DB and no LFK as the table contains references to object storage security_trainings: %w[training_provider_id], # training_provider_id is a fixed items model reference. - background_operation_jobs: %w[background_operation_worker_id] # background_operation_workers partitions have to dropped independently and background_operation_jobs will be dropped after execution (with a retention period). + background_operation_jobs_cell_local: %w[worker_id], # background operation workers partitions have to dropped independently. + background_operation_jobs: %w[worker_id] # background operation workers partitions have to dropped independently. }.with_indifferent_access.freeze end @@ -554,7 +555,8 @@ class #{model.name} models.each do |model| # Skip migration models - next if model.name.include?('Gitlab::BackgroundMigration') + next if model.name.match?(/Gitlab::Background(?:Migration|Operation)/) + next if ignored_jsonb_columns(model.name).include?(column_name) has_validator = model.validators.any? do |v| diff --git a/spec/factories/gitlab/database/background_operation/jobs.rb b/spec/factories/gitlab/database/background_operation/jobs.rb index 8e0701cb525844..0482b5a3abeffc 100644 --- a/spec/factories/gitlab/database/background_operation/jobs.rb +++ b/spec/factories/gitlab/database/background_operation/jobs.rb @@ -4,12 +4,14 @@ factory :background_operation_job, class: 'Gitlab::Database::BackgroundOperation::Job' do association :worker, factory: :background_operation_worker + organization_id { create(:common_organization).id } batch_size { 100 } sub_batch_size { 10 } min_cursor { [1] } max_cursor { [1000] } worker_id { 1 } worker_partition { 1 } + user trait :pending do status { 0 } @@ -30,9 +32,5 @@ started_at { 1.hour.ago } finished_at { Time.current } end - - trait :organization_specific do - organization_id { create(:common_organization).id } - end end end diff --git a/spec/factories/gitlab/database/background_operation/jobs_cell_local.rb b/spec/factories/gitlab/database/background_operation/jobs_cell_local.rb index 8c2aa62bfd7755..6dd12203ab2b6f 100644 --- a/spec/factories/gitlab/database/background_operation/jobs_cell_local.rb +++ b/spec/factories/gitlab/database/background_operation/jobs_cell_local.rb @@ -2,14 +2,14 @@ FactoryBot.define do factory :background_operation_job_cell_local, class: 'Gitlab::Database::BackgroundOperation::JobCellLocal' do - association :worker, factory: :background_operation_worker + association :worker, factory: :background_operation_worker_cell_local batch_size { 100 } sub_batch_size { 10 } min_cursor { [1] } max_cursor { [1000] } - background_operation_worker_id { 1 } - background_operation_worker_partition { 1 } + worker_id { 1 } + worker_partition { 1 } trait :pending do status { 0 } diff --git a/spec/factories/gitlab/database/background_operation/workers.rb b/spec/factories/gitlab/database/background_operation/workers.rb index 1e640a9aab64c1..c80a224211ddc2 100644 --- a/spec/factories/gitlab/database/background_operation/workers.rb +++ b/spec/factories/gitlab/database/background_operation/workers.rb @@ -2,6 +2,7 @@ FactoryBot.define do factory :background_operation_worker, class: 'Gitlab::Database::BackgroundOperation::Worker' do + organization_id { create(:common_organization).id } job_class_name { 'CopyColumnUsingBackgroundMigrationJob' } batch_class_name { 'PrimaryKeyBatchingStrategy' } table_name { :users } @@ -15,6 +16,7 @@ sequence(:job_arguments) { |n| [["column_#{n}"], ["column_#{n}_convert_to_bigint"]] } min_cursor { [1] } max_cursor { [1000] } + user trait :paused do status { 0 } @@ -31,9 +33,5 @@ trait :failed do status { 3 } end - - trait :organization_specific do - organization_id { create(:common_organization).id } - end end end diff --git a/spec/factories/gitlab/database/background_operation/workers_cell_local.rb b/spec/factories/gitlab/database/background_operation/workers_cell_local.rb index 5b2300c403a988..6caf009983c059 100644 --- a/spec/factories/gitlab/database/background_operation/workers_cell_local.rb +++ b/spec/factories/gitlab/database/background_operation/workers_cell_local.rb @@ -31,9 +31,5 @@ trait :failed do status { 3 } end - - trait :organization_specific do - organization_id { create(:common_organization).id } - end end end diff --git a/spec/lib/gitlab/database/background_operation/job_cell_local_spec.rb b/spec/lib/gitlab/database/background_operation/job_cell_local_spec.rb new file mode 100644 index 00000000000000..526953cced0eb9 --- /dev/null +++ b/spec/lib/gitlab/database/background_operation/job_cell_local_spec.rb @@ -0,0 +1,8 @@ +# frozen_string_literal: true + +require 'spec_helper' +require_relative 'job_shared_examples' + +RSpec.describe Gitlab::Database::BackgroundOperation::JobCellLocal, type: :model, feature_category: :database do + it_behaves_like 'background operation job functionality', :background_operation_job_cell_local +end diff --git a/spec/lib/gitlab/database/background_operation/job_shared_examples.rb b/spec/lib/gitlab/database/background_operation/job_shared_examples.rb new file mode 100644 index 00000000000000..5de9cbf500daf2 --- /dev/null +++ b/spec/lib/gitlab/database/background_operation/job_shared_examples.rb @@ -0,0 +1,132 @@ +# frozen_string_literal: true + +RSpec.shared_examples 'background operation job functionality' do |job_factory| + using RSpec::Parameterized::TableSyntax + + it { is_expected.to be_a Gitlab::Database::SharedModel } + + describe 'associations' do + it { is_expected.to belong_to(:worker).inverse_of(:jobs) } + end + + describe 'validations' do + subject { build(job_factory) } + + described_class::REQUIRED_COLUMNS.each do |column| + it { is_expected.to validate_presence_of(column) } + end + + it { is_expected.to validate_numericality_of(:pause_ms).is_greater_than_or_equal_to(100) } + end + + describe 'scopes' do + let_it_be(:job_1) { create(job_factory, :pending) } + let_it_be(:job_2) { create(job_factory, :running) } + let_it_be(:job_3) { create(job_factory, :failed) } + let_it_be(:job_4) { create(job_factory, :succeeded) } + + describe '.executable' do + it 'returns jobs with only with pending or running status' do + expect(described_class.executable).to contain_exactly(job_1, job_2) + end + end + end + + describe 'sliding_list partitioning' do + let(:connection) { described_class.connection } + let(:partition_manager) { Gitlab::Database::Partitioning::PartitionManager.new(described_class) } + + describe 'next_partition_if callback' do + let(:active_partition) { described_class.partitioning_strategy.active_partition } + + subject(:value) { described_class.partitioning_strategy.next_partition_if.call(active_partition) } + + context 'when the partition is empty' do + it { is_expected.to be(false) } + end + + context 'when the partition has recent records' do + before do + create(job_factory, created_at: 1.day.ago) + end + + it { is_expected.to be(false) } + end + + context 'when the first record of the partition is older than PARTITION_DURATION' do + before do + create(job_factory, created_at: (described_class::PARTITION_DURATION + 1.day).ago) + create(job_factory, created_at: 1.day.ago) + end + + it { is_expected.to be(true) } + end + end + + describe 'detach_partition_if callback' do + let(:active_partition) { described_class.partitioning_strategy.active_partition } + + subject(:value) { described_class.partitioning_strategy.detach_partition_if.call(active_partition) } + + context 'when the partition contains executable jobs' do + before do + create(job_factory, :pending) + create(job_factory, :running) + create(job_factory, :succeeded) + end + + it { is_expected.to be(false) } + end + + context 'when the partition contains only non-executable jobs' do + before do + create(job_factory, :succeeded) + create(job_factory, :failed) + end + + it { is_expected.to be(true) } + end + + context 'when the partition is empty' do + it { is_expected.to be(true) } + end + end + + describe 'the behavior of the strategy' do + it 'moves records to new partitions as time passes', :freeze_time do + # We start with partition 1 + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) + + # it's not a day old yet so no new partitions are created + partition_manager.sync_partitions + + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) + + # add one record so the next partition will be created + create(job_factory) # rubocop:disable Rails/SaveBang -- factory + + # after traveling forward past PARTITION_DURATION + travel(Gitlab::Database::BackgroundOperation::Worker::PARTITION_DURATION + 1.second) + + # a new partition is created + partition_manager.sync_partitions + + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1, 2]) + + # and we can insert to the new partition + expect { create(job_factory) }.not_to raise_error # rubocop:disable Rails/SaveBang -- factory + + # after marking old records as non-executable + described_class.for_partition(1).update_all(status: 3) + + partition_manager.sync_partitions + + # the old one is removed + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([2]) + + # and we only have the newly created partition left. + expect(described_class.count).to eq(1) + end + end + end +end diff --git a/spec/lib/gitlab/database/background_operation/job_spec.rb b/spec/lib/gitlab/database/background_operation/job_spec.rb index b26bcf6eafc017..3a45d1844e25cd 100644 --- a/spec/lib/gitlab/database/background_operation/job_spec.rb +++ b/spec/lib/gitlab/database/background_operation/job_spec.rb @@ -1,144 +1,8 @@ # frozen_string_literal: true require 'spec_helper' +require_relative 'job_shared_examples' RSpec.describe Gitlab::Database::BackgroundOperation::Job, type: :model, feature_category: :database do - using RSpec::Parameterized::TableSyntax - - it { is_expected.to be_a Gitlab::Database::SharedModel } - - describe 'associations' do - it { is_expected.to belong_to(:worker).inverse_of(:jobs) } - end - - describe 'validations' do - subject { build(:background_operation_job) } - - described_class::REQUIRED_COLUMNS.each do |column| - it { is_expected.to validate_presence_of(column) } - end - - it { is_expected.to validate_numericality_of(:pause_ms).is_greater_than_or_equal_to(100) } - end - - describe 'scopes' do - let_it_be(:job_1) { create(:background_operation_job, :pending) } - let_it_be(:job_2) { create(:background_operation_job, :running) } - let_it_be(:job_3) { create(:background_operation_job, :failed) } - let_it_be(:job_4) { create(:background_operation_job, :succeeded) } - - describe '.executable' do - it 'returns jobs with only with pending or running status' do - expect(described_class.executable).to contain_exactly(job_1, job_2) - end - end - end - - describe 'sliding_list partitioning' do - let(:connection) { described_class.connection } - let(:partition_manager) { Gitlab::Database::Partitioning::PartitionManager.new(described_class) } - - describe 'next_partition_if callback' do - let(:active_partition) { described_class.partitioning_strategy.active_partition } - - subject(:value) { described_class.partitioning_strategy.next_partition_if.call(active_partition) } - - context 'when the partition is empty' do - it { is_expected.to be(false) } - end - - context 'when the partition has recent records' do - before do - create(:background_operation_job, created_at: 1.day.ago) - end - - it { is_expected.to be(false) } - end - - context 'when the first record of the partition is older than PARTITION_DURATION' do - before do - create(:background_operation_job, created_at: (described_class::PARTITION_DURATION + 1.day).ago) - create(:background_operation_job, created_at: 1.day.ago) - end - - it { is_expected.to be(true) } - end - end - - describe 'detach_partition_if callback' do - let(:active_partition) { described_class.partitioning_strategy.active_partition } - - subject(:value) { described_class.partitioning_strategy.detach_partition_if.call(active_partition) } - - context 'when the partition contains executable jobs' do - before do - create(:background_operation_job, :pending) - create(:background_operation_job, :running) - create(:background_operation_job, :succeeded) - end - - it { is_expected.to be(false) } - end - - context 'when the partition contains only non-executable jobs' do - before do - create(:background_operation_job, :succeeded) - create(:background_operation_job, :failed) - end - - it { is_expected.to be(true) } - end - - context 'when the partition is empty' do - it { is_expected.to be(true) } - end - end - - describe 'the behavior of the strategy' do - it 'moves records to new partitions as time passes', :freeze_time do - # We start with partition 1 - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) - - # it's not a day old yet so no new partitions are created - partition_manager.sync_partitions - - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) - - # add one record so the next partition will be created - create(:background_operation_job) - - # after traveling forward past PARTITION_DURATION - travel(Gitlab::Database::BackgroundOperation::Worker::PARTITION_DURATION + 1.second) - - # a new partition is created - partition_manager.sync_partitions - - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1, 2]) - - # and we can insert to the new partition - expect { create(:background_operation_job) }.not_to raise_error - - # after marking old records as non-executable - described_class.for_partition(1).update_all(status: 3) - - partition_manager.sync_partitions - - # the old one is removed - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([2]) - - # and we only have the newly created partition left. - expect(described_class.count).to eq(1) - end - end - end - - # describe 'partition consistency' do - # it 'ensures job and worker are in the same partition' do - # worker = create(:background_operation_worker, partition: 2) - # job = create(:background_operation_job, worker: worker) - # - # expect(job.partition).to eq(worker.partition) - # expect(job.background_operation_worker_partition).to eq(worker.partition) - # end - # end + it_behaves_like 'background operation job functionality', :background_operation_job end diff --git a/spec/lib/gitlab/database/background_operation/worker_cell_local_spec.rb b/spec/lib/gitlab/database/background_operation/worker_cell_local_spec.rb new file mode 100644 index 00000000000000..1e4499919e4ac6 --- /dev/null +++ b/spec/lib/gitlab/database/background_operation/worker_cell_local_spec.rb @@ -0,0 +1,8 @@ +# frozen_string_literal: true + +require 'spec_helper' +require_relative 'worker_shared_examples' + +RSpec.describe Gitlab::Database::BackgroundOperation::WorkerCellLocal, type: :model, feature_category: :database do + it_behaves_like 'background operation worker functionality', :background_operation_worker_cell_local +end diff --git a/spec/lib/gitlab/database/background_operation/worker_shared_examples.rb b/spec/lib/gitlab/database/background_operation/worker_shared_examples.rb new file mode 100644 index 00000000000000..97c001d4289100 --- /dev/null +++ b/spec/lib/gitlab/database/background_operation/worker_shared_examples.rb @@ -0,0 +1,132 @@ +# frozen_string_literal: true + +RSpec.shared_examples 'background operation worker functionality' do |worker_factory| + using RSpec::Parameterized::TableSyntax + + it { is_expected.to be_a Gitlab::Database::SharedModel } + + describe 'associations' do + it { is_expected.to have_many(:jobs).inverse_of(:worker) } + end + + describe 'validations' do + subject { build(worker_factory) } + + described_class::REQUIRED_COLUMNS.each do |column| + it { is_expected.to validate_presence_of(column) } + end + + it { is_expected.to validate_numericality_of(:pause_ms).is_greater_than_or_equal_to(100) } + it { is_expected.to validate_uniqueness_of(:job_arguments).scoped_to(:job_class_name, :table_name, :column_name) } + end + + describe 'scopes' do + let_it_be(:worker_1) { create(worker_factory, :active) } + let_it_be(:worker_2) { create(worker_factory, :paused) } + let_it_be(:worker_3) { create(worker_factory, :finished) } + + describe '.executable' do + it 'returns workers with active or paused status' do + expect(described_class.executable).to contain_exactly(worker_1, worker_2) + end + end + end + + describe 'sliding_list partitioning' do + let(:connection) { described_class.connection } + let(:partition_manager) { Gitlab::Database::Partitioning::PartitionManager.new(described_class) } + + describe 'next_partition_if callback' do + let(:active_partition) { described_class.partitioning_strategy.active_partition } + + subject(:value) { described_class.partitioning_strategy.next_partition_if.call(active_partition) } + + context 'when the partition is empty' do + it { is_expected.to be(false) } + end + + context 'when the partition has recent records' do + before do + create(worker_factory, created_at: 1.day.ago) + end + + it { is_expected.to be(false) } + end + + context 'when the first record of the partition is older than PARTITION_DURATION' do + before do + create(worker_factory, created_at: (described_class::PARTITION_DURATION + 1.day).ago) + create(worker_factory, created_at: 1.day.ago) + end + + it { is_expected.to be(true) } + end + end + + describe 'detach_partition_if callback' do + let(:active_partition) { described_class.partitioning_strategy.active_partition } + + subject(:value) { described_class.partitioning_strategy.detach_partition_if.call(active_partition) } + + context 'when the partition contains executable workers' do + before do + create(worker_factory, :active) + create(worker_factory, :paused) + create(worker_factory, :finished) + end + + it { is_expected.to be(false) } + end + + context 'when the partition contains only non-executable workers' do + before do + create(worker_factory, :finished) + create(worker_factory, :failed) + end + + it { is_expected.to be(true) } + end + + context 'when the partition is empty' do + it { is_expected.to be(true) } + end + end + + describe 'the behavior of the strategy' do + it 'moves records to new partitions as time passes', :freeze_time do + # We start with partition 1 + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) + + # it's not 14 days old yet so no new partitions are created + partition_manager.sync_partitions + + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) + + # add one record so the next partition will be created + create(worker_factory) # rubocop:disable Rails/SaveBang -- factory + + # after traveling forward past PARTITION_DURATION + travel(described_class::PARTITION_DURATION + 1.minute) + + # a new partition is created + partition_manager.sync_partitions + + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1, 2]) + + # and we can insert to the new partition + expect { create(worker_factory) }.not_to raise_error # rubocop:disable Rails/SaveBang -- factory + + # after marking old records as non-executable + described_class.for_partition(1).update_all(status: 2) + + partition_manager.sync_partitions + + # the old one is removed + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([2]) + + # and we only have the newly created partition left. + expect(described_class.count).to eq(1) + end + end + end +end diff --git a/spec/lib/gitlab/database/background_operation/worker_spec.rb b/spec/lib/gitlab/database/background_operation/worker_spec.rb index 52326c79d25db8..f44d0be6a28530 100644 --- a/spec/lib/gitlab/database/background_operation/worker_spec.rb +++ b/spec/lib/gitlab/database/background_operation/worker_spec.rb @@ -1,134 +1,8 @@ # frozen_string_literal: true require 'spec_helper' +require_relative 'worker_shared_examples' RSpec.describe Gitlab::Database::BackgroundOperation::Worker, type: :model, feature_category: :database do - using RSpec::Parameterized::TableSyntax - - it { is_expected.to be_a Gitlab::Database::SharedModel } - - describe 'associations' do - it { is_expected.to have_many(:jobs).inverse_of(:worker) } - end - - describe 'validations' do - subject { build(:background_operation_worker) } - - described_class::REQUIRED_COLUMNS.each do |column| - it { is_expected.to validate_presence_of(column) } - end - - it { is_expected.to validate_numericality_of(:pause_ms).is_greater_than_or_equal_to(100) } - it { is_expected.to validate_uniqueness_of(:job_arguments).scoped_to(:job_class_name, :table_name, :column_name) } - end - - describe 'scopes' do - let_it_be(:worker_1) { create(:background_operation_worker, :active) } - let_it_be(:worker_2) { create(:background_operation_worker, :paused) } - let_it_be(:worker_3) { create(:background_operation_worker, :finished) } - - describe '.executable' do - it 'returns workers with active or paused status' do - expect(described_class.executable).to contain_exactly(worker_1, worker_2) - end - end - end - - describe 'sliding_list partitioning' do - let(:connection) { described_class.connection } - let(:partition_manager) { Gitlab::Database::Partitioning::PartitionManager.new(described_class) } - - describe 'next_partition_if callback' do - let(:active_partition) { described_class.partitioning_strategy.active_partition } - - subject(:value) { described_class.partitioning_strategy.next_partition_if.call(active_partition) } - - context 'when the partition is empty' do - it { is_expected.to be(false) } - end - - context 'when the partition has recent records' do - before do - create(:background_operation_worker, created_at: 1.day.ago) - end - - it { is_expected.to be(false) } - end - - context 'when the first record of the partition is older than PARTITION_DURATION' do - before do - create(:background_operation_worker, created_at: (described_class::PARTITION_DURATION + 1.day).ago) - create(:background_operation_worker, created_at: 1.day.ago) - end - - it { is_expected.to be(true) } - end - end - - describe 'detach_partition_if callback' do - let(:active_partition) { described_class.partitioning_strategy.active_partition } - - subject(:value) { described_class.partitioning_strategy.detach_partition_if.call(active_partition) } - - context 'when the partition contains executable workers' do - before do - create(:background_operation_worker, :active) - create(:background_operation_worker, :paused) - create(:background_operation_worker, :finished) - end - - it { is_expected.to be(false) } - end - - context 'when the partition contains only non-executable workers' do - before do - create(:background_operation_worker, :finished) - create(:background_operation_worker, :failed) - end - - it { is_expected.to be(true) } - end - - context 'when the partition is empty' do - it { is_expected.to be(true) } - end - end - - describe 'the behavior of the strategy' do - it 'moves records to new partitions as time passes', :freeze_time do - # We start with partition 1 - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) - - # it's not 14 days old yet so no new partitions are created - partition_manager.sync_partitions - - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) - - # add one record so the next partition will be created - create(:background_operation_worker) - - # after traveling forward past PARTITION_DURATION - travel(described_class::PARTITION_DURATION + 1.minute) - - # a new partition is created - partition_manager.sync_partitions - - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1, 2]) - - # and we can insert to the new partition - expect { create(:background_operation_worker) }.not_to raise_error - - # after marking old records as non-executable - described_class.for_partition(1).update_all(status: 2) - - partition_manager.sync_partitions - - # the old one is removed - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([2]) - - # and we only have the newly created partition left. - expect(described_class.count).to eq(1) - end - end - end + it_behaves_like 'background operation worker functionality', :background_operation_worker end -- GitLab From 36ba63a3fe5f2d4b1e498aab7bcfd1bac95ea408 Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Wed, 17 Sep 2025 17:36:54 +0200 Subject: [PATCH 10/17] Fixes structure.sql file --- ...background_operation_workers_cell_local.rb | 2 +- ...te_background_operation_jobs_cell_local.rb | 2 +- ...841_create_background_operation_workers.rb | 2 +- ...164941_create_background_operation_jobs.rb | 2 +- db/structure.sql | 116 +++++++++--------- .../job_shared_examples.rb | 8 +- .../worker_shared_examples.rb | 8 +- 7 files changed, 70 insertions(+), 70 deletions(-) diff --git a/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb b/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb index dbfcdf93c40b12..8698b87daac3fa 100644 --- a/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb +++ b/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb @@ -3,7 +3,7 @@ class CreateBackgroundOperationWorkersCellLocal < Gitlab::Database::Migration[2.3] include Gitlab::Database::PartitioningMigrationHelpers - milestone '18.4' + milestone '18.5' disable_ddl_transaction! diff --git a/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb b/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb index 6f95dee8e11e1c..0192c0f924b1bd 100644 --- a/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb +++ b/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb @@ -3,7 +3,7 @@ class CreateBackgroundOperationJobsCellLocal < Gitlab::Database::Migration[2.3] include Gitlab::Database::PartitioningMigrationHelpers - milestone '18.4' + milestone '18.5' disable_ddl_transaction! diff --git a/db/migrate/20250915164841_create_background_operation_workers.rb b/db/migrate/20250915164841_create_background_operation_workers.rb index 0918f2de08fdd9..b0fe3a29619e12 100644 --- a/db/migrate/20250915164841_create_background_operation_workers.rb +++ b/db/migrate/20250915164841_create_background_operation_workers.rb @@ -3,7 +3,7 @@ class CreateBackgroundOperationWorkers < Gitlab::Database::Migration[2.3] include Gitlab::Database::PartitioningMigrationHelpers - milestone '18.4' + milestone '18.5' disable_ddl_transaction! diff --git a/db/migrate/20250915164941_create_background_operation_jobs.rb b/db/migrate/20250915164941_create_background_operation_jobs.rb index c9a00cdb92e604..ff90da700c7a20 100644 --- a/db/migrate/20250915164941_create_background_operation_jobs.rb +++ b/db/migrate/20250915164941_create_background_operation_jobs.rb @@ -3,7 +3,7 @@ class CreateBackgroundOperationJobs < Gitlab::Database::Migration[2.3] include Gitlab::Database::PartitioningMigrationHelpers - milestone '18.4' + milestone '18.5' disable_ddl_transaction! diff --git a/db/structure.sql b/db/structure.sql index b73145295c4e84..cabf7b34dcf59c 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -4723,6 +4723,28 @@ CREATE TABLE background_operation_jobs ( ) PARTITION BY LIST (partition); +CREATE TABLE background_operation_jobs_cell_local ( + id bigint NOT NULL, + created_at timestamp with time zone NOT NULL, + started_at timestamp with time zone, + finished_at timestamp with time zone, + worker_id bigint NOT NULL, + batch_size integer NOT NULL, + sub_batch_size integer NOT NULL, + pause_ms integer DEFAULT 100 NOT NULL, + partition integer DEFAULT 1 NOT NULL, + worker_partition integer NOT NULL, + status smallint DEFAULT 0 NOT NULL, + attempts smallint DEFAULT 0 NOT NULL, + metrics jsonb DEFAULT '{}'::jsonb NOT NULL, + min_cursor jsonb, + max_cursor jsonb, + CONSTRAINT check_00bb39bb33 CHECK ((pause_ms >= 100)), + CONSTRAINT check_5b84acc749 CHECK ((num_nonnulls(min_cursor, max_cursor) = 2)), + CONSTRAINT check_ebc3302442 CHECK (((jsonb_typeof(min_cursor) = 'array'::text) AND (jsonb_typeof(max_cursor) = 'array'::text))) +) +PARTITION BY LIST (partition); + CREATE TABLE background_operation_workers ( id uuid DEFAULT gen_random_uuid() NOT NULL, organization_id bigint NOT NULL, @@ -4761,6 +4783,42 @@ CREATE TABLE background_operation_workers ( ) PARTITION BY LIST (partition); +CREATE TABLE background_operation_workers_cell_local ( + id bigint NOT NULL, + total_tuple_count bigint, + started_at timestamp with time zone, + on_hold_until timestamp with time zone, + created_at timestamp with time zone NOT NULL, + finished_at timestamp with time zone, + batch_size integer NOT NULL, + sub_batch_size integer NOT NULL, + pause_ms integer DEFAULT 100 NOT NULL, + max_batch_size integer, + partition integer DEFAULT 1 NOT NULL, + priority smallint DEFAULT 0 NOT NULL, + status smallint DEFAULT 0 NOT NULL, + "interval" smallint NOT NULL, + job_class_name text NOT NULL, + batch_class_name text DEFAULT 'PrimaryKeyBatchingStrategy'::text NOT NULL, + table_name text NOT NULL, + column_name text NOT NULL, + gitlab_schema text NOT NULL, + job_arguments jsonb DEFAULT '"[]"'::jsonb, + min_cursor jsonb, + max_cursor jsonb, + next_min_cursor jsonb, + CONSTRAINT check_1da63db6a8 CHECK ((char_length(table_name) <= 63)), + CONSTRAINT check_4cc5ecb4f2 CHECK ((char_length(column_name) <= 63)), + CONSTRAINT check_5f184cd88f CHECK ((char_length(gitlab_schema) <= 255)), + CONSTRAINT check_9d0c37a905 CHECK ((char_length(batch_class_name) <= 100)), + CONSTRAINT check_be878382ae CHECK ((batch_size >= sub_batch_size)), + CONSTRAINT check_d94474cbf2 CHECK ((char_length(job_class_name) <= 100)), + CONSTRAINT check_e40b641a88 CHECK ((num_nonnulls(min_cursor, max_cursor) = 2)), + CONSTRAINT check_f9383a3f2e CHECK ((sub_batch_size > 0)), + CONSTRAINT check_f9caba0499 CHECK (((jsonb_typeof(min_cursor) = 'array'::text) AND (jsonb_typeof(max_cursor) = 'array'::text))) +) +PARTITION BY LIST (partition); + CREATE TABLE backup_finding_evidences ( created_at timestamp with time zone NOT NULL, updated_at timestamp with time zone NOT NULL, @@ -12388,28 +12446,6 @@ CREATE TABLE aws_roles ( CONSTRAINT check_57adedab55 CHECK ((char_length(region) <= 255)) ); -CREATE TABLE background_operation_jobs_cell_local ( - id bigint NOT NULL, - created_at timestamp with time zone NOT NULL, - started_at timestamp with time zone, - finished_at timestamp with time zone, - worker_id bigint NOT NULL, - batch_size integer NOT NULL, - sub_batch_size integer NOT NULL, - pause_ms integer DEFAULT 100 NOT NULL, - partition integer DEFAULT 1 NOT NULL, - worker_partition integer NOT NULL, - status smallint DEFAULT 0 NOT NULL, - attempts smallint DEFAULT 0 NOT NULL, - metrics jsonb DEFAULT '{}'::jsonb NOT NULL, - min_cursor jsonb, - max_cursor jsonb, - CONSTRAINT check_00bb39bb33 CHECK ((pause_ms >= 100)), - CONSTRAINT check_5b84acc749 CHECK ((num_nonnulls(min_cursor, max_cursor) = 2)), - CONSTRAINT check_ebc3302442 CHECK (((jsonb_typeof(min_cursor) = 'array'::text) AND (jsonb_typeof(max_cursor) = 'array'::text))) -) -PARTITION BY LIST (partition); - CREATE SEQUENCE background_operation_jobs_cell_local_id_seq START WITH 1 INCREMENT BY 1 @@ -12419,42 +12455,6 @@ CREATE SEQUENCE background_operation_jobs_cell_local_id_seq ALTER SEQUENCE background_operation_jobs_cell_local_id_seq OWNED BY background_operation_jobs_cell_local.id; -CREATE TABLE background_operation_workers_cell_local ( - id bigint NOT NULL, - total_tuple_count bigint, - started_at timestamp with time zone, - on_hold_until timestamp with time zone, - created_at timestamp with time zone NOT NULL, - finished_at timestamp with time zone, - batch_size integer NOT NULL, - sub_batch_size integer NOT NULL, - pause_ms integer DEFAULT 100 NOT NULL, - max_batch_size integer, - partition integer DEFAULT 1 NOT NULL, - priority smallint DEFAULT 0 NOT NULL, - status smallint DEFAULT 0 NOT NULL, - "interval" smallint NOT NULL, - job_class_name text NOT NULL, - batch_class_name text DEFAULT 'PrimaryKeyBatchingStrategy'::text NOT NULL, - table_name text NOT NULL, - column_name text NOT NULL, - gitlab_schema text NOT NULL, - job_arguments jsonb DEFAULT '"[]"'::jsonb, - min_cursor jsonb, - max_cursor jsonb, - next_min_cursor jsonb, - CONSTRAINT check_1da63db6a8 CHECK ((char_length(table_name) <= 63)), - CONSTRAINT check_4cc5ecb4f2 CHECK ((char_length(column_name) <= 63)), - CONSTRAINT check_5f184cd88f CHECK ((char_length(gitlab_schema) <= 255)), - CONSTRAINT check_9d0c37a905 CHECK ((char_length(batch_class_name) <= 100)), - CONSTRAINT check_be878382ae CHECK ((batch_size >= sub_batch_size)), - CONSTRAINT check_d94474cbf2 CHECK ((char_length(job_class_name) <= 100)), - CONSTRAINT check_e40b641a88 CHECK ((num_nonnulls(min_cursor, max_cursor) = 2)), - CONSTRAINT check_f9383a3f2e CHECK ((sub_batch_size > 0)), - CONSTRAINT check_f9caba0499 CHECK (((jsonb_typeof(min_cursor) = 'array'::text) AND (jsonb_typeof(max_cursor) = 'array'::text))) -) -PARTITION BY LIST (partition); - CREATE SEQUENCE background_operation_workers_cell_local_id_seq START WITH 1 INCREMENT BY 1 diff --git a/spec/lib/gitlab/database/background_operation/job_shared_examples.rb b/spec/lib/gitlab/database/background_operation/job_shared_examples.rb index 5de9cbf500daf2..1f13ae02179f62 100644 --- a/spec/lib/gitlab/database/background_operation/job_shared_examples.rb +++ b/spec/lib/gitlab/database/background_operation/job_shared_examples.rb @@ -95,12 +95,12 @@ describe 'the behavior of the strategy' do it 'moves records to new partitions as time passes', :freeze_time do # We start with partition 1 - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to match_array([1]) # it's not a day old yet so no new partitions are created partition_manager.sync_partitions - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to match_array([1]) # add one record so the next partition will be created create(job_factory) # rubocop:disable Rails/SaveBang -- factory @@ -111,7 +111,7 @@ # a new partition is created partition_manager.sync_partitions - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1, 2]) + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to match_array([1, 2]) # and we can insert to the new partition expect { create(job_factory) }.not_to raise_error # rubocop:disable Rails/SaveBang -- factory @@ -122,7 +122,7 @@ partition_manager.sync_partitions # the old one is removed - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([2]) + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to match_array([2]) # and we only have the newly created partition left. expect(described_class.count).to eq(1) diff --git a/spec/lib/gitlab/database/background_operation/worker_shared_examples.rb b/spec/lib/gitlab/database/background_operation/worker_shared_examples.rb index 97c001d4289100..bf83860220550b 100644 --- a/spec/lib/gitlab/database/background_operation/worker_shared_examples.rb +++ b/spec/lib/gitlab/database/background_operation/worker_shared_examples.rb @@ -95,12 +95,12 @@ describe 'the behavior of the strategy' do it 'moves records to new partitions as time passes', :freeze_time do # We start with partition 1 - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to match_array([1]) # it's not 14 days old yet so no new partitions are created partition_manager.sync_partitions - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1]) + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to match_array([1]) # add one record so the next partition will be created create(worker_factory) # rubocop:disable Rails/SaveBang -- factory @@ -111,7 +111,7 @@ # a new partition is created partition_manager.sync_partitions - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([1, 2]) + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to match_array([1, 2]) # and we can insert to the new partition expect { create(worker_factory) }.not_to raise_error # rubocop:disable Rails/SaveBang -- factory @@ -122,7 +122,7 @@ partition_manager.sync_partitions # the old one is removed - expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to eq([2]) + expect(described_class.partitioning_strategy.current_partitions.map(&:value)).to match_array([2]) # and we only have the newly created partition left. expect(described_class.count).to eq(1) -- GitLab From 55d4b03284dd526b970a0e8b42396f61f36fc3c4 Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Wed, 17 Sep 2025 21:08:08 +0200 Subject: [PATCH 11/17] Includes organization_id in the uniq index --- .../20250915164841_create_background_operation_workers.rb | 2 +- db/structure.sql | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/db/migrate/20250915164841_create_background_operation_workers.rb b/db/migrate/20250915164841_create_background_operation_workers.rb index b0fe3a29619e12..39a38ace426d56 100644 --- a/db/migrate/20250915164841_create_background_operation_workers.rb +++ b/db/migrate/20250915164841_create_background_operation_workers.rb @@ -100,7 +100,7 @@ def add_indexes ) add_concurrent_partitioned_index( :background_operation_workers, - [:partition, :job_class_name, :table_name, :column_name, :job_arguments], + [:partition, :organization_id, :job_class_name, :table_name, :column_name, :job_arguments], unique: true, name: 'index_background_operation_workers_on_unique_configuration' ) diff --git a/db/structure.sql b/db/structure.sql index cabf7b34dcf59c..8d9e8f71eb60b6 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -38487,7 +38487,7 @@ CREATE INDEX index_background_operation_workers_by_status ON ONLY background_ope CREATE INDEX index_background_operation_workers_by_user ON ONLY background_operation_workers USING btree (user_id); -CREATE UNIQUE INDEX index_background_operation_workers_on_unique_configuration ON ONLY background_operation_workers USING btree (partition, job_class_name, table_name, column_name, job_arguments); +CREATE UNIQUE INDEX index_background_operation_workers_on_unique_configuration ON ONLY background_operation_workers USING btree (partition, organization_id, job_class_name, table_name, column_name, job_arguments); CREATE INDEX index_backup_finding_evidences_on_fk ON ONLY backup_finding_evidences USING btree (finding_id); -- GitLab From e5a7b6b276acd78dc1a335616a559276bff969fe Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Wed, 17 Sep 2025 22:38:44 +0200 Subject: [PATCH 12/17] Makes organization_id and user_id as LFKs --- config/gitlab_loose_foreign_keys.yml | 14 +++++++++++++ ...841_create_background_operation_workers.rb | 20 ------------------- ...164941_create_background_operation_jobs.rb | 20 ------------------- db/structure.sql | 12 ----------- 4 files changed, 14 insertions(+), 52 deletions(-) diff --git a/config/gitlab_loose_foreign_keys.yml b/config/gitlab_loose_foreign_keys.yml index 4cc010963c6c90..10f07141da03ea 100644 --- a/config/gitlab_loose_foreign_keys.yml +++ b/config/gitlab_loose_foreign_keys.yml @@ -1036,3 +1036,17 @@ vulnerability_user_mentions: - table: notes column: note_id on_delete: async_delete +background_operation_workers: + - table: organizations + column: organization_id + on_delete: async_delete + - table: users + column: user_id + on_delete: async_delete +background_operation_jobs: + - table: organizations + column: organization_id + on_delete: async_delete + - table: users + column: user_id + on_delete: async_delete diff --git a/db/migrate/20250915164841_create_background_operation_workers.rb b/db/migrate/20250915164841_create_background_operation_workers.rb index 39a38ace426d56..57042a088b2854 100644 --- a/db/migrate/20250915164841_create_background_operation_workers.rb +++ b/db/migrate/20250915164841_create_background_operation_workers.rb @@ -42,26 +42,6 @@ def up t.jsonb :next_min_cursor end - add_concurrent_partitioned_foreign_key( - :background_operation_workers, - :organizations, - column: :organization_id, - target_column: :id, - reverse_lock_order: true, - on_delete: :cascade, - validate: true - ) - - add_concurrent_partitioned_foreign_key( - :background_operation_workers, - :users, - column: :user_id, - target_column: :id, - reverse_lock_order: true, - on_delete: :cascade, - validate: true - ) - add_indexes add_constraints end diff --git a/db/migrate/20250915164941_create_background_operation_jobs.rb b/db/migrate/20250915164941_create_background_operation_jobs.rb index ff90da700c7a20..bc5e8e3979213e 100644 --- a/db/migrate/20250915164941_create_background_operation_jobs.rb +++ b/db/migrate/20250915164941_create_background_operation_jobs.rb @@ -34,26 +34,6 @@ def up t.jsonb :max_cursor end - add_concurrent_partitioned_foreign_key( - :background_operation_jobs, - :organizations, - column: :organization_id, - target_column: :id, - reverse_lock_order: true, - on_delete: :cascade, - validate: true - ) - - add_concurrent_partitioned_foreign_key( - :background_operation_jobs, - :users, - column: :user_id, - target_column: :id, - reverse_lock_order: true, - on_delete: :cascade, - validate: true - ) - add_indexes add_constraints end diff --git a/db/structure.sql b/db/structure.sql index 8d9e8f71eb60b6..214b7361a1a985 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -49855,9 +49855,6 @@ ALTER TABLE ONLY compliance_requirements_controls ALTER TABLE ONLY user_credit_card_validations ADD CONSTRAINT fk_rails_27ebc03cbf FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; -ALTER TABLE background_operation_workers - ADD CONSTRAINT fk_rails_281a813c59 FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE; - ALTER TABLE ONLY dast_site_validations ADD CONSTRAINT fk_rails_285c617324 FOREIGN KEY (dast_site_token_id) REFERENCES dast_site_tokens(id) ON DELETE CASCADE; @@ -50056,9 +50053,6 @@ ALTER TABLE ONLY project_type_ci_runner_machines ALTER TABLE ONLY description_versions ADD CONSTRAINT fk_rails_3ff658220b FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE; -ALTER TABLE background_operation_jobs - ADD CONSTRAINT fk_rails_40383bd920 FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE; - ALTER TABLE ONLY clusters_kubernetes_namespaces ADD CONSTRAINT fk_rails_40cc7ccbc3 FOREIGN KEY (cluster_project_id) REFERENCES cluster_projects(id) ON DELETE SET NULL; @@ -51217,9 +51211,6 @@ ALTER TABLE ONLY ai_user_metrics ALTER TABLE ONLY boards_epic_board_positions ADD CONSTRAINT fk_rails_cb4563dd6e FOREIGN KEY (epic_board_id) REFERENCES boards_epic_boards(id) ON DELETE CASCADE; -ALTER TABLE background_operation_jobs - ADD CONSTRAINT fk_rails_cb794cb79f FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; - ALTER TABLE ONLY vulnerability_finding_links ADD CONSTRAINT fk_rails_cbdfde27ce FOREIGN KEY (vulnerability_occurrence_id) REFERENCES vulnerability_occurrences(id) ON DELETE CASCADE; @@ -51235,9 +51226,6 @@ ALTER TABLE ONLY observability_group_o11y_settings ALTER TABLE ONLY members_deletion_schedules ADD CONSTRAINT fk_rails_ce06d97eb2 FOREIGN KEY (namespace_id) REFERENCES namespaces(id) ON DELETE CASCADE; -ALTER TABLE background_operation_workers - ADD CONSTRAINT fk_rails_ce5418a44f FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; - ALTER TABLE ONLY resource_milestone_events ADD CONSTRAINT fk_rails_cedf8cce4d FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL; -- GitLab From ed867c2aa5572a4d2693d926b434c08d77d0d6ee Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Thu, 18 Sep 2025 20:43:18 +0200 Subject: [PATCH 13/17] Makes partition_id as bigint and fixes LFK ordering --- config/gitlab_loose_foreign_keys.yml | 28 +++++++++---------- ...background_operation_workers_cell_local.rb | 2 +- ...te_background_operation_jobs_cell_local.rb | 2 +- ...841_create_background_operation_workers.rb | 2 +- ...164941_create_background_operation_jobs.rb | 2 +- db/structure.sql | 8 +++--- 6 files changed, 22 insertions(+), 22 deletions(-) diff --git a/config/gitlab_loose_foreign_keys.yml b/config/gitlab_loose_foreign_keys.yml index 10f07141da03ea..5e6f0e06a83416 100644 --- a/config/gitlab_loose_foreign_keys.yml +++ b/config/gitlab_loose_foreign_keys.yml @@ -42,6 +42,20 @@ approval_merge_request_rules: - table: approval_policy_rules column: approval_policy_rule_id on_delete: async_nullify +background_operation_jobs: + - table: organizations + column: organization_id + on_delete: async_delete + - table: users + column: user_id + on_delete: async_delete +background_operation_workers: + - table: organizations + column: organization_id + on_delete: async_delete + - table: users + column: user_id + on_delete: async_delete backup_finding_evidences: - table: projects column: project_id @@ -1036,17 +1050,3 @@ vulnerability_user_mentions: - table: notes column: note_id on_delete: async_delete -background_operation_workers: - - table: organizations - column: organization_id - on_delete: async_delete - - table: users - column: user_id - on_delete: async_delete -background_operation_jobs: - - table: organizations - column: organization_id - on_delete: async_delete - - table: users - column: user_id - on_delete: async_delete diff --git a/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb b/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb index 8698b87daac3fa..9dad22e3ef4561 100644 --- a/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb +++ b/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb @@ -17,6 +17,7 @@ def up create_table(:background_operation_workers_cell_local, **opts) do |t| t.bigserial :id t.bigint :total_tuple_count + t.bigint :partition, null: false, default: 1 t.timestamptz :started_at t.timestamptz :on_hold_until t.timestamptz :created_at, null: false @@ -25,7 +26,6 @@ def up t.integer :sub_batch_size, null: false t.integer :pause_ms, null: false, default: 100 t.integer :max_batch_size - t.integer :partition, null: false, default: 1 t.integer :priority, null: false, limit: 2, default: 0 t.integer :status, null: false, limit: 2, default: 0 t.integer :interval, null: false, limit: 2 diff --git a/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb b/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb index 0192c0f924b1bd..c8ab6b2f120d59 100644 --- a/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb +++ b/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb @@ -16,6 +16,7 @@ def up create_table(:background_operation_jobs_cell_local, **opts) do |t| t.bigserial :id + t.bigint :partition, null: false, default: 1 t.timestamptz :created_at, null: false t.timestamptz :started_at t.timestamptz :finished_at @@ -23,7 +24,6 @@ def up t.integer :batch_size, null: false t.integer :sub_batch_size, null: false t.integer :pause_ms, null: false, default: 100 - t.integer :partition, null: false, default: 1 t.integer :worker_partition, null: false t.integer :status, null: false, default: 0, limit: 2 t.integer :attempts, null: false, default: 0, limit: 2 diff --git a/db/migrate/20250915164841_create_background_operation_workers.rb b/db/migrate/20250915164841_create_background_operation_workers.rb index 57042a088b2854..8e42e828f752ac 100644 --- a/db/migrate/20250915164841_create_background_operation_workers.rb +++ b/db/migrate/20250915164841_create_background_operation_workers.rb @@ -19,6 +19,7 @@ def up t.bigint :organization_id, null: false t.bigint :user_id, null: false t.bigint :total_tuple_count + t.bigint :partition, null: false, default: 1 t.timestamptz :started_at t.timestamptz :on_hold_until t.timestamptz :created_at, null: false @@ -27,7 +28,6 @@ def up t.integer :sub_batch_size, null: false t.integer :pause_ms, null: false, default: 100 t.integer :max_batch_size - t.integer :partition, null: false, default: 1 t.integer :priority, null: false, limit: 2, default: 0 t.integer :status, null: false, limit: 2, default: 0 t.integer :interval, null: false, limit: 2 diff --git a/db/migrate/20250915164941_create_background_operation_jobs.rb b/db/migrate/20250915164941_create_background_operation_jobs.rb index bc5e8e3979213e..67da15f4530b5d 100644 --- a/db/migrate/20250915164941_create_background_operation_jobs.rb +++ b/db/migrate/20250915164941_create_background_operation_jobs.rb @@ -18,6 +18,7 @@ def up t.uuid :id, default: -> { "gen_random_uuid()" }, null: false t.bigint :organization_id, null: false t.bigint :user_id, null: false + t.bigint :partition, null: false, default: 1 t.timestamptz :created_at, null: false t.timestamptz :started_at t.timestamptz :finished_at @@ -25,7 +26,6 @@ def up t.integer :batch_size, null: false t.integer :sub_batch_size, null: false t.integer :pause_ms, null: false, default: 100 - t.integer :partition, null: false, default: 1 t.integer :worker_partition, null: false t.integer :status, null: false, default: 0, limit: 2 t.integer :attempts, null: false, default: 0, limit: 2 diff --git a/db/structure.sql b/db/structure.sql index 214b7361a1a985..dae3a4ac060915 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -4703,6 +4703,7 @@ CREATE TABLE background_operation_jobs ( id uuid DEFAULT gen_random_uuid() NOT NULL, organization_id bigint NOT NULL, user_id bigint NOT NULL, + partition bigint DEFAULT 1 NOT NULL, created_at timestamp with time zone NOT NULL, started_at timestamp with time zone, finished_at timestamp with time zone, @@ -4710,7 +4711,6 @@ CREATE TABLE background_operation_jobs ( batch_size integer NOT NULL, sub_batch_size integer NOT NULL, pause_ms integer DEFAULT 100 NOT NULL, - partition integer DEFAULT 1 NOT NULL, worker_partition integer NOT NULL, status smallint DEFAULT 0 NOT NULL, attempts smallint DEFAULT 0 NOT NULL, @@ -4725,6 +4725,7 @@ PARTITION BY LIST (partition); CREATE TABLE background_operation_jobs_cell_local ( id bigint NOT NULL, + partition bigint DEFAULT 1 NOT NULL, created_at timestamp with time zone NOT NULL, started_at timestamp with time zone, finished_at timestamp with time zone, @@ -4732,7 +4733,6 @@ CREATE TABLE background_operation_jobs_cell_local ( batch_size integer NOT NULL, sub_batch_size integer NOT NULL, pause_ms integer DEFAULT 100 NOT NULL, - partition integer DEFAULT 1 NOT NULL, worker_partition integer NOT NULL, status smallint DEFAULT 0 NOT NULL, attempts smallint DEFAULT 0 NOT NULL, @@ -4750,6 +4750,7 @@ CREATE TABLE background_operation_workers ( organization_id bigint NOT NULL, user_id bigint NOT NULL, total_tuple_count bigint, + partition bigint DEFAULT 1 NOT NULL, started_at timestamp with time zone, on_hold_until timestamp with time zone, created_at timestamp with time zone NOT NULL, @@ -4758,7 +4759,6 @@ CREATE TABLE background_operation_workers ( sub_batch_size integer NOT NULL, pause_ms integer DEFAULT 100 NOT NULL, max_batch_size integer, - partition integer DEFAULT 1 NOT NULL, priority smallint DEFAULT 0 NOT NULL, status smallint DEFAULT 0 NOT NULL, "interval" smallint NOT NULL, @@ -4786,6 +4786,7 @@ PARTITION BY LIST (partition); CREATE TABLE background_operation_workers_cell_local ( id bigint NOT NULL, total_tuple_count bigint, + partition bigint DEFAULT 1 NOT NULL, started_at timestamp with time zone, on_hold_until timestamp with time zone, created_at timestamp with time zone NOT NULL, @@ -4794,7 +4795,6 @@ CREATE TABLE background_operation_workers_cell_local ( sub_batch_size integer NOT NULL, pause_ms integer DEFAULT 100 NOT NULL, max_batch_size integer, - partition integer DEFAULT 1 NOT NULL, priority smallint DEFAULT 0 NOT NULL, status smallint DEFAULT 0 NOT NULL, "interval" smallint NOT NULL, -- GitLab From 401ece5546406acbb95598498a24afc3b8373efe Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Tue, 23 Sep 2025 20:11:40 +0200 Subject: [PATCH 14/17] Removes default value for batch_class_name And has some refactoring --- ...background_operation_workers_cell_local.rb | 2 +- ...te_background_operation_jobs_cell_local.rb | 4 ++-- ...841_create_background_operation_workers.rb | 2 +- ...164941_create_background_operation_jobs.rb | 10 ++-------- db/structure.sql | 19 ++++++++----------- .../database/background_operation/job.rb | 1 - .../database/background_operation/jobs.rb | 2 -- 7 files changed, 14 insertions(+), 26 deletions(-) diff --git a/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb b/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb index 9dad22e3ef4561..ca0381ab5ddeb2 100644 --- a/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb +++ b/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb @@ -30,7 +30,7 @@ def up t.integer :status, null: false, limit: 2, default: 0 t.integer :interval, null: false, limit: 2 t.text :job_class_name, null: false, limit: 100 - t.text :batch_class_name, null: false, default: 'PrimaryKeyBatchingStrategy', limit: 100 + t.text :batch_class_name, null: false, limit: 100 t.text :table_name, null: false, limit: 63 t.text :column_name, null: false, limit: 63 t.text :gitlab_schema, null: false, limit: 255 diff --git a/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb b/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb index c8ab6b2f120d59..f339dedc353cf8 100644 --- a/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb +++ b/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb @@ -17,14 +17,14 @@ def up create_table(:background_operation_jobs_cell_local, **opts) do |t| t.bigserial :id t.bigint :partition, null: false, default: 1 + t.bigint :worker_id, null: false + t.bigint :worker_partition, null: false t.timestamptz :created_at, null: false t.timestamptz :started_at t.timestamptz :finished_at - t.bigint :worker_id, null: false t.integer :batch_size, null: false t.integer :sub_batch_size, null: false t.integer :pause_ms, null: false, default: 100 - t.integer :worker_partition, null: false t.integer :status, null: false, default: 0, limit: 2 t.integer :attempts, null: false, default: 0, limit: 2 t.jsonb :metrics, null: false, default: {} diff --git a/db/migrate/20250915164841_create_background_operation_workers.rb b/db/migrate/20250915164841_create_background_operation_workers.rb index 8e42e828f752ac..ce8d931bde7341 100644 --- a/db/migrate/20250915164841_create_background_operation_workers.rb +++ b/db/migrate/20250915164841_create_background_operation_workers.rb @@ -32,7 +32,7 @@ def up t.integer :status, null: false, limit: 2, default: 0 t.integer :interval, null: false, limit: 2 t.text :job_class_name, null: false, limit: 100 - t.text :batch_class_name, null: false, default: 'PrimaryKeyBatchingStrategy', limit: 100 + t.text :batch_class_name, null: false, limit: 100 t.text :table_name, null: false, limit: 63 t.text :column_name, null: false, limit: 63 t.text :gitlab_schema, null: false, limit: 255 diff --git a/db/migrate/20250915164941_create_background_operation_jobs.rb b/db/migrate/20250915164941_create_background_operation_jobs.rb index 67da15f4530b5d..0d5def3e8bba71 100644 --- a/db/migrate/20250915164941_create_background_operation_jobs.rb +++ b/db/migrate/20250915164941_create_background_operation_jobs.rb @@ -16,17 +16,16 @@ def up create_table(:background_operation_jobs, **opts) do |t| t.uuid :id, default: -> { "gen_random_uuid()" }, null: false + t.uuid :worker_id, null: false t.bigint :organization_id, null: false - t.bigint :user_id, null: false t.bigint :partition, null: false, default: 1 + t.bigint :worker_partition, null: false t.timestamptz :created_at, null: false t.timestamptz :started_at t.timestamptz :finished_at - t.bigint :worker_id, null: false t.integer :batch_size, null: false t.integer :sub_batch_size, null: false t.integer :pause_ms, null: false, default: 100 - t.integer :worker_partition, null: false t.integer :status, null: false, default: 0, limit: 2 t.integer :attempts, null: false, default: 0, limit: 2 t.jsonb :metrics, null: false, default: {} @@ -55,11 +54,6 @@ def add_indexes :organization_id, name: 'index_background_operation_jobs_by_organization' ) - add_concurrent_partitioned_index( - :background_operation_jobs, - :user_id, - name: 'index_background_operation_jobs_by_user' - ) add_concurrent_partitioned_index( :background_operation_jobs, :created_at, diff --git a/db/structure.sql b/db/structure.sql index dae3a4ac060915..77647659cc68f3 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -4701,17 +4701,16 @@ PARTITION BY RANGE (created_at); CREATE TABLE background_operation_jobs ( id uuid DEFAULT gen_random_uuid() NOT NULL, + worker_id uuid NOT NULL, organization_id bigint NOT NULL, - user_id bigint NOT NULL, partition bigint DEFAULT 1 NOT NULL, + worker_partition bigint NOT NULL, created_at timestamp with time zone NOT NULL, started_at timestamp with time zone, finished_at timestamp with time zone, - worker_id bigint NOT NULL, batch_size integer NOT NULL, sub_batch_size integer NOT NULL, pause_ms integer DEFAULT 100 NOT NULL, - worker_partition integer NOT NULL, status smallint DEFAULT 0 NOT NULL, attempts smallint DEFAULT 0 NOT NULL, metrics jsonb DEFAULT '{}'::jsonb NOT NULL, @@ -4726,14 +4725,14 @@ PARTITION BY LIST (partition); CREATE TABLE background_operation_jobs_cell_local ( id bigint NOT NULL, partition bigint DEFAULT 1 NOT NULL, + worker_id bigint NOT NULL, + worker_partition bigint NOT NULL, created_at timestamp with time zone NOT NULL, started_at timestamp with time zone, finished_at timestamp with time zone, - worker_id bigint NOT NULL, batch_size integer NOT NULL, sub_batch_size integer NOT NULL, pause_ms integer DEFAULT 100 NOT NULL, - worker_partition integer NOT NULL, status smallint DEFAULT 0 NOT NULL, attempts smallint DEFAULT 0 NOT NULL, metrics jsonb DEFAULT '{}'::jsonb NOT NULL, @@ -4763,7 +4762,7 @@ CREATE TABLE background_operation_workers ( status smallint DEFAULT 0 NOT NULL, "interval" smallint NOT NULL, job_class_name text NOT NULL, - batch_class_name text DEFAULT 'PrimaryKeyBatchingStrategy'::text NOT NULL, + batch_class_name text NOT NULL, table_name text NOT NULL, column_name text NOT NULL, gitlab_schema text NOT NULL, @@ -4799,7 +4798,7 @@ CREATE TABLE background_operation_workers_cell_local ( status smallint DEFAULT 0 NOT NULL, "interval" smallint NOT NULL, job_class_name text NOT NULL, - batch_class_name text DEFAULT 'PrimaryKeyBatchingStrategy'::text NOT NULL, + batch_class_name text NOT NULL, table_name text NOT NULL, column_name text NOT NULL, gitlab_schema text NOT NULL, @@ -38463,10 +38462,10 @@ CREATE UNIQUE INDEX index_aws_roles_on_user_id ON aws_roles USING btree (user_id CREATE INDEX index_background_jobs_by_status ON ONLY background_operation_jobs USING btree (status); -CREATE INDEX index_background_jobs_by_worker_id_and_id ON ONLY background_operation_jobs USING btree (worker_id, id); - CREATE INDEX index_background_jobs_by_worker_id_and_status ON ONLY background_operation_jobs USING btree (worker_id, status); +CREATE INDEX index_background_jobs_by_worker_id_and_id ON ONLY background_operation_jobs USING btree (worker_id, id); + CREATE INDEX index_background_jobs_on_worker_id_and_cursor_max_value ON ONLY background_operation_jobs USING btree (worker_id, max_cursor) WHERE (max_cursor IS NOT NULL); CREATE INDEX index_background_jobs_on_worker_id_and_finished_at ON ONLY background_operation_jobs USING btree (worker_id, finished_at); @@ -38475,8 +38474,6 @@ CREATE INDEX index_background_operation_jobs_by_created_at ON ONLY background_op CREATE INDEX index_background_operation_jobs_by_organization ON ONLY background_operation_jobs USING btree (organization_id); -CREATE INDEX index_background_operation_jobs_by_user ON ONLY background_operation_jobs USING btree (user_id); - CREATE INDEX index_background_operation_workers_by_created_at ON ONLY background_operation_workers USING btree (created_at); CREATE INDEX index_background_operation_workers_by_organization ON ONLY background_operation_workers USING btree (organization_id); diff --git a/lib/gitlab/database/background_operation/job.rb b/lib/gitlab/database/background_operation/job.rb index e69fc3a489cf09..eb0ca574e7fb1b 100644 --- a/lib/gitlab/database/background_operation/job.rb +++ b/lib/gitlab/database/background_operation/job.rb @@ -16,7 +16,6 @@ class Job < SharedModel inverse_of: :jobs belongs_to :organization - belongs_to :user end end end diff --git a/spec/factories/gitlab/database/background_operation/jobs.rb b/spec/factories/gitlab/database/background_operation/jobs.rb index 0482b5a3abeffc..1b84be1758436d 100644 --- a/spec/factories/gitlab/database/background_operation/jobs.rb +++ b/spec/factories/gitlab/database/background_operation/jobs.rb @@ -9,9 +9,7 @@ sub_batch_size { 10 } min_cursor { [1] } max_cursor { [1000] } - worker_id { 1 } worker_partition { 1 } - user trait :pending do status { 0 } -- GitLab From c3926eb455ed50551f22d90c846c5c8a0d3344a0 Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Wed, 24 Sep 2025 16:13:20 +0200 Subject: [PATCH 15/17] Fixes schema file --- config/gitlab_loose_foreign_keys.yml | 3 --- db/structure.sql | 4 ++-- 2 files changed, 2 insertions(+), 5 deletions(-) diff --git a/config/gitlab_loose_foreign_keys.yml b/config/gitlab_loose_foreign_keys.yml index 5e6f0e06a83416..c34d297ca40107 100644 --- a/config/gitlab_loose_foreign_keys.yml +++ b/config/gitlab_loose_foreign_keys.yml @@ -46,9 +46,6 @@ background_operation_jobs: - table: organizations column: organization_id on_delete: async_delete - - table: users - column: user_id - on_delete: async_delete background_operation_workers: - table: organizations column: organization_id diff --git a/db/structure.sql b/db/structure.sql index 77647659cc68f3..1ecb4acc6d37bd 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -38462,10 +38462,10 @@ CREATE UNIQUE INDEX index_aws_roles_on_user_id ON aws_roles USING btree (user_id CREATE INDEX index_background_jobs_by_status ON ONLY background_operation_jobs USING btree (status); -CREATE INDEX index_background_jobs_by_worker_id_and_status ON ONLY background_operation_jobs USING btree (worker_id, status); - CREATE INDEX index_background_jobs_by_worker_id_and_id ON ONLY background_operation_jobs USING btree (worker_id, id); +CREATE INDEX index_background_jobs_by_worker_id_and_status ON ONLY background_operation_jobs USING btree (worker_id, status); + CREATE INDEX index_background_jobs_on_worker_id_and_cursor_max_value ON ONLY background_operation_jobs USING btree (worker_id, max_cursor) WHERE (max_cursor IS NOT NULL); CREATE INDEX index_background_jobs_on_worker_id_and_finished_at ON ONLY background_operation_jobs USING btree (worker_id, finished_at); -- GitLab From 11ca6838ee3cc3005a70e719c8e3355cabaae4fa Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Fri, 26 Sep 2025 13:24:18 +0200 Subject: [PATCH 16/17] Removes additional indexes and adds queued state --- ...background_operation_workers_cell_local.rb | 5 ----- ...te_background_operation_jobs_cell_local.rb | 20 ------------------- ...841_create_background_operation_workers.rb | 5 ----- ...164941_create_background_operation_jobs.rb | 20 ------------------- db/structure.sql | 20 ------------------- .../background_operation/common_worker.rb | 9 +++++---- .../database/background_operation/job.rb | 2 -- .../database/background_operation/worker.rb | 2 -- .../database/background_operation/workers.rb | 10 +++++++--- .../workers_cell_local.rb | 10 +++++++--- .../job_cell_local_spec.rb | 4 +++- .../job_shared_examples.rb | 10 +++++++++- .../database/background_operation/job_spec.rb | 2 +- .../worker_shared_examples.rb | 15 ++++++++------ 14 files changed, 41 insertions(+), 93 deletions(-) diff --git a/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb b/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb index ca0381ab5ddeb2..d565b2de17be30 100644 --- a/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb +++ b/db/migrate/20250915164710_create_background_operation_workers_cell_local.rb @@ -56,11 +56,6 @@ def add_indexes :status, name: 'index_bow_cell_local_by_status' ) - add_concurrent_partitioned_index( - :background_operation_workers_cell_local, - :priority, - name: 'index_bow_cell_local_by_priority' - ) add_concurrent_partitioned_index( :background_operation_workers_cell_local, [:partition, :job_class_name, :table_name, :column_name, :job_arguments], diff --git a/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb b/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb index f339dedc353cf8..9f75a3a2729653 100644 --- a/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb +++ b/db/migrate/20250915164741_create_background_operation_jobs_cell_local.rb @@ -48,26 +48,6 @@ def add_indexes :status, name: 'index_bj_cell_local_by_status' ) - add_concurrent_partitioned_index( - :background_operation_jobs_cell_local, - [:worker_id, :id], - name: 'index_bj_cell_local_by_worker_id_and_id' - ) - add_concurrent_partitioned_index( - :background_operation_jobs_cell_local, - [:worker_id, :status], - name: 'index_bj_cell_local_by_worker_id_and_status' - ) - add_concurrent_partitioned_index( - :background_operation_jobs_cell_local, - [:worker_id, :max_cursor], - name: 'index_bj_cell_local_on_worker_id_and_cursor_max_value', where: 'max_cursor IS NOT NULL' - ) - add_concurrent_partitioned_index( - :background_operation_jobs_cell_local, - [:worker_id, :finished_at], - name: 'index_bj_cell_local_on_worker_id_and_finished_at' - ) end def add_constraints diff --git a/db/migrate/20250915164841_create_background_operation_workers.rb b/db/migrate/20250915164841_create_background_operation_workers.rb index ce8d931bde7341..0366a8ace93c16 100644 --- a/db/migrate/20250915164841_create_background_operation_workers.rb +++ b/db/migrate/20250915164841_create_background_operation_workers.rb @@ -58,11 +58,6 @@ def add_indexes :status, name: 'index_background_operation_workers_by_status' ) - add_concurrent_partitioned_index( - :background_operation_workers, - :priority, - name: 'index_background_operation_workers_by_priority' - ) add_concurrent_partitioned_index( :background_operation_workers, :organization_id, diff --git a/db/migrate/20250915164941_create_background_operation_jobs.rb b/db/migrate/20250915164941_create_background_operation_jobs.rb index 0d5def3e8bba71..049b0a6e5920c2 100644 --- a/db/migrate/20250915164941_create_background_operation_jobs.rb +++ b/db/migrate/20250915164941_create_background_operation_jobs.rb @@ -59,26 +59,6 @@ def add_indexes :created_at, name: 'index_background_operation_jobs_by_created_at' ) - add_concurrent_partitioned_index( - :background_operation_jobs, - [:worker_id, :id], - name: 'index_background_jobs_by_worker_id_and_id' - ) - add_concurrent_partitioned_index( - :background_operation_jobs, - [:worker_id, :status], - name: 'index_background_jobs_by_worker_id_and_status' - ) - add_concurrent_partitioned_index( - :background_operation_jobs, - [:worker_id, :max_cursor], - name: 'index_background_jobs_on_worker_id_and_cursor_max_value', where: 'max_cursor IS NOT NULL' - ) - add_concurrent_partitioned_index( - :background_operation_jobs, - [:worker_id, :finished_at], - name: 'index_background_jobs_on_worker_id_and_finished_at' - ) end def add_constraints diff --git a/db/structure.sql b/db/structure.sql index 1ecb4acc6d37bd..5636beea022ee7 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -38462,14 +38462,6 @@ CREATE UNIQUE INDEX index_aws_roles_on_user_id ON aws_roles USING btree (user_id CREATE INDEX index_background_jobs_by_status ON ONLY background_operation_jobs USING btree (status); -CREATE INDEX index_background_jobs_by_worker_id_and_id ON ONLY background_operation_jobs USING btree (worker_id, id); - -CREATE INDEX index_background_jobs_by_worker_id_and_status ON ONLY background_operation_jobs USING btree (worker_id, status); - -CREATE INDEX index_background_jobs_on_worker_id_and_cursor_max_value ON ONLY background_operation_jobs USING btree (worker_id, max_cursor) WHERE (max_cursor IS NOT NULL); - -CREATE INDEX index_background_jobs_on_worker_id_and_finished_at ON ONLY background_operation_jobs USING btree (worker_id, finished_at); - CREATE INDEX index_background_operation_jobs_by_created_at ON ONLY background_operation_jobs USING btree (created_at); CREATE INDEX index_background_operation_jobs_by_organization ON ONLY background_operation_jobs USING btree (organization_id); @@ -38478,8 +38470,6 @@ CREATE INDEX index_background_operation_workers_by_created_at ON ONLY background CREATE INDEX index_background_operation_workers_by_organization ON ONLY background_operation_workers USING btree (organization_id); -CREATE INDEX index_background_operation_workers_by_priority ON ONLY background_operation_workers USING btree (priority); - CREATE INDEX index_background_operation_workers_by_status ON ONLY background_operation_workers USING btree (status); CREATE INDEX index_background_operation_workers_by_user ON ONLY background_operation_workers USING btree (user_id); @@ -38562,14 +38552,6 @@ CREATE UNIQUE INDEX index_batched_migrations_on_gl_schema_and_unique_configurati CREATE INDEX index_bj_cell_local_by_status ON ONLY background_operation_jobs_cell_local USING btree (status); -CREATE INDEX index_bj_cell_local_by_worker_id_and_id ON ONLY background_operation_jobs_cell_local USING btree (worker_id, id); - -CREATE INDEX index_bj_cell_local_by_worker_id_and_status ON ONLY background_operation_jobs_cell_local USING btree (worker_id, status); - -CREATE INDEX index_bj_cell_local_on_worker_id_and_cursor_max_value ON ONLY background_operation_jobs_cell_local USING btree (worker_id, max_cursor) WHERE (max_cursor IS NOT NULL); - -CREATE INDEX index_bj_cell_local_on_worker_id_and_finished_at ON ONLY background_operation_jobs_cell_local USING btree (worker_id, finished_at); - CREATE INDEX index_board_assignees_on_assignee_id ON board_assignees USING btree (assignee_id); CREATE UNIQUE INDEX index_board_assignees_on_board_id_and_assignee_id ON board_assignees USING btree (board_id, assignee_id); @@ -38656,8 +38638,6 @@ CREATE INDEX index_boards_on_milestone_id ON boards USING btree (milestone_id); CREATE INDEX index_boards_on_project_id ON boards USING btree (project_id); -CREATE INDEX index_bow_cell_local_by_priority ON ONLY background_operation_workers_cell_local USING btree (priority); - CREATE INDEX index_bow_cell_local_by_status ON ONLY background_operation_workers_cell_local USING btree (status); CREATE UNIQUE INDEX index_bow_cell_local_on_unique_configuration ON ONLY background_operation_workers_cell_local USING btree (partition, job_class_name, table_name, column_name, job_arguments); diff --git a/lib/gitlab/database/background_operation/common_worker.rb b/lib/gitlab/database/background_operation/common_worker.rb index 27307d1fef1de8..b32478ecad4471 100644 --- a/lib/gitlab/database/background_operation/common_worker.rb +++ b/lib/gitlab/database/background_operation/common_worker.rb @@ -38,7 +38,7 @@ module CommonWorker } scope :for_partition, ->(partition) { where(partition: partition) } - scope :executable, -> { with_statuses(:active, :paused) } + scope :executable, -> { with_statuses(:queued, :active, :paused) } partitioned_by :partition, strategy: :sliding_list, next_partition_if: ->(active_partition) do @@ -60,10 +60,11 @@ module CommonWorker end state_machine :status, initial: :paused do - state :paused, value: 0 + state :queued, value: 0 state :active, value: 1 - state :finished, value: 2 - state :failed, value: 3 + state :paused, value: 2 + state :finished, value: 3 + state :failed, value: 4 end end end diff --git a/lib/gitlab/database/background_operation/job.rb b/lib/gitlab/database/background_operation/job.rb index eb0ca574e7fb1b..d55a052301f560 100644 --- a/lib/gitlab/database/background_operation/job.rb +++ b/lib/gitlab/database/background_operation/job.rb @@ -9,9 +9,7 @@ class Job < SharedModel self.table_name = :background_operation_jobs belongs_to :worker, - ->(job) { where(partition: job.worker_partition) }, class_name: 'Gitlab::Database::BackgroundOperation::Worker', - foreign_key: :worker_id, partition_foreign_key: :worker_partition, inverse_of: :jobs diff --git a/lib/gitlab/database/background_operation/worker.rb b/lib/gitlab/database/background_operation/worker.rb index c1f31fc4558fbb..374d378d934a69 100644 --- a/lib/gitlab/database/background_operation/worker.rb +++ b/lib/gitlab/database/background_operation/worker.rb @@ -9,9 +9,7 @@ class Worker < SharedModel self.table_name = :background_operation_workers has_many :jobs, - ->(worker) { where(worker_partition: worker.partition) }, class_name: 'Gitlab::Database::BackgroundOperation::Job', - foreign_key: :worker_id, inverse_of: :worker, partition_foreign_key: :worker_partition diff --git a/spec/factories/gitlab/database/background_operation/workers.rb b/spec/factories/gitlab/database/background_operation/workers.rb index c80a224211ddc2..aeb388e7987968 100644 --- a/spec/factories/gitlab/database/background_operation/workers.rb +++ b/spec/factories/gitlab/database/background_operation/workers.rb @@ -18,7 +18,7 @@ max_cursor { [1000] } user - trait :paused do + trait :queued do status { 0 } end @@ -26,12 +26,16 @@ status { 1 } end - trait :finished do + trait :paused do status { 2 } end - trait :failed do + trait :finished do status { 3 } end + + trait :failed do + status { 4 } + end end end diff --git a/spec/factories/gitlab/database/background_operation/workers_cell_local.rb b/spec/factories/gitlab/database/background_operation/workers_cell_local.rb index 6caf009983c059..baddc38d8d369e 100644 --- a/spec/factories/gitlab/database/background_operation/workers_cell_local.rb +++ b/spec/factories/gitlab/database/background_operation/workers_cell_local.rb @@ -16,7 +16,7 @@ min_cursor { [1] } max_cursor { [1000] } - trait :paused do + trait :queued do status { 0 } end @@ -24,12 +24,16 @@ status { 1 } end - trait :finished do + trait :paused do status { 2 } end - trait :failed do + trait :finished do status { 3 } end + + trait :failed do + status { 4 } + end end end diff --git a/spec/lib/gitlab/database/background_operation/job_cell_local_spec.rb b/spec/lib/gitlab/database/background_operation/job_cell_local_spec.rb index 526953cced0eb9..20facc031b41c7 100644 --- a/spec/lib/gitlab/database/background_operation/job_cell_local_spec.rb +++ b/spec/lib/gitlab/database/background_operation/job_cell_local_spec.rb @@ -4,5 +4,7 @@ require_relative 'job_shared_examples' RSpec.describe Gitlab::Database::BackgroundOperation::JobCellLocal, type: :model, feature_category: :database do - it_behaves_like 'background operation job functionality', :background_operation_job_cell_local + it_behaves_like 'background operation job functionality', + :background_operation_job_cell_local, + :background_operation_worker_cell_local end diff --git a/spec/lib/gitlab/database/background_operation/job_shared_examples.rb b/spec/lib/gitlab/database/background_operation/job_shared_examples.rb index 1f13ae02179f62..0533856a4a7b2f 100644 --- a/spec/lib/gitlab/database/background_operation/job_shared_examples.rb +++ b/spec/lib/gitlab/database/background_operation/job_shared_examples.rb @@ -1,12 +1,20 @@ # frozen_string_literal: true -RSpec.shared_examples 'background operation job functionality' do |job_factory| +RSpec.shared_examples 'background operation job functionality' do |job_factory, worker_factory| using RSpec::Parameterized::TableSyntax it { is_expected.to be_a Gitlab::Database::SharedModel } describe 'associations' do + let_it_be(:worker) { create(worker_factory) } # rubocop:disable Rails/SaveBang -- factory, not an AR object + let_it_be(:job) { create(job_factory, worker: worker, worker_partition: worker.partition) } + it { is_expected.to belong_to(:worker).inverse_of(:jobs) } + + it 'maintains inverse relationship with the worker' do + expect(worker.jobs).to match_array([job]) + expect(job.worker).to eq(worker) + end end describe 'validations' do diff --git a/spec/lib/gitlab/database/background_operation/job_spec.rb b/spec/lib/gitlab/database/background_operation/job_spec.rb index 3a45d1844e25cd..5ff6e9dd685492 100644 --- a/spec/lib/gitlab/database/background_operation/job_spec.rb +++ b/spec/lib/gitlab/database/background_operation/job_spec.rb @@ -4,5 +4,5 @@ require_relative 'job_shared_examples' RSpec.describe Gitlab::Database::BackgroundOperation::Job, type: :model, feature_category: :database do - it_behaves_like 'background operation job functionality', :background_operation_job + it_behaves_like 'background operation job functionality', :background_operation_job, :background_operation_worker end diff --git a/spec/lib/gitlab/database/background_operation/worker_shared_examples.rb b/spec/lib/gitlab/database/background_operation/worker_shared_examples.rb index bf83860220550b..58445f624513de 100644 --- a/spec/lib/gitlab/database/background_operation/worker_shared_examples.rb +++ b/spec/lib/gitlab/database/background_operation/worker_shared_examples.rb @@ -21,13 +21,16 @@ end describe 'scopes' do - let_it_be(:worker_1) { create(worker_factory, :active) } - let_it_be(:worker_2) { create(worker_factory, :paused) } - let_it_be(:worker_3) { create(worker_factory, :finished) } + let_it_be(:queued_worker) { create(worker_factory, :queued) } + let_it_be(:active_worker) { create(worker_factory, :active) } + let_it_be(:paused_worker) { create(worker_factory, :paused) } + let_it_be(:finished_worker) { create(worker_factory, :finished) } + + let(:executable_workers) { [queued_worker, active_worker, paused_worker] } describe '.executable' do - it 'returns workers with active or paused status' do - expect(described_class.executable).to contain_exactly(worker_1, worker_2) + it 'returns workers with queued, active or paused status' do + expect(described_class.executable).to match_array(executable_workers) end end end @@ -117,7 +120,7 @@ expect { create(worker_factory) }.not_to raise_error # rubocop:disable Rails/SaveBang -- factory # after marking old records as non-executable - described_class.for_partition(1).update_all(status: 2) + described_class.for_partition(1).update_all(status: 3) partition_manager.sync_partitions -- GitLab From c15fb429080dded1e26df9b94a472c7f27b4618e Mon Sep 17 00:00:00 2001 From: Prabakaran Murugesan Date: Mon, 29 Sep 2025 10:49:37 +0200 Subject: [PATCH 17/17] Removes extra definitions from association --- lib/gitlab/database/background_operation/job_cell_local.rb | 2 -- 1 file changed, 2 deletions(-) diff --git a/lib/gitlab/database/background_operation/job_cell_local.rb b/lib/gitlab/database/background_operation/job_cell_local.rb index 6b987839c995af..24a37745a9b3e0 100644 --- a/lib/gitlab/database/background_operation/job_cell_local.rb +++ b/lib/gitlab/database/background_operation/job_cell_local.rb @@ -9,9 +9,7 @@ class JobCellLocal < SharedModel self.table_name = :background_operation_jobs_cell_local belongs_to :worker, - ->(job) { where(partition: job.worker_partition) }, class_name: 'Gitlab::Database::BackgroundOperation::WorkerCellLocal', - foreign_key: :worker_id, partition_foreign_key: :worker_partition, inverse_of: :jobs end -- GitLab