Postgresql Connection Slowness : localhost vs 127.0.0.1 on Ubuntu server

localhost_vs_127.0.0.1_postgres_benchmark.md

The benchmark :

$ time psql -h localhost -U laravel_user -d test_db -c "select 1;"
Password for user laravel_user:
 ?column?
----------
        1
(1 row)

real    0m0.962s
user    0m0.037s
sys     0m0.023s

and :

$ time psql -h 127.0.0.1 -U laravel_user -d test_db -c "select 1;"
Password for user laravel_user:
 ?column?
----------
        1
(1 row)

real    0m0.614s
user    0m0.067s
sys     0m0.009s

That is a 350ms difference for a single lightweight query.

Quick diagnostic :

check how system resolves hostnames:

$ getent hosts localhost
::1             localhost ip6-localhost ip6-loopback

and :

$ getent hosts $(hostname)
127.0.1.1       java-station java-station java-station localhost java-station.localdomain
127.0.0.1       java-station java-station java-station localhost java-station.localdomain

When using localhost in application's configuration, the system may try to connect via ip v6 (::1) first or perform a hostname lookup which adds latency,
so using 127.0.0.1 directly forces an immediate ip v4 connection, faster in most vps environments.

Fix :

Using 127.0.0.1 instead of localhost in the application's configuration,

(when the application uses ip v4 and runs on the same server as the database).

And/OR clean up /etc/hosts file, example :

127.0.0.1   localhost
127.0.0.1   java-station java-station.localdomain
127.0.0.1   keycloak.local

# ip v6
::1         localhost ip6-localhost ip6-loopback
ff02::1     ip6-allnodes
ff02::2     ip6-allrouters