Re: Available disk space per tablespace

From: Quan Zongliang <quanzongliang(at)yeah(dot)net>
To: Christoph Berg <myon(at)debian(dot)org>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Available disk space per tablespace
Date: 2025-03-14 02:14:35
Message-ID: 2df8509c-e28a-467a-8f03-f3cfe812ed62@yeah.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0002-Add-pg_tablespace_avail-functions.patch text/plain 6.9 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2025-03-14 02:26:49 Re: Query ID Calculation Fix for DISTINCT / ORDER BY and LIMIT / OFFSET
Previous Message Michael Paquier 2025-03-14 01:50:55 Re: Query ID Calculation Fix for DISTINCT / ORDER BY and LIMIT / OFFSET