pg_relusage

Overview

Version

Build

You can build the RPM / DEB packages for pg_relusage using pig build:

pig build pkg pg_relusage         # build RPM / DEB packages

Install

You can install pg_relusage directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_relusage;          # Install for current active PG version

pig ext install -y pg_relusage -v 18  # PG 18
pig ext install -y pg_relusage -v 17  # PG 17
pig ext install -y pg_relusage -v 16  # PG 16
pig ext install -y pg_relusage -v 15  # PG 15
pig ext install -y pg_relusage -v 14  # PG 14

dnf install -y pg_relusage_18       # PG 18
dnf install -y pg_relusage_17       # PG 17
dnf install -y pg_relusage_16       # PG 16
dnf install -y pg_relusage_15       # PG 15
dnf install -y pg_relusage_14       # PG 14

apt install -y postgresql-18-pg-relusage   # PG 18
apt install -y postgresql-17-pg-relusage   # PG 17
apt install -y postgresql-16-pg-relusage   # PG 16
apt install -y postgresql-15-pg-relusage   # PG 15
apt install -y postgresql-14-pg-relusage   # PG 14

Preload:

shared_preload_libraries = 'pg_relusage';

Usage

pg_relusage: log relations actually used by SQL statements

pg_relusage hooks into the query executor and logs which relations (tables, views, indexes, etc.) are actually used by each SQL statement. Unlike the statement log, it reports relations after view expansion and unused join elimination.

How It Works

Once loaded, each SQL statement emits a log message listing all referenced relations:

SELECT * FROM pg_stats LIMIT 1;

Produces log output:

relations used: pg_stats,pg_statistic,pg_class,pg_attribute,pg_namespace

Loading

-- Per-session
LOAD 'pg_relusage';

-- Or globally in postgresql.conf
shared_preload_libraries = 'pg_relusage'

Configuration

Relation kind codes: r = table, i = index, S = sequence, v = view, m = materialized view, f = foreign table, p = partitioned table.

Use Case

This extension is useful for discovering unused objects in legacy databases. By analyzing the logged relation usage over time, you can identify which tables, views, and indexes are actually accessed by your application.