Update default value for code_suggestions settings

What does this MR do and why?

This sets the default value for code_suggestions on the namespace_settings table. The change is required to allow users to have more control over the code_suggestions feature. It also allows the owning group to disable the feature for all members.

• For group namespace, the default is true.
• For user namespace, the default is false.
• For project namespace, it is left untouched.

🐘 Databases

Sub-Batch Update Query

Query

UPDATE 
  "namespace_settings" 
SET 
  "code_suggestions" = TRUE 
WHERE 
  "namespace_settings"."namespace_id" IN (
    SELECT 
      "namespace_settings"."namespace_id" 
    FROM 
      "namespace_settings" 
      INNER JOIN namespaces ON namespaces.id = namespace_settings.namespace_id 
    WHERE 
      "namespace_settings"."namespace_id" BETWEEN 1 
      AND 100 
      AND "namespaces"."type" = 'Group'
  )

Plan with Execution

 ModifyTable on public.namespace_settings  (cost=445.02..475.77 rows=3 width=137) (actual time=64.383..64.391 rows=0 loops=1)
   Buffers: shared hit=408 read=57
   I/O Timings: read=62.233 write=0.000
   ->  Nested Loop  (cost=445.02..475.77 rows=3 width=137) (actual time=64.381..64.387 rows=0 loops=1)
         Buffers: shared hit=408 read=57
         I/O Timings: read=62.233 write=0.000
         ->  HashAggregate  (cost=444.59..444.68 rows=9 width=20) (actual time=64.380..64.385 rows=0 loops=1)
               Group Key: namespace_settings_1.namespace_id
               Buffers: shared hit=408 read=57
               I/O Timings: read=62.233 write=0.000
               ->  Nested Loop  (cost=1.00..444.56 rows=9 width=20) (actual time=64.377..64.381 rows=0 loops=1)
                     Buffers: shared hit=408 read=57
                     I/O Timings: read=62.233 write=0.000
                     ->  Index Scan using namespace_settings_pkey on public.namespace_settings namespace_settings_1  (cost=0.44..96.82 rows=97 width=10) (actual time=3.369..4.108 rows=91 loops=1)
                           Index Cond: ((namespace_settings_1.namespace_id >= 1) AND (namespace_settings_1.namespace_id <= 100))
                           Buffers: shared hit=8 read=2
                           I/O Timings: read=3.910 write=0.000
                     ->  Index Scan using namespaces_pkey on public.namespaces  (cost=0.56..3.58 rows=1 width=10) (actual time=0.660..0.660 rows=0 loops=91)
                           Index Cond: (namespaces.id = namespace_settings_1.namespace_id)
                           Filter: ((namespaces.type)::text = 'Group'::text)
                           Rows Removed by Filter: 1
                           Buffers: shared hit=400 read=55
                           I/O Timings: read=58.323 write=0.000
         ->  Index Scan using namespace_settings_pkey on public.namespace_settings  (cost=0.44..3.46 rows=1 width=116) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: (namespace_settings.namespace_id = namespaces.id)
               I/O Timings: read=0.000 write=0.000

Summary

Time: 66.462 ms  
  - planning: 1.841 ms  
  - execution: 64.621 ms  
    - I/O read: 62.233 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 408 (~3.20 MiB) from the buffer pool  
  - reads: 57 (~456.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0  

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/18711/commands/62127.

Rows affected

explain SELECT id FROM "namespaces" WHERE "namespaces"."type" in ('Group', 'User')

 Index Only Scan using index_namespaces_on_type_and_id on public.namespaces  (cost=0.56..460944.87 rows=18155043 width=4) (actual time=0.113..4821.061 rows=18131546 loops=1)
   Index Cond: (namespaces.type = ANY ('{Group,User}'::text[]))
   Heap Fetches: 613272
   Buffers: shared hit=6087924 read=1597
   I/O Timings: read=123.165 write=0.000

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/18711/commands/62128

~18 mil (18,155,043)

Estimated runtime

6 h 38 min 46 s

Breakdown:

• per sub-batch (2 queries): 66.462 ms * 2  = 132.92 ms
• per batch (500 sub-batches per batch): 132.92 ms * 500 = 66,460 ms
• total (360 batches required to process 18M records): 66,460 ms * 360 = 23,925,600 ms

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

Related to #411936 (closed)