i have logical replication setup on AWS RDS from a PG 9.5 primary to a PG 12.3 read replica via AWS DMS. (fyi the replica session_replication_role is set to "replica".)
query plans are not optimized on the PG 12.3 read replica and even basic queries run slowly without taking advantage of indexes. normally this is easily fixed by running "vacuumdb analyze".
question: is it safe to run analyze on the read replica? or should i run it on the master and it will propagate?
That should be no problem, since it modifies the table physically, but not logically.
However, I would only gather statistics with vacuumdb --analyze-only. Running VACUUM is seldom needed to improve query performance, and is uses way more resourcesthan ANALYZE.
I tried doing vacuumdb --analyze-only -j 1 on a read replica using Postgres 13.3 and it failed with an error saying that the replica was in recovery mode:
vacuumdb: processing database "mydb": Generating minimal optimizer statistics (1 target)
vacuumdb: error: processing of database "mydb" failed: ERROR: cannot execute ANALYZE during recovery
It seems that read replicas do get the statistics synced from primary DB, so there should be no need for vacuum analyze to be run on the replica.
This question covers a case where read-replica was had too little memory to use an index. So it may be worth trying a larger instance type for the read replica in case that helps.
