Subject: PoC - psql - emphases line with table name in verbose output

From: Pavel Stehule <[email-masked]>
Date: Sun, 29 Mar 2026 18:42:46 +0200

Hi

Sometimes I have to run vacuum verbose in environments with hundreds of
tables. The result is pretty unreadable. Attached patch try introduce some
emphasis of interesting lines from INFO output. It is proof concept if some
game with output has some benefits or not.

What do you think about this?

Regards

Pavel
From abc37216aecb9b0201a8fa88d642d4e3cfd30432 Mon Sep 17 00:00:00 2001
From: "[email-masked]" <[email-masked]>
Date: Sun, 29 Mar 2026 18:01:16 +0200
Subject: [PATCH] initial

---
 src/common/logging.c | 17 ++++++++++++++++-
 1 file changed, 16 insertions(+), 1 deletion(-)

diff --git a/src/common/logging.c b/src/common/logging.c
index 5206949e5d8..c6229940f4f 100644
--- a/src/common/logging.c
+++ b/src/common/logging.c
@@ -30,11 +30,13 @@ static const char *sgr_error = NULL;
 static const char *sgr_warning = NULL;
 static const char *sgr_note = NULL;
 static const char *sgr_locus = NULL;
+static const char *sgr_info_command = NULL;
 
 #define SGR_ERROR_DEFAULT "01;31"
 #define SGR_WARNING_DEFAULT "01;35"
 #define SGR_NOTE_DEFAULT "01;36"
 #define SGR_LOCUS_DEFAULT "01"
+#define SGR_INFO_COMMAND_DEFAULT "07"
 
 #define ANSI_ESCAPE_FMT "\x1b[%sm"
 #define ANSI_ESCAPE_RESET "\x1b[0m"
@@ -143,6 +145,8 @@ pg_logging_init(const char *argv0)
 							sgr_note = strdup(value);
 						if (strcmp(name, "locus") == 0)
 							sgr_locus = strdup(value);
+						if (strcmp(name, "info_command") == 0)
+							sgr_info_command = strdup(value);
 					}
 				}
 
@@ -155,6 +159,7 @@ pg_logging_init(const char *argv0)
 			sgr_warning = SGR_WARNING_DEFAULT;
 			sgr_note = SGR_NOTE_DEFAULT;
 			sgr_locus = SGR_LOCUS_DEFAULT;
+			sgr_info_command = SGR_INFO_COMMAND_DEFAULT;
 		}
 	}
 }
@@ -331,7 +336,17 @@ pg_log_generic_v(enum pg_log_level level, enum pg_log_part part,
 	if (required_len >= 2 && buf[required_len - 2] == '\n')
 		buf[required_len - 2] = '\0';
 
-	fprintf(stderr, "%s\n", buf);
+	if (level == PG_LOG_INFO && sgr_info_command &&
+		(strncmp(buf, "INFO:  vacuuming", strlen("INFO:  vacuuming")) == 0 ||
+		 strncmp(buf, "INFO:  repacking", strlen("INFO:  vacuuming")) == 0 ||
+		 strncmp(buf, "INFO:  analyzing", strlen("INFO:  vacuuming")) == 0))
+	{
+		fprintf(stderr, ANSI_ESCAPE_FMT, sgr_info_command);
+		fprintf(stderr, "%s\n", buf);
+		fprintf(stderr, ANSI_ESCAPE_RESET);
+	}
+	else
+		fprintf(stderr, "%s\n", buf);
 
 	free(buf);
 }
-- 
2.53.0

---

From: Pavel Stehule <[email-masked]>
Date: Tue, 14 Apr 2026 05:42:24 +0200

Hi

rebase, new commit message and minor cleaning

Regards

Pavel
From 37bd1aa00fea7c7d88c81bea15694212e99b4e52 Mon Sep 17 00:00:00 2001
From: "[email-masked]" <[email-masked]>
Date: Sun, 29 Mar 2026 18:01:16 +0200
Subject: [PATCH] Print opening INFO lines with coulours

By default it use inverse printing for lines:

  INFO: vacuuming tablename
  INFO: repacking tablename
  INFO: analyzing tablename

It helps with orientation inside verbose output of REINDEX, VACUUM and ANALYZE commands.
---
 src/common/logging.c | 17 ++++++++++++++++-
 1 file changed, 16 insertions(+), 1 deletion(-)

diff --git a/src/common/logging.c b/src/common/logging.c
index 4a69d96281b..3e5e9eea6e8 100644
--- a/src/common/logging.c
+++ b/src/common/logging.c
@@ -32,11 +32,13 @@ static const char *sgr_error = NULL;
 static const char *sgr_warning = NULL;
 static const char *sgr_note = NULL;
 static const char *sgr_locus = NULL;
+static const char *sgr_info_command = NULL;

@@ -145,6 +147,8 @@ pg_logging_init(const char *argv0)
 
@@ -157,6 +161,7 @@ pg_logging_init(const char *argv0)
 			sgr_warning = SGR_WARNING_DEFAULT;
 			sgr_note = SGR_NOTE_DEFAULT;
 			sgr_locus = SGR_LOCUS_DEFAULT;
+			sgr_info_command = SGR_INFO_COMMAND_DEFAULT;
 		}
 	}
 }
@@ -353,7 +358,17 @@ pg_log_generic_v(enum pg_log_level level, enum pg_log_part part,
 	if (required_len >= 2 && buf[required_len - 2] == '\n')
 		buf[required_len - 2] = '\0';
 
-	fprintf(stderr, "%s\n", buf);
+	if (level == PG_LOG_INFO && sgr_info_command &&
+		(strncmp(buf, "INFO:  vacuuming", strlen("INFO:  vacuuming")) == 0 ||
+		 strncmp(buf, "INFO:  repacking", strlen("INFO:  repacking")) == 0 ||
+		 strncmp(buf, "INFO:  analyzing", strlen("INFO:  analyzing")) == 0))
+	{
+		fprintf(stderr, ANSI_ESCAPE_FMT, sgr_info_command);
+		fprintf(stderr, "%s\n", buf);
+		fprintf(stderr, ANSI_ESCAPE_RESET);
+	}
+	else
+		fprintf(stderr, "%s\n", buf);
 	if (log_logfile)
 	{
 		fprintf(log_logfile, "%s\n", buf);
-- 
2.53.0

---

From: Jim Jones <[email-masked]>
Date: Thu, 23 Apr 2026 17:17:47 +0200

Hi Pavel

> rebase, new commit message and minor cleaning
Thanks for the patch!

I tested the patch and setting PG_COLOR highlights the INFO messages.

A few observations:

== string matching is locale-fragile ==

Since the code relies on these fixed strings ...

if (level == PG_LOG_INFO && sgr_info_command &&
  (strncmp(buf, "INFO:  vacuuming", strlen("INFO:  vacuuming")) == 0 ||
   strncmp(buf, "INFO:  repacking", strlen("INFO:  repacking")) == 0 ||
   strncmp(buf, "INFO:  analyzing", strlen("INFO:  analyzing")) == 0))

.. the conditions only work if lc_messages is set to English. For
instance, in German you get a different string, which means that
highlighting won't work:

$ psql postgres -c "VACUUM VERBOSE pg_class;" 2>&1 | grep INFO
INFO:  Vacuum von »postgres.pg_catalog.pg_class«
INFO:  beende Vacuum der Tabelle »postgres.pg_catalog.pg_class«:
Index-Scans: 0

$ psql postgres -c "ANALYSE VERBOSE pg_class;" 2>&1 | grep INFO
INFO:  analysiere »pg_catalog.pg_class«
INFO:  »pg_class«: 15 von 15 Seiten gelesen, enthalten 452 lebende
Zeilen und 0 tote Zeilen; 452 Zeilen in Stichprobe, schätzungsweise 452
Zeilen insgesamt
INFO:  finished analyzing table "postgres.pg_catalog.pg_class"

== fixed command list ==

Future verbose operations, if not added to this list, would silently get
no highlighting.

I'm wondering if it is possible to achieve it (locale-agnostic) only for
certain commands without touching the code on the server side. Only by
checking strings it'll be difficult to identify which INFO messages to
highlight.

Thanks!

Best, Jim

---

From: Pavel Stehule <[email-masked]>
Date: Fri, 24 Apr 2026 08:56:36 +0200

Hi

> checking strings it'll be difficult to identify which INFO messages to

I am afraid this is the end of this direction. :-/

Please, can you check the functionality (only in english). I am interested
if this is just helpful and if it makes sense to continue in this feature.
Unfortunately, there are not too many possibilities about possible formats,
colors in terminals (that can work mostly everywhere).

I don't think it is possible to implement this without  communication
protocol enhancement. And if we will do this, the next question is if we
cannot use this for some more complex information about the executed
command.

For example - I thought about the possibility of teaching psql to read
progress stat tables - so can be nice, if the server can send some
information to client - maybe pgstat_progres_update can send INFO

like - "emphasize: nextinfo, pid: xxxx, progress table: pg_stat_vacuum,
commandtype: vacuum, ....

Maybe a different approach - instead of a plain text message, we can send
messages of this type in client side parsable format - if I am not wrong,
we are able to parse json on client side. json is still readable for humans
for old clients. On the client side we decide what and how we will display.
This can be more generic than just for VERBOSE mode of ANALYZE, VACUUM or
REINDEX.

some like

elog(INFO_CLIENT, '{ "cmdtag": "VACUUM", "state":"started", "progress_tab":
"pg_stat_progress_vacuum", "table_name": "yyy", "schema_name":"xxx", ...)

elog(INFO_CLIENT, '{"cmdatag": "VACUUM", "state":"finished",
"pages_removed": 0, "pages_ ...

I don't see some simple and nice solution at this moment. Maybe just using
new line after INFO with details

so results can looks like

INFO:  vacuuming "postgres.pg_catalog.pg_class"
INFO:  finished vacuuming "postgres.pg_catalog.pg_class": index scans: 0
pages: 0 removed, 15 remain, 15 scanned (100.00% of total), 0 eagerly
scanned
tuples: 0 removed, 452 remain, 0 are dead but not yet removable
removable cutoff: 701, which was 0 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were
all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item
identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 75 hits, 0 reads, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes, 0 full page image bytes,
0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit
64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

INFO:  vacuuming "postgres.pg_catalog.pg_proc"
INFO:  finished vacuuming "postgres.pg_catalog.pg_proc": index scans: 0
pages: 0 removed, 101 remain, 1 scanned (0.99% of total), 0 eagerly scanned
tuples: 0 removed, 3437 remain, 0 are dead but not yet removable
removable cutoff: 701, which was 0 XIDs old when operation ended
new relfrozenxid: 701, which is 17 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were
all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item
identifiers removed
avg read rate: 4.534 MB/s, avg write rate: 1.133 MB/s
buffer usage: 15 hits, 4 reads, 1 dirtied
WAL usage: 1 records, 1 full page images, 5871 bytes, 5752 full page image
bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit
64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

INFO:  vacuuming "postgres.pg_toast.pg_toast_1255"
INFO:  finished vacuuming "postgres.pg_toast.pg_toast_1255": index scans: 0
pages: 0 removed, 2 remain, 2 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 7 remain, 0 are dead but not yet removable
removable cutoff: 701, which was 0 XIDs old when operation ended
new relfrozenxid: 701, which is 17 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were
all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item
identifiers removed
avg read rate: 19.462 MB/s, avg write rate: 2.780 MB/s
buffer usage: 36 hits, 7 reads, 1 dirtied
WAL usage: 1 records, 1 full page images, 4255 bytes, 4136 full page image
bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit
64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

This is small change and maybe it can be enough

Regards

Pavel

---

From: Jim Jones <[email-masked]>
Date: Fri, 24 Apr 2026 12:41:30 +0200

On 24/04/2026 08:56, Pavel Stehule wrote:
> Please, can you check the functionality (only in english). I am
> interested if this is just helpful and if it makes sense to continue in
> this feature. Unfortunately, there are not too many possibilities about
> possible formats, colors in terminals (that can work mostly everywhere). 

I tested it yesterday with lc_messages = 'en_GB.UTF-8' and it worked
just fine. And I also agree that it is currently hard to spot tables in
the verbose output.

> Maybe a different approach - instead of a plain text message, we can
> send messages of this type in client side parsable format - if I am not
> wrong, we are able to parse json on client side. json is still readable
> for humans for old clients. On the client side we decide what and how we
> will display. This can be more generic than just for VERBOSE mode of
> ANALYZE, VACUUM or REINDEX.
> 
> some like
> 
> elog(INFO_CLIENT, '{ "cmdtag": "VACUUM", "state":"started",
> "progress_tab": "pg_stat_progress_vacuum", "table_name": "yyy",
> "schema_name":"xxx", ...)
> 

I think it is feasible. The question is now is rather, is it worth the
trouble just to highlight an output?

I also don't see an easy way to implement this feature. It's virtually
impossible to do that without some change in the server side. I took a
look at the code and perhaps NoticeProcessor() at common.c would be
better than pg_log_generic_v() for that, but still the problem of
identifying the INFO messages remains.

One option would be to create a new SQLSTATE and add it to the ereport
calls, e.g. ERRCODE_VERBOSE_PROGRESS_INFO

if (verbose)
{
  if (vacrel->aggressive)
    ereport(INFO,
	errcode(ERRCODE_VERBOSE_PROGRESS_INFO),
	(errmsg("aggressively vacuuming \"%s.%s.%s\"",
	  vacrel->dbname, vacrel->relnamespace,
          vacrel->relname)));
  else
    ereport(INFO,
	errcode(ERRCODE_VERBOSE_PROGRESS_INFO),
	(errmsg("vacuuming \"%s.%s.%s\"",
	  vacrel->dbname, vacrel->relnamespace,
	  vacrel->relname)));
}

Then in NoticeProcessor() check for it and act accordingly:

const char *state = PQresultErrorField(result, PG_DIAG_SQLSTATE);
if (state && strcmp(state, "00001") == 0)
 ... apply color / blank line / bold / whatever

But it also looks like we'd be using SQLSTATES incorrectly. That would
certainly require a bit more research.

> I don't see some simple and nice solution at this moment. Maybe just
> using new line after INFO with details

It's much less invasive with more or less the same effect. However, the
Error Message Style Guide explicitly says "Don't put any specific
assumptions about formatting into the message texts" and "Don't end a
message with a newline"[1]. So I'm afraid it's not an option either :\
Of course, it could also disturb external tools that parse the verbose
output, but that alone wouldn't be a blocker IMHO.

Thanks!

Best, Jim

1 -
https://www.postgresql.org/docs/current/error-style-guide.html#ERROR-STYLE-GUIDE-FORMATTING