Drop updated_at column on audit_events

Problem to solve

Audit events should be append-only. The updated_at field is redundant and can be dropped to save storage spaces and reduce query payload.

Proposal

Drop updated_at column from audit_events table.

This change will be more likely to be spread across multiple releases (https://docs.gitlab.com/ee/development/what_requires_downtime.html#dropping-columns)

• 13.2: Ignore updated_at column and stop all writes
• 13.3: Remove updated_at column on audit_events
• 13.4: Clean up ignore column code in AuditEvent model

Further details

This will shave a shy (242,000,000 mil records * 8 bytes) in Gb = 2.2352 Gb

• https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/issues/9226#master-1022016106-18
• https://www.postgresql.org/docs/11/datatype-datetime.html

Availability & Testing

There is no known consumer of this field. Both UI and API only allow querying and presenting of created_at field.

Require collaboration with groupdatabase to understand if there is any risk.

changed milestone to %Backlog

added backend devopsmanage groupcompliance maintenancerefactor workflowsolution validation labels

added to epic &2765

added [deprecated] Accepting merge requests label

changed the description

@abrandl @mwoolf @djensen I would love to hear your thoughts on this issue.

15.96 Gb

🤯

Yep, yep, yep!! Since audit events are append-only and there's no consumer of updated_at, I'm definitely in favour of removing it. Nice find @tancnle!

Good call @tancnle , let's remove it!

As you pointed out already, we'll need to spread this out across releases. First step would be to ignore the column in ActiveRecord.

Thinking that in general, we might also be able to incorporate this change into a larger refactoring (if/when we end up doing that). There's no harm in starting down the standard ignore/drop/unignore route to just drop the column though.

We currently have 300M records in audit_events. I think this would boil down to about 2.2 GB (unless my math is broken) of savings. In any case that's worth doing!

There is only exactly one record with created_at <> updated_at on GitLab.com.

Thanks @abrandl. My calculation was a bit exaggerated I have naively multiply the number of records with the storage size of timestamp data type which is 8 bytes. Could help me to understand how it is calculated the right way?

@tancnle Umh, I think that's the right approach (number of records * 8 bytes). I just end up with a different result:

300,000,000 * 8 bytes = 2400000000 bytes => about 2.2 GiB

300*10**6 * 8 / (1024.0**3)
 => 2.2351741790771484

I wouldn't be surprised if my basic math is flawed for some reason, so please let me know.

Ah yes. That's my math failing me I have corrected that in the description.

changed the description

mentioned in issue #217471 (closed)

marked this issue as related to #215717 (closed)

mentioned in merge request !34156 (merged)

assigned to @tancnle

changed milestone to %13.2

changed milestone to %13.3

mentioned in merge request !35690 (merged)

removed [deprecated] Accepting merge requests label

mentioned in merge request !35697 (merged)

mentioned in issue gitlab-org/manage/general-discussion#17236

marked this issue as related to gitlab-org/manage/general-discussion#17236

added workflowin dev label and removed workflowsolution validation label

set weight to 1

changed the description

marked the checklist item Ignore updated_at column and stop all writes as completed

marked the checklist item Remove updated_at column on audit_events as completed

@djensen @mattgonzales FYI the chunk of work for %13.3 has been completed. We will have to wait until %13.4 to remove the code and call this done.

changed the description

changed milestone to %13.4

set weight to 5

set weight to 3

mentioned in issue gitlab-org/manage/general-discussion#17265

added Deliverable label

marked this issue as related to gitlab-org/manage/general-discussion#17265

added sectiondev label

mentioned in merge request !40249 (merged)

closed with merge request !40249 (merged)

mentioned in commit ae81cd5d

marked the checklist item 13.4: Clean up ignore column code in AuditEvent model as completed

mentioned in issue gl-retrospectives/manage#51 (closed)

mentioned in merge request !43703 (merged)