Thread: Replica lag, high read IO, vacuum index scanning bug?

We are seeing an issue on RDS 9.4.5 where replica lag gets really high.
This lag seems to be correlated to a saturation of disk read throughput,
and vacuums of one of our larger and more frequently updated tables.

I've heard that there's a bug where the replica will scan indexes
unnecessarily when doing the WAL replay when the vacuums happen. Can
anyone confirm this and maybe point me towards the commit where it was
fixed? Has there been a backport to the 9.4 series?

Any other ideas for investigation or mitigation?

Phil Frost wrote:
> We are seeing an issue on RDS 9.4.5 where replica lag gets really high.
> This lag seems to be correlated to a saturation of disk read throughput,
> and vacuums of one of our larger and more frequently updated tables.
>
> I've heard that there's a bug where the replica will scan indexes
> unnecessarily when doing the WAL replay when the vacuums happen. Can
> anyone confirm this and maybe point me towards the commit where it was
> fixed? Has there been a backport to the 9.4 series?

Hi Phil,

The 9.4 backport of the patch in question is this commit by yours truly:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=30e3cb307d494990cb38cebff6bfc8b4ce27d7ab

This will appear in the next minor release of the 9.4 series (as well as
9.5), due out sometime during February 2017.

Thanks,

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services