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 onpush_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 Eventcompatible 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 -
RemoveEvent#commits/PushEvent#commitsonce it's no longer in use (after taking care of the above)-
Event#commitshas to stay until all existing events have been processed
-
-
Check if we can simply drop events.datain 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.datais only used by push events
- Confirmed as of July 25th, 2017:
-
Verify the API output for push events
Database Checklist
When adding migrations:
-
Updated db/schema.rb -
Added a downmethod 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/migrationsif 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 ANALYZEand 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 msThis 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 0This 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 msSo by using JOIN LATERAL we can query the data 66x faster.
Edited by Yorick Peterse