Ability to query available disk space

[steve-chavez]

Problem

Indexing a vector column can outgrow available disk space.

Proposal

Expose a function that can query available disk space. Later create a wrapper CREATE INDEX statement that aborts it if the available space is not enough.

Prior art

• https://github.com/greenplum-db/diskquota
• https://github.com/hlinnaka/pg_quota

[steve-chavez]

Edit: On second thought, I'll just create some functions. It's too hacky to do this on GUCs which are meant to be static.

To not depend on a table or function (thus on a CREATE EXTENSION supautils) to get this info, we can store it as a PGC_INTERNAL GUC. So the interface to get this info would be:

select current_setting('supautils.free_disk_space', true);

Steps for implementation:

• [ ] Get the data_directory from pg
• [ ] Use https://man7.org/linux/man-pages/man3/statvfs.3.html to get free space.
• [ ] Store it on supautils.free_disk_space.

[olirice]

could we add number of cpus and total memory here too, or would those be better as separate issues?

[steve-chavez]

Sure, we can track those here.

• The number of cpus is one function call https://linux.die.net/man/3/get_nprocs.
• Total memory can be obtained with https://linux.die.net/man/2/sysinfo.

[burggraf]

Was this ever implemented?

[olirice]

bump @steve-chavez ^

[steve-chavez]

This was done only for the case of constrained extensions, which was the immediate use case: https://github.com/supabase/supautils#constrained-extensions (see disk)

The code for getting disk space is there but not sure if we should add SQL objects to supautils since it would make it harder to upgrade (we'd need migrations). Right now it only works with db settings (GUCs).

@burggraf What's the use case you had in mind?

[steve-chavez]

We can implement any resource constraints through config options, but we'd need specific use cases.

As we've had issues with the removed supautils stdlib when doing migrations, I don't think we should add database objects for any functionality.

I've added a clarification for this at the intro of supautils: https://github.com/supabase/supautils#supautils

Will close.