Postgres database size much bigger than tablespaces on filesystem

Question

I have a Postgres 8.3 instance with tablespaces totalling on about 74G. This is fine.

But if I ask postgres how big my database is, I get a (unexpected) large answer: 595 GB.

This seems very strange. Disk I/O tests on the system are in the 'normal' range, but queries are slower than they used to be.

Is this corruption of the database? Or are there ways to 'fix' this oddity?

[Edit] I check the tablespace size by the os:

/usr/local/pgsql/data/tblspaces/du -c -h
74G total 

All tablespaces are there

I check te database size like this:

select pg_size_pretty(pg_database_size('database')) 

[Edit2]

I also checked the entire /usr/local/pgsql/ dir. It is 76 G

And I ran this query:

select
  tablename
, pg_relation_size(tablename)
, pg_size_pretty(pg_relation_size(tablename) ) as relsize
, pg_size_pretty(pg_total_relation_size(tablename) ) as disksize
, pg_total_relation_size(tablename)
from pg_tables where schemaname <> 'information_schema'
order by 2 desc

Which returned 'normal' relationsizes, none of which exceeded the disksize.

Answer 1

Turned out to be a corruption issue. Full restore of a backup fixed the problem

Answer 2

The size of the tablespace can be checked by using:

SELECT pg_size_pretty(pg_tablespace_size('name of tablespace'));

It looks like your database didn't store all it's objects (tables, indexes, etc.) in your specific tablespace but also another (default?) tablespace. Check pg_class and pg_tablespace, something like this:

SELECT 
    *
FROM pg_class 
    JOIN pg_tablespace ON reltablespace = pg_tablespace.oid
WHERE
    spcname <> 'name of tablespace';