Thread
-
Available disk space per tablespace
Christoph Berg <myon@debian.org> — 2025-03-13T18:10:16Z
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() Christoph -
Re: Available disk space per tablespace
Quan Zongliang <quanzongliang@yeah.net> — 2025-03-14T02:14:35Z
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 -
Re: Available disk space per tablespace
Christoph Berg <myon@debian.org> — 2025-03-14T15:39:55Z
Re: Quan Zongliang > 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. Thanks for catching that. f_frsize is the correct field to use. The statvfs(3) manpage on Linux has it as well, but it's less pronounced there so I missed it: struct statvfs { unsigned long f_bsize; /* Filesystem block size */ unsigned long f_frsize; /* Fragment size */ fsblkcnt_t f_blocks; /* Size of fs in f_frsize units */ fsblkcnt_t f_bfree; /* Number of free blocks */ fsblkcnt_t f_bavail; /* Number of free blocks for unprivileged users */ > In addition, many systems use 1000 as 1k to represent the storage size. > Shouldn't we consider this factor as well? That would be a different pg_size_pretty() function, unrelated to this patch. I'm still unconvinced if we should use statfs() instead of statvfs() on *BSD or if their manpage is just trolling us and statvfs is just fine. DESCRIPTION The statvfs() and fstatvfs() functions fill the structure pointed to by buf with garbage. This garbage will occasionally bear resemblance to file system statistics, but portable applications must not depend on this. Christoph -
Re: Available disk space per tablespace
Thomas Munro <thomas.munro@gmail.com> — 2025-03-15T01:04:23Z
On Sat, Mar 15, 2025 at 4:40 AM Christoph Berg <myon@debian.org> wrote: > I'm still unconvinced if we should use statfs() instead of statvfs() > on *BSD or if their manpage is just trolling us and statvfs is just > fine. > > DESCRIPTION > The statvfs() and fstatvfs() functions fill the structure pointed to by > buf with garbage. This garbage will occasionally bear resemblance to > file system statistics, but portable applications must not depend on > this. Hah, I see this in my local FreeBSD man page. I guess this might be a reference to POSIX's 100% get-out clause "it is unspecified whether all members of the statvfs structure have meaningful values on all file systems". The statfs() man page doesn't say that (a nonstandard syscall that originated in 4.4BSD, which POSIX decided to rename because other systems sprouted incompatible statfs() interfaces?). It's hard to imagine a system that doesn't track free space and report it here, and if it doesn't, well so what, that's probably also a system that can't report free space to the "df" command, so what are we supposed to do? We could perhaps add a note to the documentation that this field relies on the OS providing meaningful "avail" field in statvfs(), but it's hard to imagine. Maybe just defer that until someone shows up with a real report? So +1 from me, go for it, call statvfs() and don't worry. I tried your v3 patch on my FreeBSD 14.2 battle station: postgres=# \db+ List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Free | Description ------------+--------+----------+-------------------+---------+--------+--------+------------- pg_default | tmunro | | | | 22 MB | 290 GB | pg_global | tmunro | | | | 556 kB | 290 GB | That is the correct answer: tmunro@build1:~/projects/postgresql/build $ df -h . Filesystem Size Used Avail Capacity Mounted on zroot/usr/home 331G 41G 290G 12% /usr/home I also pushed your patch to CI and triggered the NetBSD and OpenBSD tasks and they passed your sanity test, though that only checks that the reported some number > 1MB. I looked at the source, and on FreeBSD statvfs[1] is just a libc function that calls statfs() (as does df). The statfs() man page has no funny disclaimers. OpenBSD's[2] too. NetBSD seems to have a real statvfs (or statvfs1) syscall but its man page has no funny disclaimers. +#ifdef WIN32 + if (GetDiskFreeSpaceEx(tblspcPath, &lpFreeBytesAvailable, NULL, NULL) == false) + return -1; + + return lpFreeBytesAvailable.QuadPart; /* ULONGLONG part of ULARGE_INTEGER */ +#else + if (statvfs(tblspcPath, &fst) < 0) + return -1; + + return fst.f_bavail * fst.f_frsize; /* available blocks times fragment size */ +#endif What's the rationale for not raising an error if the system call fails? If someone complains that it's showing -1, doesn't that mean we'll have to ask them to trace the system calls to figure out why, or if it's Windows, likely abandon all hope of ever knowing why? Should statvfs() retry on EINTR? Style nit: maybe ! instead of == false? Nice feature. [1] https://github.com/freebsd/freebsd-src/blob/36782aaba4f1a7d054aa405357a8fa2bc0f94eb0/lib/libc/gen/statvfs.c#L70 [2] https://github.com/openbsd/src/blob/70ab9842eb8b368612eb098db19dcf94c19d673d/lib/libc/gen/statvfs.c#L59 -
Re: Available disk space per tablespace
Christoph Berg <myon@debian.org> — 2025-03-15T12:09:13Z
Re: Thomas Munro > Hah, I see this in my local FreeBSD man page. I guess this might be a > reference to POSIX's 100% get-out clause "it is unspecified whether > all members of the statvfs structure have meaningful values on all > file systems". Yeah I could hear someone being annoyed by POSIX echoed in that paragraph. > system that can't report free space to the "df" command, so what are > we supposed to do? We could perhaps add a note to the documentation > that this field relies on the OS providing meaningful "avail" field in > statvfs(), but it's hard to imagine. Maybe just defer that until > someone shows up with a real report? So +1 from me, go for it, call > statvfs() and don't worry. I was reading looking into gnulib's wrapper around this - it's also basically calling statvfs() except on assorted older systems. https://github.com/coreutils/gnulib/blob/master/lib/fsusage.c#L114 Do we care about any of these? AIX OSF/1 2.6 < glibc/Linux < 2.6.36 glibc/Linux < 2.6, 4.3BSD, SunOS 4, \ Mac OS X < 10.4, FreeBSD < 5.0, \ NetBSD < 3.0, OpenBSD < 4.4k SunOS 4.1.2, 4.1.3, and 4.1.3_U1 4.4BSD and older NetBSD SVR3, old Irix If not, then statvfs seems safe. > I also pushed your patch to CI and triggered the NetBSD and OpenBSD > tasks and they passed your sanity test, though that only checks that > the reported some number > 1MB. I thought about making that test "between 1MB and 10PB", but that seemed silly - it's not testing much, and some day, someone will try to run the test on a system where it will still fail. > What's the rationale for not raising an error if the system call > fails? That's mirroring the behavior of calculate_tablespace_size() in the same file. I thought that's to allow \db+ to succeed even if some of the tablespaces are botched/missing/whatever. But now on closer inspection, I see that db_dir_size() is erroring out on problems, it just ignores the top-level directory missing. Fixed in the attached patch. \db+ FEHLER: XX000: could not statvfs directory "pg_tblspc/16384/PG_18_202503111": Zu viele Ebenen aus symbolischen Links LOCATION: calculate_tablespace_avail, dbsize.c:373 But this is actually something I wanted to address in a follow-up patch: Currently, non-superusers cannot run \db+ because they lack CREATE on pg_global (but `\db+ pg_default` works). Should we rather make pg_database_size and pg_database_avail return NULL for insufficient permissions instead of throwing an error? > If someone complains that it's showing -1, doesn't that mean (-1 is translated to NULL for the SQL level.) > we'll have to ask them to trace the system calls to figure out why, or > if it's Windows, likely abandon all hope of ever knowing why? Should > statvfs() retry on EINTR? Hmm. Is looping on EINTR worth the trouble? > Style nit: maybe ! instead of == false? Changed. > Nice feature. Thanks! Christoph -
Re: Available disk space per tablespace
Laurenz Albe <laurenz.albe@cybertec.at> — 2025-03-15T12:17:18Z
On Sat, 2025-03-15 at 13:09 +0100, Christoph Berg wrote: > Do we care about any of these? > > AIX We dropped support for it, but there are efforts to change that. Yours, Laurenz Albe
-
Re: Available disk space per tablespace
Thomas Munro <thomas.munro@gmail.com> — 2025-03-15T13:15:17Z
On Sun, Mar 16, 2025 at 1:17 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > On Sat, 2025-03-15 at 13:09 +0100, Christoph Berg wrote: > > Do we care about any of these? > > > > AIX > > We dropped support for it, but there are efforts to change that. FWIW AIX does have it, according to its manual, in case it comes back. The others in the list are defunct or obsolete versions.
-
Re: Available disk space per tablespace
Thomas Munro <thomas.munro@gmail.com> — 2025-03-15T13:24:56Z
On Sun, Mar 16, 2025 at 1:09 AM Christoph Berg <myon@debian.org> wrote: > Hmm. Is looping on EINTR worth the trouble? I was just wondering if it might be one of those oddballs that ignores SA_RESTART, but I guess that doesn't seem too likely (I mean, first you'd probably have to have a reason to sleep or some other special reason, and who knows what some unusual file systems might do). It certainly doesn't on the systems I tried. So I guess not until we have other evidence.
-
Re: Available disk space per tablespace
Christoph Berg <myon@debian.org> — 2025-03-15T17:00:36Z
Re: Thomas Munro > > Hmm. Is looping on EINTR worth the trouble? > > I was just wondering if it might be one of those oddballs that ignores > SA_RESTART, but I guess that doesn't seem too likely (I mean, first > you'd probably have to have a reason to sleep or some other special > reason, and who knows what some unusual file systems might do). It > certainly doesn't on the systems I tried. So I guess not until we > have other evidence. Gnulib's get_fs_usage() (which is what GNU coreutil's df uses) does not handle EINTR either. There is some code that does int width expansion, but I believe we don't need that since the `fst.f_bavail * fst.f_frsize` multiplication takes care of converting that to int64 (if it wasn't already 64bits before). Christoph
-
Re: Available disk space per tablespace
said assemlal <oyoun@gmx.com> — 2025-04-24T19:26:24Z
Hi, I also tested the patch on Linux mint 22.1 with the btrfs and ext4 partitions. I generated some data and the outcome looks good: postgres=# \db+ List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Free | Description ------------------+----------+---------------------------+-------------------+---------+---------+---------+------------- pg_default | postgres | | | | 1972 MB | 29 GB | pg_global | postgres | | | | 556 kB | 29 GB | tablespace_test2 | postgres | /media/said/queryme/pgsql | | | 3147 MB | 1736 GB | Numbers are the same as if I were executing the command: df -h tablespace_test2 was the ext4 partition on usb stick. Numbers are correct. Said On 2025-03-13 14 h 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() > > Christoph
-
Re: Available disk space per tablespace
Christoph Berg <myon@debian.org> — 2026-05-05T17:54:23Z
I'm picking this up again. Attached is version 5 of the pg_tablespace_avail() patch. Difference to v4 is that the \db+ query used in psql is now checking tablespace permissions before blindly calling the function. This avoids raising errors when some tablespace is not accessible. postgres =# \db+ /**** INTERNAL QUERY ****/ /* Get matching tablespaces */ SELECT spcname AS "Name", pg_catalog.pg_get_userbyid(spcowner) AS "Owner", pg_catalog.pg_tablespace_location(tblspc.oid) AS "Location", CASE WHEN pg_catalog.array_length(spcacl, 1) = 0 THEN '(none)' ELSE pg_catalog.array_to_string(spcacl, E'\n') END AS "Access privileges", spcoptions AS "Options", CASE WHEN dbsub.dattablespace OPERATOR(pg_catalog.=) tblspc.oid OR pg_catalog.has_tablespace_privilege(tblspc.oid, 'CREATE') OR pg_catalog.pg_has_role('pg_read_all_stats', 'USAGE') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(tblspc.oid)) ELSE 'No Access' END as "Size", CASE WHEN dbsub.dattablespace OPERATOR(pg_catalog.=) tblspc.oid OR pg_catalog.has_tablespace_privilege(tblspc.oid, 'CREATE') OR pg_catalog.pg_has_role('pg_read_all_stats', 'USAGE') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_avail(tblspc.oid)) ELSE 'No Access' END as "Free", pg_catalog.shobj_description(tblspc.oid, 'pg_tablespace') AS "Description" FROM pg_catalog.pg_tablespace tblspc CROSS JOIN (SELECT dattablespace FROM pg_catalog.pg_database db wHERE db.datname OPERATOR(pg_catalog.=) pg_catalog.current_database()) dbsub ORDER BY 1; /************************/ The logic is the same as in pg_tablespace_size (which wasn't guarded in psql before): * this database's default tablespace is ok * having CREATE is ok * rold pg_read_all_stats is ok List of tablespaces Name │ Owner │ Location │ Access privileges │ Options │ Size │ Free │ Description ────────────┼───────┼──────────┼───────────────────┼─────────┼────────┼────────┼───────────── pg_default │ myon │ │ ∅ │ ∅ │ 24 MB │ 365 GB │ ∅ pg_global │ myon │ │ ∅ │ ∅ │ 549 kB │ 365 GB │ ∅ (2 rows) I think this patch is useful as-is and could be committed. As a followup, I would like to include pg_wal in this list since it can be moved to a separate disk. There are several ways forward: 1) include a pg_wal entry in pg_tablespace. Together with a trivial addition to get_tablespace_location: + if (tablespaceOid == WALTABLESPACE_OID) + snprintf(sourcepath, sizeof(sourcepath), "%s", XLOGDIR); this makes the \db+ query report size/free out of the box. This seemed very clean to me until I discovered the downside that it required not-so-trivial guarding against WALTABLESPACE_OID being used as tablespace in SQL commands in many code places. 2) add new pg_wal_size() and pg_wal_avail() functions 3) reserve a special value that makes a combination of get_tablespace_location, pg_tablespace_size and pg_tablespace_avail work on pg_wal even when that's not registered in pg_tablespace. Not sure what way is best, perhaps something between 2 and 3? Christoph -
Re: Available disk space per tablespace
Zsolt Parragi <zsolt.parragi@percona.com> — 2026-05-06T22:59:21Z
Hello! #ifdef WIN32 + if (! GetDiskFreeSpaceEx(tblspcPath, &lpFreeBytesAvailable, NULL, NULL)) + elog(ERROR, "GetDiskFreeSpaceEx failed: error code %lu", GetLastError()); + + return lpFreeBytesAvailable.QuadPart; /* ULONGLONG part of ULARGE_INTEGER */ +#else Shouldn't this use proper error codes similar to the else branch, and also _dosmaperr? There's also a behavior difference here compared to Linux, it returns -1 on ENOENT, the Windows version errors out on the matching condition. + " wHERE db.datname OPERATOR(pg_catalog.=) pg_catalog.current_database()) dbsub\n"); typo, should be WHERE + (errcode_for_file_access(), + errmsg("could not statvfs directory \"%s\": %m", tblspcPath))); Is this error message user friendly? Wouldn't be something like "could not get free disk space for directory" be better? + Returns the available disk space in the tablespace with the + specified name or OID. Does the tablespace have a disk space? Maybe "returns the space on the filesystem hosting the tablespace"? + return fst.f_bavail * fst.f_frsize; /* available blocks times fragment size */ > There is some code that does int width expansion, but I believe we > don't need that since the `fst.f_bavail * fst.f_frsize` multiplication > takes care of converting that to int64 (if it wasn't already 64bits > before). I don't think this is the case, we first multiply and then cast. Multiplication still happens with 32 bit types. Relevant parts on Godbolt: https://godbolt.org/z/7dj7crf6K -
Re: Available disk space per tablespace
solai v <solai.cdac@gmail.com> — 2026-05-22T06:15:25Z
Hi , I tested the v5 of the pg_tablespace_avail() patch on Linux. The patch applied and built cleanly for me .After applying the patch and re-running initdb,pg_tablespace_avail() worked correctly and \db+ showed the new Free column as expected. The reported values matched the output from df -h on my system.I also tested custom tablespace and non-superuser access,and both behaved correctly. Additionally,I ran : make check TESTS= tablespace and all tests passed. Overall , the feature looks useful and worked well in my testing. Regards Solai