PostgreSQL DB Table Selector Stalls for large database

Hi KNIME,

We have a relatively large database: ~5.000 schemas and ~100.000 tables.

When using

Select a table

in

DB Table Selector

node, the

Database Metadata Browser

stalls on the message “Fetching metadata…”

Metadata browsing for such a large database is slow. For other tools like DBeaver Database Manager,

it’s possible to set Schema filtering in connection settings, so database navigation is easier.

For this issue, a temporary work-around has been created.

A Component node that discover the Schemas available for the user and selection. A subsequent Component node that discover tables and views in the schema.

Example of schema discovery

PostgreSQL: Documentation: 17: 35.46. schemata

-- List user schemas
SELECT schema_name as "schema"
FROM information_schema.schemata
order by schema_name

And for tables+views

select * from (
select pt.schemaname, pt.tablename as "tableview", pt.tableowner as "owner", 'table' as "type"
from pg_tables pt
where schemaname='$${Sschema}$$'
union
select pv.schemaname, pv.viewname as "tableview", pv.viewowner as "owner", 'view' as "type"
from pg_views pv
where schemaname='$${Sschema}$$'
) as tv
order by tv.type, tv.tableview

Having this information, the user can then use “DB Table Selector” without the “Select a table” button.

This “necessecity” is not easy to explain in “On-boarding” sessions with our new KNIME users.

Would it be possible to make a configuration option in PostgreSQL connector or DB Table Selector, that only show the Schemas/Tables/Views available to the user?