Re: Available disk space per tablespace

On 2025/3/14 02:10, Christoph Berg wrote:
> Hi,
> 
> I'm picking up a 5 year old patch again:
> https://www.postgresql.org/message-id/flat/20191108132419.GG8017%40msg.df7cb.de
> 
> Users will be interested in knowing how much extra data they can load
> into a database, but PG currently does not expose that number. This
> patch introduces a new function pg_tablespace_avail() that takes a
> tablespace name or oid, and returns the number of bytes "available"
> there. This is the number without any reserved blocks (Unix, f_avail)
> or available to the current user (Windows).
> 
> (This is not meant to replace a full-fledged OS monitoring system that
> has much more numbers about disks and everything, it is filling a UX
> gap.)
> 
> Compared to the last patch, this just returns a single number so it's
> easier to use - total space isn't all that interesting, we just return
> the number the user wants.
> 
> The free space is included in \db+ output:
> 
> postgres =# \db+
>                                       List of tablespaces
>      Name    │ Owner │ Location │ Access privileges │ Options │  Size   │  Free  │ Description
> ────────────┼───────┼──────────┼───────────────────┼─────────┼─────────┼────────┼─────────────
>   pg_default │ myon  │          │ ∅                 │ ∅       │ 23 MB   │ 538 GB │ ∅
>   pg_global  │ myon  │          │ ∅                 │ ∅       │ 556 kB  │ 538 GB │ ∅
>   spc        │ myon  │ /tmp/spc │ ∅                 │ ∅       │ 0 bytes │ 31 GB  │ ∅
> (3 rows)
> 
> The patch has also been tested on Windows.
> 
> TODO: Figure out which systems need statfs() vs statvfs()
> 

I tested the patch under macos. Abnormal work:

                                      List of tablespaces
     Name    | Owner  | Location | Access privileges | Options |  Size 
| Free  | Description
------------+--------+----------+-------------------+---------+--------+-------+-------------
  pg_default | quanzl |          |                   |         | 23 MB 
|23 TB |
  pg_global  | quanzl |          |                   |         | 556 kB 
| 23 TB |
(2 rows)
Actually my disk is 1TB.

According to the statvfs documentation for macOS
f_frsize       The size in bytes of the minimum unit of allocation on 
this file system.
f_bsize        The preferred length of I/O requests for files on this 
file system.

I tweaked the code a little bit. See the attachment.
                                      List of tablespaces
     Name    | Owner  | Location | Access privileges | Options |  Size 
|  Free  | Description
------------+--------+----------+-------------------+---------+--------+--------+-------------
  pg_default | quanzl |          |                   |         | 22 MB 
| 116 GB |
  pg_global  | quanzl |          |                   |         | 556 kB 
| 116 GB |
(2 rows)

In addition, many systems use 1000 as 1k to represent the storage size. 
Shouldn't we consider this factor as well?

> Christoph