Re: Available disk space per tablespace

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

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