pg_relusage
pg_relusage is a PostgreSQL extension which allows one to discover and log the relations used in SQL statements. Supported PostgreSQL versions: 9.5 to 17
Why?
This extension will be useful if you are dealing with (large?) legacy database and suspect that it contains plenty of unused objects.
This extension will allow you to quickly get an understanding of which clients use which objects / relations. Unlike statement log, which will only show you the views and tables explicitly referenced by the query, this extension will look at the result of view expansion, unused joins elimination etc etc and report the relations that were actually used by the statement.
Installation
Extension hooks into PostgreSQL query executor and therefore needs to be compiled using PostgreSQL headers for the appropriate server version, and then loaded into server process.
Extension uses standard PGXS build infrastructure and (provided that pg_config is somewhere in your PATH) could be built with make install.
Usage
After installation, extension has to be either loaded into a single client process via load statement or enabled for all sessions globally via shared_preload_libraries configuration parameter.
Once extension is loaded, each SQL statement will emit one extra log message which will list all the referenced relations (by name).
Statement:
select * from pg_stats limit 1;
should produce log message along the lines of:
relations used: pg_stats,pg_statistic,pg_class,pg_attribute,pg_namespace
Configuration
Extension provides two user settings:
- pg_relusage.log_level (defaults to
LOG), with the obvious meaning and the same set of values that similar configuration items (likeclient_min_messagessupport) - pg_relusage.rel_kinds (defaults to
'riSvmfp') which specifies which relation kinds will be reported. This is a list of one-letter codes used in therelkindfield in thepg_class(full list could be seen here).
Limitations
The earliest supported version is PostgreSQL v9.5.
Development and testing
In addition to make install && make installcheck, you can run ./all_tests.sh to run tests in the Docker container (which will test all supported PostgreSQL versions) or ./run_test 11 to test just the specific version (11, in this case).