Replay ctid lookup: primary-key ROW() bounds make the planner pick a prefix index (0.5 changes/s on a 1.3B-row table)

Inizia da writeCompositeIdentityCTIDPredicateMode e writeBatchIdentityPredicate, quindi esamina come sono rappresentati gli indici univoci e le colonne di replica identity della relazione di destinazione. Riproduci la ricerca di ctid con e senza limiti ROW sullo schema descritto e considera completata la modifica quando le identità univoche corrispondenti evitano i limiti, le identità di fallback li mantengono e il predicato generato è verificato.

Summary

During catchup on a real migration (feeds DB, shard_schema.follows, 1.31B rows, PK (app_pk, source_fid, target_fid)), the replay DELETE/UPDATE lookups ran at ~0.5 changes/second: 206 deletes applied in 5 hours, 7–8 applier sessions each spending minutes in IO:DataFileRead per statement.

The cause is the equal lower/upper ROW(...) bounds that writeCompositeIdentityCTIDPredicateMode(..., primaryKeyBounds=true) appends to the ctid lookup. Their comment says they exist to stop PostgreSQL from choosing a smaller prefix index; on this table they make PostgreSQL do exactly that.

Evidence (PostgreSQL 18.4, Cloud SQL, target freshly built + ANALYZEd by pgmigrate v71 / main 96ca2ad)

Statement as emitted by the applier:

DELETE FROM "shard_schema"."follows" AS pgmigrate_target
USING unnest($1,$2,$3) WITH ORDINALITY AS pgmigrate_batch(identity_0,identity_1,identity_2,ordinal)
WHERE pgmigrate_target.ctid=(SELECT pgmigrate_lookup.ctid FROM "shard_schema"."follows" AS pgmigrate_lookup
  WHERE pgmigrate_lookup."app_pk"=pgmigrate_batch.identity_0
    AND pgmigrate_lookup."source_fid"=pgmigrate_batch.identity_1
    AND pgmigrate_lookup."target_fid"=pgmigrate_batch.identity_2
    AND ROW(pgmigrate_lookup."app_pk",pgmigrate_lookup."source_fid",pgmigrate_lookup."target_fid")>=ROW(pgmigrate_batch.identity_0,pgmigrate_batch.identity_1,pgmigrate_batch.identity_2)
    AND ROW(pgmigrate_lookup."app_pk",pgmigrate_lookup."source_fid",pgmigrate_lookup."target_fid")<=ROW(pgmigrate_batch.identity_0,pgmigrate_batch.identity_1,pgmigrate_batch.identity_2)
  OFFSET 0)
RETURNING pgmigrate_batch.ordinal - 1

Plan chosen for the inner lookup with the ROW bounds (one real key):

Index Scan using idx_follows_target_status on follows l  (cost=0.70..3106.84 rows=1) (actual time=40252.700..40252.700 rows=1)
  Index Cond: ((app_pk >= one.app_pk) AND (app_pk <= one.app_pk) AND (app_pk = one.app_pk) AND (target_fid = one.target_fid))
  Filter: ((one.source_fid = source_fid) AND (ROW(...) >= ROW(...)) AND (ROW(...) <= ROW(...)))
  Rows Removed by Filter: 610968
  Buffers: shared hit=1 read=435672
Execution Time: 40252.836 ms

idx_follows_target_status is (app_pk, target_fid, status), 9 GB. The PK index is 96 GB. The target here is a popular account with 611k followers, so every delete of a follow to a popular target reads all of its followers.

Same lookup without the two ROW bounds, same key:

Index Scan using follows_pkey on follows l  (cost=0.70..3.32 rows=1) (actual time=5.554..5.555 rows=1)
  Index Cond: ((app_pk = one.app_pk) AND (source_fid = one.source_fid) AND (target_fid = one.target_fid))
  Buffers: shared hit=2 read=4
Execution Time: 5.619 ms

Column stats at the time: app_pk n_distinct 3, source_fid 897,999, target_fid 10,768 (real distinct is higher; eToro-style data, few targets with huge fan-in). random_page_cost=1.3, effective_cache_size 112 GB.

Why

btcostestimate only applies the "unique index, all columns matched by equality ⇒ 1 tuple" shortcut when every index qual is a plain equality. A RowCompareExpr on the leading column is not an equality, so the shortcut is lost on the PK, the PK's cost is estimated from generic selectivity, and a smaller index whose leading columns match two of the three equalities wins on size. In other words the bounds do not "keep the full ordered key as one index qual" — they knock the PK out of the cheapest-plan slot.

The ROW bounds were presumably added for the case where the replica identity is not the leading prefix of any unique index, or where the identity columns have poor stats. When the identity is the table's primary key (or any unique index over exactly those columns, in any order), the pure-equality form already forces the unique index and the bounds are strictly harmful.

Suggested fix

In writeCompositeIdentityCTIDPredicateMode / writeBatchIdentityPredicate: skip the ROW bounds when identityColumns equals the column set of a unique index on the target relation (the replica identity is almost always the PK, so this is the common case). Keep the bounds only as a fallback for identities with no matching unique index. Alternatively expose a flag to disable them.

Workaround used

Stopped the migration op from the controller, DROP INDEX idx_follows_target_status on the target, resumed. The index has to be recreated before cutover. Migration: INF-1754 (dublin.c9 → europe-west4:c9), runbook runbooks/google-migration/dublin/c9/migration-log.md in GetStream/chat.