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.

19 files changed, 442 insertions, 167 deletions

diff --git a/Makefile.am b/Makefile.am
index d72c437..9d97045 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -87,6 +87,7 @@ SOURCES =									\
   guix-data-service/model/channel-news.scm					\
   guix-data-service/model/derivation.scm					\
   guix-data-service/model/git-branch.scm					\
+  guix-data-service/model/git-commit.scm					\
   guix-data-service/model/git-repository.scm					\
   guix-data-service/model/guix-revision-package-derivation.scm			\
   guix-data-service/model/guix-revision.scm					\
@@ -140,6 +141,7 @@ TESTS =					\
   tests/jobs-load-new-guix-revision.scm	\
   tests/model-derivation.scm			\
   tests/model-git-branch.scm			\
+  tests/model-git-commit.scm			\
   tests/model-git-repository.scm		\
   tests/model-license-set.scm			\
   tests/model-license.scm			\
diff --git a/guix-data-service/branch-updated-emails.scm b/guix-data-service/branch-updated-emails.scm
index 684c2e3..38432e6 100644
--- a/guix-data-service/branch-updated-emails.scm
+++ b/guix-data-service/branch-updated-emails.scm
@@ -22,6 +22,7 @@
   #:use-module (email email)
   #:use-module (guix-data-service model git-repository)
   #:use-module (guix-data-service model git-branch)
+  #:use-module (guix-data-service model git-commit)
   #:use-module (guix-data-service jobs load-new-guix-revision)
   #:export (enqueue-job-for-email))
 
@@ -59,13 +60,19 @@
               (when (and (not excluded-branch?)
                          (or (null? included-branches)
                              included-branch?))
-                (insert-git-branch-entry conn
-                                         branch-name
+                (insert-git-commit-entry conn
+                                         (or (git-branch-for-repository-and-name
+                                              conn
+                                              git-repository-id
+                                              branch-name)
+                                             (insert-git-branch-entry
+                                              conn
+                                              git-repository-id
+                                              branch-name))
                                          (if (string=? commit-all-zeros
                                                        x-git-newrev)
                                              ""
                                              x-git-newrev)
-                                         git-repository-id
                                          date)
 
                 (unless (string=? commit-all-zeros x-git-newrev)
diff --git a/guix-data-service/data-deletion.scm b/guix-data-service/data-deletion.scm
index 6480785..2e7af48 100644
--- a/guix-data-service/data-deletion.scm
+++ b/guix-data-service/data-deletion.scm
@@ -90,7 +90,7 @@ WHERE guix_revisions.git_repository_id = "
             (number->string git-repository-id) " AND
   commits.column1 NOT IN (
     SELECT commit
-    FROM git_branches
+    FROM git_commits
 )")))))
 
     (unless (null? guix-revision-ids)
@@ -130,8 +130,10 @@ WHERE id IN ("
 AND id NOT IN (
   SELECT id FROM guix_revisions
   INNER JOIN git_branches ON
-    git_branches.commit = guix_revisions.commit AND
     git_branches.git_repository_id = guix_revisions.git_repository_id
+  INNER JOIN git_commits ON
+    git_commits.git_branch_id = git_branches.id AND
+    git_commits.commit = guix_revisions.commit
 )"))
 
       (delete-unreferenced-package-derivations)
@@ -176,16 +178,22 @@ WHERE git_repository_id = " (number->string git-repository-id) " AND
  ", ")
 ")")))
 
-  (define (delete-from-git-branches conn)
+  (define (delete-from-git-commits conn)
     (exec-query
      conn
      (simple-format
       #f
       "
-DELETE FROM git_branches
-WHERE git_repository_id = ~A AND
-  name = '~A' AND
-  commit IN (~A)"
+DELETE FROM git_commits
+WHERE id IN (
+  SELECT id
+  FROM git_commits
+  INNER JOIN git_branches
+    ON git_branches.id = git_commits.git_branch_id
+  WHERE git_branches.git_repository_id = ~A
+    AND git_branches.name = '~A' AND
+    AND git_commits.commit IN (~A)
+)"
       git-repository-id
       branch-name
       (string-join
@@ -197,7 +205,7 @@ WHERE git_repository_id = ~A AND
   (with-postgresql-transaction
    conn
    (lambda (conn)
-     (delete-from-git-branches conn)
+     (delete-from-git-commits conn)
      (delete-jobs conn)
 
      (exec-query
@@ -216,9 +224,12 @@ WHERE git_repository_id = $1 AND
     (map car
          (exec-query conn
                      "
-SELECT commit
+SELECT git_commits.commit
 FROM git_branches
-WHERE git_repository_id = $1 AND name = $2"
+INNER JOIN git_commits
+  ON git_branches.id = git_commits.git_branch_id
+WHERE git_repository_id = $1
+  AND git_branches.name = $2"
                      (list (number->string git-repository-id)
                            branch-name))))
 
@@ -236,7 +247,9 @@ WHERE git_repository_id = $1 AND name = $2"
          (exec-query conn
                      "
 SELECT commit
-FROM git_branches
+FROM git_commits
+INNER JOIN git_branches
+  ON git_branches.id = git_commits.git_branch_id
 WHERE git_repository_id = $1 AND name = $2
 ORDER BY datetime DESC
 OFFSET $3"
@@ -311,6 +324,8 @@ FROM (
   SELECT DISTINCT ON (name, git_repository_id)
     name, git_repository_id, commit
   FROM git_branches
+  INNER JOIN git_commits
+    ON git_commits.git_branch_id = git_branches.id
   ORDER BY git_repository_id, name, datetime DESC
 ) AS git_branches_latest_revision
 WHERE commit = ''")))))
diff --git a/guix-data-service/model/git-branch.scm b/guix-data-service/model/git-branch.scm
index a9b0202..78e149a 100644
--- a/guix-data-service/model/git-branch.scm
+++ b/guix-data-service/model/git-branch.scm
@@ -21,32 +21,52 @@
   #:use-module (squee)
   #:use-module (srfi srfi-19)
   #:use-module (guix-data-service model utils)
-  #:export (insert-git-branch-entry
+  #:export (git-branch-for-repository-and-name
+            insert-git-branch-entry
             git-branches-for-commit
             git-branches-with-repository-details-for-commit
             most-recent-commits-for-branch
             latest-processed-commit-for-branch
             all-branches-with-most-recent-commit))
 
+(define (git-branch-for-repository-and-name conn
+                                            git-repository-id
+                                            name)
+  (match (exec-query
+          conn
+          "
+SELECT id
+FROM git_branches
+WHERE git_repository_id = $1
+  AND name = $2"
+          (list (number->string git-repository-id)
+                name))
+    (#f #f)
+    (((id)) (string->number id))))
+
 (define (insert-git-branch-entry conn
-                                 name commit
-                                 git-repository-id datetime)
-  (exec-query
-   conn
-   (string-append
-    "INSERT INTO git_branches (name, commit, git_repository_id, datetime) "
-    "VALUES ($1, $2, $3, to_timestamp($4)) "
-    "ON CONFLICT DO NOTHING")
-   (list name
-         commit
-         (number->string git-repository-id)
-         (date->string datetime "~s"))))
+                                 git-repository-id
+                                 name)
+  (match (exec-query
+          conn
+          "
+INSERT INTO git_branches (git_repository_id, name)
+VALUES ($1, $2)
+RETURNING id"
+          (list (number->string git-repository-id)
+                name))
+    (((id))
+     (string->number id))))
 
 (define (git-branches-for-commit conn commit)
   (define query
     "
-SELECT name, datetime FROM git_branches WHERE commit = $1
-ORDER BY datetime DESC")
+SELECT name, git_commits.datetime
+FROM git_commits
+INNER JOIN git_branches
+  ON git_commits.git_branch_id = git_branches.id
+WHERE commit = $1
+ORDER BY git_commits.datetime DESC")
 
   (exec-query conn query (list commit)))
 
@@ -55,16 +75,49 @@ ORDER BY datetime DESC")
     "
 SELECT git_repositories.id, git_repositories.label,
        git_repositories.url, git_repositories.cgit_url_base,
-       git_branches.name, git_branches.datetime
-FROM git_branches
+       git_branches.name, git_commits.datetime
+FROM git_commits
+INNER JOIN git_branches
+  ON git_commits.git_branch_id = git_branches.id
 INNER JOIN git_repositories
   ON git_branches.git_repository_id = git_repositories.id
-WHERE git_branches.commit = $1")
+WHERE git_commits.commit = $1")
 
   (group-list-by-first-n-fields
    4
    (exec-query conn query (list commit))))
 
+(define* (latest-processed-commit-for-branch conn repository-id branch-name)
+  (define query
+    (string-append
+     "
+SELECT git_commits.commit
+FROM git_branches
+INNER JOIN git_commits
+  ON git_branches.id = git_commits.git_branch_id
+INNER JOIN guix_revisions
+  ON git_commits.commit = guix_revisions.commit
+INNER JOIN load_new_guix_revision_jobs
+  ON load_new_guix_revision_jobs.commit = guix_revisions.commit
+INNER JOIN load_new_guix_revision_job_events
+  ON job_id = load_new_guix_revision_jobs.id
+WHERE guix_revisions.git_repository_id = $1
+  AND git_branches.git_repository_id = $1
+  AND git_branches.name = $2
+  AND load_new_guix_revision_job_events.event = 'success'
+ORDER BY datetime DESC
+LIMIT 1"))
+
+  (match (exec-query
+          conn
+          query
+          (list repository-id branch-name))
+    (((commit-hash))
+     commit-hash)
+    ('()
+     #f)))
+
+
 (define* (most-recent-commits-for-branch conn git-repository-id
                                          branch-name
                                          #:key
@@ -74,7 +127,7 @@ WHERE git_branches.commit = $1")
   (define query
     (string-append
      "
-SELECT git_branches.commit,
+SELECT git_commits.commit,
        datetime,
        (
          load_new_guix_revision_job_events.event IS NOT NULL
@@ -84,12 +137,14 @@ SELECT git_branches.commit,
           FROM load_new_guix_revision_job_events
           INNER JOIN load_new_guix_revision_jobs ON
             load_new_guix_revision_jobs.id = load_new_guix_revision_job_events.job_id
-          WHERE load_new_guix_revision_jobs.commit = git_branches.commit AND
+          WHERE load_new_guix_revision_jobs.commit = git_commits.commit AND
                 git_branches.git_repository_id = load_new_guix_revision_jobs.git_repository_id
        ) AS job_events
 FROM git_branches
+INNER JOIN git_commits
+  ON git_branches.id = git_commits.git_branch_id
 LEFT OUTER JOIN guix_revisions
-  ON git_branches.commit = guix_revisions.commit
+  ON git_commits.commit = guix_revisions.commit
 LEFT JOIN load_new_guix_revision_jobs
   ON load_new_guix_revision_jobs.commit = guix_revisions.commit
 LEFT JOIN load_new_guix_revision_job_events
@@ -129,40 +184,12 @@ LIMIT " (number->string limit))
     (list branch-name
           (number->string git-repository-id)))))
 
-(define* (latest-processed-commit-for-branch conn repository-id branch-name)
-  (define query
-    (string-append
-     "
-SELECT git_branches.commit
-FROM git_branches
-INNER JOIN guix_revisions
-  ON git_branches.commit = guix_revisions.commit
-INNER JOIN load_new_guix_revision_jobs
-  ON load_new_guix_revision_jobs.commit = guix_revisions.commit
-INNER JOIN load_new_guix_revision_job_events
-  ON job_id = load_new_guix_revision_jobs.id
-WHERE guix_revisions.git_repository_id = $1
-  AND git_branches.git_repository_id = $1
-  AND git_branches.name = $2
-  AND load_new_guix_revision_job_events.event = 'success'
-ORDER BY datetime DESC
-LIMIT 1"))
-
-  (match (exec-query
-          conn
-          query
-          (list repository-id branch-name))
-    (((commit-hash))
-     commit-hash)
-    ('()
-     #f)))
-
 (define (all-branches-with-most-recent-commit conn git-repository-id)
   (define query
     (string-append
      "
 SELECT DISTINCT ON (name)
-  name, git_branches.commit,
+  name, git_commits.commit,
   datetime,
   (
     load_new_guix_revision_jobs.succeeded_at IS NOT NULL
@@ -172,14 +199,16 @@ SELECT DISTINCT ON (name)
     FROM load_new_guix_revision_job_events
     INNER JOIN load_new_guix_revision_jobs ON
       load_new_guix_revision_jobs.id = load_new_guix_revision_job_events.job_id
-    WHERE load_new_guix_revision_jobs.commit = git_branches.commit AND
+    WHERE load_new_guix_revision_jobs.commit = git_commits.commit AND
           git_branches.git_repository_id = load_new_guix_revision_jobs.git_repository_id
   ) AS job_events
 FROM git_branches
+INNER JOIN git_commits
+  ON git_branches.id = git_commits.git_branch_id
 LEFT OUTER JOIN guix_revisions
-  ON git_branches.commit = guix_revisions.commit
+  ON git_commits.commit = guix_revisions.commit
 LEFT JOIN load_new_guix_revision_jobs
-  ON git_branches.commit = load_new_guix_revision_jobs.commit
+  ON git_commits.commit = load_new_guix_revision_jobs.commit
  AND git_branches.git_repository_id = load_new_guix_revision_jobs.git_repository_id
 WHERE git_branches.git_repository_id = $1
 ORDER BY name, datetime DESC"))
@@ -199,4 +228,3 @@ ORDER BY name, datetime DESC"))
     conn
     query
     (list (number->string git-repository-id)))))
-
diff --git a/guix-data-service/model/git-commit.scm b/guix-data-service/model/git-commit.scm
new file mode 100644
index 0000000..d017384
--- /dev/null
+++ b/guix-data-service/model/git-commit.scm
@@ -0,0 +1,38 @@
+;;; Guix Data Service -- Information about Guix over time
+;;; Copyright © 2019 Christopher Baines <mail@cbaines.net>
+;;;
+;;; This program is free software: you can redistribute it and/or
+;;; modify it under the terms of the GNU Affero General Public License
+;;; as published by the Free Software Foundation, either version 3 of
+;;; the License, or (at your option) any later version.
+;;;
+;;; This program is distributed in the hope that it will be useful,
+;;; but WITHOUT ANY WARRANTY; without even the implied warranty of
+;;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+;;; Affero General Public License for more details.
+;;;
+;;; You should have received a copy of the GNU Affero General Public
+;;; License along with this program.  If not, see
+;;; <http://www.gnu.org/licenses/>.
+
+(define-module (guix-data-service model git-commit)
+  #:use-module (ice-9 match)
+  #:use-module (json)
+  #:use-module (squee)
+  #:use-module (srfi srfi-19)
+  #:use-module (guix-data-service model utils)
+  #:export (insert-git-commit-entry))
+
+(define (insert-git-commit-entry conn
+                                 git-branch-id
+                                 commit
+                                 datetime)
+  (exec-query
+   conn
+   "
+INSERT INTO git_commits (commit, git_branch_id, datetime)
+VALUES ($1, $2, to_timestamp($3))
+ON CONFLICT DO NOTHING"
+   (list commit
+         (number->string git-branch-id)
+         (date->string datetime "~s"))))
diff --git a/guix-data-service/model/git-repository.scm b/guix-data-service/model/git-repository.scm
index 9db682e..35342f6 100644
--- a/guix-data-service/model/git-repository.scm
+++ b/guix-data-service/model/git-repository.scm
@@ -163,6 +163,8 @@ SELECT id, label, url, cgit_url_base
 FROM git_repositories WHERE id IN (
   SELECT git_repository_id
   FROM git_branches
+  INNER JOIN git_commits
+    ON git_branches.id = git_commits.git_branch_id
   WHERE commit = $1
 )")
 
diff --git a/guix-data-service/model/guix-revision.scm b/guix-data-service/model/guix-revision.scm
index 2ebeacc..40bff02 100644
--- a/guix-data-service/model/guix-revision.scm
+++ b/guix-data-service/model/guix-revision.scm
@@ -95,17 +95,18 @@ INSERT INTO guix_revisions (git_repository_id, commit)
 SELECT guix_revisions.id,
        guix_revisions.commit,
        guix_revisions.git_repository_id,
-       git_branches.datetime
+       git_commits.datetime
 FROM guix_revisions
 INNER JOIN git_branches
-  ON git_branches.commit = guix_revisions.commit
- AND git_branches.git_repository_id = guix_revisions.git_repository_id
+  ON git_branches.git_repository_id = guix_revisions.git_repository_id
+INNER JOIN git_commits
+  ON git_commits.commit = guix_revisions.commit
 INNER JOIN load_new_guix_revision_jobs
   ON load_new_guix_revision_jobs.commit = guix_revisions.commit
 WHERE git_branches.name = $1
-  AND git_branches.datetime <= $2
+  AND git_commits.datetime <= $2
   AND load_new_guix_revision_jobs.succeeded_at IS NOT NULL
-ORDER BY git_branches.datetime DESC
+ORDER BY git_commits.datetime DESC
 LIMIT 1")
 
   (match (exec-query conn query
diff --git a/guix-data-service/model/package-derivation-by-guix-revision-range.scm b/guix-data-service/model/package-derivation-by-guix-revision-range.scm
index 4bcba1b..671771e 100644
--- a/guix-data-service/model/package-derivation-by-guix-revision-range.scm
+++ b/guix-data-service/model/package-derivation-by-guix-revision-range.scm
@@ -19,9 +19,9 @@
   #:use-module (ice-9 match)
   #:use-module (squee)
   #:use-module (guix-data-service database)
+  #:use-module (guix-data-service model git-branch)
   #:use-module (guix-data-service utils)
-  #:export (delete-guix-revision-package-derivation-entries
-            insert-guix-revision-package-derivation-entries
+  #:export (insert-guix-revision-package-derivation-entries
             update-package-derivations-table
             vacuum-package-derivations-table
             rebuild-package-derivations-table))
@@ -47,17 +47,83 @@ WHERE git_repository_id = $1 AND
          branch-name
          guix-revision-id)))
 
-(define* (insert-guix-revision-package-derivation-entries conn
-                                                          git-repository-id
-                                                          branch-name
-                                                          #:key guix-revision-id)
+(define (insert-guix-revision-package-derivation-entries conn
+                                                         git-repository-id
+                                                         branch-name)
+  (define query
+    "
+INSERT INTO package_derivations_by_guix_revision_range
+SELECT DISTINCT
+       $1,
+       packages.name AS package_name,
+       packages.version AS package_version,
+       revision_packages.derivation_id AS derivation_id,
+       revision_packages.system AS system,
+       revision_packages.target AS target,
+       first_value(guix_revisions.id)
+         OVER package_version AS first_guix_revision_id,
+       last_value(guix_revisions.id)
+         OVER package_version AS last_guix_revision_id
+FROM packages
+INNER JOIN (
+  SELECT package_derivations.package_id,
+         package_derivations.derivation_id,
+         systems.system,
+         package_derivations.target,
+         guix_revision_package_derivations.revision_id
+  FROM package_derivations
+  INNER JOIN systems
+    ON package_derivations.system_id = systems.id
+  INNER JOIN guix_revision_package_derivations
+    ON package_derivations.id = guix_revision_package_derivations.package_derivation_id
+) AS revision_packages ON packages.id = revision_packages.package_id
+INNER JOIN guix_revisions
+  ON guix_revisions.git_repository_id = $1
+ AND revision_packages.revision_id = guix_revisions.id
+INNER JOIN git_branches
+  ON git_branches.id = $2
+INNER JOIN git_commits
+  ON git_branches.id = git_commits.git_branch_id
+ AND guix_revisions.commit = git_commits.commit
+WINDOW package_version AS (
+  PARTITION BY git_branches.git_repository_id, git_branches.name,
+               packages.name, packages.version, revision_packages.derivation_id
+  ORDER BY git_commits.datetime
+  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+)
+ORDER BY packages.name, packages.version")
+
+  (let ((git-branch-id (git-branch-for-repository-and-name conn
+                                                           (string->number
+                                                            git-repository-id)
+                                                           branch-name)))
+    (exec-query
+     conn
+     (string-append
+      "
+CREATE TABLE package_derivations_by_guix_revision_range_git_branch_"
+      (number->string git-branch-id) "
+PARTITION OF package_derivations_by_guix_revision_range FOR VALUES IN ("
+      (number->string git-branch-id)
+      ")"))
+
+    (exec-query
+     conn
+     query
+     (list git-repository-id
+           (number->string git-branch-id)))))
+
+(define (insert-guix-revision-package-derivation-entries-for-guix-revision
+         conn
+         git-repository-id
+         branch-name
+         guix-revision-id)
+
   (define query
-    (string-append
-     "
+    "
 INSERT INTO package_derivations_by_guix_revision_range
 SELECT DISTINCT
-       git_branches.git_repository_id,
-       git_branches.name AS branch_name,
+       git_branches.id
        packages.name AS package_name,
        packages.version AS package_version,
        revision_packages.derivation_id AS derivation_id,
@@ -85,35 +151,30 @@ INNER JOIN guix_revisions
  AND revision_packages.revision_id = guix_revisions.id
 INNER JOIN git_branches
   ON git_branches.name = $2
- AND guix_revisions.commit = git_branches.commit
-"
-     (if guix-revision-id
-         "WHERE
-      revision_packages.derivation_id IN (
+INNER JOIN git_commits
+  ON git_branches.id = git_commits.git_branch_id
+ AND guix_revisions.commit = git_commits.commit
+WHERE revision_packages.derivation_id IN (
         SELECT package_derivations.derivation_id
         FROM package_derivations
         INNER JOIN guix_revision_package_derivations
           ON package_derivations.id = guix_revision_package_derivations.package_derivation_id
         WHERE revision_id = $3
-      )"
-         "")
-     "
+      )
 WINDOW package_version AS (
   PARTITION BY git_branches.git_repository_id, git_branches.name,
                packages.name, packages.version, revision_packages.derivation_id
-  ORDER BY git_branches.datetime
+  ORDER BY git_commits.datetime
   RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 )
-ORDER BY packages.name, packages.version"))
+ORDER BY packages.name, packages.version")
 
   (exec-query
    conn
    query
-   `(,git-repository-id
-     ,branch-name
-     ,@(if guix-revision-id
-           (list guix-revision-id)
-           '()))))
+   (list git-repository-id
+         branch-name
+         guix-revision-id)))
 
 (define (update-package-derivations-table conn
                                           git-repository-id
@@ -140,14 +201,21 @@ LOCK TABLE ONLY package_derivations_by_guix_revision_range
       (with-time-logging
           (simple-format #f "inserting package derivation entries for ~A"
                          branch-name)
-        (insert-guix-revision-package-derivation-entries
+        (insert-guix-revision-package-derivation-entries-for-guix-revision
          conn
          git-repository-id
          branch-name
-         #:guix-revision-id guix-revision-id))))
+         guix-revision-id))))
    (exec-query
     conn
-    "SELECT name FROM git_branches WHERE commit = $1 AND git_repository_id = $2"
+    "
+SELECT name
+FROM git_branches
+INNER JOIN git_commits
+  ON git_branches.id = git_commits.git_branch_id
+ AND git_commits.commit = $1
+WHERE git_repository_id = $2
+"
     (list commit git-repository-id)))
 
   #t)
diff --git a/guix-data-service/model/package.scm b/guix-data-service/model/package.scm
index 97deefc..c0b1c40 100644
--- a/guix-data-service/model/package.scm
+++ b/guix-data-service/model/package.scm
@@ -306,26 +306,28 @@ FROM (
                   -- be parameters?
          package_version,
          first_guix_revisions.commit AS first_guix_revision_commit,
-         first_git_branches.datetime AS first_datetime,
+         first_git_commits.datetime AS first_datetime,
          last_guix_revisions.commit AS last_guix_revision_commit,
-         last_git_branches.datetime AS last_datetime
+         last_git_commits.datetime AS last_datetime
   FROM package_derivations_by_guix_revision_range
   INNER JOIN guix_revisions AS first_guix_revisions
     ON first_guix_revision_id = first_guix_revisions.id
-  INNER JOIN git_branches AS first_git_branches
-    ON package_derivations_by_guix_revision_range.branch_name = first_git_branches.name
-   AND first_guix_revisions.git_repository_id = first_git_branches.git_repository_id
-   AND first_guix_revisions.commit = first_git_branches.commit
+  INNER JOIN git_branches
+    ON git_branches.git_repository_id = $2
+   AND git_branches.name = $3
+  INNER JOIN git_commits AS first_git_commits
+    ON first_git_commits.git_branch_id = git_branches.id
+   AND first_guix_revisions.git_repository_id = git_branches.git_repository_id
+   AND first_guix_revisions.commit = first_git_commits.commit
   INNER JOIN guix_revisions AS last_guix_revisions
     ON last_guix_revision_id = last_guix_revisions.id
-  INNER JOIN git_branches AS last_git_branches
-    ON package_derivations_by_guix_revision_range.branch_name = last_git_branches.name
-   AND last_guix_revisions.git_repository_id = last_git_branches.git_repository_id
-   AND last_guix_revisions.commit = last_git_branches.commit
+   AND last_guix_revisions.git_repository_id = git_branches.git_repository_id
+  INNER JOIN git_commits AS last_git_commits
+    ON last_git_commits.git_branch_id = git_branches.id
+   AND last_guix_revisions.commit = last_git_commits.commit
   WHERE package_name = $1
-  AND package_derivations_by_guix_revision_range.git_repository_id = $2
-  AND package_derivations_by_guix_revision_range.branch_name = $3
-  ORDER BY package_version DESC, first_git_branches.datetime ASC
+    AND package_derivations_by_guix_revision_range.git_branch_id = git_branches.id
+  ORDER BY package_version DESC, first_git_commits.datetime ASC
 ) AS data1
 WINDOW version_window AS (
   PARTITION BY data1.package_version
@@ -348,9 +350,9 @@ ORDER BY package_version DESC, first_datetime ASC"
 SELECT package_version,
        derivations.file_name,
        first_guix_revisions.commit AS first_guix_revision_commit,
-       first_git_branches.datetime AS first_datetime,
+       first_git_commits.datetime AS first_datetime,
        last_guix_revisions.commit AS last_guix_revision_commit,
-       last_git_branches.datetime AS last_datetime,
+       last_git_commits.datetime AS last_datetime,
        JSON_AGG(
          json_build_object(
            'build_server_id', builds.build_server_id,
@@ -374,19 +376,19 @@ LEFT OUTER JOIN latest_build_status
   ON builds.id = latest_build_status.build_id
 INNER JOIN guix_revisions AS first_guix_revisions
   ON first_guix_revision_id = first_guix_revisions.id
-INNER JOIN git_branches AS first_git_branches
-  ON first_guix_revisions.git_repository_id = first_git_branches.git_repository_id
- AND first_guix_revisions.commit = first_git_branches.commit
+INNER JOIN git_branches
+  ON git_branches.git_repository_id = $2
+ AND git_branches.name = $3
+INNER JOIN git_commits AS first_git_commits
+  ON first_guix_revisions.commit = first_git_commits.commit
+ AND first_git_commits.git_branch_id = git_branches.id
 INNER JOIN guix_revisions AS last_guix_revisions
   ON last_guix_revision_id = last_guix_revisions.id
-INNER JOIN git_branches AS last_git_branches
-  ON last_guix_revisions.git_repository_id = last_git_branches.git_repository_id
- AND last_guix_revisions.commit = last_git_branches.commit
+INNER JOIN git_commits AS last_git_commits
+  ON last_guix_revisions.commit = last_git_commits.commit
+ AND last_git_commits.git_branch_id = git_branches.id
 WHERE package_name = $1
-AND package_derivations_by_guix_revision_range.git_repository_id = $2
-AND package_derivations_by_guix_revision_range.branch_name = $3
-AND first_git_branches.name = $3
-AND last_git_branches.name = $3
+AND package_derivations_by_guix_revision_range.git_branch_id = git_branches.id
 AND package_derivations_by_guix_revision_range.system = $4
 AND package_derivations_by_guix_revision_range.target = $5
 GROUP BY 1, 2, 3, 4, 5, 6
@@ -459,9 +461,9 @@ FROM (
            derivation_output_details.path,
            derivations_by_output_details_set.derivation_output_details_set_id,
            first_guix_revisions.commit AS first_guix_revision_commit,
-           first_git_branches.datetime AS first_datetime,
+           first_git_commits.datetime AS first_datetime,
            last_guix_revisions.commit AS last_guix_revision_commit,
-           last_git_branches.datetime AS last_datetime
+           last_git_commits.datetime AS last_datetime
     FROM package_derivations_by_guix_revision_range
     INNER JOIN derivations
       ON package_derivations_by_guix_revision_range.derivation_id = derivations.id
@@ -469,24 +471,24 @@ FROM (
       ON derivation_outputs.derivation_id = derivations.id
     INNER JOIN derivation_output_details
       ON derivation_outputs.derivation_output_details_id = derivation_output_details.id
+    INNER JOIN git_branches
+      ON git_branches.git_repository_id = $2
+     AND git_branches.name = $3
     INNER JOIN guix_revisions AS first_guix_revisions
       ON first_guix_revision_id = first_guix_revisions.id
     INNER JOIN derivations_by_output_details_set
       ON derivations_by_output_details_set.derivation_id = derivations.id
-    INNER JOIN git_branches AS first_git_branches
-      ON first_guix_revisions.git_repository_id = first_git_branches.git_repository_id
-     AND first_guix_revisions.commit = first_git_branches.commit
+    INNER JOIN git_commits AS first_git_commits
+      ON first_git_commits.git_branch_id = git_branches.id
+     AND first_guix_revisions.commit = first_git_commits.commit
     INNER JOIN guix_revisions AS last_guix_revisions
       ON last_guix_revision_id = last_guix_revisions.id
-    INNER JOIN git_branches AS last_git_branches
-      ON last_guix_revisions.git_repository_id = last_git_branches.git_repository_id
-     AND last_guix_revisions.commit = last_git_branches.commit
+    INNER JOIN git_commits AS last_git_commits
+      ON last_git_commits.git_branch_id = git_branches.id
+     AND last_guix_revisions.commit = last_git_commits.commit
     WHERE package_name = $1
-    AND package_derivations_by_guix_revision_range.git_repository_id = $2
-    AND package_derivations_by_guix_revision_range.branch_name = $3
+    AND package_derivations_by_guix_revision_range.git_branch_id = git_branches.id
     AND derivation_outputs.name = $4
-    AND first_git_branches.name = $3
-    AND last_git_branches.name = $3
     AND package_derivations_by_guix_revision_range.system = $5
     AND package_derivations_by_guix_revision_range.target = $6
   ) AS data1
diff --git a/guix-data-service/model/system-test.scm b/guix-data-service/model/system-test.scm
index 3a37cd4..fe2fb83 100644
--- a/guix-data-service/model/system-test.scm
+++ b/guix-data-service/model/system-test.scm
@@ -176,7 +176,7 @@ FROM (
         OVER derivation_window AS last_datetime
   FROM (
     SELECT guix_revision_id,
-           git_branches.datetime,
+           git_commits.datetime,
            derivation_id
     FROM guix_revision_system_test_derivations
     INNER JOIN system_tests
@@ -185,10 +185,12 @@ FROM (
       ON guix_revisions.id = guix_revision_id
     INNER JOIN git_branches
       ON guix_revisions.git_repository_id = git_branches.git_repository_id
-     AND git_branches.commit = guix_revisions.commit
+     AND git_branches.name = $3
+    INNER JOIN git_commits
+      ON git_branches.id = git_commits.branch_id
+     AND guix_revisions.commit = git_commits.commit
     WHERE system_tests.name = $1
       AND guix_revisions.git_repository_id = $2
-      AND git_branches.name = $3
       AND system = $4
   ) AS data1
   WINDOW derivation_window AS (
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 <mail@cbaines.net> # Add so
 package_metadata_location_id_index 2021-05-27T19:51:13Z Canan Talayhan <canan.t.talayhan@gmail.com> # Add index for location id
 packages_replacement 2021-04-24T04:52:57Z Christopher Baines <mail@cbaines.net> # Add packages.replacement_package_id
 package_derivations_not_null_hash_index 2021-07-11T14:19:32Z Christopher Baines <mail@cbaines.net> # Add an index to package_derivations
+git_branch_id 2022-05-23T18:11:14Z Chris <chris@felis> # Add git_branch.id
+partition_package_derivations_by_guix_revision_range 2022-05-23T18:20:37Z Chris <chris@felis> # 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;
diff --git a/tests/model-git-branch.scm b/tests/model-git-branch.scm
index 46412bc..1bcc1c3 100644
--- a/tests/model-git-branch.scm
+++ b/tests/model-git-branch.scm
@@ -17,32 +17,10 @@
       conn
       (lambda (conn)
         (let* ((url "test-url")
-               (id (git-repository-url->git-repository-id conn url)))
-          (insert-git-branch-entry conn
-                                   "master"
-                                   "test-commit"
-                                   id
-                                   (current-date)))
-        #t)
-      #:always-rollback? #t))
-
-   (test-assert "insert-git-branch-entry works twice"
-     (with-postgresql-transaction
-      conn
-      (lambda (conn)
-        (let* ((url "test-url")
-               (id (git-repository-url->git-repository-id conn url)))
-          (insert-git-branch-entry conn
-                                   "master"
-                                   "test-commit"
-                                   id
-                                   (current-date))
-          (insert-git-branch-entry conn
-                                   "master"
-                                   "test-commit"
-                                   id
-                                   (current-date)))
-        #t)
+               (git-repository-id
+                (git-repository-url->git-repository-id conn url)))
+          (insert-git-branch-entry conn git-repository-id "master")
+        #t))
       #:always-rollback? #t))))
 
 (test-end)
diff --git a/tests/model-git-commit.scm b/tests/model-git-commit.scm
new file mode 100644
index 0000000..b8bc3d8
--- /dev/null
+++ b/tests/model-git-commit.scm
@@ -0,0 +1,52 @@
+(define-module (test-model-git-commit)
+  #:use-module (srfi srfi-19)
+  #:use-module (srfi srfi-64)
+  #:use-module (guix-data-service database)
+  #:use-module (guix-data-service model git-repository)
+  #:use-module (guix-data-service model git-branch)
+  #:use-module (guix-data-service model git-commit))
+
+(test-begin "test-model-git-commit")
+
+(with-postgresql-connection
+ "test-module-git-commit"
+ (lambda (conn)
+   (check-test-database! conn)
+
+   (test-assert "insert-git-commit-entry works"
+     (with-postgresql-transaction
+      conn
+      (lambda (conn)
+        (let* ((url "test-url")
+               (git-repository-id
+                (git-repository-url->git-repository-id conn url))
+               (git-branch-id
+                (insert-git-branch-entry conn git-repository-id "master")))
+          (insert-git-commit-entry conn
+                                   git-branch-id
+                                   "test-commit"
+                                   (current-date)))
+        #t)
+      #:always-rollback? #t))
+
+   (test-assert "insert-git-commit-entry works twice"
+     (with-postgresql-transaction
+      conn
+      (lambda (conn)
+        (let* ((url "test-url")
+               (git-repository-id
+                (git-repository-url->git-repository-id conn url))
+               (git-branch-id
+                (insert-git-branch-entry conn git-repository-id "master")))
+          (insert-git-commit-entry conn
+                                   git-branch-id
+                                   "test-commit"
+                                   (current-date))
+          (insert-git-commit-entry conn
+                                   git-branch-id
+                                   "test-commit"
+                                   (current-date)))
+        #t)
+      #:always-rollback? #t))))
+
+(test-end)