Re: Database takes up MUCH more disk space than it should

On Sunday, January 22, 2012 12:26:22 am Dan Charrois wrote:

>
> Thank you Adrian. I think that you seem to have found the trouble. For
> most of the TOAST tables I have, oid=relfilenode, but not for that one. I
> found the table that has reltoastrelid linking to that huge TOAST table..
> and it makes some sense, since it is also the largest "regular" table too
> (79 GB).

The reason for that is found here:

http://www.postgresql.org/docs/9.0/interactive/storage-file-layout.html

"
Caution
Note that while a table's filenode often matches its OID, this is not necessarily
the case; some operations, like TRUNCATE, REINDEX, CLUSTER and some forms of
ALTER TABLE, can change the filenode while preserving the OID. Avoid assuming
that filenode and table OID are the same. Also, for certain system catalogs
including pg_class itself, pg_class.relfilenode contains zero. The actual filenode
number of these catalogs is stored in a lower-level data structure, and can be
obtained using the pg_relation_filenode() function.
"

>
> So perhaps there are no orphaned TOAST tables after all, as now I know who
> its parent is. The database still takes up a lot more physical storage
> than I'd anticipated it would, but at least it appears as though that
> space can be accounted for.
>
> It's too bad \dt+ doesn't take into account the related TOAST table too -
> if it had, I would have expected that much disk space right from the
> get-go, and never thought twice about it.

In pre 9.1 databases you can use:

"
pg_total_relation_size accepts the OID or name of a table or toast table, and
returns the total on-disk space used for that table, including all associated
indexes. This function is equivalent to pg_table_size + pg_indexes_size.

pg_table_size accepts the OID or name of a table and returns the disk space
needed for that table, exclusive of indexes. (TOAST space, free space map, and
visibility map are included.)
"

See details here:
http://www.postgresql.org/docs/9.0/interactive/functions-admin.html

>
> Dan
> --
> Syzygy Research & Technology
> Box 83, Legal, AB T0G 1L0 Canada
> Phone: 780-961-2213

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com