Thread: toast tables

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

On Mon, May 9, 2016 at 6:59 PM, drum.lucas@gmail.com <drum.lucas@gmail.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?

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

  Have you tried to estimate bloat? 

(from https://github.com/ioguix/pgsql-bloat-estimation/tree/master/table)