Postgres Pro
Downloads
Home   >   mailing lists

Thread: Toast table infi

Toast table infi

From
Sathish Reddy
Date:
Hi 
  I am trying to get toast tables information from cluster level all databases tables with child tables.but not working.please help me on queries to sort out these.

Thanks 
Sathish Reddy 

Re: Toast table infi

From
Kashif Zeeshan
Date:
Hi

Please try out the following query.

This will display all the tables with associated Toast tables in the cluster.

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    t.relname AS toast_table_name
FROM
    pg_class c
JOIN
    pg_namespace n ON c.relnamespace = n.oid
JOIN
    pg_attribute a ON c.oid = a.attrelid
LEFT JOIN
    pg_class t ON a.attrelid = t.reltoastrelid
WHERE
    c.relkind = 'r'  -- Regular tables
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')  -- Exclude system schemas
    AND c.reltoastrelid != 0  -- Only tables with toast tables
ORDER BY
    n.nspname, c.relname;


To check if a table has a toast table .

select t1.oid, t1.relname, t1.relkind, t2.relkind, t2.relpages, t2.reltuples
from pg_class t1
inner join pg_class t2
on t1.reltoastrelid = t2.oid
where t1.relkind = 'r'  and t2.relkind = 't';

Regards
Kashif Zeeshan
Bitnine Global

On Mon, May 27, 2024 at 10:50 AM Sathish Reddy <sathishreddy.postgresql@gmail.com> wrote:
Hi 
  I am trying to get toast tables information from cluster level all databases tables with child tables.but not working.please help me on queries to sort out these.

Thanks 
Sathish Reddy 

Re: Toast table infi

From
"David G. Johnston"
Date:


On Sunday, May 26, 2024, Sathish Reddy <sathishreddy.postgresql@gmail.com> wrote:
Hi 
  I am trying to get toast tables information 

You may wish to narrow that down.
 

from cluster level all databases


Not directly doable - you will need to execute one query per database.



but not working.please help me on queries to sort out these.

You should at least share what you did try.
 
David J.

Toast table infi

From
"David G. Johnston"
Date:
The convention on these lists is to inline-post (and trim) or bottom-post.

On Sunday, May 26, 2024, Kashif Zeeshan <kashi.zeeshan@gmail.com> wrote:

Please try out the following query.

This will display all the tables with associated Toast tables in the cluster.

pg_class does not produce cluster-wide results (nor do the others).

David J.
 

Re: Toast table infi

From
Laurenz Albe
Date:
On Mon, 2024-05-27 at 11:20 +0530, Sathish Reddy wrote:
> I am trying to get toast tables information

What kind of information are you interested in?

Yours,
Laurenz Albe

Re: Toast table infi

From
Sathish Reddy
Date:
I am looking for all toast tables with size from cluster level all databases.it need to perform as like pg_stat_activity 


Thanks 
Sathish Reddy 

On Mon, May 27, 2024, 2:35 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Mon, 2024-05-27 at 11:20 +0530, Sathish Reddy wrote:
> I am trying to get toast tables information

What kind of information are you interested in?

Yours,
Laurenz Albe

Re: Toast table infi

From
Laurenz Albe
Date:
On Mon, 2024-05-27 at 15:16 +0530, Sathish Reddy wrote:
> I am looking for all toast tables with size from cluster level all databases.
> it need to perform as like pg_stat_activity

SELECT oid::regclass,
       pg_relation_size(oid)
FROM pg_class
WHERE relkind = 't'
  AND relnamespace = 'pg_toast'::regnamespace;

That will show you all TOAST tables from the current database.

You have to connect to all databases and run the query in each one.

Yours,
Laurenz Albe