toast tables - Mailing list pgsql-admin

From drum.lucas@gmail.com
Subject toast tables
Date
Msg-id CAE_gQfVBLNxys028=9OXj7v=LtMzUmvSK=WavpKfF34DQaj7JQ@mail.gmail.com
Whole thread Raw
Responses Re: toast tables
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?


Thanks
Lucas

pgsql-admin by date:

Previous
From: Tom Lane
Date:
Subject: Re: Autovacuum of pg_database
Next
From: Scott Mead
Date:
Subject: Re: toast tables