Add reusable IAM outbox capture for OAuth applications

Daniele Braccianirequested to merge
db/602678/introduce-microsync-drain-worker-for-iam-data-replication into master

What does this MR do and why?

Introduce a reusable transactional-outbox capture for IAM data replication and apply it to Authn::OauthApplication, the first replicated entity. Every create, update, and destroy produces an iam_outbox row. The iam_outbox table and model are already on master; this MR is the capture half. Backfilling existing applications (!246600) and the drain worker that delivers rows to the IAM stores are follow-ups.

  • Reusable concern Authn::IamReplication::Outboxable. A model includes it, declares iam_replicable entity_type: '...', and implements iam_outbox_delete_payload. Future IAM entities plug in the same way.
  • The outbox row is written in the model's in-transaction callbacks (after_create/after_update/after_destroy), so it is atomic with the domain mutation: if the surrounding transaction rolls back, no row remains. Doorkeeper's application_class is Authn::OauthApplication, so the callbacks cover every create, update, and destroy path.
  • The drain kick is deferred to after_commit and is currently a no-op (schedule_iam_outbox_drain); it starts enqueuing once the drain worker lands. The durable outbox row is the binding record.
  • upsert payload is empty (the drain re-reads Rails); delete payload carries only uid. No secret or token ever reaches the payload: this is enforced by the concern and model building the payload, not by the (permissive) JSON schema. iam_outbox_delete_payload is a required override, so a new entity cannot silently emit an unidentifiable delete row.
  • Organization transfer moves organization_id with update_all, which bypasses the callbacks. GroupsService and UsersService therefore record the upserts explicitly via Outboxable.record_iam_outbox_upserts after the bulk update, keeping the IAM shard key correct.

How to set up and validate locally

  1. Enable the flag: Feature.enable(:iam_data_replication)
  2. Create or update an Authn::OauthApplication. Confirm one Authn::IamOutbox row with event_type: 'upsert', the matching organization_id, and an empty payload.
  3. Destroy it. Confirm a delete row whose payload is { "uid" => ... }.
  4. Wrap a create in a rolled-back transaction and confirm no row remains.
  5. Transfer a group or user that owns an application (Organizations::Transfer::GroupsService / UsersService) and confirm a fresh upsert row exists with the new organization_id.

Feature flag

iam_data_replication (wip, disabled by default). A single flag for the whole IAM replication effort. It is an instance-wide gate (Feature.enabled?(:iam_data_replication, :instance)) covering the runtime outbox writes and the transfer capture. No changelog while disabled by default.

Query plan

See:

The bulk write is Authn::IamOutbox.insert_all!(rows) in record_iam_outbox_upserts (app/models/concerns/authn/iam_replication/outboxable.rb:49), run inside relation.each_batch (batch size 1000). The per-mutation write is the single-row Authn::IamOutbox.create! in write_iam_outbox_event (:78), firing on every create/update/destroy. Tested on the gitlab-production-main clone with 5,000 oauth_applications seeded on owner_id = 9970 (Namespace), organization_id = 1. The real each_batch read filters ... AND id >= <batch_start>; the batched plans below express <batch_start> as a MIN(id) sub-select (the InitPlan node), which does not run in production where each_batch passes a literal id.

Batched read + insert (organization transfer)

SELECT — sub-batch boundary query (~6ms)

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/54116/commands/156566

SELECT id FROM oauth_applications
WHERE owner_type = 'Namespace' AND owner_id IN (9970) AND organization_id = 1
  AND id >= (SELECT MIN(id) FROM oauth_applications WHERE owner_type='Namespace' AND owner_id=9970 AND organization_id=1)
ORDER BY id ASC LIMIT 1 OFFSET 1000;
Limit  (cost=6.93..6.93 rows=1 width=4) (actual time=5.182..5.184 rows=1 loops=1)
   Buffers: shared hit=2507
   I/O Timings: read=0.000 write=0.000
   InitPlan 1
     ->  Aggregate  (cost=3.45..3.46 rows=1 width=4) (actual time=3.063..3.064 rows=1 loops=1)
           Buffers: shared hit=1252
           I/O Timings: read=0.000 write=0.000
           ->  Index Scan using index_oauth_applications_on_owner_id_and_owner_type on public.oauth_applications oauth_applications_1  (cost=0.43..3.45 rows=1 width=4) (actual time=0.010..2.746 rows=5001 loops=1)
                 Index Cond: ((oauth_applications_1.owner_id = 9970) AND ((oauth_applications_1.owner_type)::text = 'Namespace'::text))
                 Filter: (oauth_applications_1.organization_id = 1)
                 Buffers: shared hit=1252
                 I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=3.46..3.47 rows=1 width=4) (actual time=5.115..5.151 rows=1001 loops=1)
         Sort Key: oauth_applications.id
         Sort Method: top-N heapsort  Memory: 49kB
         Buffers: shared hit=2507
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using index_oauth_applications_on_owner_id_and_owner_type on public.oauth_applications  (cost=0.43..3.45 rows=1 width=4) (actual time=3.091..4.689 rows=5001 loops=1)
               Index Cond: ((oauth_applications.owner_id = 9970) AND ((oauth_applications.owner_type)::text = 'Namespace'::text))
               Filter: ((oauth_applications.id >= (InitPlan 1).col1) AND (oauth_applications.organization_id = 1))
               Buffers: shared hit=2504
               I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4'
Query ID: 3802975858039579686

Summary:

Time: 6.048 ms
  - planning: 0.796 ms
  - execution: 5.252 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2507 (~19.60 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
SELECT — sub-batch read (~6ms)

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/54116/commands/156567

SELECT id, organization_id FROM oauth_applications
WHERE owner_type = 'Namespace' AND owner_id IN (9970) AND organization_id = 1
  AND id >= (SELECT MIN(id) FROM oauth_applications WHERE owner_type='Namespace' AND owner_id=9970 AND organization_id=1)
ORDER BY id ASC LIMIT 1000;
Limit  (cost=6.92..6.93 rows=1 width=12) (actual time=5.057..5.150 rows=1000 loops=1)
   Buffers: shared hit=2507
   I/O Timings: read=0.000 write=0.000
   InitPlan 1
     ->  Aggregate  (cost=3.45..3.46 rows=1 width=4) (actual time=2.647..2.648 rows=1 loops=1)
           Buffers: shared hit=1252
           I/O Timings: read=0.000 write=0.000
           ->  Index Scan using index_oauth_applications_on_owner_id_and_owner_type on public.oauth_applications oauth_applications_1  (cost=0.43..3.45 rows=1 width=4) (actual time=0.018..2.375 rows=5001 loops=1)
                 Index Cond: ((oauth_applications_1.owner_id = 9970) AND ((oauth_applications_1.owner_type)::text = 'Namespace'::text))
                 Filter: (oauth_applications_1.organization_id = 1)
                 Buffers: shared hit=1252
                 I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=3.46..3.47 rows=1 width=12) (actual time=5.056..5.090 rows=1000 loops=1)
         Sort Key: oauth_applications.id
         Sort Method: top-N heapsort  Memory: 94kB
         Buffers: shared hit=2507
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using index_oauth_applications_on_owner_id_and_owner_type on public.oauth_applications  (cost=0.43..3.45 rows=1 width=12) (actual time=2.684..4.328 rows=5001 loops=1)
               Index Cond: ((oauth_applications.owner_id = 9970) AND ((oauth_applications.owner_type)::text = 'Namespace'::text))
               Filter: ((oauth_applications.id >= (InitPlan 1).col1) AND (oauth_applications.organization_id = 1))
               Buffers: shared hit=2504
               I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'
Query ID: 1293947542003100633

Summary:

Time: 6.014 ms
  - planning: 0.766 ms
  - execution: 5.248 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 2507 (~19.60 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
INSERT — sub-batch insert_all! (~25ms per 1000 rows)

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/54116/commands/156568

INSERT INTO iam_outbox
  (entity_type, entity_id, organization_id, event_type, payload, created_at, updated_at)
SELECT 'oauth_application', o.id, o.organization_id, 0, '{}'::jsonb, NOW(), NOW()
FROM oauth_applications o
WHERE o.owner_type = 'Namespace' AND o.owner_id IN (9970) AND o.organization_id = 1
  AND o.id >= (SELECT MIN(id) FROM oauth_applications WHERE owner_type='Namespace' AND owner_id=9970 AND organization_id=1)
ORDER BY o.id ASC LIMIT 1000;
Insert on public.iam_outbox  (cost=6.93..6.95 rows=0 width=0) (actual time=17.614..17.617 rows=0 loops=1)
   Buffers: shared hit=12658 dirtied=26 written=26
   WAL: records=5104 fpi=0 bytes=497621
   I/O Timings: read=0.000 write=2.471
   ->  Subquery Scan on "*SELECT*"  (cost=6.93..6.95 rows=1 width=190) (actual time=7.322..8.192 rows=1000 loops=1)
         Buffers: shared hit=3521
         WAL: records=31 fpi=0 bytes=3069
         I/O Timings: read=0.000 write=0.000
         ->  Limit  (cost=6.93..6.93 rows=1 width=96) (actual time=7.237..7.369 rows=1000 loops=1)
               Buffers: shared hit=2507
               I/O Timings: read=0.000 write=0.000
               InitPlan 1
                 ->  Aggregate  (cost=3.45..3.46 rows=1 width=4) (actual time=4.089..4.090 rows=1 loops=1)
                       Buffers: shared hit=1252
                       I/O Timings: read=0.000 write=0.000
                       ->  Index Scan using index_oauth_applications_on_owner_id_and_owner_type on public.oauth_applications  (cost=0.43..3.45 rows=1 width=4) (actual time=0.016..3.662 rows=5001 loops=1)
                             Index Cond: ((oauth_applications.owner_id = 9970) AND ((oauth_applications.owner_type)::text = 'Namespace'::text))
                             Filter: (oauth_applications.organization_id = 1)
                             Buffers: shared hit=1252
                             I/O Timings: read=0.000 write=0.000
               ->  Sort  (cost=3.47..3.47 rows=1 width=96) (actual time=7.236..7.295 rows=1000 loops=1)
                     Sort Key: o.id
                     Sort Method: top-N heapsort  Memory: 131kB
                     Buffers: shared hit=2507
                     I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using index_oauth_applications_on_owner_id_and_owner_type on public.oauth_applications o  (cost=0.43..3.46 rows=1 width=96) (actual time=4.131..6.119 rows=5001 loops=1)
                           Index Cond: ((o.owner_id = 9970) AND ((o.owner_type)::text = 'Namespace'::text))
                           Filter: ((o.id >= (InitPlan 1).col1) AND (o.organization_id = 1))
                           Buffers: shared hit=2504
                           I/O Timings: read=0.000 write=0.000
Trigger RI_ConstraintTrigger_c_2290945145 for constraint fk_rails_432d441dae: time=6.272 calls=1000
Settings: effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4'
Query ID: -1529901244399655894

Summary:

Time: 25.232 ms
  - planning: 1.142 ms
  - execution: 24.090 ms
    - I/O read: 0.000 ms
    - I/O write: 2.471 ms

Shared buffers:
  - hits: 12658 (~98.90 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 26 (~208.00 KiB)
  - writes: 26 (~208.00 KiB)

INSERT (single-row, per-mutation)

INSERT — Authn::IamOutbox.create! (~2.3ms)

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/54307/commands/156960

INSERT INTO iam_outbox (organization_id, entity_id, event_type, entity_type, payload, created_at, updated_at)
VALUES (1, 424242, 0, 'oauth_application', '{}'::jsonb, NOW(), NOW());
Insert on public.iam_outbox  (cost=0.00..0.02 rows=0 width=0) (actual time=0.520..0.520 rows=0 loops=1)
   Buffers: shared hit=141
   WAL: records=5 fpi=0 bytes=452
   I/O Timings: read=0.000 write=0.000
   ->  Result  (cost=0.00..0.02 rows=1 width=190) (actual time=0.073..0.074 rows=1 loops=1)
         Buffers: shared hit=15
         I/O Timings: read=0.000 write=0.000
Trigger RI_ConstraintTrigger_c_2290945145 for constraint fk_rails_432d441dae: time=1.701 calls=1
Settings: jit = 'off', random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: 6936637358172976415

Summary:

Time: 2.326 ms
  - planning: 0.068 ms
  - execution: 2.258 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 141 (~1.10 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Conclusion

  • Reads use index_oauth_applications_on_owner_id_and_owner_type; organization_id and id >= <batch_start> are applied as filters. No sequential scan, no new index required.
  • The insert_all! is ~25ms per 1000-row batch into iam_outbox (PK-only index), ~498KB WAL per batch. The per-row FK trigger on organization_id (fk_rails_432d441dae -> organizations) accounts for ~6ms of that; it is inherent to the FK and bounded by the 1000-row batch.
  • The single-row create! (hot path, every create/update/destroy) is ~2.3ms with no scan (Result node), dominated by the same fk_rails_432d441dae FK check (~1.7ms, 1 call), so per-mutation cost is negligible.
  • each_batch caps each batch at 1000 rows, so per-statement cost stays flat regardless of how many applications a transfer moves.

References

Edited by Daniele Bracciani

Merge request reports