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

Sources:

• README.md
• Control file
• Implementation

pg_relusage 1.0.2 logs relations used by statements after plan simplification. It supports PostgreSQL 10–18 and defines no SQL objects. Loading the library enables it; optional CREATE EXTENSION only records installation.

Core Workflow

LOAD 'pg_relusage';
SET pg_relusage.nested_statements = 'fold';
SET pg_relusage.log_level = 'notice';
CREATE TABLE relusage_demo (id integer);
SELECT * FROM relusage_demo;

Operational Boundaries

All settings require superuser: pg_relusage.enabled defaults on, pg_relusage.log_level defaults log and pg_relusage.rel_kinds defaults riSvmfp. pg_relusage.nested_statements accepts all (default: each statement), top (ignore nested statements) or fold (merge nested relations into the outer log). Version 1.0.1 fixes fold handling after a failed subtransaction.

Use session_preload_libraries for new sessions without a restart, or shared_preload_libraries for all sessions with a restart; preserve existing preload entries. LOAD normally requires superuser. Log messages omit SQL text, so configure log_line_prefix to identify the client.

System catalogs are excluded. Relations eliminated by planning or startup pruning are excluded, but partitions skipped later during execution can still appear. INSERT into a partitioned table reports only its parent. Deferred triggers at COMMIT, COPY FROM triggers and planner-invoked functions are treated as top-level statements. DDL and maintenance commands are excluded. A quiet log alone does not prove an object can be dropped.

1.0.2 and Partitioned Tables

Version 1.0.2 reduces relation-membership and partition-matching overhead for statements involving many partitions. Log format and settings are unchanged. Replace the library and restart if using shared preload, or reconnect when using session preload or LOAD. If installation was recorded with CREATE EXTENSION, also run ALTER EXTENSION pg_relusage UPDATE.