How to pick up the percentage of Postgre database storage space via EF Core in Asp.net Core

Question

I need to create in my application a feature that checks in the database (PostGreSQL) the percentage of storage space used to be shown in a graph.

I have a generic repository, and I thought I’d create a function GetStorage(), but I have no idea how this should be done, because I don’t know if the EF Core has this feature, should this be done using Script SQL?

Something similar to the code below, but returning the percentage:

public decimal GetStorage()
{
    var valor = Db.FromSql("SELECT pg_size_pretty(pg_database_size('SistemaComercial'));";
    return valor;
}

Answer 1

The physical space, in bytes, occupied by the database SistemaComercial can be calculated using the function pg_database_size():

SELECT pg_database_size( 'SistemaComercial' );

To get the free space of a deteminated partition and/or drive, you can write a stored Procedure using the language PL/Python, let’s see:

CREATE FUNCTION calcular_espaco_livre( path TEXT )
RETURNS BIGINT AS
$BODY$
    import os
    info = os.statvfs( path )
    return info.f_bsize * info.f_bavail
$BODY$
LANGUAGE plpythonu;

So you can write another stored Procedure in PL/PgSQL to solve your problem:

CREATE FUNCTION obter_espaco_ocupado( dbnome TEXT )
RETURNS REAL AS
$BODY$
DECLARE
    diretorio TEXT;
    livre BIGINT;
    ocupado BIGINT;
BEGIN
    -- Recupera o diretorio onde a database está instalada
    SELECT setting FROM pg_settings WHERE name = 'data_directory' INTO diretorio;

    -- Calcula o espaco livre desse diretorio
    SELECT calcular_espaco_livre( diretorio ) INTO livre;

    -- Calcula o espaco ocupado pela database
    SELECT pg_database_size( dbnome ) INTO ocupado;

    -- Calcula a porcentagem ocupada
    RETURN (100.0 / livre) * ocupado;
END;
$BODY$
LANGUAGE plpgsql;

Soon:

SELECT obter_espaco_ocupado( 'SistemaComercial' );