Database Introspection Performance#

When Sequin adds a new database connection, it immediately introspects the target PostgreSQL catalog to enumerate schemas and tables. On instances with large catalogs (10,000+ tables, 500,000+ columns), this step exceeds the hardcoded 15-second Postgrex query timeout, causing the database creation flow to crash. This is a known open issue (#2157).

The Failure Path#

Form submission triggers create_db_with_slot → create_db → update_tables → list_tables, which runs two catalog queries in sequence 1:

1. list_schemas/1 — queries information_schema.schemata 2
2. fetch_tables_with_columns/2 — queries pg_class, pg_namespace, pg_attribute, pg_type, pg_index directly 3

The bottleneck is step 1. information_schema.schemata performs a has_schema_privilege() check for every row, which scales poorly on catalogs with many schemas. The "Test connection" button does not trigger this path — it only runs a cheap has_database_privilege check 4, so the timeout is only encountered at form submit time.

Notably, fetch_tables_with_columns/2 already avoids information_schema by querying pg_catalog tables directly, so step 2 is not the issue.

Hardcoded Timeout Limits#

There are two relevant timeout constants with no user-facing override:

The 15-second query timeout is applied as the fallback in query/4 and is what triggers the DBConnection.ConnectionError on large catalogs. Introspection functions (list_schemas, fetch_tables_with_columns) do not pass a custom :timeout opt, so they inherit this default 5.

The connect_timeout (20 s) is set in to_postgrex_opts/1 and governs initial TCP connection establishment — it is not the limiting factor here.

Observed Error#

[error] Crashed in databases/form.ex:handle_event/2: %DBConnection.ConnectionError{
  message: "tcp recv: closed (the connection was closed by the pool, possibly due to a timeout or because the pool has been terminated)",
  ...
}

The Postgrex log also shows the connection was dropped after 15,000 ms while blocked at prim_inet.recv0/3 inside Postgrex.Protocol.rows_recv/5 1.

Proposed Fixes#

Three options are tracked in issue #2157:

1. Replace information_schema.schemata with pg_namespace in list_schemas/1. This is a one-line change and the highest-leverage fix — pg_namespace doesn't perform per-row privilege checks. On the reported catalog (10,442 tables), this alone is expected to resolve the timeout.

2. Increase or make the query timeout configurable for the introspection flow. The query/4 function already supports a per-call :timeout opt 6; the fix would be to pass a higher value from list_tables callers.

3. Scope introspection to a user-selected schema up front rather than fetching all schemas in one pass 5.

Key Source Files#