DEV Community

loading...

Check the connectivity of PG database

nuttapon profile image Nuttee Updated on ・1 min read

Check the connectivity of PG database

Basically, You want to know that the PG connection is still alive and ready for the queries used by your application.

You may have your application checked for the database connectivity via /ping for a health check.

Which Rails provide the built-in method for checking the status as follows;

> ActiveRecord::Base.connection.active?
> # true
Enter fullscreen mode Exit fullscreen mode

If true means that the PG connection is still alive; otherwise, it was down.

Or maybe you will try something like this instead.

> ActiveRecord::Base.connection.execute('SELECT 1')
> # (0.5ms)  SELECT 1
> #<PG::Result:0x007f8e98bf3758 status=PGRES_TUPLES_OK ntuples=1 nfields=1 cmd_tuples=1>
Enter fullscreen mode Exit fullscreen mode

The above sample works well, but I found that the query performance of SELECT 1 is worse than the empty string ''. It is raised on Here

The solution is referred to Use empty query instead of "SELECT 1" That improves database throughput to reduce the usage of CPUs in the database due to health checks.

Luckily, Rails supports queries with the empty string as follows;

> ActiveRecord::Base.connection.execute('')
> (0.4ms)
> #<PG::Result:0x007f8ea485e8e8 status=PGRES_EMPTY_QUERY ntuples=0 nfields=0 cmd_tuples=0>
Enter fullscreen mode Exit fullscreen mode

Discussion (0)

pic
Editor guide