Edson Richter <edsonrichter(at)hotmail(dot)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