Does a read replica help if the problem are heavy queries?

Question

The application that our team is responsible for is having ever more often DB performance problems. The application querying it doesn't throw lots of concurrent queries, the problem we have are rather some heavy queries joining a few tables. These queries are executed just every few minutes, and execution seldom overlap, but they sometimes make the DB choke and as the system has more data, it's a growing problem. The solution so far has been optimizing these queries, by trying e.g. to fetch less fields, and scaling up the DB.

I am relatively new to the team and today I asked if a read replica had been considered. I know little about DBs, so that felt like natural to me, but a senior engineer told me that it would barely help, because the read replica would have the same problems as the master: it would still need to do the same writes as the master, and the heavy queries would be equally heavy to it and the chances of it timing out, the same. The only gain would be the amount of them each of the instances has to serve.

Put like that, sounds very reasonable. My questions are:

1. Is a read replica really not of any help in this situation?
2. What are some alternatives?

PS: in case it matters, it's PostgreSQL.

Answer 1

If you had a lot of concurrent heavy queries, the replica could help spread the workload. Or if the heavy query was interfering with other work the master database needs to do. But if you don't run multiple queries at the same time and the problem is only that they take too long, there is no reason to think a replica would help that situation.

make the DB choke

What does that mean? Databases don't get food lodged in their trachea. Are you getting error messages(other than apparently query time-outs)?

The solution so far has been optimizing these queries, by trying e.g. to fetch less fields, and scaling up the DB.

There is a lot more to performance optimization than that. What about indexes? Are you using a version of PostgreSQL that supports parallelism? See https://dba.stackexchange.com/tags/postgresql-performance/info for more info on how to optimize (or at least, how to ask optimization questions).

What are alternatives? Well, if the only problem is that the queries time out, then change your time outs. Tune your queries, as above. Upgrade the database version so it can use parallelism.