Re: Need help debugging slow logical replication - Mailing list pgsql-general

ccing the mailist again in case someone else would have a idea how to debug:

Here is what I see in the pg_locks on subscribe at all time:

As you can see, it mostly concern following 3 tables, accounts,
ledger, pending_ledger_fees, which I have tried analyze, vacuum them
etc, none of them helped much:

I do see in the log these tables are getting autovaccumed *very*
frequently, is that a problem for logical replication?

2023-02-08 04:59:47 UTC::@:[13626]:LOG:  automatic vacuum of table
"dev.public.accounts": index scans: 0
pages: 0 removed, 71437 remain, 0 skipped due to pins, 11465 skipped frozen
tuples: 0 removed, 3590987 remain, 3522495 are dead but not yet
removable, oldest xmin: 2893907681
index scan bypassed: 4 pages from table (0.01% of total) have 5 dead
item identifiers
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 120107 hits, 0 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 708 bytes
system usage: CPU: user: 0.23 s, system: 0.00 s, elapsed: 1.01 s
2023-02-08 04:59:47 UTC::@:[13626]:LOG:  automatic vacuum of table
"dev.public.pending_fee_ledger": index scans: 0
pages: 0 removed, 199818 remain, 0 skipped due to pins, 117769 skipped frozen
tuples: 0 removed, 3670095 remain, 3520000 are dead but not yet
removable, oldest xmin: 2893907681
index scan not needed: 0 pages from table (0.00% of total) had 0 dead
item identifiers removed
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 164212 hits, 0 misses, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes
system usage: CPU: user: 0.27 s, system: 0.00 s, elapsed: 0.27 s

Thanks

On Tue, Feb 7, 2023 at 6:10 PM sunyucong@gmail.com <sunyucong@gmail.com> wrote:
>
> Hi Justin, thanks for the response!
>
> > REPLICA IDENTITY DEFAULT   will only use primary keys,  if the publisher  includes those tables, the subscriber
when replaying the WAL will stop throwing an error not knowing how to replay the UPDATE/DELETE.
 
>
> But I don't see any errors being thrown out in the postgresql logs?
> Should I be seeing it complain there? Is postgresql falling back to
> replica identity full here?
>
> However I checked that table, it doesn't seem to be making progress at
> all: so I suspect you are right that it is the problem.
>
> > Logical Replication is most likely broken at this point.
> >
> > I suggest stopping logical replication and correcting tables that don't have qualifying indexes for logical
replicationby creating the necessary indexes and avoid using replica identity full.  Then restart logical replication
fromthe beginning.
 
> >
> >