public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected] <[email protected]>
To: Önder Kalacı <[email protected]>
To: [email protected] <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: RE: [PATCH] Use indexes on the subscriber when REPLICA IDENTITY is full on the publisher
Date: Thu, 20 Oct 2022 02:37:43 +0000
Message-ID: <OSZPR01MB63109FF52120649EECACC7AEFD2A9@OSZPR01MB6310.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <CACawEhXBtt9aMoU0j6funj-s+CW+e8HMFCGz30gyEwLazXB_1w@mail.gmail.com>
References: <CACawEhVLqmAAyPXdHEPv1ssU2c=dqOniiGz7G73HfyS7+nGV4w@mail.gmail.com>
<CAA4eK1JfOBtdObF420-jnCYt8nhfWCWKq-7pX4KM3RQsjis+6Q@mail.gmail.com>
<CACawEhXgP_Kj_1iyNAp16MYos4Anrtz+OZVtj2z-QOPGdPCt_A@mail.gmail.com>
<CAA4eK1+mmsZqyVrVO6ey2QjYzd2EX344-jryiz3BXiHVwnQ7Eg@mail.gmail.com>
<CACawEhUZ=7nT=KWGza+ME0ztx=nczzOpdAQELLqr9KpcyKOCtQ@mail.gmail.com>
<CAA4eK1K9e1zVhNtkuRV128JqJ28pj2_vfBMiyDLie2BO7OKU4w@mail.gmail.com>
<CACawEhWV=H_jDyJPm_CUqkBZNMkK-1gtt3ddShj_7p3eF3iwRQ@mail.gmail.com>
<CAA4eK1+q_6dJksasaA1SOibQk2huUFUEvZRa3Mi0J9uU235Fvw@mail.gmail.com>
<CACawEhVZUJ0jDe3W-_U51Xt28p_s03sHGo82QmUm5t3X7aVQCg@mail.gmail.com>
<CAA4eK1J3fFvQUGoOPCsyUxwpXMBEA007CnEJd3qHw8jJngKq8A@mail.gmail.com>
<CACawEhXavM7kjzVgMfRSN+YyB_Lxqjoi8PJ2Gh7MdL-eScb9vw@mail.gmail.com>
<OSZPR01MB6310C166D4E56B521C61984AFD709@OSZPR01MB6310.jpnprd01.prod.outlook.com>
<CACawEhXr+CYdfr_0dE8F=2Sm3hSFOw5N8wKKPY+CyO7oBoMyAg@mail.gmail.com>
<OSZPR01MB63105BD4B03B3C1FBFCC7E6CFD249@OSZPR01MB6310.jpnprd01.prod.outlook.com>
<CACawEhWdoiaXwKjn5ZfAKF_Toz=t1aqT1ewHmJLRnXmKOQKwyg@mail.gmail.com>
<OS3PR01MB62754E945BD95A9B2A1164789E289@OS3PR01MB6275.jpnprd01.prod.outlook.com>
<CACawEhXBtt9aMoU0j6funj-s+CW+e8HMFCGz30gyEwLazXB_1w@mail.gmail.com>
On Wed, Oct 19, 2022 12:05 AM Önder Kalacı <[email protected]> wrote:
>
> Attached v19.
>
Thanks for updating the patch. Here are some comments on v19.
1.
In execReplication.c:
+ TypeCacheEntry **eq = NULL; /* only used when the index is not unique */
Maybe the comment here should be changed. Now it is used when the index is not
primary key or replica identity index.
2.
+# wait until the index is created
+$node_subscriber->poll_query_until(
+ 'postgres', q{select count(*)=1 from pg_stat_all_indexes where indexrelname = 'test_replica_id_full_idx';}
+) or die "Timed out while waiting for check subscriber tap_sub_rep_full updates one row via index";
The message doesn't seem right, should it be changed to "Timed out while
waiting for creating index test_replica_id_full_idx"?
3.
+# now, ingest more data and create index on column y which has higher cardinality
+# then create an index on column y so that future commands uses the index on column
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO test_replica_id_full SELECT 50, i FROM generate_series(0,3100)i;");
The comment say "create (an) index on column y" twice, maybe it can be changed
to:
now, ingest more data and create index on column y which has higher cardinality,
so that future commands will use the index on column y
4.
+# deletes 200 rows
+$node_publisher->safe_psql('postgres',
+ "DELETE FROM test_replica_id_full WHERE x IN (5, 6);");
+
+# wait until the index is used on the subscriber
+$node_subscriber->poll_query_until(
+ 'postgres', q{select (idx_scan = 200) from pg_stat_all_indexes where indexrelname = 'test_replica_id_full_idx';}
+) or die "Timed out while waiting for check subscriber tap_sub_rep_full updates 200 rows via index";
It would be better to call wait_for_catchup() after DELETE. (And some other
places in this file.)
Besides, the "updates" in the message should be "deletes".
5.
+# wait until the index is used on the subscriber
+$node_subscriber->poll_query_until(
+ 'postgres', q{select sum(idx_scan)=10 from pg_stat_all_indexes where indexrelname ilike 'users_table_part_%';}
+) or die "Timed out while waiting for check subscriber tap_sub_rep_full updates partitioned table";
Maybe we should say "updates partitioned table with index" in this message.
Regards,
Shi yu
view thread (61+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: RE: [PATCH] Use indexes on the subscriber when REPLICA IDENTITY is full on the publisher
In-Reply-To: <OSZPR01MB63109FF52120649EECACC7AEFD2A9@OSZPR01MB6310.jpnprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox