Find which functions are used

Question

I have a legacy PostgreSQL database schema with hundreds of functions. I know that some of them aren't used anymore, and I want to delete them.

What is the easiest way to find which procedures are executed sometimes and which aren't? Is there any kind of statistics about procedure usage in PostgreSQL?

Used engine version is PostgreSQL 9.5.

Answer 1

Yes, use the statistics collector. The manual:

PostgreSQL's statistics collector is a subsystem that supports collection and reporting of information about server activity. [...] It can also count calls to user-defined functions and the total time spent in each one.

[...]

The parameter track_functions enables tracking of usage of user-defined functions.

Bold emphasis mine.

track_functions (enum)

Enables tracking of function call counts and time used. Specify pl to track only procedural-language functions, all to also track SQL and C language functions. The default is none, which disables function statistics tracking. Only superusers can change this setting.

Note: SQL-language functions that are simple enough to be "inlined" into the calling query will not be tracked, regardless of this setting.

So this is off by default. If all relevant functions are LANGUAGE plpgsql, it's enough to set it to pl in postgresql.conf and reload. No need to restart the server for this setting.

track_functions = pl

Reload with pg_ctl reload. Or on a typical Debian(-related) installation:

sudo -u postgres pg_ctlcluster 12 main reload

Postgres starts to gather information. After an adequate period of time, check track records. Careful though, some important functions may not be called in weeks. Your call ...

How to access gathered statistics? The manual again:

When statistics collection is enabled, the data that is produced can be accessed via the pg_stat and pg_statio family of system views. Refer to Chapter 27 for more information.

Basically:

SELECT * FROM pg_stat_user_functions;

Or you can easily look it up with pgAdmin (or another GUI). In pgAdmin (3 or 4), select the function and look at the "Statistics" tab top right: "Number of Calls".

Aside:
There are no true "stored procedures" before Postgres 11, just functions. The term "procedure" is often used synonymously.