pg_relusage

pg_relusage : Log all the queries that reference a particular column

Overview

Packages

Source

pig build pkg pg_relusage;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

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

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

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_relusage';

This extension does not need CREATE EXTENSION DDL command

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.