From 64be52844e679d205d959ea06d50c44366c39097 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Mon, 23 May 2022 19:10:25 +0100 Subject: Partition the package_derivations_by_guix_revision_range table And create a proper git_branches table in the process. I'm hoping this will help with slow deletions from the package_derivations_by_guix_revision_range table in the case where there are lots of branches, since it'll separate the data for one branch from another. These migrations will remove the existing data, so rebuild-package-derivations-table will currently need manually running to regenerate it. --- sqitch/deploy/git_branch_id.sql | 32 ++++++++++++++++++++++ ..._package_derivations_by_guix_revision_range.sql | 18 ++++++++++++ sqitch/revert/git_branch_id.sql | 7 +++++ ..._package_derivations_by_guix_revision_range.sql | 7 +++++ sqitch/sqitch.plan | 2 ++ sqitch/verify/git_branch_id.sql | 7 +++++ ..._package_derivations_by_guix_revision_range.sql | 7 +++++ 7 files changed, 80 insertions(+) create mode 100644 sqitch/deploy/git_branch_id.sql create mode 100644 sqitch/deploy/partition_package_derivations_by_guix_revision_range.sql create mode 100644 sqitch/revert/git_branch_id.sql create mode 100644 sqitch/revert/partition_package_derivations_by_guix_revision_range.sql create mode 100644 sqitch/verify/git_branch_id.sql create mode 100644 sqitch/verify/partition_package_derivations_by_guix_revision_range.sql (limited to 'sqitch') diff --git a/sqitch/deploy/git_branch_id.sql b/sqitch/deploy/git_branch_id.sql new file mode 100644 index 0000000..9da071c --- /dev/null +++ b/sqitch/deploy/git_branch_id.sql @@ -0,0 +1,32 @@ +-- Deploy guix-data-service:git_branch_id to pg + +BEGIN; + +ALTER TABLE git_branches RENAME TO git_branches_old; + +CREATE TABLE git_branches ( + id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + name character varying NOT NULL, + git_repository_id integer NOT NULL REFERENCES git_repositories (id), + CONSTRAINT git_repository_id_name_unique UNIQUE (git_repository_id, name) +); + +CREATE TABLE git_commits ( + id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, + commit character varying NOT NULL, + git_branch_id integer NOT NULL REFERENCES git_branches (id), + datetime timestamp without time zone NOT NULL +); + +INSERT INTO git_branches (name, git_repository_id) + SELECT DISTINCT name, git_repository_id + FROM git_branches_old; + +INSERT INTO git_commits (commit, git_branch_id, datetime) + SELECT commit, git_branches.id, datetime + FROM git_branches_old + INNER JOIN git_branches + ON git_branches_old.name = git_branches.name + AND git_branches_old.git_repository_id = git_branches.git_repository_id; + +COMMIT; diff --git a/sqitch/deploy/partition_package_derivations_by_guix_revision_range.sql b/sqitch/deploy/partition_package_derivations_by_guix_revision_range.sql new file mode 100644 index 0000000..88611f6 --- /dev/null +++ b/sqitch/deploy/partition_package_derivations_by_guix_revision_range.sql @@ -0,0 +1,18 @@ +-- Deploy guix-data-service:partition_package_derivations_by_guix_revision_range to pg + +BEGIN; + +DROP TABLE package_derivations_by_guix_revision_range; + +CREATE TABLE package_derivations_by_guix_revision_range ( + git_branch_id integer NOT NULL REFERENCES git_branches (id), + package_name varchar NOT NULL, + package_version varchar NOT NULL, + derivation_id integer NOT NULL, + system varchar NOT NULL, + target varchar NOT NULL, + first_guix_revision_id integer NOT NULL REFERENCES guix_revisions (id), + last_guix_revision_id integer NOT NULL REFERENCES guix_revisions (id) +) PARTITION BY LIST (git_branch_id); + +COMMIT; diff --git a/sqitch/revert/git_branch_id.sql b/sqitch/revert/git_branch_id.sql new file mode 100644 index 0000000..d9a18da --- /dev/null +++ b/sqitch/revert/git_branch_id.sql @@ -0,0 +1,7 @@ +-- Revert guix-data-service:git_branch_id from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/revert/partition_package_derivations_by_guix_revision_range.sql b/sqitch/revert/partition_package_derivations_by_guix_revision_range.sql new file mode 100644 index 0000000..f3a1436 --- /dev/null +++ b/sqitch/revert/partition_package_derivations_by_guix_revision_range.sql @@ -0,0 +1,7 @@ +-- Revert guix-data-service:partition_package_derivations_by_guix_revision_range from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan index 44138d6..1f329a5 100644 --- a/sqitch/sqitch.plan +++ b/sqitch/sqitch.plan @@ -83,3 +83,5 @@ some_indexes 2021-05-17T17:36:38Z Christopher Baines # Add so package_metadata_location_id_index 2021-05-27T19:51:13Z Canan Talayhan # Add index for location id packages_replacement 2021-04-24T04:52:57Z Christopher Baines # Add packages.replacement_package_id package_derivations_not_null_hash_index 2021-07-11T14:19:32Z Christopher Baines # Add an index to package_derivations +git_branch_id 2022-05-23T18:11:14Z Chris # Add git_branch.id +partition_package_derivations_by_guix_revision_range 2022-05-23T18:20:37Z Chris # Partition package_derivations_by_guix_revision_range diff --git a/sqitch/verify/git_branch_id.sql b/sqitch/verify/git_branch_id.sql new file mode 100644 index 0000000..aa5749b --- /dev/null +++ b/sqitch/verify/git_branch_id.sql @@ -0,0 +1,7 @@ +-- Verify guix-data-service:git_branch_id on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; diff --git a/sqitch/verify/partition_package_derivations_by_guix_revision_range.sql b/sqitch/verify/partition_package_derivations_by_guix_revision_range.sql new file mode 100644 index 0000000..8401cf4 --- /dev/null +++ b/sqitch/verify/partition_package_derivations_by_guix_revision_range.sql @@ -0,0 +1,7 @@ +-- Verify guix-data-service:partition_package_derivations_by_guix_revision_range on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- cgit v1.2.3