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

>>
>> SELECT relname, pg_size_pretty(relpages::bigint * 8 *1024) AS size, CASE
>> WHEN relkind = 't' THEN (SELECT pgd.relname FROM pg_class pgd WHERE
>> pgd.relfilenode::text = SUBSTRING(pg.relname FROM 10)) ELSE (SELECT
>> pgc.relname FROM pg_class pgc WHERE pg.reltoastrelid = pgc.relfilenode)
>> END AS refrelname, relfilenode, relkind, reltuples::bigint, relpages FROM
>> pg_class pg ORDER BY relpages DESC;
>
> If I follow the query above correctly, it is not getting the information you
> think it is. In particular this part:
>
> ...SELECT pgc.relname FROM pg_class pgc WHERE pg.reltoastrelid = pgc.relfilenode)
>
> Per the docs:
> http://www.postgresql.org/docs/8.4/interactive/catalog-pg-class.html
> reltoastrelid = The OID of the TOAST table not the relfilenode
> When I table is created those numbers are the same, but they can diverge over
> time.
>
> I would do something like
> select oid, relfilenode, relname from pg_class where relname = 'pg_toast_101748';
>
> This will get you the OID and also show if it differs from the relfilenode.
>
> Then something like:
> select * from pg_class where relkind='r' and reltoastrelid=[oid from above]
>
> This should show you if the TOAST table has been orphaned and if not what table
> it is associated with.

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).

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. I suppose that's the danger of not learning enough about administration of PostgreSQL and trying to troubleshoot a perceived problem that may not even have been a problem in the first place. Until a few days ago, I hadn't even heard of TOAST tables, and just presumed all the data was stuffed into the database I created directly. From what I've read about them since, they sound like a great idea - but I never anticipated them, or their effect on trying to sort out exactly where my data went.

Thanks a lot for shedding the light on this subject that I needed!

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