Re: Fwd: "SELECT COUNT(*) FROM" still causing issues (deadlock) in PostgreSQL 14.3/4?

Marco Boeringa <marco(at)boeringa(dot)demon(dot)nl> writes:
> Ok, I found it out using 'sudo baobab'. It is the:
> 'var/lib/postgresql/14/main/pg_wal'

> folder that is filled up with 890 GB of data... causing the file system
> root to run out of space and Ubuntu opening the disk usage analyzer and
> a warning as a consequence.

The most likely explanations for this are
(a) misconfiguration of WAL archiving, so that the server thinks
it should keep WAL files till they've been archived, only that
never happens.
(b) inability to complete checkpoints for some reason, preventing
WAL files from being recycled.

It doesn't look like you have wal_archiving on, so (a) *should*
be ruled out, but you never know. If there are a ton of "nnn.ready"
files underneath pg_wal then trouble here would be indicated.

As for (b), you might try enabling log_checkpoints and seeing if
the log messages give any clue.

regards, tom lane