Add data retention expiry columns to user mapping tables

George Koltsovrequested to merge
georgekoltsov/add-placeholder-references-db-columns into master

What does this MR do and why?

Adds three nullable timestamp columns to placeholder user import tables for data retention (issue #576118, part of #527002):

  • import_source_users.reassignment_expires_at
  • import_source_user_placeholder_references.expires_at
  • import_placeholder_memberships.retention_expires_at

The third table uses retention_expires_at because it already has an unrelated expires_at (date) column for membership access expiry.

All three tables are backfilled via batched background migrations, queued in this MR/milestone (precedent: merge_request_diffs queued its BBM in the same MR/milestone as its column-add).

Indexes are created synchronously on the smaller tables via add_concurrent_index. import_source_user_placeholder_references (~52M rows) uses prepare_async_index instead, to avoid blocking the GitLab.com deploy.

NOT NULL constraints are deferred until after backfill — these columns have no DB-level default, so adding the constraint now would make every existing row a permanent violation.

Model-level defaults (Time.current + 1.year, unconditional - not falling back to created_at, to avoid setting an already-expired timestamp on a pre-existing row revalidated before the backfill BBM reaches it) are added as an interim safeguard until the retention-policy backend sets real values. No separate presence validation - the ||= default already guarantees a value on every save, which made a redundant validates :presence untestable, so it was dropped.

References

Database review data

Table Rows table_size in db/docs
import_source_users ~533K small
import_source_user_placeholder_references ~52.3M medium
import_placeholder_memberships ~21.1M small 
EXPLAIN SELECT * FROM import_source_users;
--  Seq Scan on public.import_source_users  (cost=0.00..44309.50 rows=529350 width=192) (actual time=2.523..369.120 rows=533223 loops=1)

EXPLAIN SELECT * FROM import_source_user_placeholder_references;
--  Seq Scan on public.import_source_user_placeholder_references  (cost=0.00..22258249.28 rows=73183328 width=123) (actual time=1.805..108219.300 rows=52333684 loops=1)

EXPLAIN SELECT * FROM import_placeholder_memberships;
--  Seq Scan on public.import_placeholder_memberships  (cost=0.00..1066128.44 rows=21144444 width=54) (actual time=2.242..7060.010 rows=21138946 loops=1)

Backfill query plans (per Danger's bulk-update requirement)

Each BBM's each_sub_batch block runs an update_all — full query and EXPLAIN plan for each, plus the column-add timing, on a GitLab.com thin clone:

import_source_users 
ALTER TABLE import_source_users ADD COLUMN reassignment_expires_at timestamp with time zone;
-- Duration: 12.904 ms
EXPLAIN UPDATE import_source_users SET reassignment_expires_at = NOW() + INTERVAL '1 year' WHERE reassignment_expires_at IS NULL AND id IN (SELECT id FROM import_source_users WHERE reassignment_expires_at IS NULL ORDER BY id LIMIT 200);
Update on public.import_source_users  (cost=3573.13..4244.12 rows=0 width=0) (actual time=205.275..205.280 rows=0 loops=1)
  Buffers: shared hit=6337 read=192 dirtied=177 written=1
  WAL: records=1841 fpi=175 bytes=1234035
  I/O Timings: read=188.854 write=0.045
  ->  Nested Loop  (cost=3573.13..4244.12 rows=1 width=46) (actual time=0.800..2.408 rows=200 loops=1)
        Buffers: shared hit=910 read=1
        I/O Timings: read=0.479 write=0.000
        ->  HashAggregate  (cost=3572.70..3574.70 rows=200 width=40) (actual time=0.778..0.979 rows=200 loops=1)
              Group Key: "ANY_subquery".id
              Batches: 1  Memory Usage: 48kB
              Buffers: shared hit=110 read=1
              I/O Timings: read=0.479 write=0.000
              ->  Subquery Scan on "ANY_subquery"  (cost=0.42..3572.20 rows=200 width=40) (actual time=0.030..0.739 rows=200 loops=1)
                    Buffers: shared hit=110 read=1
                    I/O Timings: read=0.479 write=0.000
                    ->  Limit  (cost=0.42..3570.20 rows=200 width=8) (actual time=0.018..0.695 rows=200 loops=1)
                          Buffers: shared hit=110 read=1
                          I/O Timings: read=0.479 write=0.000
                          ->  Index Scan using import_source_users_pkey on public.import_source_users import_source_users_1  (cost=0.42..47246.45 rows=2647 width=8) (actual time=0.017..0.678 rows=200 loops=1)
                                Filter: (import_source_users_1.reassignment_expires_at IS NULL)
                                Buffers: shared hit=110 read=1
                                I/O Timings: read=0.479 write=0.000
        ->  Index Scan using import_source_users_pkey on public.import_source_users  (cost=0.42..3.34 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=200)
              Index Cond: (import_source_users.id = "ANY_subquery".id)
              Filter: (import_source_users.reassignment_expires_at IS NULL)
              Buffers: shared hit=800

Time: 206.591 ms (planning: 1.135 ms, execution: 205.456 ms)
Shared buffers: hits 6337 (~49.50 MiB), reads 192 (~1.50 MiB), dirtied 177, writes 1
import_source_user_placeholder_references 
ALTER TABLE import_source_user_placeholder_references ADD COLUMN expires_at timestamp with time zone;
-- Duration: 214.124 ms
EXPLAIN UPDATE import_source_user_placeholder_references SET expires_at = NOW() + INTERVAL '1 year' WHERE expires_at IS NULL AND id IN (SELECT id FROM import_source_user_placeholder_references WHERE expires_at IS NULL ORDER BY id LIMIT 500);
Update on public.import_source_user_placeholder_references  (cost=13816.16..15613.10 rows=0 width=0) (actual time=248.151..248.154 rows=0 loops=1)
  Buffers: shared hit=7963 read=359 dirtied=371
  WAL: records=1579 fpi=363 bytes=2712081
  I/O Timings: read=229.514 write=0.000
  ->  Nested Loop  (cost=13816.16..15613.10 rows=3 width=46) (actual time=180.045..181.910 rows=500 loops=1)
        [plan truncated by Database Lab]

Time: 249.685 ms (planning: 1.381 ms, execution: 248.304 ms)
Shared buffers: hits 7963 (~62.20 MiB), reads 359 (~2.80 MiB), dirtied 371, writes 0
import_placeholder_memberships 
ALTER TABLE import_placeholder_memberships ADD COLUMN retention_expires_at timestamp with time zone;
-- Duration: 23.725 ms
EXPLAIN UPDATE import_placeholder_memberships SET retention_expires_at = NOW() + INTERVAL '1 year' WHERE retention_expires_at IS NULL AND id IN (SELECT id FROM import_placeholder_memberships WHERE retention_expires_at IS NULL ORDER BY id LIMIT 500);
Update on public.import_placeholder_memberships  (cost=5341.96..7072.56 rows=0 width=0) (actual time=151.317..151.321 rows=0 loops=1)
  Buffers: shared hit=16941 read=245 dirtied=234 written=6
  WAL: records=4564 fpi=224 bytes=1999919
  I/O Timings: read=119.135 write=1.806
  ->  Nested Loop  (cost=5341.96..7072.56 rows=2 width=46) (actual time=26.645..29.022 rows=500 loops=1)
        [plan truncated by Database Lab]

Time: 153.306 ms (planning: 1.734 ms, execution: 151.572 ms)
Shared buffers: hits 16941 (~132.40 MiB), reads 245 (~1.90 MiB), dirtied 234, writes 6

Database review needed: schema changes, new indexes (one async), and the backfill queries above.

Screenshots or screen recordings

Not applicable — database schema change, no UI changes.

How to set up and validate locally

  1. bundle exec rails db:migrate (regenerates db/structure.sql)
  2. Run these specs: 
    bundle exec rspec spec/migrations/20260907090500_queue_backfill_import_source_users_reassignment_expires_at_spec.rb
bundle exec rspec spec/migrations/20260907090600_queue_backfill_import_source_user_placeholder_references_expires_at_spec.rb
bundle exec rspec spec/migrations/20260907090700_queue_backfill_import_placeholder_memberships_retention_expires_at_spec.rb
bundle exec rspec spec/lib/gitlab/background_migration/backfill_import_source_users_reassignment_expires_at_spec.rb
bundle exec rspec spec/lib/gitlab/background_migration/backfill_import_source_user_placeholder_references_expires_at_spec.rb
bundle exec rspec spec/lib/gitlab/background_migration/backfill_import_placeholder_memberships_retention_expires_at_spec.rb
bundle exec rspec spec/models/import/placeholders/membership_spec.rb
bundle exec rspec spec/models/import/source_user_placeholder_reference_spec.rb

The three plain add_column migrations have no dedicated specs — schema-only, exempt per doc/development/testing_guide/testing_migrations_guide.md.

MR acceptance checklist

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

Deferred to follow-up work (tracked in #576118, not in scope here):

  • Finalizing the three batched background migrations.
  • Async-index verification and the synchronous follow-up migration for import_source_user_placeholder_references (no fixed milestone — checked periodically).
  • Adding and validating the NOT NULL constraints on the two placeholder columns once backfilled.
Edited by George Koltsov

Merge request reports