On Tue, Jun 20, 2017 at 3:06 AM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com > wrote:
Not easily. You could play around with pg_xlogdump to see what's going
on in the WAL. But even if you figure it out, there is not much you can
do about it.
I could do a lot. For example, if I could confirm what I expect, that the upgrades on the table with arrays generates much traffic, then I could redesign the table.
Try perhaps logical replication. That would save you the bandwidth for
updating all the indexes at least. It might work for you.
Problems:
* I can’t really "try" things on production. The was the starting point of my email
* Logical replication is in 10.0 Beta 1. I might be oldschool but I would install 10.1 or maybe 10.0.2 into production
* If I am right about the arrays, logical replication would no help. The full arrays would still be sent on each update, because it’s a single column, and the indices on the array tables are much smaller
Yes, there would be some benefit from not transferring the indices and the vacuum, but I’m not into random attempts on a live system.
What I could do is:
1. Dump production and restore it on a test machine
2. Log the queries on production (as plain text) after the dump was started, maybe for a day
3. "grep" out the queries for the suspicious tables one by one
4. Replay the queries on the restored dump (on a test machine)
5. See how much WAL is written for each table (executing only the related updates from table to table)
This, by the way, would be easier to do with logical replication. I could use single table subscriptions, but then we are back to an earlier problem: it’s in beta.
It will be painful, but at least now I know I have to do it as I can’t easily read the WAL.
Thank you.
M
