[SPIKE] Experiment with new `group_rollup` column on `AuditEvent` table

Background Context

We want to be able to return all audit events under a given groups via a single endpoint. We have an issue, API endpoint to retrieve all audit events under a given group, to track this feature.

We actually had already implemented that feature, but the performance was so poor in production that it functionally did not work. The database query timed out almost 100% of the time.

The proposal was to improve the existing query's performance by using the InOperatorOptimization library.

Attempts to do this did not show a good performance boost (details here)

New Approach

We want to attempt this feature with a new approach. The Idea is that we can add a new column, group_rollup, to the audit_events table. This column will be the id of the top-level group the audit event belongs to.

We can then update the Auditor to write this value at audit event creation, allowing us to implement the endpoint with a simple query:

SELECT * FROM "audit_events" WHERE "audit_events"."group_rollup_id" == @group.id

This query should be straightforward to index and paginate, giving us acceptable performance.

This Issue

We want this issue to track the validation of this approach in database lab.

We should:

1. add the column in database lab
2. add an index for the column in database lab
3. backfill the new column in database lab
4. generate the query plan

If the performance looks good in the query plan, we can move ahead with implementing the feature (using #337757 (closed) to track the implementation work)

added POC database groupcompliance labels

set weight to 3

marked this issue as related to #337757 (closed)

added to epic &1985

added devopsgovern sectionsec labels

changed the description

changed milestone to %15.11

@wandering_person - please see the following guidance and update this issue.

	1 Error
	Please add typebug typefeature, typemaintenance and a subtype label to this issue.

If you do not feel the purpose of this issue matches one of the types, you may apply the typeignore label to exclude it from type tracking metrics and future prompts.

This message was generated automatically. You're welcome to improve it.

added typemaintenance label

changed title from [POC] Experiment with new group_rollup column on AuditEvent table to [SPIKE] Experiment with new group_rollup column on AuditEvent table

Thanks for creating this @wandering_person, assigning to you for 15.11

assigned to @wandering_person

added workflowin dev label

mentioned in issue #332979 (closed)

added Deliverable label

Setting health status to on track as the milestone has just begun.

Issue participants are welcome to override this by setting the health status to another value.

changed health status to on track

Setup

In order to gather the EXPLAIN results for the new query I ran the following steps in database lab:

1. Added the new column to the audit events table

   ALTER TABLE "audit_events" ADD "group_rollup_id" integer

2. Added an index for the new column

   CREATE INDEX "index_audit_events_on_group_rolliup_id" ON "audit_events" ("group_rollup_id")

3. Backfilled the column for audit events belonging to group 9970

   UPDATE "audit_events" SET "group_rollup_id" = 9970 WHERE "audit_events"."entity_type" = 'Group' AND "audit_events"."entity_id" = 9970;
   UPDATE "audit_events" SET "group_rollup_id" = 9970 WHERE "audit_events"."entity_type" = 'Project' AND "audit_events"."entity_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}'))));

4. And finally ran the query we plan on using with this new column

   SELECT "audit_events".* FROM "audit_events" WHERE "audit_events"."group_rollup_id" = 9970 LIMIT 100

Results

database lab session

     Limit  (cost=0.14..145.23 rows=100 width=398) (actual time=9.777..242.900 rows=100 loops=1)
       Buffers: shared hit=2 read=72 dirtied=31
       I/O Timings: read=241.097 write=0.000
       ->  Append  (cost=0.14..9780707.85 rows=6741363 width=398) (actual time=9.773..242.848 rows=100 loops=1)
             Buffers: shared hit=2 read=72 dirtied=31
             I/O Timings: read=241.097 write=0.000

@wandering_person This looks really positive. Would you be able to add the original timings for comparison?

@nrosandich sure thing!

@nrosandich the original feature has now been removed, so this isn't a comparison vs a "current" implementation, as currently there is nothing. Just wanted to make that clear.

All that said, here are some database lab results from that now-removed implementation

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/14753/commands/51582 (link has expired, however I included a screenshot of the summary from slack)

Summary

Time: 18.734 s
  - planning: 71.411 ms
  - execution: 18.662 s
    - I/O read: 16.764 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 501160 (~3.80 GiB) from the buffer pool
  - reads: 106977 (~835.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 3582 (~28.00 MiB)
  - writes: 0

slack screenshot

Thanks @wandering_person and fully acknowledge that this comparison is not against "current" implementation.

From 18.734 s to 26.00 ms definitely makes for arresting reading though

@nrosandich !

mentioned in issue #337757 (closed)

added workflowin review label and removed workflowin dev label

closed

removed workflowin review label

added workflowcomplete label

mentioned in issue gitlab-org/govern/compliance/general#92

mentioned in commit 5c540ef2

mentioned in merge request !115898 (merged)

mentioned in commit 2f2da14d

mentioned in commit 58d3fd4a

mentioned in commit eb6241c3

mentioned in commit 7b61e693

mentioned in commit c116b28e

mentioned in commit bdb2c923

mentioned in commit 7cbc7480

mentioned in commit 5e736913

mentioned in commit 02c5f96b

mentioned in commit 6d17460e

mentioned in commit 130dce95

mentioned in commit 216aeccd

mentioned in commit 4eba451d

mentioned in commit 2295da5c

mentioned in commit 9d05f187

mentioned in commit 4e7dc880

mentioned in issue #414258 (closed)

mentioned in commit 79491101

changed epic to &13820 (closed)