Re: Compressed binary field

Edson Richter <edsonrichter@hotmail.com> wrote:

> SELECT pg_size_pretty(pg_database_size('mydatabase'));
>   pg_size_pretty
> ----------------
>   7234 MB

> SELECT nspname || '.' || relname AS "relation",
>      pg_size_pretty(pg_relation_size(C.oid)) AS "size"
>    FROM pg_class C
>    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
>    WHERE nspname NOT IN ('pg_catalog', 'information_schema')
>    ORDER BY pg_relation_size(C.oid) DESC
>    LIMIT 10;
>              relation             |  size
> ---------------------------------+---------
>   pg_toast.pg_toast_18409         | 4976 MB
>   pg_toast.pg_toast_18146         | 290 MB
> [ ... ]

> SELECT relkind, oid, relfilenode, reltoastrelid,
>         relpages, reltuples
>    FROM pg_class
>    ORDER BY relpages DESC
>    LIMIT 10;
>
> results
>   relkind |  oid   | relfilenode | reltoastrelid | relpages |
> reltuples
>
---------+--------+-------------+---------------+----------+------
> -----
>   r       |  18064 |       18064 |         18086 |    29332 |
> 639639
>   r       |  18179 |       18179 |             0 |    22797 |
> 1.811e+06
>   r       |  18116 |       18116 |         18121 |    19772 |
> 724370
>   r       |  18343 |       18343 |         18347 |    14311 |
> 928633
>   r       |  18352 |       18352 |             0 |    10488 |
> 917134
>   r       |  18092 |       18092 |             0 |     7691 |
> 640709
>   r       |  18396 |       18396 |         18404 |     7670 |
> 172791
>   r       |  18558 |       18558 |             0 |     7608 |
> 386907
>   i       | 747805 |      747805 |             0 |     6976 |
> 1.811e+06
>   r       |  18409 |       18409 |         18413 |     6684 |
> 715084

When I run that query on a big database here, my top three entries
are for relkind of 't' (starting with a 2TB TOAST table for our
document images) and number four is a TOAST index.  It's hard to see
why you don't have TOAST entries at the top of your list.  Instead
of a VACUUM FULL, could you try a VACUUM FREEZE VERBOSE ANALYZE
against the full database (using a database superuser login) and
capture the output?  Please post the portion of the output for the
big table and its TOAST table, and see whether the numbers
(pg_class.relpages * 8KB versus pg_relation_size()) start to match
up.

You might also want to confirm that neither pg_stat_activity nor
pg_prepared_xacts shows any lingering transactions started more than
a few minutes ago.

> So, all of this information was get using unique database session,
> so they must related to same database and data files

Thanks, that helps suggest where to look next.

-Kevin