## Motivation In gitlab~10690711 we store the information about all [**11**](https://docs.gitlab.com/ee/user/packages/package_registry/supported_package_managers.html) packages formats in one database table `packages_packages`. The table was designed using [Single Table Inheritance](https://docs.gitlab.com/ee/development/database/single_table_inheritance.html), which is currently highly discouraged because of the following reasons: * Lead to tables with large number of rows, when we should strive to keep tables small. * Need additional indexes, increasing our usage of [lightweight locks](https://gitlab.com/groups/gitlab-org/-/epics/11543), whose saturation can cause incidents. * Add overhead by having to filter all of the data by a value, leading to more page accesses on read. The chosen design brings further divergence, when implementing the new package format or adding an index. Decomposing the `packages_packages` table into the specific tables for every package format will bring several benefits: 1. Improved query performance: the decomposed tables can result in faster query execution, as the database will more efficiently retrieve and process data from smaller tables. Additionally, we could merge packages metadata tables with packages tables. This'll avoid the previously required queries to fetch metadata information [link (internal)](https://log.gprd.gitlab.net/app/r/s/CmW9f) `~10%` of the slowest queries in the package registry. 2. Better scalability: we could apply partitioning to the decomposed tables. 3. Easier maintenance: the regular tasks like `VACUUM ANALYZE` will run faster. 4. Increased flexibility: the decomposition allows for more flexible database design, making it easier to add new columns or modify existing ones. 5. Better support for data relationships: proper decomposition helps in establishing clear and logical relationships between different entities in the database. ### Risks - Currently the `packages_packages` table contains `26` indexes. This affects query planning, and may lead to [light-weight lock (LWLock) contention](https://gitlab.com/groups/gitlab-org/-/epics/11543). - Adding new package formats or tuning the performance for existing formats might require adding new indexes, which is currently under the limitation [link](https://gitlab.com/gitlab-org/gitlab/-/blob/master/rubocop/cop/migration/prevent_index_creation.rb#L18). - Further grow of the table will increase WAL contribution due to the high amount of updates [link (internal)](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22pum%22:%7B%22datasource%22:%22mimir-gitlab-gprd%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22sum%20by%20%28operation%29%20%28label_replace%28rate%28pg_stat_user_tables_n_tup_upd%7Brelname%3D%5C%22packages_packages%5C%22,%20env%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%5B1m%5D%29%20and%20on%20%28fqdn%29%20pg_replication_is_replica%20%3D%3D%200,%20%5C%22operation%5C%22,%20%5C%22update%5C%22,%20%5C%22operation%5C%22,%20%5C%22%5C%22%29%20or%20label_replace%28rate%28pg_stat_user_tables_n_tup_ins%7Brelname%3D%5C%22packages_packages%5C%22,%20env%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%5B1m%5D%29%20and%20on%20%28fqdn%29%20pg_replication_is_replica%20%3D%3D%200,%20%5C%22operation%5C%22,%20%5C%22insert%5C%22,%20%5C%22operation%5C%22,%20%5C%22%5C%22%29%29%22,%22range%22:true,%22instant%22:true,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22mimir-gitlab-gprd%22%7D,%22editorMode%22:%22code%22,%22legendFormat%22:%22__auto%22%7D%5D,%22range%22:%7B%22from%22:%22now-30d%22,%22to%22:%22now%22%7D%7D%7D&orgId=1) and inserts [link (internal)](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22pum%22:%7B%22datasource%22:%22mimir-gitlab-gprd%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22sum%28rate%28pg_stat_user_tables_n_tup_ins%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22,%20relname%3D~%5C%22packages_packages%5C%22%7D%5B1m%5D%29%20%2B%20rate%28pg_stat_user_tables_n_tup_upd%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22,%20relname%3D~%5C%22packages_packages%5C%22%7D%5B1m%5D%29%20%2B%20rate%28pg_stat_user_tables_n_tup_del%7Benv%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22,%20relname%3D~%5C%22packages_packages%5C%22%7D%5B1m%5D%29%29%22,%22range%22:true,%22instant%22:true,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22mimir-gitlab-gprd%22%7D,%22editorMode%22:%22code%22,%22legendFormat%22:%22__auto%22%7D%5D,%22range%22:%7B%22from%22:%22now-7d%22,%22to%22:%22now%22%7D%7D%7D&orgId=1).\ Peak update rate: 270 op/s [link (internal)](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22pum%22:%7B%22datasource%22:%22mimir-gitlab-gprd%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22sum%20by%20%28relname%29%20%28rate%28pg_stat_user_tables_n_tup_upd%7Brelname%3D~%5C%22%28packages_packages%29%5C%22,%20env%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%5B1m%5D%29%29%22,%22range%22:true,%22instant%22:true,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22mimir-gitlab-gprd%22%7D,%22editorMode%22:%22code%22,%22legendFormat%22:%22__auto%22%7D%5D,%22range%22:%7B%22from%22:%22now-7d%22,%22to%22:%22now%22%7D%7D%7D&orgId=1)\ Peak insert rate: 3 op/s [link (internal)](https://dashboards.gitlab.net/explore?schemaVersion=1&panes=%7B%22pum%22:%7B%22datasource%22:%22mimir-gitlab-gprd%22,%22queries%22:%5B%7B%22refId%22:%22A%22,%22expr%22:%22sum%20by%20%28relname%29%20%28rate%28pg_stat_user_tables_n_tup_ins%7Brelname%3D~%5C%22%28packages_packages%29%5C%22,%20env%3D%5C%22gprd%5C%22,%20type%3D%5C%22patroni%5C%22%7D%5B1m%5D%29%29%22,%22range%22:true,%22instant%22:true,%22datasource%22:%7B%22type%22:%22prometheus%22,%22uid%22:%22mimir-gitlab-gprd%22%7D,%22editorMode%22:%22code%22,%22legendFormat%22:%22__auto%22%7D%5D,%22range%22:%7B%22from%22:%22now-7d%22,%22to%22:%22now%22%7D%7D%7D&orgId=1)\ WAL rate: 58'968 KiB/s (calculating using the formula `(1 + num_indexes) * 8 KiB * (insert_rate + update_rate) = wal_rate` [link](https://gitlab.com/gitlab-org/gitlab/-/issues/460799#note_1930059499) - The table size is `15Gb` including indexes, and it's recommended to start actions to reduce table size when the table is around `10Gb` [link](https://handbook.gitlab.com/handbook/engineering/architecture/design-documents/database_size_limits/#target-all-physical-tables-on-gitlabcom-are--100-gb-including-indexes) Historical growth rate of the Packages table: {width=900 height=538} In https://gitlab.com/groups/gitlab-org/-/epics/12294+ we started changing the application code to have separate database tables for different package formats. This's the first required step prior to the database changes. ## Tables numbers _Updated Nov 2025:_ The `packages_packages` table size is `21Gb` and has `31M` rows. The `packages_packages` table currently contains about `21M` (Dec 2024) entries and has the size of `15Gb` including indexes. The table contains the foreign keys to `users` and `projects` tables. ```sql Foreign-key constraints: "fk_c188f0dba4" FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE SET NULL "fk_rails_e1ac527425" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE ``` It's connected with other packages related tables through the foreign keys: ```sql Referenced by: TABLE "ml_model_versions" CONSTRAINT "fk_39f8aa0b8a" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE SET NULL TABLE "packages_package_files" CONSTRAINT "fk_86f0f182f8" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "ml_candidates" CONSTRAINT "fk_a1d5f1bc45" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE SET NULL TABLE "packages_maven_metadata" CONSTRAINT "fk_be88aed360" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "coverage_fuzzing_corpuses" CONSTRAINT "fk_ef5ebf339f" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "packages_tags" CONSTRAINT "fk_rails_1dfc868911" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "packages_terraform_module_metadata" CONSTRAINT "fk_rails_33c045442a" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "packages_nuget_symbols" CONSTRAINT "fk_rails_5df972da14" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE SET NULL TABLE "packages_debian_publications" CONSTRAINT "fk_rails_7668c1d606" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "packages_conan_metadata" CONSTRAINT "fk_rails_8c68cfec8b" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "packages_rubygems_metadata" CONSTRAINT "fk_rails_95a3f5ce78" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "packages_pypi_metadata" CONSTRAINT "fk_rails_9698717cdd" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "packages_dependency_links" CONSTRAINT "fk_rails_96ef1c00d3" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "packages_composer_metadata" CONSTRAINT "fk_rails_ad48c2e5bb" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "packages_build_infos" CONSTRAINT "fk_rails_b18868292d" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "packages_npm_metadata" CONSTRAINT "fk_rails_c0e5fce6f3" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "packages_rpm_metadata" CONSTRAINT "fk_rails_d79f02264b" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE TABLE "packages_nuget_metadata" CONSTRAINT "fk_rails_fc0c19f5b4" FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE CASCADE ``` ## Proposal 1. Have dedicated table per package format. 1. Merge metadata table into the package specific table. We could start with (1.) and implement (2.) as a second step. Let's take as an example NuGet packages: ```mermaid flowchart TB; packages_package_files --> packages_nuget_packages packages_tags --> packages_nuget_packages packages_dependency_links --> packages_nuget_packages packages_build_infos --> packages_nuget_packages packages_nuget_packages --> packages_nuget_symbols packages_nuget_packages --> packages_nuget_metadata ``` ## Approximate implementation plan We could do the changes iteratively per package format. ### Step 1. Prepare a table - Prepare a new table with all columns from `packages_packages table` beside `package_type`, keeping constraints. - Add shared indexes and special indexes per format (indexes that contain `package_type = …`).
 Example: `packages_nuget_packages` table Columns: - `id`, bigint, not null - `project_id`, integer, not null - `created_at`, timestamp with time zone, not null - `updated_at`, timestamp with time zone, not null - `name`, character varying, not null - `version`, character varying - `creator_id`, integer - `status`, smallint, not null - `last_downloaded_at`, timestamp with time zone - `status_message`, text
 Indexes: ```sql - "packages_nuget_packages_pkey" PRIMARY KEY, btree (id) - "idx_installable_helm_pkgs_on_project_id_id" btree (project_id, id) - "idx_packages_packages_on_project_id_name_version_package_type" btree (project_id, name, version, package_type) - "idx_pkgs_pkgs_on_project_id_and_lower_name_and_id_when_nuget" btree (project_id, lower(name::text), id) WHERE package_type = 4 - "idx_pkgs_project_id_lower_name_when_nuget_installable_version" btree (project_id, lower(name::text)) WHERE package_type = 4 AND version IS NOT NULL AND (status = ANY (ARRAY[0, 1])) - "index_packages_packages_on_creator_id" btree (creator_id) - "index_packages_packages_on_id_and_created_at" btree (id, created_at) - "index_packages_packages_on_name_trigram" gin (name gin_trgm_ops) - "index_packages_packages_on_project_id_and_created_at" btree (project_id, created_at) - "index_packages_packages_on_project_id_and_package_type" btree (project_id, package_type) - "index_packages_packages_on_project_id_and_status_and_id" btree (project_id, status, id) - "index_packages_packages_on_project_id_and_version" btree (project_id, version) - "index_packages_project_id_name_partial_for_nuget" btree (project_id, name) WHERE name::text <> 'NuGet.Temporary.Package'::text AND version IS NOT NULL AND package_type = 4 - "package_name_index" btree (name) ``` I kept the existing names, but it will make sense to have more appropriate names.
 ### Step 2. Sync data - ~~Add new `uuid`(?) foreign key to keep a reference to the `packages_packages` table. Add `UNIQUE` constraint to the column. The new `uuid` column will be used when searching for a package.~~ - Setup the triggers for `packages_packages` table `INSERT`, `UPDATE` and `DELETE` to sync the new data between `packages_packages` table and `packages_nuget_packages` table, when a new entry in the `packages_packages` table with `package_type = 4` is inserted/updated or deleted. Use `id` column to save Primary `id` from the `packages_packages` table. - When the _synchronization_ is in place, prepare the data migration for existing entries. For every entry in the `packages_packages` table with the `package_type = 4` insert the new entry to the `packages_nuget_packages` table. Could we use the background migration for that? ### Step 3. Link new table to existing tables ~~The new table needs to be linked to common tables like `packages_package_files` or `packages_build_info` and package format specific tables like `packages_nuget_symbols`.
I don't see a clear path forward here yet, only theoretical.~~ ~~- How to reference the new `packages_nuget_packages` table from `packages_package_files`? Do we need to create an intermediate table with `packages_package_file_id` AND `packages_nuget_package_id` columns?~~ ~~- We'll need the data migration for the every linked table.~~ When we create the new table, we could use `packages_packages_id_seq` sequence for `id` column and also copy `id` from the `packages_packages` table to the `id` of new table when synching data using triggers. On the application side, we could re-use existing `package_id` foreign key and define a new association in the models. Example: ```ruby class Packages::PackageFile < ApplicationRecord belongs_to :package belongs_to :<format>_package, class: 'Packages::<format>::Package' end ``` In addition to that, we'll need to replace code `package_file.package` to `package_file.<format>_package` (with the ~"feature flag"). We might have several places, when it's not clear from the context, to which <format> belongs a package file, but I'd suggest to look into every case individually. Perhaps, we might need some refactoring. Later, when we're ready to swap the tables, we could start the ~"feature flag" rollout. ### Step 4. Prepare the application to handle multiple tables - In most cases only the package(s) of specific formats is requested. That should work just fine with the dedicated tables. No changes will be required but switching the `ActiveRecord` model to a new table. - For the cases when the packages are requested without specific type, it might be possible to use `UNION` to combine the search of multiple queries. --- Steps 1, 2 and 3 needs to be done for every package format.\ Starting with the second format, only the first part of step 4 will be necessary for a package format.