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.

How did you check the database size? And the tablespace? – Frank Heikens Sep 14 '11 at 07:37 — Frank Heikens, Sep 14 '11 at 07:37

score 2 · Accepted Answer · answered Jan 17 '13 at 12:07

2

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

answered Jan 17 '13 at 12:07

Rob Audenaerde

19,195
10
76
121

I just had this exact problem. On 3 computers a db was 12gb. On another it was 96gb. A full backup and restore totally fixed it. Still have no idea what's going on but this totally works – dabobert Dec 10 '15 at 18:14

score 1 · Answer 2 · answered Sep 14 '11 at 10:14

1

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';

answered Sep 14 '11 at 10:14

Frank Heikens

117,544
24
142
135

As said in the post above, all tablespaces in the database are stored on the OS in one directory, and the content of that directory are a lot smaller than the database size. – Rob Audenaerde Sep 14 '11 at 10:39
Did you actually check if all database objects are stored in these tablespaces? Some objects might be on the default tablespace, like the TOAST tables and indexes. – Frank Heikens Sep 14 '11 at 12:04
Tablespaces on different volumes are implemented as symlinks in that folder so won't count against the size – JamesRyan Jan 09 '13 at 14:23

score 0 · Answer 3 · answered Sep 14 '11 at 08:36

0

It sounds so strange! How did you check the database size and the tablespace size? may be your database locate in different tablespaces.

--to get the databasesize mydb=> select pg_size_pretty(pg_database_size('mydb'));

pg_size_pretty

214 MB (1 row)

--to show all the tablespace connect your db,and type the following commond \db

answered Sep 14 '11 at 08:36

francs

8,511
7
39
43

Strange indeed. I verified the tablespaces from the \db with the ones on the os, they are the same – Rob Audenaerde Sep 14 '11 at 09:56

James Felix Black · Answer 4 · 2011-09-15T04:23:56.127

0

Have you vacuumed the DB recently? That can potentially have dramatic effects, particularly if there's a lot of churn on larger tables.

Also, how big is the result of a pg_dump?

edited Sep 15 '11 at 04:23

answered Sep 15 '11 at 03:36

James Felix Black

300
1
10

Postgres database size much bigger than tablespaces on filesystem

4 Answers4

pg_size_pretty