0

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.

  • 2
    If you rarely have concurrent queries, then your senior is right: a second server wouldn't really help. In this case the only things that help are: more CPUs, more (and faster) hard disks and more memory. A replica typically only helps if distributing load if you have multiple queries running at the same time and each of them saturates the system. – a_horse_with_no_name Jul 29 '19 at 20:23
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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