I promoted a Postgres 11.16 RDS cluster secondary to function as a stand-alone database. This caused a significant decrease in the RR’s performance. Digging deeper, when comparing the results of running Postgres EXPLAIN on a single production read replica (PROD), and the promoted read replica (RR), there is a 40x decline in performance on the RR. According to AWS there is no change in the environment so I started looking into what Postres is doing.
Here is a summary of an EXPLAIN query; notice the 40x jump in the Actual Total Time After Startup:
| Database | Estimated Startup Cost (units) | Estimated Total Cost (units) | Actual Startup Time (ms) | Actual Total Time After Startup (ms) |
|---|---|---|---|---|
| PROD | 329.96 | 29,132.77 | 18.624 | 23.968 |
| RR | 296.49 | 26,883.29 | 28.733 | 974.719 |
Part of the performance loss, about half, goes away after 24 hours.
Are there side effects to promoting a RR to a standalone DB? I.e., does Postgress need to do additional organization due to the change?