From 58368b10fb37cfcd56738324f90942a8fd8e3793 Mon Sep 17 00:00:00 2001
From: Tom Hughes <tom@compton.nu>
Date: Sun, 27 Aug 2023 19:07:39 +0100
Subject: [PATCH] Run a monthly reindex of the smaller database tables

---
 .../db/files/default/monthly-reindex.sql      | 35 +++++++++++++++++++
 cookbooks/db/recipes/master.rb                | 23 ++++++++++++
 cookbooks/db/recipes/slave.rb                 |  4 +++
 3 files changed, 62 insertions(+)
 create mode 100644 cookbooks/db/files/default/monthly-reindex.sql

diff --git a/cookbooks/db/files/default/monthly-reindex.sql b/cookbooks/db/files/default/monthly-reindex.sql
new file mode 100644
index 000000000..fc9bc7675
--- /dev/null
+++ b/cookbooks/db/files/default/monthly-reindex.sql
@@ -0,0 +1,35 @@
+REINDEX (VERBOSE) TABLE CONCURRENTLY acls;
+REINDEX (VERBOSE) TABLE CONCURRENTLY active_storage_attachments;
+REINDEX (VERBOSE) TABLE CONCURRENTLY active_storage_blobs;
+REINDEX (VERBOSE) TABLE CONCURRENTLY active_storage_variant_records;
+REINDEX (VERBOSE) TABLE CONCURRENTLY ar_internal_metadata;
+REINDEX (VERBOSE) TABLE CONCURRENTLY changeset_comments;
+REINDEX (VERBOSE) TABLE CONCURRENTLY changeset_tags;
+REINDEX (VERBOSE) TABLE CONCURRENTLY changesets;
+REINDEX (VERBOSE) TABLE CONCURRENTLY changesets_subscribers;
+REINDEX (VERBOSE) TABLE CONCURRENTLY client_applications;
+REINDEX (VERBOSE) TABLE CONCURRENTLY current_relation_members;
+REINDEX (VERBOSE) TABLE CONCURRENTLY delayed_jobs;
+REINDEX (VERBOSE) TABLE CONCURRENTLY diary_comments;
+REINDEX (VERBOSE) TABLE CONCURRENTLY diary_entries;
+REINDEX (VERBOSE) TABLE CONCURRENTLY diary_entry_subscriptions;
+REINDEX (VERBOSE) TABLE CONCURRENTLY friends;
+REINDEX (VERBOSE) TABLE CONCURRENTLY issue_comments;
+REINDEX (VERBOSE) TABLE CONCURRENTLY issues;
+REINDEX (VERBOSE) TABLE CONCURRENTLY languages;
+REINDEX (VERBOSE) TABLE CONCURRENTLY messages;
+REINDEX (VERBOSE) TABLE CONCURRENTLY note_comments;
+REINDEX (VERBOSE) TABLE CONCURRENTLY notes;
+REINDEX (VERBOSE) TABLE CONCURRENTLY oauth_access_grants;
+REINDEX (VERBOSE) TABLE CONCURRENTLY oauth_access_tokens;
+REINDEX (VERBOSE) TABLE CONCURRENTLY oauth_applications;
+REINDEX (VERBOSE) TABLE CONCURRENTLY oauth_nonces;
+REINDEX (VERBOSE) TABLE CONCURRENTLY oauth_tokens;
+REINDEX (VERBOSE) TABLE CONCURRENTLY redactions;
+REINDEX (VERBOSE) TABLE CONCURRENTLY reports;
+REINDEX (VERBOSE) TABLE CONCURRENTLY schema_migrations;
+REINDEX (VERBOSE) TABLE CONCURRENTLY user_blocks;
+REINDEX (VERBOSE) TABLE CONCURRENTLY user_preferences;
+REINDEX (VERBOSE) TABLE CONCURRENTLY user_roles;
+REINDEX (VERBOSE) TABLE CONCURRENTLY user_tokens;
+REINDEX (VERBOSE) TABLE CONCURRENTLY users;
diff --git a/cookbooks/db/recipes/master.rb b/cookbooks/db/recipes/master.rb
index fe9c4cacf..905d7a221 100644
--- a/cookbooks/db/recipes/master.rb
+++ b/cookbooks/db/recipes/master.rb
@@ -83,3 +83,26 @@ postgresql_extension "btree_gist" do
   database "openstreetmap"
   only_if { node[:postgresql][:clusters][node[:db][:cluster]] && node[:postgresql][:clusters][node[:db][:cluster]][:version] >= 9.0 }
 end
+
+cookbook_file "/usr/local/share/monthly-reindex.sql" do
+  owner "root"
+  group "root"
+  mode "644"
+end
+
+systemd_service "monthly-reindex" do
+  description "Monthly database reindex"
+  exec_start "/usr/bin/psql -f /usr/local/share/monthly-reindex.sql openstreetmap"
+  user "postgres"
+  sandbox true
+  restrict_address_families "AF_UNIX"
+end
+
+systemd_timer "monthly-reindex" do
+  description "Monthly database reindex"
+  on_calendar "Sun *-*-1..7 02:00"
+end
+
+service "monthly-reindex.timer" do
+  action [:enable, :start]
+end
diff --git a/cookbooks/db/recipes/slave.rb b/cookbooks/db/recipes/slave.rb
index 70dc970e7..f882db614 100644
--- a/cookbooks/db/recipes/slave.rb
+++ b/cookbooks/db/recipes/slave.rb
@@ -18,3 +18,7 @@
 #
 
 include_recipe "db::base"
+
+service "monthly-reindex.timer" do
+  action [:disable, :stop]
+end
-- 
2.45.1