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.
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.