cancel
Showing results for 
Search instead for 
Did you mean: 

Getting the Most of out Application_Name

PostgreSQL Core Team - EDB

3/15/2017

 

Application_name might be one of those Postgres settings that you have seen in various places but never understood its purpose. It is true that setting application_name doesn't change the behavior of Postgres (with one small exception), but it is very useful for monitoring. Its value appears in pg_stat_activity and can be prepended to every log line with log_line_prefix.

Applications can set application_name when they connect as part of the connection string.Psql, pg_dump, and other tools set application_name. (They actually set fallback_application_name so application_name can be overridden by users.)

A more interesting use of application_name is to change it while the application is running. pgAdmin updates the application_name when a user changes screens so administrators can know exactly what screen users are on. While you can't change application_name while a query is running, you can change it between queries, which allows you to monitor the progress of long-running batch jobs, e.g.:

SELECT application_name
FROM pg_stat_activity
WHERE pid = pg_backend_pid();
 application_name
------------------
 psql

SET application_name = 'demo';

SELECT application_name
FROM pg_stat_activity
WHERE pid = pg_backend_pid();
 application_name
------------------
 demo

Some people might complain about the overhead of a separate query to update application_name. There are two solutions. One solution is to send two queries in a single string, e.g. select application_name = 'demo2'; select 100. Some libraries like libpq support this, but psql sends these as two separate queries — you have to enable log_statement to see how queries are sent to the server.

Another approach is to bundle the setting of application_name inside the query:

SELECT 100 FROM set_config('application_name', 'demo3', false);

SELECT application_name
FROM pg_stat_activity
WHERE pid = pg_backend_pid();
 application_name
------------------
 demo3

Unfortunately, there is no way to guarantee that set_config() will be run first, e.g. in the first query, set_config() is run first, and in the second query, run second, and the optimizer is allowed to run from-clause expressions in any order:

SELECT 100 FROM set_config('application_name', 'demo4', false), pg_sleep(5);

SELECT 100 FROM pg_sleep(5), set_config('application_name', 'demo5', false);

As you can see, application_name gives you a way to monitor what Postgres is doing, not only at connection time but during the life of the connection.

 

Bruce Momjian is Senior Database Architect at EnterpriseDB. 

This post originally appeared on Bruce's personal blog