I am now moving my website from EC2 to lambda+RDS and I don't have much experience in RDS. As you know, AWS RDS supports a proxy to provide a connection pool and the lambda MySQL node.js module also supports a connection pool. Is it okay to use both pools? What's the benefit of using both pools? Any help would be appreciated. Thanks in advance.
Yes, it's OK to use a database connection pool in your application and RDS Proxy together.
The purpose of the database connection pool in your application is to make it more efficient. Lambda can reuse an instance of a function for multiple invocations, and you'd want to avoid opening and closing a database connection per invocation. Size the connection pool based on how many connections the instance of the function needs per invocation.
Lambda scales your application horizontally by creating multiple instances of the function. There's one connection pool per instance. So, let's say your pool size is 10, and there are 5 instances of the function. That means there are 50 (10 times 5) open database connections. Basically, you want to make sure that this number (the product of pool size times lambda concurrency) is less than the max connections setting of your database. Otherwise, you'll run out of database connections!
You'll have to think a bunch about how to configure those two things, application connection pool size and Lambda concurrency. This becomes even more difficult if you have multiple Lambdas, all scaling independently. And, as Lambda starts/stop function instances, your database might have to use a bunch of resources to open/close connections. Lastly, if you have a database failover, then your Lambda function will be unable to connect until the failover is complete.
RDS Proxy helps with all three of these problems. It lets Lambdas scale horizontally and independently, without maxing out the database's connections. It prevents the database from wasting resources on opening/closing connections as function instances come and go. And lastly, it reduces failover time.
So, yes, use them together. I provided a little more information about how RDS Proxy connection multiplexing in response to a different question: Does RDS proxy affects current application side pooling?.