Use a separate table for storing push events

This MR adds a table called push_event_payloads and migrates push events data to this new table.

Events Checklist

• Index (project_id, created_at), then remove the standalone index on push_events.project_id
• Add a background migration to migrate a single row to the new format
• Add a post-deployment migration that schedules migrations for all existing push events
• Adjust the code so that creating push events uses PushEventPayload
• Adjust Atom feeds to show push events using the new format, and make Event compatible with this so we can show old data at the same time
• Adjust the UI to show push events using the new format
• Adjust the event queries to use a LATERAL JOIN on PostgreSQL to more efficiently get the data
• Remove Event#commits / PushEvent#commits once it's no longer in use (after taking care of the above)
  • Event#commits has to stay until all existing events have been processed
• Check if we can simply drop events.data in the next release since it seems to only be used by push events. This would remove the need for updating events, thus reducing table bloat (and subsequent DB load)
  • Confirmed as of July 25th, 2017: events.data is only used by push events
• Verify the API output for push events

Database Checklist

When adding migrations:

• Updated db/schema.rb
• Added a down method so the migration can be reverted
• Added the output of the migration(s) to the MR body
• Added the execution time of the migration(s) to the MR body
• Added tests for the migration in spec/migrations if necessary (e.g. when migrating data)
• Made sure the migration won't interfere with a running GitLab cluster, for example by disabling transactions for long running migrations

When adding or modifying queries:

• Included the raw SQL queries of the relevant queries
• Included the output of EXPLAIN ANALYZE and execution timings of the relevant queries
• Added tests for the relevant changes

When adding tables:

• Ordered columns based on their type sizes in descending order
• Added foreign keys if necessary
• Added indexes if necessary

General Checklist

• Changelog entry added, if necessary
• Documentation created/updated
• Tests added for this feature/bug
• Conform by the merge request performance guides
• Conform by the style guides
• Squashed related commits together

Migration Output

From my personal staging environment, which uses the same setup as production:

== 20170608152747 PrepareEventsTableForPushEventsMigration: migrating =========                                                                               
-- create_table(:events_for_migration)                                                                                                                        
   -> 0.2035s                                                                  
-- transaction_open?()                                                         
   -> 0.0000s                                                                  
-- execute("SET statement_timeout TO 0")                                       
   -> 0.0007s                                                                  
-- execute("ALTER TABLE events_for_migration\nADD CONSTRAINT fk_edfd187b6f\nFOREIGN KEY (author_id)\nREFERENCES users (id)\nON DELETE cascade\nNOT VALID;\n") 
   -> 0.0089s                                                                  
-- execute("ALTER TABLE events_for_migration VALIDATE CONSTRAINT fk_edfd187b6f;")                                                                             
   -> 0.0037s                                                                  
== 20170608152747 PrepareEventsTableForPushEventsMigration: migrated (0.2174s

== 20170608152748 CreatePushEventPayloadsTables: migrating ====================
-- create_table(:push_event_payloads, {:id=>false})
   -> 0.0227s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- execute("ALTER TABLE push_event_payloads\nADD CONSTRAINT fk_36c74129da\nFOREIGN KEY (event_id)\nREFERENCES events_for_migration (id)\nON DELETE cascade\nNOT VALID;\n")
   -> 0.0031s
-- execute("ALTER TABLE push_event_payloads VALIDATE CONSTRAINT fk_36c74129da;")
   -> 0.0032s
== 20170608152748 CreatePushEventPayloadsTables: migrated (0.0298s) ===========

== 20170627101016 ScheduleEventMigrations: migrating ==========================
== 20170627101016 ScheduleEventMigrations: migrated (278.5996s) ===============

== 20170727123534 AddIndexOnEventsProjectIdId: migrating ======================                                                                               
-- index_exists?(:events, [:project_id, :id])                                  
   -> 0.0068s                                                                  
-- transaction_open?()                                                         
   -> 0.0000s                                                                  
-- execute("SET statement_timeout TO 0")                                       
   -> 0.0005s                                                                  
-- add_index(:events, [:project_id, :id], {:algorithm=>:concurrently})         
   -> 589.0104s                                                                
-- index_exists?(:events, :project_id)                                         
   -> 0.0077s                                                                  
-- transaction_open?()                                                         
   -> 0.0000s                                                                  
-- select_one("SELECT current_setting('server_version_num') AS v")             
   -> 0.0010s                                                                  
-- execute("SET statement_timeout TO 0")                                       
   -> 0.0005s                                                                  
-- remove_index(:events, {:algorithm=>:concurrently, :column=>:project_id})    
   -> 0.2620s                                                                  
-- index_exists?(:events_for_migration, [:project_id, :id])                    
   -> 0.0047s                                                                  
-- transaction_open?()                                                         
   -> 0.0000s                                                                  
-- execute("SET statement_timeout TO 0")                                       
   -> 0.0005s                                                                  
-- add_index(:events_for_migration, [:project_id, :id], {:algorithm=>:concurrently})                                                                          
   -> 0.0164s                                                                  
-- index_exists?(:events_for_migration, :project_id)                           
   -> 0.0054s                                                                  
-- transaction_open?()                                                         
   -> 0.0000s                                                                  
-- select_one("SELECT current_setting('server_version_num') AS v")             
   -> 0.0005s                                                                  
-- execute("SET statement_timeout TO 0")                                       
   -> 0.0005s                                                                  
-- remove_index(:events_for_migration, {:algorithm=>:concurrently, :column=>:project_id})                                                                     
   -> 0.1220s                                                                  
== 20170727123534 AddIndexOnEventsProjectIdId: migrated (589.4401s) ===========                            

Migration Timings

Migration	Duration
PrepareEventsTableForPushEventsMigration	0.2 sec
CreatePushEventPayloadsTables	0.2 sec
ScheduleEventMigrations	278.5 seconds (4.6 minutes)
AddIndexOnEventsProjectIdId	589.4 sec (9.8 minutes)

Query Plans

This query is used to get events using a JOIN LATERAL when supported (PostgreSQL 9.3 or newer):

SELECT  "events".* 
FROM (
  SELECT "projects"."id" 
  FROM "projects" 
  INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" 
  WHERE "project_authorizations"."user_id" = 1
) projects_for_lateral 
JOIN LATERAL (
  SELECT  "events".* 
  FROM "events" 
  WHERE (events.project_id = projects_for_lateral.id)  
  ORDER BY "events"."id" DESC 
  LIMIT 20
) AS events ON true 
ORDER BY "events"."id" DESC 
LIMIT 20 
OFFSET 0;

The plan for this query is:

                                                                                                       QUERY PLAN                                                                                                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2825.33..2825.38 rows=20 width=1875) (actual time=12.069..12.079 rows=20 loops=1)
   ->  Sort  (cost=2825.33..2828.93 rows=1440 width=1875) (actual time=12.068..12.074 rows=20 loops=1)
         Sort Key: events.id DESC
         Sort Method: top-N heapsort  Memory: 36kB
         ->  Nested Loop  (cost=1.43..2787.02 rows=1440 width=1875) (actual time=0.041..10.669 rows=3631 loops=1)
               ->  Nested Loop  (cost=0.86..186.63 rows=72 width=4) (actual time=0.028..1.677 rows=299 loops=1)
                     ->  Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations  (cost=0.43..5.69 rows=72 width=4) (actual time=0.020..0.113 rows=299 loops=1)
                           Index Cond: (user_id = 1)
                           Heap Fetches: 15
                     ->  Index Only Scan using projects_pkey on projects  (cost=0.43..2.50 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=299)
                           Index Cond: (id = project_authorizations.project_id)
                           Heap Fetches: 3
               ->  Limit  (cost=0.57..35.72 rows=20 width=1875) (actual time=0.008..0.025 rows=12 loops=299)
                     ->  Index Scan Backward using index_events_on_project_id_and_id on events  (cost=0.57..2165.74 rows=1232 width=1875) (actual time=0.007..0.022 rows=12 loops=299)
                           Index Cond: (project_id = projects.id)
 Planning time: 0.436 ms
 Execution time: 12.122 ms

This is much better compared to the fallback query used for MySQL / PostgreSQL 9.2 (and is basically what we currently use):

SELECT  "events".* 
FROM "events" 
WHERE (
  EXISTS (
    SELECT 1 
    FROM "projects" 
    INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" 
    WHERE "project_authorizations"."user_id" = 1 
    AND (projects.id = events.project_id)
  )
) 
ORDER BY "events"."id" DESC 
LIMIT 20 
OFFSET 0

This produces plan:

                                                                                                          QUERY PLAN                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=5455.33..5455.38 rows=20 width=1875) (actual time=1587.420..1587.433 rows=20 loops=1)
   ->  Sort  (cost=5455.33..5677.15 rows=88726 width=1875) (actual time=1587.419..1587.426 rows=20 loops=1)
         Sort Key: events.id DESC
         Sort Method: top-N heapsort  Memory: 36kB
         ->  Nested Loop  (cost=187.38..3094.37 rows=88726 width=1875) (actual time=1.541..1403.954 rows=583589 loops=1)
               ->  HashAggregate  (cost=186.81..187.53 rows=72 width=8) (actual time=1.518..1.678 rows=299 loops=1)
                     Group Key: projects.id
                     ->  Nested Loop  (cost=0.86..186.63 rows=72 width=8) (actual time=0.024..1.433 rows=299 loops=1)
                           ->  Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations  (cost=0.43..5.69 rows=72 width=4) (actual time=0.017..0.101 rows=299 loops=1)
                                 Index Cond: (user_id = 1)
                                 Heap Fetches: 15
                           ->  Index Only Scan using projects_pkey on projects  (cost=0.43..2.50 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=299)
                                 Index Cond: (id = project_authorizations.project_id)
                                 Heap Fetches: 3
               ->  Index Scan using index_events_on_project_id_and_id on events  (cost=0.57..28.05 rows=1232 width=1875) (actual time=0.011..3.901 rows=1952 loops=299)
                     Index Cond: (project_id = projects.id)
 Planning time: 0.664 ms
 Execution time: 1587.500 ms

So by using JOIN LATERAL we can query the data 66x faster.

app/controllers/dashboard/projects_controller.rb

@@ -50,8 +50,10 @@ def load_projects(finder_params)
   end
 
   def load_events
-    @events = Event.in_projects(load_projects(params.merge(non_public: true)))
-    @events = event_filter.apply_filter(@events).with_associations
-    @events = @events.limit(20).offset(params[:offset] || 0)
+    projects = load_projects(params.merge(non_public: true))
+
+    @events = EventCollection
+      .new(projects, offset: params[:offset].to_i, filter: event_filter)
+      .to_a
   end
 end