support separate connection strings for read and write operations

[joninsky]

AWS Aurora has a feature where it replicates read copies of your DB across multiple availability zones in a VPC. I think the purpose is to get better performance and increase DB redundancy.

Here is a little documentation on Aurora DB Clusters.

Could Hasura at some point support multiple DB Connections with read write options? Or, thoughts on how to design connection options to take advantage of Aurora? (Running as PostgreSQL 10.6)

[0x777]

Could Hasura at some point support multiple DB Connections with read write options?

This is something we will definitely add in the near future, maybe by an optional 'read-only' connection string?

[litchfield]

maybe by an optional 'read-only' connection string?

Multiple connection strings, each with a "read only" flag, default off (read/write). Reason being that if you're using read replicas, you'll quite likely have more than one (eg Aurora supports up to 15x).

(It's also possible to have multiple-masters, although rare).

[coco98]

@litchfield I was about to suggest the same thing, that the input could be an array of objects, each object has the endpoint and a read-only flag.

However, I'm thinking that ideally one shouldn't have to reconfigure Hasura for changes in the number of read-replicas or the exact endpoints. Most services should offer a common "reader endpoint" that spreads queries across the read replicas.

For example: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Overview.Endpoints.html#Aurora.Endpoints.Reader

Thoughts?

[joninsky]

I think this paragraph in the documentation is key:

"Using endpoints, you can map each connection to the appropriate instance or group of instances based on your use case. For example, to perform DDL statements you can connect to whichever instance is the primary instance. To perform queries, you can connect to the reader endpoint, with Aurora automatically performing load-balancing among all the Aurora Replicas. For clusters with DB instances of different capacities or configurations, you can connect to custom endpoints associated with different subsets of DB instances. For diagnosis or tuning, you can connect to a specific instance endpoint to examine details about a specific DB instance."

The design of multiple endpoints with a --read-only flag holds up here. You can just pass the Aurora read endpoint and if that endpoint is the one that performs load balancing across your cluster then great. You would also have the option to pass a custom endpoint. This works for Aurora and any other service that might offer the same design.

[Turee]

maybe by an optional 'read-only' connection string?

Multiple connection strings, each with a "read only" flag, default off (read/write). Reason being that if you're using read replicas, you'll quite likely have more than one (eg Aurora supports up to 15x).

(It's also possible to have multiple-masters, although rare).

I think two endpoints should be enough:

• one for read/write
• one for read-only

RDS handles the load balancing between readers, and if you are rolling your own PG cluster I think it would be wise to add the read replicas behind a TCP load balancer so you don't have to make application configuration changes when you add/remove replicas.

[bpiel]

Apologies if this is inappropriate hijacking, but:

Has anyone considered, or tried, solving this with pgpool?

https://aws.amazon.com/blogs/database/a-single-pgpool-endpoint-for-reads-and-writes-with-amazon-aurora-postgresql/

I found pgpool just a few minutes ago, but can't find anything online about hasura+pgpool. Glancing over the docs linked above, it seems like it may be a solution to the original problem.

[ghost]

It's more like an emergency for HA and applications running a lot of requests to PostgreSQL cluster.

I run a PostgreSQL cluster similar to https://github.com/vitabaks/postgresql_cluster#type-a-postgresql-high-availability-with-load-balancing

Where we can do all write on master (only) and all read on all replicas.

At this time, I'm looking to inspect request and do routing depending if it's a mutation or not, so I plan to setup this architecture:

request arrives to nginx (with inspection in LUA) and evalute boolean: if mutation

• if yes => proxy_pass to upstream hasura_master
• if no => proxy_pass to upstream hasura_replicas

It's not very good but I don't look another way at this time. It's bad to build a tool like Hasura without using the possibility to read through replicas.

[mehmetaydogduu]

+1 for multiple db connections, both read and write.

[mralanlee]

I think two endpoints should be enough:

• one for read/write
• one for read-only

RDS handles the load balancing between readers, and if you are rolling your own PG cluster I think it would be wise to add the read replicas behind a TCP load balancer so you don't have to make application configuration changes when you add/remove replicas.

Ideally, if you could support an array or list of read replicas that'd be great. RDS (specifically Aurora) seems to support load balancing but other platforms like GCP CloudSQL does not automatically load balance this.

[herenickname]

Update

[ttelfer]

+1

[naveennazimudeen]

+1 . Any updates?

[dvekeman]

Any updates on this?

Hasura cloud seems to have HASURA_GRAPHQL_READ_REPLICA_URLS (https://hasura.io/docs/cloud/1.0/manual/read-replicas.html). It would be nice to be able to use it as well on ECS.

(We are running an AWS Aurora db cluster with read replicas which provides a single endpoint for the READER replicates and we have Hasura running on ECS (Fargate))

Thanks!

[TejasQ]

+1

[mduecker]

+1 hoping that HASURA_GRAPHQL_READ_REPLICA_URLS will be a feature available for Hasura Core users too

[perrosnk]

Is there a plan to support read replicas for the Core users too?

[robnewton]

+1 we will need this functionality in Core soon as well

[clementnero]

+1 ! On my opinion it's a needed feature to use the core version of Hasura in production. I have the situation where I must use OSS only. Can someone give a clear answer if there is a chance to see this feature included in the core version of Hasura ? @0x777 ... ?

[raphaeltm]

Would love to see this in core. Was thinking of just setting up multiple hasura deployments, but this would be a lot easier.

[danazkari]

This would be very helpful in order to get the best performance out of our database setup. Please add this feature or at least provide a clear path so that the community can help make this happen, this is clearly a very needed feature and I'm sure the community would be more than glad to lend a hand or two.

[blennopardim]

+1 we will need this functionality in Core soon as well

[TejasQ]

I understand this is open source and we aren't entitled to any of this for free -- so I am happy to sponsor this project or pay or do anything at all to help make this feature a reality.

I use Hasura daily professionally and recommend it to everyone I know. It will become even more recommended if the ceiling of vertical DB scale is removed and we can scale horizontally to n replicas.

[mdonajca]

+1

[msalehy]

https://hgiasac.github.io/posts/2020-12-21-Read-Replicas-on-Hasura-OSS.html

[abdullah2993]

any plans on adding the same option to hasura core?

[AndrewCraswell]

https://hgiasac.github.io/posts/2020-12-21-Read-Replicas-on-Hasura-OSS.html

@msalehy I'm having trouble understanding why you would need a special image to support Read Replicas here. It seems like the Apollo Client split() function here is enough, right? You can setup the self-hosted engine in several regions. In the primary region, the engine points to the read/write primary database. All other regions would point to a single read replica.

Then using the split function, you ensure that only the mutations go to the primary hasura engine, and the subscriptions or queries route to the engine pointing to a replica. Of course, if you have multiple regions with replicas, then you would need to hide them behind a traffic manager so that there is one endpoint that will just go to the nearest replica. This way you can deploy your frontend with just two connection strings: one for mutations, and the other for reads.

I haven't tried this, but I don't see why it wouldn't work.

[msalehy]

Practically split() simulate same practice from client side. But having read replica need to manage by provider that in this case is Hasura. if you check hasura cloud they do have this feature. any many other network reasons that force solution to have read replica.

[tirumaraiselvan]

Hey folks,

Read replicas are available only on Cloud and EE offerings. Read more about them here: https://hasura.io/docs/latest/databases/connect-db/read-replicas/

For OSS, a workaround is to add a read-replica as a distinct source to a different Hasura deployment and then choose the appropriate graphql endpoint based on query/mutation on the client side. We understand that this may not work for all environments and we recommend checking out our Cloud/EE offerings which has many features for performance and security.

[davidpanic]

Boooo! Failover and read replicas are such a core feature of database software nowadays that you should be offering them to people without having to use your cloud service. You're definitely going to lose customers over this.

[JefferyHus]

This is absolutely nuts, use our cloud or don't use our solution. Hire a better sales managment team