Skip to content

Start basic implementation of Group feature class

Francisco Javier López (ex-Gitlab) requested to merge 208412-fj-add-group-features-table-and-basics into master

What does this MR do and why?

In this MR, we're starting the foundations for #208412 (closed) and start creating group feature visibility levels.

We're creating a new model called Groups::FeatureSetting with very basic functionality. We're mostly introducing the database structure and the backfilling migration needed to populate the new model.

Migration output

Up

== 20220302110724 AddGroupFeaturesTable: migrating ============================
-- create_table(:group_features, {})
   -> 0.0241s
== 20220302110724 AddGroupFeaturesTable: migrated (0.0242s) ===================

== 20220302114046 BackfillGroupFeatures: migrating ============================
== 20220302114046 BackfillGroupFeatures: migrated (0.0512s) ===================

Down

== 20220302114046 BackfillGroupFeatures: reverting ============================
== 20220302114046 BackfillGroupFeatures: reverted (0.0225s) ===================

== 20220302110724 AddGroupFeaturesTable: reverting ============================
-- drop_table(:group_features, {})
   -> 0.0060s
== 20220302110724 AddGroupFeaturesTable: reverted (0.0093s) ===================

Execution times

There are around 3189500 groups and since the batch is going to be 10_000, we're going to schedule 319 jobs.

Within each job, we're going to perform the backfill operation over a sub batch size of 100.

The query would be:

INSERT INTO group_features (group_id, created_at, updated_at)
SELECT namespaces.id as group_id, now(), now()
FROM namespaces
WHERE namespaces.type = 'Group' AND namespaces.id IN(SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."id" BETWEEN 1 AND 10000 AND "namespaces"."type" = 'Group' AND "namespaces"."id" >= 2244 AND "namespaces"."id" < 2344)
ON CONFLICT (group_id) DO NOTHING;

The times for this query with "cold" caches are:

Time: 36.329 ms
  - planning: 3.906 ms
  - execution: 32.423 ms
    - I/O read: 9.820 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 72 (~576.00 KiB) from the buffer pool
  - reads: 10 (~80.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 5 (~40.00 KiB)
  - writes: 3 (~24.00 KiB)

The query plan is https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9386/commands/33224

To perform the whole batch at once (10k), the times with "cold" caches are:

Time: 820.049 ms
  - planning: 1.061 ms
  - execution: 818.988 ms
    - I/O read: 66.865 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 6922 (~54.10 MiB) from the buffer pool
  - reads: 43 (~344.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 10 (~80.00 KiB)
  - writes: 8 (~64.00 KiB)

And the query plan https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9386/commands/33226.

MR acceptance checklist

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

Related to #208412 (closed)

Edited by Francisco Javier López (ex-Gitlab)

Merge request reports