Re: Discussion: Fast DB/Schema/Table disk size check in Postgresql

Thanks Stephen.
>
> In the largest portion of the system, when it comes to tables, indexes,
> and such, if there's a file 'X.1', you can be pretty darn sure that 'X'
> is 1GB in size. If there's a file 'X.245' then you can know that
> there's 245 files (X, X.1, X.2, X.3 ... X.244) that are 1GB in size.

Good idea. We could just add your logic in db_dir_size(). to teach both
pg_database_size_name() and pg_database_size_oid().
'X' maybe larger than 1GB with custom configuration.
So the size of the table could be calculated with two stats: size(X.245) +
245*size(X)

On Sun, Jan 6, 2019 at 3:52 AM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Hubert Zhang (hzhang(at)pivotal(dot)io) wrote:
> > For very large databases, the dbsize function `pg_database_size_name()`
> > etc. could be quite slow, since it needs to call `stat()` system call on
> > every file in the target database.
>
> I agree, it'd be nice to improve this.
>
> > We proposed a new solution to accelerate these dbsize functions which
> check
> > the disk usage of database/schema/table. The new functions avoid to call
> > `stat()` system call, and instead store the disk usage of database
> objects
> > in user tables(called diskquota.xxx_size, xxx could be db/schema/table).
> > Checking the size of database 'postgres' could be converted to the SQL
> > query `select size from diskquota.db_size where name = `postgres``.
>
> This seems like an awful lot of work though.
>
> I'd ask a different question- why do we need to stat() every file?
>
> In the largest portion of the system, when it comes to tables, indexes,
> and such, if there's a file 'X.1', you can be pretty darn sure that 'X'
> is 1GB in size. If there's a file 'X.245' then you can know that
> there's 245 files (X, X.1, X.2, X.3 ... X.244) that are 1GB in size.
>
> Maybe we should teach pg_database_size_name() about that?
>
> Thanks!
>
> Stephen
>

--
Thanks

Hubert Zhang