I have an application (AWS API Gateway) using an Aurora PostgreSQL cluster.
The cluster has 1 read/write (primary) and one reader endpoint.
At the moment, my application connections to the specific writer instance for all operations: rds-instance-1.xxx.ap-southeast-2.rds.amazonaws.com
But I have the following endpoints available:
rds.cluster-xxx.ap-southeast-2.rds.amazonaws.com
rds.cluster-ro-xxx.ap-southeast-2.rds.amazonaws.com
rds-instance-1.xxx.ap-southeast-2.rds.amazonaws.com
rds-instance-1-ap-southeast-2c.xxx.ap-southeast-2.rds.amazonaws.com
If I am doing read and write operations, should I be connecting to the instance endpoint I'm using? Or should i use rds.cluster-xxx.ap-southeast-2.rds.amazonaws.com ? What are the benefits of using the different endpoints? I understand that if I connect to a read only endpoint I can only do reads, but for read/writes what's the difference connecting to:
rds.cluster-xxx.ap-southeast-2.rds.amazonaws.com
Or
rds-instance-1.xxx.ap-southeast-2.rds.amazonaws.com
?
What is the right / best endpoint to use for general workloads, and why?
You should use cluster reader/writer endpoint.
rds.cluster-xxx.ap-southeast-2.rds.amazonaws.com
rds.cluster-ro-xxx.ap-southeast-2.rds.amazonaws.com
The main benefit of using cluster endpoint is that if the failover occurs due to some reason you will not worry about the endpoint and you will can expect a minimal interruption of service.
Or what if you have 3 read replica then how you will manage to connect the reader? so Better to use cluster reader/writer endpoint.
Using the Reader Endpoint
You use the reader endpoint for read-only connections for your Aurora cluster. This endpoint uses a load-balancing mechanism to help your cluster handle a query-intensive workload. The reader endpoint is the endpoint that you supply to applications that do reporting or other read-only operations on the cluster.
Using the Cluster Endpoint
You use the cluster endpoint when you administer your cluster, perform extract, transform, load (ETL) operations, or develop and test applications. The cluster endpoint connects to the primary instance of the cluster. The primary instance is the only DB instance where you can create tables and indexes, run INSERT statements, and perform other DDL and DML operations.
Instance endpoint
The instance endpoint provides direct control over connections to the DB cluster, for scenarios where using the cluster endpoint or reader endpoint might not be appropriate. For example, your client application might require more fine-grained load balancing based on workload type. In this case, you can configure multiple clients to connect to different Aurora Replicas in a DB cluster to distribute read workloads. For an example that uses instance endpoints to improve connection speed after a failover for Aurora PostgreSQL
You can check furhter details AWS RDS Endpoints