Re: logical replication in high volume transactions

On Mon, Jul 5, 2021, 6:18 PM Yambu <hyambu(at)gmail(dot)com> wrote:

> Hello
>
> May i know if there is any drawback in using logical replication in a high
> volume of transactions per second?
> Has anyone used it without problems in a fast paced environment?
>

I am not sure what a high volume really means without knowing hardware
resources and network bandwidth, size of rows etc.
If you do not see saturation due to high volume nature, you need to know
that LR would spawn worker processes that would eat up existing resources,
so you need to up your resources accordingly.

Assuming you have enough resources to handle large concurrent connections
generating large amount of wals much more then the network can handle, it
would result in piling up at source.

https://severalnines.com/database-blog/how-optimize-postgresql-logical-replication

I had a flaky network when I migrated a db that was working like a queue
with 20k tps on a 8gb 2 vcpu server, it choked on network , but eventually
wals got flushed at the destination.
You also need to know that logical replication is severely impacted if the
table has large number of indexes, just like in DMLs, so used to drop
indexes at destination but primary keys and then rebuilt them concurrently
once a large part of db is synced.

Also one thing to note, newer pg versions are more robust with LR then the
pg10, with new features introduced to support partitions error handling etc.