Actual used and free space in WAL segments

Question

I am new to the PostgreSQL world. I have an experience in administrating Microsoft SQL Server and Oracle databases.

In SQL Server, we are able to find how much space is actually used in pre-allocated log file. I wonder if we can do the same thing in PostgreSQL?

How can we identify which WAL segments are actually full and which WAL segments are empty?

I am currently using PostgreSQL version 10.

Answer 1

You can use something like this:

select name > pg_walfile_name(pg_current_wal_lsn()) as "future", sum(size) from pg_ls_waldir() group by 1;

This counts it as not-empty (not future) if even one byte has been written to it, you could elaborate on that using pg_walfile_name_offset if it is important to you but I don't see why it would be.