01/02: Vacuum the package derivations table after each job

[Christopher Baines]

cbaines pushed a commit to branch master
in repository data-service.

commit e07b3291e84b9c25b5183e5b3332f1540d3fc6b4
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Tue Nov 17 20:47:16 2020 +0000

    Vacuum the package derivations table after each job
    
    As each job deletes lots of rows in the table, vacuum it to free up space 
for
    the next job.
    
    I don't think this is something to do widely, but I wanted to try it out for
    the tables which have a lot of churn.
---
 guix-data-service/jobs/load-new-guix-revision.scm                   | 5 +++++
 .../model/package-derivation-by-guix-revision-range.scm             | 6 ++++++
 2 files changed, 11 insertions(+)

diff --git a/guix-data-service/jobs/load-new-guix-revision.scm 
b/guix-data-service/jobs/load-new-guix-revision.scm
index 596891b..398f1cb 100644
--- a/guix-data-service/jobs/load-new-guix-revision.scm
+++ b/guix-data-service/jobs/load-new-guix-revision.scm
@@ -1758,6 +1758,11 @@ SKIP LOCKED")
               (record-job-succeeded conn id)
               (record-job-event conn id "success")
               (exec-query conn "COMMIT")
+
+              (with-time-logging
+                  "vacuuming package derivations by guix revision range table"
+                (vacuum-package-derivations-table conn))
+
               #t)
             (begin
               (exec-query conn "ROLLBACK")
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 32e91b9..c6832ac 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
@@ -23,6 +23,7 @@
   #:export (delete-guix-revision-package-derivation-entries
             insert-guix-revision-package-derivation-entries
             update-package-derivations-table
+            vacuum-package-derivations-table
             rebuild-package-derivations-table))
 
 (define (delete-guix-revision-package-derivation-entries conn
@@ -149,6 +150,11 @@ LOCK TABLE ONLY package_derivations_by_guix_revision_range
 
   #t)
 
+(define (vacuum-package-derivations-table conn)
+  (exec-query
+   conn
+   "VACUUM package_derivations_by_guix_revision_range"))
+
 (define (rebuild-package-derivations-table conn)
   (with-postgresql-transaction
    conn