Re: my table is empty, pg_relation_size(name) return 38 MB

Shivakumar Ramannavar <shivasr@gmail.com> wrote:

> Though my table is empty, pg_relation_size(name) return 38 MB. I
> use below query to determine tables disk usages.
>
> SELECT nspname || '.' || relname AS "Table",
>     pg_total_relation_size(C.oid) "Size",
>     pg_size_pretty(pg_total_relation_size(C.oid)) AS "Total size"
>   FROM pg_class C
>   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
>   WHERE nspname NOT IN ('pg_catalog', 'information_schema')
>     AND C.relkind <> 'i'
>     AND nspname !~ '^pg_toast'

You haven't given much information, like PostgreSQL version or what
has happened to the table.

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

My first guess, in the absence of evidence to the contrary, is that
the table is taking that much space because a large number of rows
were deleted from it and VACUUM hasn't been run since.

Do the numbers change if you VACUUM the table explicitly?  How about
if you TRUNCATE it or CLUSTER it?

-Kevin