Support PostgreSQL

[metaskills]

FORWARD: I'm not sure this work is worth it given RDS proxy is in preview. https://aws.amazon.com/about-aws/whats-new/2019/12/amazon-rds-proxy-available-in-preview/ However, if someone does want to push on this work, here are some thoughts since Aurora Serverless does support PostgreSQL.

During our initial work (#6) we did leave a good module & client structure in place so that we could mixing PostgreSQL client hacks as needed. My biggest question is, how would we handle loading, move the requires to the core ConnectionHandler?

[Genkilabs]

This is very interesting. Currently the docs for the RDS Proxy preview include the points:

• Currently, RDS Proxy is only available for the MySQL engine family. This engine family includes RDS MySQL 5.6 and 5.7, and Aurora versions 1 and 2. Proxy support for PostgreSQL databases isn't available in the public preview.
• You can't use RDS Proxy with Aurora Serverless clusters.

Meaning, RDS Proxy is not really a viable option for people who won't/can't convert from PostgreSQL (or won't be for some time).

Right now, this gem is the closest thing I have come across for Rails -> RDS Serverless PSQL ...

[metaskills]

So for "RDS Proxy" this gem would be moot right? My understanding is that it uses the same mysql/pg clients and protocols. Is that incorrect? I've not dug too deep on it quite yet.

[Genkilabs]

I believe that is their plan, but there is no timeline up yet that I could find.
We needed something now, so the pattern we ended up going with is:

Rails <-> net-ssh-gateway <-> Bastion AWS instance <-> Aurora Serverless

It let us use the default postgresql AR adapter at the cost of slightly more AWS moving parts.

For those who find their way here before RDS Proxy is available for their use case...
Basically, you can connect to serverless as normal (without this gem) if you connect through a TCP tunnel. For their own reasons, Amazon decided that for their serverless RDS product it would be locked down to ONLY allow access from within the same VPC, regardless of what the user wants to setup.

So, this approach to connect from an external ActiveRecord AbstractAdapter is to make an EC2 instance (or autoscaling group of them) and a security group that opens only one TCP port and has best practice keys/certs. Then use that bastion/jump-box to tunnel through.
The image can be just the default AWS Linux 2 box at size nano. But I recommend for something public facing folks make their own image and use the new Launch Template in combination with and Auto Scaling Group and a Load Balancer. Then benchmark and size up/out the boxes appropriately.

It takes some fiddling with the dashboards so I'm thinking to write up a gist when I get the time...
^_^

[metaskills]

Whoa, that's some absolute, next level, righteous hacks!

[joelvh]

Hey @Genkilabs I started using RDS Serverless PostgreSQL from Lambda and ran into an issue where some queries would hang. I thought it was Sequel (stripped down our Lambda function from using Rails), but it might seem it's the pg gem.

I had hoped to try the RDS proxy or Data API, but your research above was informative about the former... leaving the latter as an alternative to pg if that's really the issue in my case.

Did you ever run into behavior where queries hung? If not, maybe it's related to Lambda and maybe how it access RDS Serverless over the network. I haven't found others reporting this issue, so I'm hoping to narrow it down to a configuration issue.

Thanks.

[Genkilabs]

@joelvh Sorry for the radio silence. In our case we needed to be able to browse and modify the DB with existing, 3rd party tools as well as our code. The Data API is all well and good if you are ok with writing software to connect to it, but if you have a need for an existing toolkit to also touch your DB then it is (or was at the time we reviewed it) a non-starter.

As far as Lambda goes, our end user connections (the high traffic ones) to Serverless all go through AppSync. This lets us scale and connect to the serverless data source directly, as a first class citizen, rather than proxied through a runtime. Between the Resolvers and Functions, we were able to create individual graph queries composed form a highly relational DB design including self-referential tables, and polymorphic models, and thereby avoid Lambda altogether. If you can figure out how to limit Lambda usage to only data/queries that you absolutely must have complicated runtime operations on, it's a good move IMO.

ie the pattern we chose for this project was:
[Admin Toolkit] <-bastion/tunnel-> [Serverless PGSQL] <-AppSync-> [GraphQL client requests]

[metaskills]

I'd like to point out that now Lambda Containers is out, the use of this gem is really only driven by how small of a DB you need. If you are using Aurora proper, you can connect to MySQL or PG using gems (and libs) bundled in a container. If you use MySQL, I make that easy by avoid sys deps (https://github.com/customink/mysql2-lambda) but this is not required. You can always yum install client libs as needed for the container and ship.