Postgresql – how can be changed hot_standby parameter on postgres 9.6 aws/rds

I had the same issue with our staging RDS. After quite a while I found a blog post that says:

In a very busy database, with many writes per second, this number will remain fairly accurate. However, in a system where there are few writes, the "replication_delay" will continually grow because the last replayed transaction timestamp isn't increasing.

To test this I wrote a simply script which updates a random record every 0.5s and the lag went to 0.

From the AWS docs under Read Replica Limitations with PostgreSQL -

A PostgreSQL Read Replica reports a replication lag of up to five minutes if there are no user transactions occurring on the source DB instance

AFAIK, you're right that AWS RDS Aurora (a MySQL 5.6 fork) does not support automatic or transparent read/write splitting: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Aurora.html

In order to do that in a way that's completely transparent to the application, you would need an intermediate proxy. Your application would then always connect to the proxy, the proxy would then have to do packet inspection to examine each incoming query to determine if it's read-write, which then gets forwarded on to the master, or read-only, which can then get forwarded to any of N replicas.

Be aware that this has some notable implications: 1. This means that the proxy needs to understand the MySQL protocol 2. It needs to inspect each packet (query) and determine if it's RW or RO 3. It then needs to forward the query to the appropriate backend MySQL instance 4. It likely needs to keep track of each connection, maintaining a map of front-end connections between your app and the proxy, and backend connections to the mysqld instances. The front-end connection would remain stable, but the backend connection could change for each query. 5. You can potentially have some state issues as a result. For example, when you start an explicit transaction, create temporary tables, or set session variables in your connection... those could get lost when (transparently) switching backends. 6. This will have an impact on SSL and other security measures, as you are explicitly using a MITM 7. All of this typically adds quite a bit of overhead, and you will typically see noticeable query latency because of it.

This is a feature that we hope to have in the MySQL Router (the replacement for the old MySQL Proxy: http://mysqlhighavailability.com/mysql-router-on-labs-the-newest-member-of-the-mysql-family/), but we do not yet. It takes a lot of time and effort to do it properly, so as to minimize the effects noted above. One such proxy that does support that today is ProxySQL: http://www.proxysql.com (See the "Read write split" section)

You can grab the source and start playing with it here: https://github.com/renecannao/proxysql

Good luck!