From a5c8a52782ae6e948adbbba77c0ec702ffe28ae1 Mon Sep 17 00:00:00 2001
From: Yorick Peterse <yorickpeterse@gmail.com>
Date: Wed, 9 Aug 2017 15:49:30 +0200
Subject: Better caching and indexing of broadcast messages

Caching of BroadcastMessage instances has been changed so a cache stays
valid as long as the default cache expiration time permits, instead of
the cache being expired after 1 minute. When modifying broadcast
messages the cache is flushed automatically.

To remove the need for performing sequence scans on the
"broadcast_messages" table we also add an index on (starts_at, ends_at,
id), permitting PostgreSQL to use an index scan to get all necessary
data.

Finally this commit adds a few NOT NULL constraints to the table to
match the Rails validations.

Fixes gitlab-org/gitlab-ce#31706
---
 .../20170809133343_add_broadcast_messages_index.rb  | 21 +++++++++++++++++++++
 ...34_add_broadcast_message_not_null_constraints.rb | 17 +++++++++++++++++
 db/schema.rb                                        | 12 +++++++-----
 3 files changed, 45 insertions(+), 5 deletions(-)
 create mode 100644 db/migrate/20170809133343_add_broadcast_messages_index.rb
 create mode 100644 db/migrate/20170809134534_add_broadcast_message_not_null_constraints.rb

(limited to 'db')

diff --git a/db/migrate/20170809133343_add_broadcast_messages_index.rb b/db/migrate/20170809133343_add_broadcast_messages_index.rb
new file mode 100644
index 00000000000..4ab2ddb059d
--- /dev/null
+++ b/db/migrate/20170809133343_add_broadcast_messages_index.rb
@@ -0,0 +1,21 @@
+# See http://doc.gitlab.com/ce/development/migration_style_guide.html
+# for more information on how to write migrations for GitLab.
+
+class AddBroadcastMessagesIndex < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  # Set this constant to true if this migration requires downtime.
+  DOWNTIME = false
+
+  disable_ddl_transaction!
+
+  COLUMNS = %i[starts_at ends_at id].freeze
+
+  def up
+    add_concurrent_index :broadcast_messages, COLUMNS
+  end
+
+  def down
+    remove_concurrent_index :broadcast_messages, COLUMNS
+  end
+end
diff --git a/db/migrate/20170809134534_add_broadcast_message_not_null_constraints.rb b/db/migrate/20170809134534_add_broadcast_message_not_null_constraints.rb
new file mode 100644
index 00000000000..13e8ef52f22
--- /dev/null
+++ b/db/migrate/20170809134534_add_broadcast_message_not_null_constraints.rb
@@ -0,0 +1,17 @@
+# See http://doc.gitlab.com/ce/development/migration_style_guide.html
+# for more information on how to write migrations for GitLab.
+
+class AddBroadcastMessageNotNullConstraints < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  # Set this constant to true if this migration requires downtime.
+  DOWNTIME = false
+
+  COLUMNS = %i[starts_at ends_at created_at updated_at message_html]
+
+  def change
+    COLUMNS.each do |column|
+      change_column_null :broadcast_messages, column, false
+    end
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 65afe37a21c..9d3b3ad1826 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -163,16 +163,18 @@ ActiveRecord::Schema.define(version: 20170809142252) do
 
   create_table "broadcast_messages", force: :cascade do |t|
     t.text "message", null: false
-    t.datetime "starts_at"
-    t.datetime "ends_at"
-    t.datetime "created_at"
-    t.datetime "updated_at"
+    t.datetime "starts_at", null: false
+    t.datetime "ends_at", null: false
+    t.datetime "created_at", null: false
+    t.datetime "updated_at", null: false
     t.string "color"
     t.string "font"
-    t.text "message_html"
+    t.text "message_html", null: false
     t.integer "cached_markdown_version"
   end
 
+  add_index "broadcast_messages", ["starts_at", "ends_at", "id"], name: "index_broadcast_messages_on_starts_at_and_ends_at_and_id", using: :btree
+
   create_table "chat_names", force: :cascade do |t|
     t.integer "user_id", null: false
     t.integer "service_id", null: false
-- 
cgit v1.2.1