Graphql-engine: 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.

[fastengineer]

+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.

[ekifox]

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 ... ?