Skip to content

Extend Gitlab.com DuoPro active trials to 60 days

Qingyu Zhao requested to merge cdot-9483-extend-gitlabcom-duoprotrial-to-60days into master

What does this MR do and why?

Part of issue https://gitlab.com/gitlab-org/customers-gitlab-com/-/issues/9483+

⚠ please hold on the merge until we get confirm https://gitlab.slack.com/archives/C01BPM66KNE/p1714510243671959?thread_ts=1714429178.301379&cid=C01BPM66KNE. Thanks.

Extend Gitlab.com DuoPro active trials to 60 days

Gitlab.com DuoPro trials currently is 30-days duration. Many customers request a longer trial duration. This migration update the active Gitlab.com trials expires_on to 60 days after created date

OLD Raw query - can only be used to estimate the number of records in production

This is the raw query on my local development environment, where the subscription_add_on_id is 1. In production, the subscription_add_on_id will be the value of SELECT "subscription_add_ons"."id" FROM "subscription_add_ons" WHERE "subscription_add_ons"."name" = 1 LIMIT 1

SELECT "subscription_add_on_purchases".* FROM "subscription_add_on_purchases" WHERE "subscription_add_on_purchases"."subscription_add_on_id" = 1 AND "subscription_add_on_purchases"."trial" = TRUE AND (expires_on >= '2024-05-01')
OLD Query plan - can only be used to estimate the number of records in production

The full query is SELECT "subscription_add_on_purchases".* FROM "subscription_add_on_purchases" WHERE "subscription_add_on_purchases"."subscription_add_on_id" = (SELECT "subscription_add_ons"."id" FROM "subscription_add_ons" WHERE "subscription_add_ons"."name" = 1 LIMIT 1) AND "subscription_add_on_purchases"."trial" = TRUE AND (expires_on >= '2024-05-01')

NOTE: to get the subscription_add_on_id from production, we use a sub-query (SELECT "subscription_add_ons"."id" FROM "subscription_add_ons" WHERE "subscription_add_ons"."name" = 1 LIMIT 1)

The full execution plan is at https://console.postgres.ai/gitlab/gitlab-production-main/sessions/28000/commands/87156.

There are 638 active DuoPro trial records in production as of 2024-05-01

 Seq Scan on public.subscription_add_on_purchases  (cost=3.14..148.34 rows=650 width=75) (actual time=2.647..8.278 rows=638 loops=1)
   Filter: (subscription_add_on_purchases.trial AND (subscription_add_on_purchases.expires_on >= '2024-05-01'::date) AND (subscription_add_on_purchases.subscription_add_on_id = $0))
   Rows Removed by Filter: 243
   Buffers: shared hit=3 read=35 dirtied=3
   I/O Timings: read=5.399 write=0.000
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.12..3.14 rows=1 width=8) (actual time=0.738..0.739 rows=1 loops=1)
           Buffers: shared hit=3 read=2
           I/O Timings: read=0.692 write=0.000
           ->  Index Scan using index_subscription_add_ons_on_name on public.subscription_add_ons  (cost=0.12..3.14 rows=1 width=8) (actual time=0.736..0.737 rows=1 loops=1)
                 Index Cond: (subscription_add_ons.name = 1)
                 Buffers: shared hit=3 read=2
                 I/O Timings: read=0.692 write=0.000

Raw Query. As the updated version(after review) are almost using raw SQL, we can see the raw query from the below Migrate up output section

Query plan

The 2 raw SQL from migrate output has the below query plan

  1. SELECT COUNT(*) AS count FROM "subscription_add_on_purchases" WHERE "subscription_add_on_purchases"."subscription_add_on_id" = ((SELECT "subscription_add_ons"."id" FROM "subscription_add_ons" WHERE "subscription_add_ons"."name" = 1 LIMIT 1)) AND "subscription_add_on_purchases"."trial" = TRUE AND "subscription_add_on_purchases"."expires_on" >= '2024-05-02'

query plan here: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/28019/commands/87230.

 Aggregate  (cost=149.96..149.97 rows=1 width=8) (actual time=0.298..0.300 rows=1 loops=1)
   Buffers: shared hit=38
   I/O Timings: read=0.000 write=0.000
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.12..3.14 rows=1 width=8) (actual time=0.040..0.040 rows=1 loops=1)
           Buffers: shared hit=5
           I/O Timings: read=0.000 write=0.000
           ->  Index Scan using index_subscription_add_ons_on_name on public.subscription_add_ons  (cost=0.12..3.14 rows=1 width=8) (actual time=0.039..0.039 rows=1 loops=1)
                 Index Cond: (subscription_add_ons.name = 1)
                 Buffers: shared hit=5
                 I/O Timings: read=0.000 write=0.000
   ->  Seq Scan on public.subscription_add_on_purchases  (cost=0.00..145.25 rows=629 width=0) (actual time=0.048..0.265 rows=611 loops=1)
         Filter: (subscription_add_on_purchases.trial AND (subscription_add_on_purchases.expires_on >= '2024-05-02'::date) AND (subscription_add_on_purchases.subscription_add_on_id = $0))
         Rows Removed by Filter: 272
         Buffers: shared hit=38
         I/O Timings: read=0.000 write=0.000
  1. UPDATE "subscription_add_on_purchases" SET "expires_on" = (created_at + INTERVAL '60 days')::date WHERE "subscription_add_on_purchases"."id" >= 1 AND "subscription_add_on_purchases"."id" < 1000000 AND "subscription_add_on_purchases"."subscription_add_on_id" = (SELECT "subscription_add_ons"."id" FROM "subscription_add_ons" WHERE "subscription_add_ons"."name" = 1 LIMIT 1) AND "subscription_add_on_purchases"."trial" = TRUE AND "subscription_add_on_purchases"."expires_on" >= '2024-05-02'

NOTE: since I cannot know how many records the update_column_in_batches applies in production server, I used a very large scope min_id: 1, max_id: 1000000 in the above query. This is to give us some idea of the performance.

query plan: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/28019/commands/87231.

 ModifyTable on public.subscription_add_on_purchases  (cost=3.42..6.45 rows=0 width=0) (actual time=0.024..0.025 rows=0 loops=1)
   Buffers: shared hit=5
   I/O Timings: read=0.000 write=0.000
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.12..3.14 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
           I/O Timings: read=0.000 write=0.000
           ->  Index Scan using index_subscription_add_ons_on_name on public.subscription_add_ons  (cost=0.12..3.14 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=0)
                 Index Cond: (subscription_add_ons.name = 1)
                 I/O Timings: read=0.000 write=0.000
   ->  Index Scan using subscription_add_on_purchases_pkey on public.subscription_add_on_purchases  (cost=0.28..3.30 rows=1 width=10) (actual time=0.023..0.023 rows=0 loops=1)
         Index Cond: ((subscription_add_on_purchases.id >= 1) AND (subscription_add_on_purchases.id < 1000000))
         Filter: (subscription_add_on_purchases.trial AND (subscription_add_on_purchases.expires_on >= '2024-05-02'::date) AND (subscription_add_on_purchases.subscription_add_on_id = $0))
         Rows Removed by Filter: 0
         Buffers: shared hit=5
         I/O Timings: read=0.000 write=0.000

In general, it seems the SQL execution is pretty fast in production DB.

Migrate up output 
qingyuzhao@Qingyus-MacBook-Pro gitlab % bin/rails db:migrate:main
main: == [advisory_lock_connection] object_id: 124300, pg_backend_pid: 36329
main: == 20240501113440 ExtendGitlabcomDuoProTrials: migrating ======================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- exec_query("SELECT COUNT(*) AS count FROM \"subscription_add_on_purchases\" WHERE \"subscription_add_on_purchases\".\"subscription_add_on_id\" = 1 AND \"subscription_add_on_purchases\".\"trial\" = TRUE AND \"subscription_add_on_purchases\".\"expires_on\" >= '2024-05-02'")
main:    -> 0.0007s
main: -- exec_query("SELECT \"subscription_add_on_purchases\".\"id\" FROM \"subscription_add_on_purchases\" WHERE \"subscription_add_on_purchases\".\"subscription_add_on_id\" = 1 AND \"subscription_add_on_purchases\".\"trial\" = TRUE AND \"subscription_add_on_purchases\".\"expires_on\" >= '2024-05-02' ORDER BY \"subscription_add_on_purchases\".\"id\" ASC LIMIT 1")
main:    -> 0.0004s
main: -- exec_query("SELECT \"subscription_add_on_purchases\".\"id\" FROM \"subscription_add_on_purchases\" WHERE \"subscription_add_on_purchases\".\"id\" >= 22 AND \"subscription_add_on_purchases\".\"subscription_add_on_id\" = 1 AND \"subscription_add_on_purchases\".\"trial\" = TRUE AND \"subscription_add_on_purchases\".\"expires_on\" >= '2024-05-02' ORDER BY \"subscription_add_on_purchases\".\"id\" ASC LIMIT 1 OFFSET 1")
main:    -> 0.0003s
main: -- transaction(nil)
main: -- execute("UPDATE \"subscription_add_on_purchases\" SET \"expires_on\" = (created_at + INTERVAL '60 days')::date WHERE \"subscription_add_on_purchases\".\"id\" >= 22 AND \"subscription_add_on_purchases\".\"id\" < 35 AND \"subscription_add_on_purchases\".\"subscription_add_on_id\" = 1 AND \"subscription_add_on_purchases\".\"trial\" = TRUE AND \"subscription_add_on_purchases\".\"expires_on\" >= '2024-05-02'")
main:    -> 0.0006s
main:    -> 0.0024s
main: -- exec_query("SELECT \"subscription_add_on_purchases\".\"id\" FROM \"subscription_add_on_purchases\" WHERE \"subscription_add_on_purchases\".\"id\" >= 35 AND \"subscription_add_on_purchases\".\"subscription_add_on_id\" = 1 AND \"subscription_add_on_purchases\".\"trial\" = TRUE AND \"subscription_add_on_purchases\".\"expires_on\" >= '2024-05-02' ORDER BY \"subscription_add_on_purchases\".\"id\" ASC LIMIT 1 OFFSET 1")
main:    -> 0.0004s
main: -- transaction(nil)
main: -- execute("UPDATE \"subscription_add_on_purchases\" SET \"expires_on\" = (created_at + INTERVAL '60 days')::date WHERE \"subscription_add_on_purchases\".\"id\" >= 35 AND \"subscription_add_on_purchases\".\"id\" < 37 AND \"subscription_add_on_purchases\".\"subscription_add_on_id\" = 1 AND \"subscription_add_on_purchases\".\"trial\" = TRUE AND \"subscription_add_on_purchases\".\"expires_on\" >= '2024-05-02'")
main:    -> 0.0009s
main:    -> 0.0013s
main: -- exec_query("SELECT \"subscription_add_on_purchases\".\"id\" FROM \"subscription_add_on_purchases\" WHERE \"subscription_add_on_purchases\".\"id\" >= 37 AND \"subscription_add_on_purchases\".\"subscription_add_on_id\" = 1 AND \"subscription_add_on_purchases\".\"trial\" = TRUE AND \"subscription_add_on_purchases\".\"expires_on\" >= '2024-05-02' ORDER BY \"subscription_add_on_purchases\".\"id\" ASC LIMIT 1 OFFSET 1")
main:    -> 0.0005s
main: -- transaction(nil)
main: -- execute("UPDATE \"subscription_add_on_purchases\" SET \"expires_on\" = (created_at + INTERVAL '60 days')::date WHERE \"subscription_add_on_purchases\".\"id\" >= 37 AND \"subscription_add_on_purchases\".\"subscription_add_on_id\" = 1 AND \"subscription_add_on_purchases\".\"trial\" = TRUE AND \"subscription_add_on_purchases\".\"expires_on\" >= '2024-05-02'")
main:    -> 0.0007s
main:    -> 0.0010s
main: == 20240501113440 ExtendGitlabcomDuoProTrials: migrated (0.0320s) =============

main: == [advisory_lock_connection] object_id: 124300, pg_backend_pid: 36329
Rollback output 
qingyuzhao@Qingyus-MacBook-Pro gitlab % bin/rails db:rollback:main
main: == [advisory_lock_connection] object_id: 124020, pg_backend_pid: 36680
main: == 20240501113440 ExtendGitlabcomDuoProTrials: reverting ======================
main: == 20240501113440 ExtendGitlabcomDuoProTrials: reverted (0.0035s) =============

main: == [advisory_lock_connection] object_id: 124020, pg_backend_pid: 36680
qingyuzhao@Qingyus-MacBook-Pro gitlab %

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before After

How to set up and validate locally

I've done testing on my local development environment, with the following steps:

  1. Start Gitlab and CustomersDot application
  2. Create several groups in Gitlab. Apply either paid base plan(premium, ultimate, etc) or ultimate_trial plan on those groups. And then apply DuoPro trial on these groups. That will give us some records in GitlabSubscriptions::AddOnPurchase table.
  3. In Gitlab rails console, find the active DuoPro trial records
My local test result

There are two active DuoPro trial records

  • id: 35, created_at: Thu, 18 Apr 2024..., expires_on: Sat, 18 May 2024, ..., this one has 30 days duration (expires_on - created_at_date)
  • id: 37, created_at: Wed, 01 May 2024..., expires_on: Sun, 30 Jun 2024, ..., this one has 60 days duration (expires_on - created_at_date)
[48] pry(main)> GitlabSubscriptions::AddOnPurchase.where(subscription_add_on_id: duo_pro_addon_id).where(trial: true).where('expires_on >= ?', today)
  GitlabSubscriptions::AddOnPurchase Load (2.2ms)  SELECT "subscription_add_on_purchases".* FROM "subscription_add_on_purchases" WHERE "subscription_add_on_purchases"."subscription_add_on_id" = 1 AND "subscription_add_on_purchases"."trial" = TRUE AND (expires_on >= '2024-05-01') /*application:console,db_config_name:main,console_hostname:bcd0740929d1,console_username:qingyuzhao,line:bin/rails:4:in `<main>'*/
=> [#<GitlabSubscriptions::AddOnPurchase:0x0000000158d77090 id: 35, created_at: Thu, 18 Apr 2024 03:17:27.236085000 UTC +00:00, updated_at: Thu, 18 Apr 2024 03:17:27.236085000 UTC +00:00, subscription_add_on_id: 1, namespace_id: 107, quantity: 50, expires_on: Sat, 18 May 2024, purchase_xid: "trial-order-132", last_assigned_users_refreshed_at: nil, trial: true>,
 #<GitlabSubscriptions::AddOnPurchase:0x0000000158d76f50 id: 37, created_at: Wed, 01 May 2024 10:34:43.551242000 UTC +00:00, updated_at: Wed, 01 May 2024 10:34:43.551242000 UTC +00:00, subscription_add_on_id: 1, namespace_id: 111, quantity: 50, expires_on: Sun, 30 Jun 2024, purchase_xid: "trial-order-136", last_assigned_users_refreshed_at: nil, trial: true>]
[49] pry(main)>
  1. Run the migration script rails db:migrate. It succeed.
  2. In Gitlab rails console, check the active DuoPro trial records again. The 30 days duration record is extended to 60 days.
My local test result

After migrate, there are still two active DuoPro trial records:

  • id: 35, created_at: Thu, 18 Apr 2024..., expires_on: Mon, 17 Jun 2024, ..., this one is extended to 60 days duration (expires_on - created_at_date)
  • id: 37, created_at: Wed, 01 May 2024..., expires_on: Sun, 30 Jun 2024, ..., this does not change, because it already has 60 days duration (expires_on - created_at_date)
[50] pry(main)>
[51] pry(main)> GitlabSubscriptions::AddOnPurchase.where(subscription_add_on_id: duo_pro_addon_id).where(trial: true).where('expires_on >= ?', today)
  GitlabSubscriptions::AddOnPurchase Load (0.4ms)  SELECT "subscription_add_on_purchases".* FROM "subscription_add_on_purchases" WHERE "subscription_add_on_purchases"."subscription_add_on_id" = 1 AND "subscription_add_on_purchases"."trial" = TRUE AND (expires_on >= '2024-05-01') /*application:console,db_config_name:main,console_hostname:bcd0740929d1,console_username:qingyuzhao,line:bin/rails:4:in `<main>'*/
=> [#<GitlabSubscriptions::AddOnPurchase:0x0000000158d71f50 id: 35, created_at: Thu, 18 Apr 2024 03:17:27.236085000 UTC +00:00, updated_at: Wed, 01 May 2024 13:17:48.461100000 UTC +00:00, subscription_add_on_id: 1, namespace_id: 107, quantity: 50, expires_on: Mon, 17 Jun 2024, purchase_xid: "trial-order-132", last_assigned_users_refreshed_at: nil, trial: true>,
 #<GitlabSubscriptions::AddOnPurchase:0x0000000158d71e10 id: 37, created_at: Wed, 01 May 2024 10:34:43.551242000 UTC +00:00, updated_at: Wed, 01 May 2024 10:34:43.551242000 UTC +00:00, subscription_add_on_id: 1, namespace_id: 111, quantity: 50, expires_on: Sun, 30 Jun 2024, purchase_xid: "trial-order-136", last_assigned_users_refreshed_at: nil, trial: true>]
[52] pry(main)>

Those non-duopro addons records are NOT changed:

[71] pry(main)> product_analytics_addon_id = GitlabSubscriptions::AddOn.find_by(name: GitlabSubscriptions::AddOn.names[:product_analytics]).id
  GitlabSubscriptions::AddOn Load (0.3ms)  SELECT "subscription_add_ons".* FROM "subscription_add_ons" WHERE "subscription_add_ons"."name" = 2 LIMIT 1 /*application:console,db_config_name:main,console_hostname:bcd0740929d1,console_username:qingyuzhao,line:(pry):62:in `__pry__'*/
=> 2
[72] pry(main)> GitlabSubscriptions::AddOnPurchase.where(subscription_add_on_id: product_analytics_addon_id).count
  GitlabSubscriptions::AddOnPurchase Count (0.5ms)  SELECT COUNT(*) FROM "subscription_add_on_purchases" WHERE "subscription_add_on_purchases"."subscription_add_on_id" = 2 /*application:console,db_config_name:main,console_hostname:bcd0740929d1,console_username:qingyuzhao,line:(pry):63:in `__pry__'*/
=> 2
[73] pry(main)> GitlabSubscriptions::AddOnPurchase.where(subscription_add_on_id: product_analytics_addon_id)
  GitlabSubscriptions::AddOnPurchase Load (0.4ms)  SELECT "subscription_add_on_purchases".* FROM "subscription_add_on_purchases" WHERE "subscription_add_on_purchases"."subscription_add_on_id" = 2 /*application:console,db_config_name:main,console_hostname:bcd0740929d1,console_username:qingyuzhao,line:bin/rails:4:in `<main>'*/
=> [#<GitlabSubscriptions::AddOnPurchase:0x0000000158d1f7a0 id: 1, created_at: Mon, 22 Jan 2024 09:29:07.020683000 UTC +00:00, updated_at: Wed, 01 May 2024 11:52:28.693043000 UTC +00:00, subscription_add_on_id: 2, namespace_id: 51, quantity: 3, expires_on: Wed, 05 Feb 2025, purchase_xid: "A-S00475218", last_assigned_users_refreshed_at: nil, trial: false>,
 #<GitlabSubscriptions::AddOnPurchase:0x0000000158d1f660 id: 2, created_at: Mon, 12 Feb 2024 01:54:36.390004000 UTC +00:00, updated_at: Wed, 01 May 2024 11:54:06.621078000 UTC +00:00, subscription_add_on_id: 2, namespace_id: 76, quantity: 100, expires_on: Wed, 13 Mar 2024, purchase_xid: "trial-order-74", last_assigned_users_refreshed_at: nil, trial: false>]
[74] pry(main)>

Those paid DuoPro records are NOT changed:

[76] pry(main)> GitlabSubscriptions::AddOnPurchase.where(subscription_add_on_id: duo_pro_addon_id).where(trial: false).where('expires_on >= ?', today)
  GitlabSubscriptions::AddOnPurchase Load (0.7ms)  SELECT "subscription_add_on_purchases".* FROM "subscription_add_on_purchases" WHERE "subscription_add_on_purchases"."subscription_add_on_id" = 1 AND "subscription_add_on_purchases"."trial" = FALSE AND (expires_on >= '2024-05-01') /*application:console,db_config_name:main,console_hostname:bcd0740929d1,console_username:qingyuzhao,line:bin/rails:4:in `<main>'*/
=> [#<GitlabSubscriptions::AddOnPurchase:0x0000000158d1af20 id: 36, created_at: Wed, 01 May 2024 01:31:31.148868000 UTC +00:00, updated_at: Wed, 01 May 2024 01:31:31.148868000 UTC +00:00, subscription_add_on_id: 1, namespace_id: 109, quantity: 12, expires_on: Fri, 02 May 2025, purchase_xid: "A-S00113443", last_assigned_users_refreshed_at: nil, trial: false>
Edited by Qingyu Zhao

Merge request reports