Horton

Thread

  1. Regarding logical replication issues with PostgreSQL versions 16 and above

    yexiu-glory <yexiu-glory@qq.com> — 2025-07-18T11:08:48Z

    I'm facing a problem here: our business requires logical data replication to other departments, but at the same time, sensitive fields need to be filtered out. Therefore, we used the column filtering function when creating logical replication. If we use `alter table table1 replica identity default;`, there is no issue at all. However, when encountering new business scenarios, we need to use the CSI columnar index function. Under the default setting, the synchronization delay of the CSI columnar index is severe. Therefore, we need to use `alter table table1 replica identity full;` so that the CSI columnar index can perform data comparison and only convert changed data when receiving data, thereby speeding up the data conversion efficiency of the CSI columnar index. However, after using `alter table table1 replica identity full;`, updating the table will result in an error. ERROR: cannot update table "table1" DETAIL: The column list used by the publication does not cover the replica identity. So, is there any solution to this problem, or is there a better approach to accelerate the conversion efficiency of the CSI columnar index.

  2. Re: Regarding logical replication issues with PostgreSQL versions 16 and above

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-07-18T13:40:06Z

    On Fri, 2025-07-18 at 19:08 +0800, yexiu-glory wrote:
> I'm facing a problem here: our business requires logical data replication to other
> departments, but at the same time, sensitive fields need to be filtered out.
> Therefore, we used the column filtering function when creating logical replication.
> If we use `alter table table1 replica identity default;`, there is no issue at all.
> However, when encountering new business scenarios, we need to use the CSI columnar
> index function. Under the default setting, the synchronization delay of the CSI
> columnar index is severe. Therefore, we need to use `alter table table1 replica
> identity full;` so that the CSI columnar index can perform data comparison and only
> convert changed data when receiving data, thereby speeding up the data conversion
> efficiency of the CSI columnar index. However, after using `alter table table1
> replica identity full;`, updating the table will result in an error.
> ERROR: cannot update table "table1"
> DETAIL: The column list used by the publication does not cover the replica identity.
> So, is there any solution to this problem, or is there a better approach to
> accelerate the conversion efficiency of the CSI columnar index.

I cannot help with that CSI thing.

The only workaround I can think of is to create an index on the publisher that
contains all the columns you need in the replica identity and then using
that with ALTER TABLE ... REPLICA IDENTITY USING INDEX ...

Yours,
Laurenz Albe

  3. Re: Regarding logical replication issues with PostgreSQL versions 16 and above

    DINESH NAIR <dinesh_nair@iitmpravartak.net> — 2025-07-28T06:06:54Z

    
Hi ,
            On Fri, 2025-07-18 at 19:08 +0800, yexiu-glory wrote:
> I'm facing a problem here: our business requires logical data replication to other
> departments, but at the same time, sensitive fields need to be filtered out.
> Therefore, we used the column filtering function when creating logical replication.
> If we use `alter table table1 replica identity default;`, there is no issue at all.
> However, when encountering new business scenarios, we need to use the CSI columnar
> index function. Under the default setting, the synchronization delay of the CSI
> columnar index is severe. Therefore, we need to use `alter table table1 replica
> identity full;` so that the CSI columnar index can perform data comparison and only
> convert changed data when receiving data, thereby speeding up the data conversion
> efficiency of the CSI columnar index. However, after using `alter table table1
> replica identity full;`, updating the table will result in an error.
> ERROR: cannot update table "table1"
> DETAIL: The column list used by the publication does not cover the replica identity.
> So, is there any solution to this problem, or is there a better approach to
> accelerate the conversion efficiency of the CSI columnar index.

>I cannot help with that CSI thing.

>The only workaround I can think of is to create an index on the >publisher that
>contains all the columns you need in the replica identity and then ?>using
>that with ALTER TABLE ... REPLICA IDENTITY USING INDEX ...

>Yours,
>Laurenz Albe

         For the logical treplication issues we can try this out :

  1.
Create a unique index on a non-sensitive and  non-null column field
Or create Surrogate Primary Key
  2.
Use that index as the REPLICA IDENTITY.

        

Regards

Dinesh Nair


________________________________
From: yexiu-glory <yexiu-glory@qq.com>
Sent: Friday, July 18, 2025 4:38 PM
To: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Regarding logical replication issues with PostgreSQL versions 16 and above

You don't often get email from yexiu-glory@qq.com. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification>
Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments.
I'm facing a problem here: our business requires logical data replication to other departments, but at the same time, sensitive fields need to be filtered out. Therefore, we used the column filtering function when creating logical replication. If we use `alter table table1 replica identity default;`, there is no issue at all. However, when encountering new business scenarios, we need to use the CSI columnar index function. Under the default setting, the synchronization delay of the CSI columnar index is severe. Therefore, we need to use `alter table table1 replica identity full;` so that the CSI columnar index can perform data comparison and only convert changed data when receiving data, thereby speeding up the data conversion efficiency of the CSI columnar index. However, after using `alter table table1 replica identity full;`, updating the table will result in an error. ERROR: cannot update table "table1" DETAIL: The column list used by the publication does not cover the replica identity. So, is there any solution to this problem, or is there a better approach to accelerate the conversion efficiency of the CSI columnar index.

  4. Re: Regarding logical replication issues with PostgreSQL versions 16and above

    yexiu-glory <yexiu-glory@qq.com> — 2025-07-29T07:00:17Z

    &gt;&gt;&gt;On Fri, 2025-07-18 at 19:08 +0800, yexiu-glory wrote:
&gt;
&gt;&gt;&gt;I'm facing a problem here: our business requires logical data replication to other departments,
&gt;&gt;&gt;but at the same time, sensitive fields need to be filtered out. Therefore,
&gt;&gt;&gt;we used the column filtering function when creating logical replication.
&gt;&gt;&gt;If we use `alter table table1 replica identity default;`, there is no issue at all.
&gt;&gt;&gt; However, when encountering new business scenarios, we need to use the CSI columnar index function.
&gt;&gt;&gt;Under the default setting, the synchronization delay of the CSI columnar index is severe.
&gt;&gt;&gt;Therefore, we need to use `alter table table1 replica identity full;`
&gt;&gt;&gt;so that the CSI columnar index can perform data comparison and only convert changed data when receiving data,
&gt;&gt;&gt;thereby speeding up the data conversion efficiency of the CSI columnar index. However,
&gt;&gt;&gt;after using `alter table table1 replica identity full;`, updating the table will result in an error.
&gt;&gt;&gt;ERROR: cannot update table "table1" DETAIL: The column list used by the publication does not cover the replica identity.
&gt;&gt;&gt;So, is there any solution to this problem, or is there a better approach to accelerate the conversion efficiency of the CSI columnar index.
&gt;&gt;&gt;
&gt;&gt;&gt;
&gt;&gt;On On Fri, 2025-07-18 at 21:40 +0800, laurenz.albe wrote:
&gt;&gt;I cannot help with that CSI thing.
&gt;&gt;The only workaround I can think of is to create an index on the &gt;publisher that
&gt;&gt;contains all the columns you need in the replica identity and then ?&gt;using
&gt;&gt;that with ALTER TABLE ... REPLICA IDENTITY USING INDEX ...
&gt;&gt;
&gt;&gt;Yours,
&gt;&gt;Laurenz Albe


&gt; On Fri, 2025-07-28 at 14:06 +0800,dinesh_nair write:
&gt;  For the logical treplication issues we can try this out :
&gt; 1.Create a unique index on a non-sensitive and &nbsp;non-null column field
&gt; Or create Surrogate Primary Key
&gt; 2.Use that index as the REPLICA IDENTITY.




The approach of adding unique indexes is not suitable for our business.
Even if it is applicable to some individual tables, it is not suitable for all tables.
My idea is whether it is possible to synchronize only the data of all columns that are filtered
and retained based on the configuration of the filter columns during full synchronization.

  5. Re: Regarding logical replication issues with PostgreSQL versions 16and above

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-07-29T15:35:52Z

    On Tue, 2025-07-29 at 15:00 +0800, yexiu-glory wrote:
> The approach of adding unique indexes is not suitable for our business.

Well, then I guess that logical replication is not suitable for your business.

Yours,
Laurenz Albe