RDS - Read/Write Replica Loadbalancing

Question

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!

Answer

Hi AWS-User-4612171,

today I learned that there's also a commercial offering by the AWS partner [Heimdall Data](https://www.heimdalldata.com/), 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](https://www.youtube.com/watch?v=jjINqyPS10c) 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](https://aws.amazon.com/blogs/apn/using-the-heimdall-proxy-to-split-reads-and-writes-for-amazon-aurora-and-amazon-rds/).

Get the product directly from the [AWS Marketplace](https://aws.amazon.com/marketplace/seller-profile?id=ca9b43e9-af75-4d5b-bf22-89b138927902&ref=dtl_B079817LYD).

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.

Answer

Hi,
have a look at the AWS Big Data Blog: [Query Routing and Rewrite: Introducing pgbouncer-rr for Amazon Redshift and PostgreSQL](https://aws.amazon.com/blogs/big-data/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](https://github.com/awslabs/pgbouncer-rr-patch) 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](https://github.com/ibot3/pgbouncer-rr-patch/pull/1/files) that adds *transaction state awareness*, so that all statements within a transaction can be sent to the same instance.

------

[crunchy-proxy](https://github.com/CrunchyData/crunchy-proxy#readme) 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.

RDS - Read/Write Replica Loadbalancing

관련 콘텐츠