pg_stat_tcpinfo

pg_stat_tcpinfo : Linux TCP connection statistics, latency and retransmissions

Overview

Packages

Source

pig build pkg pg_stat_tcpinfo;		# build rpm/deb

Install

Make sure PGDG repo available:

pig repo add pgdg -u    # add pgdg repo and update cache

Install this extension with pig:

pig install pg_stat_tcpinfo;		# install via package name, for the active PG version

pig install pg_stat_tcpinfo -v 18;   # install for PG 18
pig install pg_stat_tcpinfo -v 17;   # install for PG 17
pig install pg_stat_tcpinfo -v 16;   # install for PG 16
pig install pg_stat_tcpinfo -v 15;   # install for PG 15
pig install pg_stat_tcpinfo -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION pg_stat_tcpinfo;

Usage

Sources:

• README
• Installation SQL
• Extension control
• Release 1.1
• License

pg_stat_tcpinfo 1.1 exposes Linux TCP socket statistics through SQL. It helps investigate slow client connections, replication links and foreign-server traffic using round-trip time, retransmissions, congestion control and queue sizes, without shell access to the host.

It requires Linux 4.18 or later, access to /proc, and the NETLINK_INET_DIAG interface. Install as a superuser; no preload or server restart is required.

Core Workflow

CREATE EXTENSION pg_stat_tcpinfo;

SELECT a.pid, a.usename, a.application_name,
       t.dst_addr, t.dst_port, t.state, t.recvq, t.sendq,
       t.tcpinfo->>'rtt' AS rtt_ms,
       t.tcpinfo->>'total_retrans' AS retransmissions,
       t.tcpinfo->>'congestion' AS congestion_control
FROM pg_stat_activity a
JOIN pg_stat_tcpinfo t ON t.pid = a.pid
WHERE t.state = 'ESTABLISHED';

Objects and Metrics

The pg_stat_tcpinfo view calls pg_stat_get_tcpinfo(). Each row contains a process and OS user ID, local and remote addresses and ports, connection state, receive and send queues, and a tcpinfo JSONB object. IPv4 and IPv6 sockets are supported. The rtt field is expressed in milliseconds; total_retrans is the cumulative retransmission counter.

The view includes every TCP socket owned by the server’s OS user, including sockets from other database clusters or programs running under that account. Joining on the backend PID narrows the result to connections visible in the current cluster. Unix-domain connections are not TCP sockets and do not appear; TIME-WAIT sockets have no owner and are omitted. Metrics unavailable on an older kernel may be null.

Permissions

The installation revokes public execution of the function and grants function and view access to pg_read_all_stats; pg_monitor inherits that access. Ordinary application roles cannot read the metrics by default. Additional grants expose all sockets owned by the server OS user, not only the caller’s database connections.

Operational Boundaries

Each call scans process descriptors and queries the kernel; it is an on-demand observation, not a historical sampler. Container PID and network namespaces and procfs restrictions determine which sockets and PIDs are visible. Choose a sampling interval appropriate to the number of processes and connections.

Use a package built for the server’s PostgreSQL major version. The 1.1 release includes fresh-install SQL for versions 1.0 and 1.1 but no upgrade script between them, so an existing 1.0 installation has no upstream-provided ALTER EXTENSION pg_stat_tcpinfo UPDATE path.