Discovery Performance

[BubbaTLC]

Original Issue in Slack Thread

tap-postgres==v0.0.2

Performance on discovery is taking about 1 second per table. This is extremely slow for large schemas (even 60 tables would take a minute)

Discovery on remote PSQL 11 server on high perfomance internet connection. Schema 3452 tables.

tap-postgres --config config.json --discover  59.60s user 10.66s system 1% cpu 1:21:01.21 total

Please try and replicate the issue. Might still be something on our end

Proposed solutions

1. Increase discovery efficiency
2. Only run discover on selected tables
3. Allow the ability to specify tables to discover.

[florian-ernst-alan]

I stumbled upon the same issue. Looks like the root cause is in singer_sdk's SQLConnector class (here). Time complexity is indeed linear.

[florian-ernst-alan]

This would probably require the SDK to move to SQLAlchemy 2.0 in order to use all the inspected.get_multi_* methods (documentation)

[edgarrmondragon]

This would probably require the SDK to move to SQLAlchemy 2.0 in order to use all the inspected.get_multi_* methods (documentation)

@florian-ernst-alan interesting. Is this the feature that would be relevant here?

The objects can be filtered by passing the names to use to filter_names.

[florian-ernst-alan]

I think it would yes. Basically, instead of getting all informations individually (for each table, get each column names, get each primary key...), get all of them at once and then use them downstream.

Let's say I'm 90% sure.