Make use of pgx pool

[EssaAlshammri]

By submitting this pull request, I confirm that my contribution is made under the terms of the MIT license.

Problem/Feature

in the https://github.com/jackc/pgx/wiki/Getting-started-with-pgx they stated that using a pool of connection is better than using sql.Open and here I used a feature in the pgx stdlib which takes in a pool and returns a *sql.DB

Description of Changes:

• the database.New function now make use of a connection pool instead of sql.Open

Checklist

• [x] I have self-reviewed the changes being requested
• [x] I have updated the documentation (if applicable)

[H0llyW00dzZ]

By submitting this pull request, I confirm that my contribution is made under the terms of the MIT license.

Problem/Feature

in the https://github.com/jackc/pgx/wiki/Getting-started-with-pgx they stated that using a pool of connection is better than using sql.Open and here I used a feature in the pgx stdlib which takes in a pool and returns a *sql.DB

Description of Changes:

• the database.New function now make use of a connection pool instead of sql.Open

Checklist

• [x] I have self-reviewed the changes being requested
• [x] I have updated the documentation (if applicable)

By the way, does that connection pool work well with a single database instance pattern?

[EssaAlshammri]

@H0llyW00dzZ

By the way, does that connection pool work well with a single database instance pattern?

yeah it should work.

note: I don't see any where in the code the New function is called again to use the dbInstance instead of making a new connection

[EssaAlshammri]

taken from OpenDBFromPool docs

OpenDBFromPool creates a new sql.DB from the given pgxpool.Pool. Note that this method automatically sets the maximum number of idle connections in sql.DB to zero, since they must be managed from the pgxpool.Pool. This is required to avoid acquiring all the connections from the pgxpool and starving any direct users of the pgxpool.

the pool will be managed by pgx

[H0llyW00dzZ]

@H0llyW00dzZ

By the way, does that connection pool work well with a single database instance pattern?

yeah it should work.

note: I don't see any where in the code the New function is called again to use the dbInstance instead of making a new connection

Nice, because it worked fine for me too when using multiple databases for testing (redis and mysql rn). After testing MySQL with 100K fake POST requests, it was stable with an average response time as shown in the image:

Note: that image testing MySQL only with 100K fake POST requests

[briancbarrow]

Hey @EssaAlshammri, thanks for opening the PR. I was under the impression that sql.Open prepares the database for connection and automatically manages a pool of connections, which would allow for concurrency.

In the link you gave I didn't see any mention of sql.Open, only pgxpool.New() and pgx.Connect().

Can you elaborate why you think this would be good for the project?

[EssaAlshammri]

Hey @EssaAlshammri, thanks for opening the PR. I was under the impression that sql.Open prepares the database for connection and automatically manages a pool of connections, which would allow for concurrency.

In the link you gave I didn't see any mention of sql.Open, only pgxpool.New() and pgx.Connect().

Can you elaborate why you think this would be good for the project?

you are right.

I actually couldn't find where I read what I've stated above.

now I'm reading this from go docs

The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the OpenDB function should be called just once. It is rarely necessary to close a DB.

[EssaAlshammri]

never mind this pull request all it's doing is letting pgx manage the pool instead.

[briancbarrow]

No worries. Thanks again for looking for ways to contribute!