We have been maintaining a project which has both web and mobile application platform. The backend of the project is developed in Django 1.10 and deployed in AWS.

At the beginning, when there were few users, we deployed using one EC2 instance and an RDS instance with a PostgreSQL database. After some time the number of users increased and we were facing problems like very slow response, time-out in different pages etc. Due to performance issue we took the following measures:

  • We started using Redis cache for frequently accessed data (ElasticCache in AWS)
  • We deployed in two different EC2 instances (one for web platform and another one for API for mobile application).
  • We created a read-replica in RDS and added a Database router which chooses to use master database only for write operations and all read operations are performed in read-replica database.

This solution was working good for fast few weeks. After some time, all read operations became too slow. At this time, number of database connections to master database was on average 2-3 and spiked to 5-7 at times. But due to slow query execution in read replica database, 30-50 connections were common in read-replica database.

Queries using JOIN and Aggregates often failed with the following error in read-replica:

canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed.

But generally all queries were very slow in read-replica compared to master database, even the simplest SELECT queries.

To be sure that the problem is not with a specific read-replica instance, we created another read-replica RDS instance (say read-replica-2) and pointed all read operations to read-replica-2 DB. This configuration began to perform well at the beginning but performance declined significantly within one day (for the first read-replica, it took 3-4 weeks).

After that, we modified our database router to randomly peak either one of the read-replica and read-replica-2 for any read operations, but still all queries to both of the read-replica databases are being executed very slowly. We checked by switching read operations to master database, and same read operations are being executed smoothly in master database without any problem.

Some server load related information:

  • At peak hours, 500-1000 users use the system, most of them are from mobile application (through API EC2 instance).
  • At peak hours, few users access web platform. But they often perform heavy DB intensive tasks (like bulk import and export of data).
  • At off-peak hours (in a 6 hours window at local night time), some heavy DB intensive cron jobs are performed in the system for report generation and maintenance.

What should be a suitable architecture for us given the scenario? Are we missing something obvious? What can cause same queries run fast in master database and very slowly in read-replica database in RDS?


1 Answer 1


Slowness other issue but I can tell you about the query time out due to recovery error.

canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed.

Its happening because wal files are getting applied on read replica at time when select queries are running. This will terminate the queries.

There are two solution to it.

  1. hot_standby_feedback enable this. But be careful if suppose read replica queries are running for longer then wal files keep accumulating and cause disk full prod also lag will be higher. So don't suggest an ideal approach.

  2. Enable these parameters and increase the value of both considering highest time you require for any particular giant query execution. Let us say if highest query run with in 10 min on replica then keep the value of below parameters to bit higher value.

900000 ms ( 15 min) max_standby_archive_delay = 900000
max_standby_streaming_delay = 900000

I too faced same issue and resolved using this approach.

These parameters need to set for the replica parameter group.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.