I have a base with 500MB I consider small, but this is slow to open some tables, checking are tables that have Bytea field, the other programmer saved images in the bank, but it is unworkable to work, left the application slow, because sometimes the images are not treated and they are huge, in short, it is a situation that I can put the images in a directory, they are a table at the base.
Is there a way to give PostgreSQL a choice to show me which fields have Bytea type?
Make the following query:
select table_schema, table_name, column_name from information_schema.columns where data_type like 'bytea'
Among the postgres catalog tables there is pg_attribute that contains the column information of all database tables. In this table there is the atttypid column that stores the id of the data type, so it would be necessary to filter the records corresponding to type bytea according to your oid in the pg_type table.
select
relname as tabela,
relnamespace::regnamespace as schema,
attname as coluna
from pg_attribute a
inner join pg_class c
on (a.attrelid=c.oid)
inner join pg_type t
on (a.atttypid=t.oid)
where typname='bytea'
and relnamespace not in (
'pg_catalog'::regnamespace,
'pg_toast'::regnamespace
);
Below are the links to information in the catalog tables used in the query, in case you need to extract more information: link link link