Thread: DB size

Hi!

I've looked around, but could only find very old answers to this question, and maybe it changed  since then...

I'm struggling to identify the cause of the difference in size between the sum of all relations (via
pg_total_relation_size)and pg_database_size:
 

  SELECT sum(pg_total_relation_size(relid)), 
         pg_size_pretty(sum(pg_total_relation_size(relid)))
    FROM pg_catalog.pg_stat_all_tables 


sum         |pg_size_pretty|
------------+--------------+
518549716992|483 GB        |


    SELECT pg_database_size('mydb'),
       pg_size_pretty(pg_database_size('mydb'))


pg_database_size|pg_size_pretty|
----------------+--------------+
    869150909087|809 GB        |

There are three databases in the cluster, apart from 'mydb' (one of them is the 'postgres' database). These other two
sizeabout 8MB each.
 


We run pg_repack weekly and recently had crashes related to disk running out of space, so my guess is something got
'lost'during repack.
 

What can I do to recover the wasted space?

Thanks!

Luis R. Weck 

po 26. 4. 2021 v 22:25 odesílatel <luis.roberto@siscobra.com.br> napsal:
>
> Hi!
>
> I've looked around, but could only find very old answers to this question, and maybe it changed  since then...
>
> I'm struggling to identify the cause of the difference in size between the sum of all relations (via
pg_total_relation_size)and pg_database_size: 
>
>   SELECT sum(pg_total_relation_size(relid)),
>          pg_size_pretty(sum(pg_total_relation_size(relid)))
>     FROM pg_catalog.pg_stat_all_tables
>
>
> sum         |pg_size_pretty|
> ------------+--------------+
> 518549716992|483 GB        |
>
>
>     SELECT pg_database_size('mydb'),
>            pg_size_pretty(pg_database_size('mydb'))
>
>
> pg_database_size|pg_size_pretty|
> ----------------+--------------+
>     869150909087|809 GB        |
>
> There are three databases in the cluster, apart from 'mydb' (one of them is the 'postgres' database). These other two
sizeabout 8MB each. 

Do you have any indexes in mydb database?

>
> We run pg_repack weekly and recently had crashes related to disk running out of space, so my guess is something got
'lost'during repack. 
>
> What can I do to recover the wasted space?
>
> Thanks!
>
> Luis R. Weck
>
>

On 2021-Apr-26, luis.roberto@siscobra.com.br wrote:

>   SELECT sum(pg_total_relation_size(relid)), 
>          pg_size_pretty(sum(pg_total_relation_size(relid)))
>     FROM pg_catalog.pg_stat_all_tables 
> 
> 
> sum         |pg_size_pretty|
> ------------+--------------+
> 518549716992|483 GB        |
> 
> 
>     SELECT pg_database_size('mydb'),
>        pg_size_pretty(pg_database_size('mydb'))
> 
> 
> pg_database_size|pg_size_pretty|
> ----------------+--------------+
>     869150909087|809 GB        |
> 
> There are three databases in the cluster, apart from 'mydb' (one of
> them is the 'postgres' database). These other two size about 8MB each.

I would guess that there are leftover files because of those crashes you
mentioned.  You can probably look for files in the database subdir in
the data directory that do not appear in the pg_class.relfilenode
listing for the database.

-- 
Álvaro Herrera                            39°49'30"S 73°17'W
<inflex> really, I see PHP as like a strange amalgamation of C, Perl, Shell
<crab> inflex: you know that "amalgam" means "mixture with mercury",
       more or less, right?
<crab> i.e., "deadly poison"

----- Mensagem original -----
> De: "Josef Šimánek" <josef.simanek@gmail.com>
> Para: "luis.roberto" <luis.roberto@siscobra.com.br>
> Cc: "pgsql-general" <pgsql-general@lists.postgresql.org>
> Enviadas: Segunda-feira, 26 de abril de 2021 17:40:05
> Assunto: Re: DB size

> Do you have any indexes in mydb database?


Yes, I do. I believe pg_total_relation_size accounts for these. These are the results for my biggest table:

SELECT pg_table_size('my_table'),
       pg_size_pretty(pg_table_size('my_table')) AS tb_pretty,
       pg_indexes_size('my_table'),
       pg_size_pretty(pg_indexes_size('my_table')) AS idx_pretty,
       pg_total_relation_size('my_table'),
       pg_size_pretty(pg_total_relation_size('my_table')) AS total_pretty

pg_table_size|tb_pretty|pg_indexes_size|idx_pretty|pg_total_relation_size|total_pretty|
-------------+---------+---------------+----------+----------------------+------------+
  82016485376|76 GB    |    88119033856|82 GB     |          170135519232|158 GB      |


Luis R. Weck

----- Mensagem original -----
> De: "Alvaro Herrera" <alvherre@alvh.no-ip.org>
> Para: "luis.roberto" <luis.roberto@siscobra.com.br>
> Cc: "pgsql-general" <pgsql-general@lists.postgresql.org>
> Enviadas: Segunda-feira, 26 de abril de 2021 17:45:34
> Assunto: Re: DB size

> I would guess that there are leftover files because of those crashes you
> mentioned. You can probably look for files in the database subdir in
> the data directory that do not appear in the pg_class.relfilenode
> listing for the database.

> --
> Álvaro Herrera 39°49'30"S 73°17'W
> <inflex> really, I see PHP as like a strange amalgamation of C, Perl, Shell
> <crab> inflex: you know that "amalgam" means "mixture with mercury",
> more or less, right?
> <crab> i.e., "deadly poison"


Thanks Alvaro!

That's what I read in an old thread, back in the 8.3 days... Can you point me in the right direction as to where sould
Ilook and how do I know which files exactly are safe to remove? 


Luis R. Weck

On Mon, 2021-04-26 at 16:45 -0400, Alvaro Herrera wrote:
> I would guess that there are leftover files because of those crashes you
> mentioned.  You can probably look for files in the database subdir in
> the data directory that do not appear in the pg_class.relfilenode
> listing for the database.

Not all tables have their "relfilenode" set:

SELECT relfilenode FROM pg_class WHERE relname = 'pg_class';
 relfilenode 
═════════════
           0
(1 row)

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On Tue, Apr 27, 2021 at 8:59 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Mon, 2021-04-26 at 16:45 -0400, Alvaro Herrera wrote:
> > I would guess that there are leftover files because of those crashes you
> > mentioned.  You can probably look for files in the database subdir in
> > the data directory that do not appear in the pg_class.relfilenode
> > listing for the database.
>
> Not all tables have their "relfilenode" set:
>
> SELECT relfilenode FROM pg_class WHERE relname = 'pg_class';
>  relfilenode
> ═════════════
>            0
> (1 row)

Yeah, you want to use pg_relation_filenode(oid) rather than looking
directly at relfilenode.

When compared to the filesystem, it's probably even easier to use
pg_relation_filepath(oid).

--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

----- Mensagem original -----
> De: "Magnus Hagander" <magnus@hagander.net>
> Para: "Laurenz Albe" <laurenz.albe@cybertec.at>
> Cc: "Alvaro Herrera" <alvherre@alvh.no-ip.org>, "luis.roberto" <luis.roberto@siscobra.com.br>, "pgsql-general"
> <pgsql-general@lists.postgresql.org>
> Enviadas: Terça-feira, 27 de abril de 2021 4:05:42
> Assunto: Re: DB size

> Yeah, you want to use pg_relation_filenode(oid) rather than looking
> directly at relfilenode.
>
> When compared to the filesystem, it's probably even easier to use
> pg_relation_filepath(oid).
>
> --
> Magnus Hagander
> Me: https://www.hagander.net/
> Work: https://www.redpill-linpro.com/

Thanks all!

Looks like pg_orphaned is what I need! I'll use pg_relation_filepath too to double check.



Luis R. Weck