PostgreSQL 19: Two nice little improvements: log_autoanalyze_min_duration and search_path in the psql prompt

Two nice little improvements have been committed for PostgreSQL 19. The first one is about logging the duration of automatic analyze while the second one is about displaying the current search_path in psql’s prompt.

Lets start with the improvement for psql. As you probably know, the default prompt in psql looks like this:

postgres@:/home/postgres/ [pgdev] psql
psql (19devel)
Type "help" for help.

postgres=# 

While I do not have an issue with the default prompt, you maybe want to see more information. An example of what you might do is this:

postgres=# \set PROMPT1 '%M:%> %n@%/%R%#%x '
[local]:5432 postgres@postgres=# 

Now you immediately see that this is a connection over a socket on port 5432, and you’re connected as the “postgres” user to the “postgres” database. If you want to make this permanent, add it to your “.psqlrc” file.

The new prompting option which will come with PostgreSQL 19 is “%S”, and this will give you the search_path:

postgres=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

postgres=# \set PROMPT1 '%/%R%x%..%S..# '
postgres=.."$user", public..# set search_path = 'xxxxxxx';
SET
postgres=..xxxxxxx..# 

Nice. You can find all the other prompting options in the documentation of psql, the commit is here.

The second improvement is about logging the time of automatic analyze. Before PostgreSQL 19 we only had log_autovacuum_min_duration. This logs all actions of autovacuum if they cross the specified threshold. This of course includes the auto analyze as well, but usually it is autovacuum taking most of the time. This is now separated and there is a new parameter called “log_autovacuum_min_duration”. You can easily test this with the following snippet:

postgres=# create table t ( a int , b text );
CREATE TABLE
postgres=# insert into t select i, i::text from generate_series(1,1000000) i;
INSERT 0 1000000
postgres=# alter system set log_autoanalyze_min_duration = '1ms';
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

postgres=# insert into t select i, i::text from generate_series(1,1000000) i;
INSERT 0 1000000

Looking at the log file there is now this:

2025-10-29 08:05:52.744 CET - 1 - 3454 -  - @ - 771LOG:  automatic analyze of table "postgres.public.t"
        avg read rate: 0.033 MB/s, avg write rate: 0.033 MB/s
        buffer usage: 10992 hits, 1 reads, 1 dirtied
        WAL usage: 5 records, 1 full page images, 10021 bytes, 0 buffers full
        system usage: CPU: user: 0.10 s, system: 0.01 s, elapsed: 0.23 s

Also nice, the commit is here.