Rebalance without transaction (!68746) · Merge requests · GitLab.org / GitLab

Alexandru Croitor requested to merge 329357-move-rebalancing-outside-transaction into master Aug 23, 2021

What does this MR do?

Moves issues positions rebalancing outside of a single transaction for the entire rebalancing.

re https://gitlab.com/gitlab-org/gitlab/-/issues/329357

Database

Queries to fetch issue ids to be cached

Fetch 10K issues from project:

   SELECT "issues"."id", "issues"."relative_position" FROM "issues" WHERE "issues"."project_id" = 29003858 ORDER BY 
   relative_position asc NULLS LAST, "issues"."id" ASC LIMIT 10000;

  Limit  (cost=0.57..12.33 rows=273 width=8) (actual time=0.093..0.214 rows=2 loops=1)
    Buffers: shared hit=6 read=1
    I/O Timings: read=0.113
    ->  Index Only Scan using idx_issues_on_project_id_and_rel_asc_and_id on issues  (cost=0.57..12.33 rows=273 width=8) (actual time=0.090..0.211 rows=2 loops=1)
          Index Cond: (project_id = 29003858)
          Heap Fetches: 1
          Buffers: shared hit=6 read=1
          I/O Timings: read=0.113
  Planning Time: 181.407 ms
  Execution Time: 0.260 ms
 (10 rows)

Keyset pagination for projects that have more than 10K issues:


  SELECT
      "issues".*
  FROM ((
          SELECT
              "issues"."id",
              "issues"."relative_position"
          FROM
              "issues"
          WHERE
              "issues"."project_id" = 29003858
              AND 178180803 IS NULL
              AND "issues"."relative_position" IS NULL
              AND "issues"."id" > 92338178
          ORDER BY
              relative_position ASC NULLS LAST,
              "issues"."id" ASC
          LIMIT 10000)
  UNION ALL (
      SELECT
          "issues"."id",
          "issues"."relative_position"
      FROM
          "issues"
      WHERE
          "issues"."project_id" = 29003858
          AND 178180803 IS NOT NULL
          AND "issues"."relative_position" IS NULL
      ORDER BY
          relative_position ASC NULLS LAST,
          "issues"."id" ASC
      LIMIT 10000)
  UNION ALL (
      SELECT
          "issues"."id",
          "issues"."relative_position"
      FROM
          "issues"
      WHERE
          "issues"."project_id" = 29003858
          AND 178180803 IS NOT NULL
          AND "issues"."relative_position" > 178180803
      ORDER BY
          relative_position ASC NULLS LAST,
          "issues"."id" ASC
      LIMIT 10000)
  UNION ALL (
      SELECT
          "issues"."id",
          "issues"."relative_position"
      FROM
          "issues"
      WHERE
          "issues"."project_id" = 29003858
          AND 178180803 IS NOT NULL
          AND "issues"."relative_position" = 178180803
          AND "issues"."id" > 92338178
      ORDER BY
          relative_position ASC NULLS LAST,
          "issues"."id" ASC
      LIMIT 10000)) issues
  ORDER BY
      relative_position ASC NULLS LAST,
      "issues"."id" ASC
  LIMIT 10000;
  
   Limit  (cost=4.79..21.22 rows=205 width=8) (actual time=0.605..0.608 rows=0 loops=1)
     Buffers: shared hit=8 read=4
     I/O Timings: read=0.481
     ->  Merge Append  (cost=4.79..21.22 rows=205 width=8) (actual time=0.603..0.606 rows=0 loops=1)
           Sort Key: relative_position, id
           Buffers: shared hit=8 read=4
           I/O Timings: read=0.481
           ->  Limit  (cost=0.01..0.02 rows=1 width=8) (actual time=0.009..0.010 rows=0 loops=1)
                 ->  Sort  (cost=0.01..0.02 rows=0 width=8) (actual time=0.009..0.009 rows=0 loops=1)
                       Sort Key: relative_position, id
                       Sort Method: quicksort  Memory: 25kB
                       ->  Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                             One-Time Filter: false
           ->  Limit  (cost=0.57..7.25 rows=122 width=8) (actual time=0.572..0.572 rows=0 loops=1)
                 Buffers: shared read=4
                 I/O Timings: read=0.481
                 ->  Index Only Scan using idx_issues_on_project_id_and_rel_asc_and_id on issues  (cost=0.57..7.25 rows=122 width=8) (actual time=0.571..0.571 rows=0 loops=1)
                       Index Cond: ((project_id = 29003858) AND (relative_position IS NULL))
                       Heap Fetches: 0
                       Buffers: shared read=4
                       I/O Timings: read=0.481
           ->  Limit  (cost=0.57..5.19 rows=81 width=8) (actual time=0.009..0.009 rows=0 loops=1)
                 Buffers: shared hit=4
                 ->  Index Only Scan using idx_issues_on_project_id_and_rel_asc_and_id on issues issues_1  (cost=0.57..5.19 rows=81 width=8) (actual time=0.009..0.009 rows=0 loops=1)
                       Index Cond: ((project_id = 29003858) AND (relative_position > 178180803))
                       Heap Fetches: 0
                       Buffers: shared hit=4
           ->  Sort  (cost=3.61..3.61 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=1)
                 Sort Key: issues_2.relative_position, issues_2.id
                 Sort Method: quicksort  Memory: 25kB
                 Buffers: shared hit=4
                 ->  Limit  (cost=0.57..3.59 rows=1 width=8) (actual time=0.005..0.006 rows=0 loops=1)
                       Buffers: shared hit=4
                       ->  Index Only Scan using idx_issues_on_project_id_and_rel_asc_and_id on issues issues_2  (cost=0.57..3.59 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                             Index Cond: ((project_id = 29003858) AND (relative_position = 178180803) AND (id > 92338178))
                             Heap Fetches: 0
                             Buffers: shared hit=4
   Planning Time: 60.538 ms
   Execution Time: 0.680 ms
  (39 rows)

Query to update positions

SELECT *
 FROM (VALUES (77189145, -329794362), (87038514, -329793336), (87815823, -329792310), (87039707, -329791284), (87250858, -329790258), (78521201, -329789232), (86285883, -329788206), (88384977, -329787180), (86612548, -329786154), (85208749, -329785128), (73882344, -329784102), (88385099, -329783076), (82118182, -329782050), (85208745, -329781024), (80943865, -329779998), (82130203, -329778972), (84641107, -329777946), (82130223, -329776920), (79811294, -329775894), (88385191, -329774868), (80259042, -329773842), (80262728, -329772816), (81196260, -329771790), (78431222, -329770764), (88385259, -329769738), (81196234, -329768712), (77572067, -329767686), (88385296, -329766660), (81196212, -329765634), (77572094, -329764608), (88385402, -329763582), (81196223, -329762556), (81185495, -329761530), (73824169, -329760504), (88385465, -329759478), (81196261, -329758452), (73882372, -329757426), (78457270, -329756400), (77572017, -329755374), (88385519, -329754348), (80259415, -329753322), (79408930, -329752296), (80258854, -329751270), (79560446, -329750244), (80258778, -329749218), (79560687, -329748192), (80259139, -329747166), (78457267, -329746140), (77613458, -329745114), (88385600, -329744088), (78457262, -329743062), (77572343, -329742036), (87039988, -329741010), (87816261, -329739984), (78457260, -329738958), (80259302, -329737932), (80259214, -329736906), (75730964, -329735880), (80205706, -329734854), (78822927, -329733828), (79245939, -329732802), (73823768, -329731776), (88393105, -329730750), (80208978, -329729724), (73823784, -329728698), (87816397, -329727672), (88393150, -329726646), (80208973, -329725620), (74686021, -329724594), (87039993, -329723568), (87816500, -329722542), (80208982, -329721516), (74759613, -329720490), (87816545, -329719464), (80208979, -329718438), (76602748, -329717412), (77660670, -329716386), (78440423, -329715360), (75585087, -329714334), (80203498, -329713308), (75785193, -329712282), (78277971, -329711256), (76080348, -329710230), (79903271, -329709204), (76385229, -329708178), (76822572, -329707152), (80203592, -329706126), (75584696, -329705100), (80203600, -329704074), (73882377, -329703048), (73944672, -329702022), (73944662, -329700996), (73945163, -329699970), (73934172, -329698944), (76052189, -329697918), (73945174, -329696892), (74997622, -329695866), (74254023, -329694840), (73945178, -329693814), (73945789, -329692788)) as t (id, pos)
)
UPDATE issues
SET relative_position = cte.new_pos
FROM cte
WHERE cte_id = id

Screenshots or Screencasts (strongly suggested)

How to setup and validate locally (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

I have included changelog trailers, or none are needed. (Does this MR need a changelog?)
I have added/updated documentation, or it's not needed. (Is documentation required?)
I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?)
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?)
I have self-reviewed this MR per code review guidelines.
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines)
I have followed the style guides.
This change is backwards compatible across updates, or this does not apply.

Availability and Testing

I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.)
I have tested this MR in all supported browsers, or it's not needed.
I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Related to #329357

Edited Aug 25, 2021 by Alexandru Croitor

Rebalance without transaction