perf(pgsql): batch schema fetching for changed tables (#165, #184) - #183

Closes #165. Closes #184.

Makes schema diffing cost a constant number of round-trips instead of one set per table, then fixes the query that was absorbing most of that gain. Along the way this fixes a constraint-name collision bug and adds test coverage for the two performance passes, neither of which had any.

1. Batch schema fetching (#165)

The pre-scan in #180 skips tables that are already identical. The tables that genuinely differ still needed 8 queries per table per side. This loads them all together instead.

• New BulkSchemaAdapterInterface with a single getBulkTableSchema() method. It is a separate opt-in interface, so DBAdapterInterface is untouched and MySQL/SQLite need no changes — MySQL already resolves a table in ~2 queries, and SQLite is a local file with no network latency.
• PostgresAdapter implements it: 7 fixed queries per side regardless of table count. getTableSchema() and getCreateStatement() now delegate to it.
• DBSchema::getDiff() partitions common tables by hash, then bulk-fetches only the changed ones.
• TableSchema::getDiff() accepts pre-fetched schemas, falling back to per-table queries when none are supplied.

For a 300-table database with 7 real changes: 16 queries total, against roughly 4,800 with per-table fetching.

Also fixes a duplicate query — the named NOT NULL lookup previously ran twice per table, once from fetchColumns() and once from fetchConstraints().

2. pg_catalog rewrite of the constraint query (#184)

Timing the seven batch queries individually showed one was 99.2% of the whole fetch — information_schema.table_constraints joined to key_column_usage, referential_constraints and constraint_column_usage. Those views wrap the catalogs in per-row privilege checks, which stops the planner pushing table_name IN (...) down through them.

Reading pg_constraint directly, with unnest(conkey) WITH ORDINALITY for the column list:

Roughly 120x, taking per-table cost from ~39 ms to ~0.3 ms.

Output is unchanged — the CASE arms reproduce exactly what the information_schema views emit, including Postgres mapping simple match to NONE rather than SIMPLE. Incidentally, the column ordinal is now explicit rather than relying on key_column_usage ordering, and pg_catalog does not omit constraints the connecting role lacks privileges on.

3. Constraint-name collision fix

Found while adding tests. The constraint query joined pg_constraint on conname alone, but Postgres scopes constraint names per table, not per schema. A constraint on one table matched a same-named constraint on another, duplicating the row and producing corrupt DDL:

old (name-only join):  users | shared_name | ref     <- duplicated
                       users | shared_name | ref
new (table-scoped):    users | shared_name | ref

That duplicate became FOREIGN KEY ("ref", "ref"). Reproduced on Postgres 16 with two tables sharing a constraint name. Grouped constraint columns are now keyed by name so any join fan-out collapses — an N-column FK referencing an N-column key produces N×N rows. Constraints buildConstraintDef() cannot render are dropped rather than stored as nulls in the diff map.

Shared helpers

MySQLAdapter and PostgresAdapter both sit on the 20-method SonarCloud ceiling, so two duplicated snippets moved to DBDiff\DB\Support\QueryHelper: IN-clause placeholder building (PostgresAdapter, StreamingMergeDiff) and table-map narrowing (getSchemaHashMap on both adapters).

Tests

Both performance passes had shipped with no coverage. This adds 91 tests:

• QueryHelperTest — placeholder generation and table narrowing.
• PostgresBulkSchemaTest — assembly of columns, indexes and constraints from pre-fetched rows, concentrating on the cross-table isolation that single-table fetching used to provide for free.
• SchemaBatchFetchTest — DBSchema wiring, asserting the negatives: unchanged tables are never fetched, changed tables are batched exactly once per side, non-bulk adapters still fall back per table.
• BulkSchemaPostgresTest — live-server suite asserting the batch path returns exactly what the per-table path returns, that the query budget stays at 7 per side regardless of table count, that schema hashes track schema but not data, and that every referential-action mapping renders correctly. It builds its own database and compares the two code paths against each other, so it needs no per-version golden files.

Verified against a live Postgres 16: 788 tests pass, including the golden-file end-to-end migration test, which still matches byte for byte. The new suite is registered in the Postgres testsuite, so CI runs it across PG 14–18.

Docs

The README documented neither performance pass. Adds a Schema Diff Performance section with the per-driver matrix and the round-trip maths. Separately, --allow-destructive from #174 had shipped with no README coverage at all — the flag was missing from the options table and the blocking behaviour was undocumented — so that gets a section too.

Not included

Chunking the IN (...) list, measured and rejected. At 1000 tables it makes under 1% difference (39246 ms vs 38908 ms, within noise), peak memory is ~4 MB, and padding the parameter list to 65000 while holding result size fixed adds only ~0.6 s. The 65535 bind-parameter ceiling is real and fails cleanly, but requires more than 65535 changed tables in a single diff. Rationale recorded in #184.