Re: toast tables - Mailing list pgsql-admin

Hi all,

I'm currently using PostgreSQL 9.2 and my DB size is 2.2 TB..

Running the query below;

WITH schema_size AS (
SELECT
tab.table_catalog AS database_name,
tab.table_schema AS schema_name,
tab.table_name,
pg_total_relation_size(table_schema || '.' || tab.table_name) AS table_size_total,
pg_relation_size(table_schema || '.' || tab.table_name) AS table_size
FROM information_schema.tables tab
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
), pretty_size AS (
SELECT
database_name,
schema_name,
pg_database_size(database_name) AS database_size_bigint,
pg_size_pretty(pg_database_size(database_name)) AS database_size,
sum(table_size_total) AS schema_size_bigint_total,
pg_size_pretty(sum(table_size_total)) AS schema_size_total,
sum(table_size) AS schema_size_bigint,
pg_size_pretty(sum(table_size)) AS schema_size
FROM schema_size
GROUP BY database_name, schema_name
)
SELECT
database_name,
schema_name,
database_size,
schema_size_total,
schema_size,
((schema_size_bigint_total * 100) / database_size_bigint) AS perc_total,
((schema_size_bigint * 100) / database_size_bigint) AS perc
FROM pretty_size

I get the following data:

schema | schema_size_total | schema_size | perc_total | perc

 gorfs | 1824 GB | 20 GB | 85.4308477608319514 | 0.94562882033477939710

As you can see, the GORFS schema is 1.8 TB.

That is all pg_Toast tables....

I read the documentation [1] but actually I couldn't understand:

Is there any way to get the size decreased ?

The DB is in production, so I can't run the VACCUM FULL.

Is there anything I can do?

Is the pg_toast normal?

[1] - http://www.postgresql.org/docs/9.2/static/storage-toast.html

Thanks
Lucas