Thread: Logical Replication and table bloat
Hello, Yesterday we stumbled upon a performance issue that we were not expecting. We are replicating our database using AWS DMSwhich uses logical replication to capture changes. We have some hot tables that get updated very regularly and with theDMS turned on we started noticing that in those table, table bloat increased considerably ~15 times more free_tuples thanthe average. When doing logical replication, the subscriber will hold the tuples that could be flagged for reuse until they are sent ?Just trying to understand a little bit better how the logical replication is affecting the vacuuming. Thanks before hand! Best, Martín
On 6/5/20 12:53 PM, Martín Fernández wrote: > Hello, > > Yesterday we stumbled upon a performance issue that we were not expecting. We are replicating our database using AWS DMSwhich uses logical replication to capture changes. We have some hot tables that get updated very regularly and with theDMS turned on we started noticing that in those table, table bloat increased considerably ~15 times more free_tuples thanthe average. > > When doing logical replication, the subscriber will hold the tuples that could be flagged for reuse until they are sent? Just trying to understand a little bit better how the logical replication is affecting the vacuuming. A question for the AWS folks: https://forums.aws.amazon.com/forum.jspa?forumID=60 > > Thanks before hand! > > Best, > Martín > -- Adrian Klaver adrian.klaver@aklaver.com
On 2020-06-05 21:53, Martín Fernández wrote: > Yesterday we stumbled upon a performance issue that we were not expecting. We are replicating our database using AWS DMSwhich uses logical replication to capture changes. We have some hot tables that get updated very regularly and with theDMS turned on we started noticing that in those table, table bloat increased considerably ~15 times more free_tuples thanthe average. > > When doing logical replication, the subscriber will hold the tuples that could be flagged for reuse until they are sent? Just trying to understand a little bit better how the logical replication is affecting the vacuuming. As far as vacuum is concerned, it is very similar to a normal client session: It may insert tuples, update tuples, delete tuples; update and delete create bloat, autovacuum should come along to clean up. There isn't normally any separate vacuum tuning necessary for this, but if you are experiencing issues, first treat it like a normal vacuum configuration problem. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services