RDS - Read/Write Replica Loadbalancing

1

Hi All,

We've identified that our RDS Aurora (Postgresql-13.6) cluster is a key bottleneck in our application's performance. Until we can rearchitect the code, we were hoping to try and mitigate some of this through infrastructure changes.

We're going to add additional read replicas to help reduce the amount of calls and hopefully allow more parallel processing to take place.

I'm having trouble pinpointing if there's a way to use a single endpoint, as we do now with our single write instance, and through some AWS feature have the queries be routed properly to the read and write instances based on the type of query. As opposed to making code changes where we have two sessions, one for read and one for write. I assume there must be a way but I can't seem find an exact answer.

If someone can post relevant documentation for me to review, that would be perfect, thank you!

2 Answers
0

Hi, have a look at the AWS Big Data Blog: Query Routing and Rewrite: Introducing pgbouncer-rr for Amazon Redshift and PostgreSQL. This should provide a suitable solution with no code changes.

Quick overview: The pgbouncer-rr project is based on PgBouncer, an open source PostgreSQL connection pooler that also works for Amazon Redshift. It adds two new significant features:

  1. Routing: intelligently send queries to different database servers from one client connection; use it to partition or load balance across multiple servers/clusters.
  2. Rewrite: intercept and programmatically change client queries before they are sent to the server: use it to optimize or otherwise alter queries without modifying your application.

There's a patch for this patch that adds transaction state awareness, so that all statements within a transaction can be sent to the same instance.


crunchy-proxy is a second solution that comes close, but it requires you to annotate at least the read-only queries - which means that you would have to touch your application code, which you wanted to avoid. But the changes you'd have to make are far less complicated than implementing different connection handling for reads and writes, so maybe it is viable for you.

profile pictureAWS
Uwe K
answered 2 years ago
0

Hi AWS-User-4612171,

today I learned that there's also a commercial offering by the AWS partner Heimdall Data, called the "Heimdall Proxy".

Most important for your use case is that it does read/write splits transparently, so there is no code change necessary to spread queries over several read replica.

You can find a hands-on lab with a short product presentation on YouTube and lots of whitepapers on the Heimdall Website linked above.

See also the AWS Blog post: Using the Heimdall Proxy to Split Reads and Writes for Amazon Aurora and RDS.

Get the product directly from the AWS Marketplace.

OP if you still read this and one of my answers satisfied your requirements, then I'd appreciate if you mark my answer as "accepted". Otherwise I thought I'd share with anyone coming here with a similar requirement.

profile pictureAWS
Uwe K
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions