Skip to content

Add dependency_list_exports table

Zamir Martins requested to merge add_dependency_list_exports_table into master

What does this MR do and why?

Add dependency_list_exports table which will be used by the export feature.

Currently dependency list exporter isn't persisted and its endpoint is shared with regular fetching with the purpose of providing data to be populated by the frontend code.

Related issue: https://gitlab.com/gitlab-org/gitlab/-/issues/383052

Model will be developed as part of a follow-up MR

Migrate/Rollback

$ bundle exec rails db:migrate:down:main VERSION=20221111135238
main: == 20221111135238 CreateDependencyListExportsTable: reverting =================
main: -- drop_table(:dependency_list_exports)
main:    -> 0.0084s
main: == 20221111135238 CreateDependencyListExportsTable: reverted (0.0091s) ========

$ bundle exec rails db:migrate:down:ci VERSION=20221111135238
ci: == 20221111135238 CreateDependencyListExportsTable: reverting =================
ci: -- drop_table(:dependency_list_exports)
ci:    -> 0.0149s
ci: == 20221111135238 CreateDependencyListExportsTable: reverted (0.0163s) ========

$ bundle exec rails db:migrate VERSION=20221111135238
main: == 20221111135238 CreateDependencyListExportsTable: migrating =================
main: -- create_table(:dependency_list_exports, {})
main: -- quote_column_name(:file)
main:    -> 0.0001s
main:    -> 0.0129s
main: == 20221111135238 CreateDependencyListExportsTable: migrated (0.0136s) ========

ci: == 20221111135238 CreateDependencyListExportsTable: migrating =================
ci: -- create_table(:dependency_list_exports, {})
ci: -- quote_column_name(:file)
ci:    -> 0.0000s
ci:    -> 0.0102s
ci: == 20221111135238 CreateDependencyListExportsTable: migrated (0.0104s) ========
$ bundle exec rails db:migrate VERSION=20221116100056
main: == 20221116100056 AddForeignKeyToDependencyListExports: migrating =============
main: -- transaction_open?()
main:    -> 0.0001s
main: -- foreign_keys(:dependency_list_exports)
main:    -> 0.0087s
main: -- transaction_open?()
main:    -> 0.0001s
main: -- execute("ALTER TABLE dependency_list_exports\nADD CONSTRAINT fk_5b3d11e1ef\nFOREIGN KEY (user_id)\nREFERENCES users (id)\nON DELETE SET NULL\nNOT VALID;\n")
main:    -> 0.0024s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- execute("ALTER TABLE dependency_list_exports VALIDATE CONSTRAINT fk_5b3d11e1ef;")
main:    -> 0.0040s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:dependency_list_exports)
main:    -> 0.0034s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE dependency_list_exports\nADD CONSTRAINT fk_d871d74675\nFOREIGN KEY (project_id)\nREFERENCES projects (id)\nON DELETE CASCADE\nNOT VALID;\n")
main:    -> 0.0026s
main: -- execute("ALTER TABLE dependency_list_exports VALIDATE CONSTRAINT fk_d871d74675;")
main:    -> 0.0045s
main: == 20221116100056 AddForeignKeyToDependencyListExports: migrated (0.0484s) ====

ci: == 20221116100056 AddForeignKeyToDependencyListExports: migrating =============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- foreign_keys(:dependency_list_exports)
ci:    -> 0.0051s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE dependency_list_exports\nADD CONSTRAINT fk_5b3d11e1ef\nFOREIGN KEY (user_id)\nREFERENCES users (id)\nON DELETE SET NULL\nNOT VALID;\n")
ci:    -> 0.0023s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- execute("ALTER TABLE dependency_list_exports VALIDATE CONSTRAINT fk_5b3d11e1ef;")
ci:    -> 0.0041s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0006s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- foreign_keys(:dependency_list_exports)
ci:    -> 0.0038s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE dependency_list_exports\nADD CONSTRAINT fk_d871d74675\nFOREIGN KEY (project_id)\nREFERENCES projects (id)\nON DELETE CASCADE\nNOT VALID;\n")
ci:    -> 0.0017s
ci: -- execute("ALTER TABLE dependency_list_exports VALIDATE CONSTRAINT fk_d871d74675;")
ci:    -> 0.0046s
ci: == 20221116100056 AddForeignKeyToDependencyListExports: migrated (0.0304s) ====

$ bundle exec rails db:migrate:down:main VERSION=20221116100056
main: == 20221116100056 AddForeignKeyToDependencyListExports: reverting =============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- foreign_keys(:dependency_list_exports)
main:    -> 0.0095s
main: -- remove_foreign_key(:dependency_list_exports, {:column=>:user_id})
main:    -> 0.0096s
main: -- foreign_keys(:dependency_list_exports)
main:    -> 0.0041s
main: -- remove_foreign_key(:dependency_list_exports, {:column=>:project_id})
main:    -> 0.0094s
main: == 20221116100056 AddForeignKeyToDependencyListExports: reverted (0.0834s) ====

$ bundle exec rails db:migrate:down:ci VERSION=20221116100056
ci: == 20221116100056 AddForeignKeyToDependencyListExports: reverting =============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- foreign_keys(:dependency_list_exports)
ci:    -> 0.0113s
ci: -- remove_foreign_key(:dependency_list_exports, {:column=>:user_id})
ci:    -> 0.0069s
ci: -- foreign_keys(:dependency_list_exports)
ci:    -> 0.0049s
ci: -- remove_foreign_key(:dependency_list_exports, {:column=>:project_id})
ci:    -> 0.0082s
ci: == 20221116100056 AddForeignKeyToDependencyListExports: reverted (0.0733s) ====

Query planner

exec CREATE TABLE dependency_list_exports (id bigint NOT NULL, created_at timestamp with time zone NOT NULL, updated_at timestamp with time zone NOT NULL, project_id bigint NOT NULL, user_id bigint, file_store integer, status smallint DEFAULT 0 NOT NULL, file text, CONSTRAINT check_fff6fc9b2f CHECK ((char_length(file) <= 255)));

The query has been executed. Duration: 129.824 ms

exec ALTER TABLE ONLY dependency_list_exports ADD CONSTRAINT fk_5b3d11e1ef FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;

The query has been executed. Duration: 175.058 ms

exec ALTER TABLE ONLY dependency_list_exports ADD CONSTRAINT fk_d871d74675 FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE;

The query has been executed. Duration: 47.544 ms

Preparation when adding tables

  • Order columns based on the Ordering Table Columns guidelines.
  • Add foreign keys to any columns pointing to data in other tables, including an index.
  • Add indexes for fields that are used in statements such as WHERE, ORDER BY, GROUP BY, and JOINs.

What is the anticipated growth for the new table over the next 3 months, 6 months, 1 year? What assumptions are these based on?

I don't think the endpoint currently used has any metrics. Therefore any attempt to quantify the growth of the table would be inconclusive.

How many reads and writes per hour would you expect this table to have in 3 months, 6 months, 1 year? Under what circumstances are rows updated? What assumptions are these based on?

Similar to the previous answer, I don't think the endpoint currently used has any metrics. With that said, as an export related feature there shouldn't be many reads and writes per hour. Row will be updated whenever: (1) a new request to generate the export data is triggered and (2) status is changed. The idea is to have the records with the export file will be only available only for one hour.

Based on the anticipated data volume and access patterns, does the new table pose an availability risk to GitLab.com or self-managed instances? Does the proposed design scale to support the needs of GitLab.com and self-managed customers?

It doesn't seem likely as we aim to delete each record with its respective file after one hour of being generated.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Zamir Martins

Merge request reports