Re: toast tables

On Mon, May 9, 2016 at 7:05 PM, Scott Mead <scottm(at)openscg(dot)com> wrote:

> On Mon, May 9, 2016 at 6:59 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com
> > wrote:
>
>> 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?
>>
>
If you have bloat, and you want to reclaim, try pg_repack (
https://github.com/reorg/pg_repack)

VACUUM FULL without *all* of the locks. (minimal locking)

--Scott

>
>> Is the pg_toast normal?
>>
>
> TOAST (The Oversized Attribute Storage Technique) is completely normal and
> automatic based on your datatypes.
>
>
>
> Have you tried to estimate bloat?
>
> (from https://github.com/ioguix/pgsql-bloat-estimation/tree/master/table)
>
> /* WARNING: executed with a non-superuser role, the query inspect only
> tables you are granted to read.
> * This query is compatible with PostgreSQL 9.0 and more
> */
> SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size,
> (tblpages-est_tblpages)*bs AS extra_size,
> CASE WHEN tblpages - est_tblpages > 0
> THEN 100 * (tblpages - est_tblpages)/tblpages::float
> ELSE 0
> END AS extra_ratio, fillfactor, (tblpages-est_tblpages_ff)*bs AS
> bloat_size,
> CASE WHEN tblpages - est_tblpages_ff > 0
> THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float
> ELSE 0
> END AS bloat_ratio, is_na
> -- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag
> FROM (
> SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil(
> toasttuples / 4 ) AS est_tblpages,
> ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) +
> ceil( toasttuples / 4 ) AS est_tblpages_ff,
> tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages,
> toastpages, is_na
> -- , stattuple.pgstattuple(tblid) AS pst
> FROM (
> SELECT
> ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
> - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
> - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE
> ceil(tpl_data_size)::int%ma END
> ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages +
> toastpages) AS tblpages, heappages,
> toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname,
> tblname, fillfactor, is_na
> FROM (
> SELECT
> tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS
> tblname, tbl.reltuples,
> tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS
> toastpages,
> coalesce(toast.reltuples, 0) AS toasttuples,
> coalesce(substring(
> array_to_string(tbl.reloptions, ' ')
> FROM '%fillfactor=#"__#"%' FOR '#')::smallint, 100) AS
> fillfactor,
> current_setting('block_size')::numeric AS bs,
> CASE WHEN version()~'mingw32' OR
> version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
> 24 AS page_hdr,
> 23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*)
> ) / 8 ELSE 0::int END
> + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,
> sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024) )
> AS tpl_data_size,
> bool_or(att.atttypid = 'pg_catalog.name'::regtype) AS is_na
> FROM pg_attribute AS att
> JOIN pg_class AS tbl ON att.attrelid = tbl.oid
> JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
> JOIN pg_stats AS s ON s.schemaname=ns.nspname
> AND s.tablename = tbl.relname AND s.inherited=false AND
> s.attname=att.attname
> LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
> WHERE att.attnum > 0 AND NOT att.attisdropped
> AND tbl.relkind = 'r'
> GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids
> ORDER BY 2,3
> ) AS s
> ) AS s2
> ) AS s3
> -- WHERE NOT is_na
> -- AND tblpages*((pst).free_percent +
> (pst).dead_tuple_percent)::float4/100 >= 1
>
>
> --
> Scott Mead
> Sr. Architect
> OpenSCG
> http://openscg.com
>
>
>> [1] - http://www.postgresql.org/docs/9.2/static/storage-toast.html
>>
>> Thanks
>> Lucas
>>
>
>