Feature: schema-level checksum pre-scan to skip unchanged tables #179

Problem or use case

PR #158 introduced a checksum pre-scan for data diffs — CHECKSUM TABLE on MySQL returns in milliseconds and skips the full SHA2 scan for identical tables. This same pattern could be applied to schema comparison to skip the expensive per-table information_schema / pg_catalog queries when a table's schema hasn't changed.

Currently, dbdiff performs a full sequential schema comparison for every table in the database, even when most tables are identical between source and target. For databases with many tables over higher-latency connections, this causes the schema diff to take 5+ minutes or time out entirely — even when comparing a database to itself (zero drift).

Proposed solution

Before running the full schema diff for each table, compute a lightweight hash of its schema metadata and compare:

PostgreSQL example:

SELECT md5(string_agg(
  column_name || '|' || data_type || '|' || COALESCE(column_default, '') || '|' || is_nullable,
  ',' ORDER BY ordinal_position
))
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = $1;

This could be extended to include indexes, constraints, and triggers in the hash. If the hash matches between source and target, skip that table entirely.

Performance impact

In a typical drift-detection scenario, the vast majority of tables have identical schemas between environments. If 90% of tables are unchanged:

• Current: Full schema comparison on all N tables (sequential, slow)
• With pre-scan: Cheap hash on all N tables, full comparison on only ~10% of them

Combined with parallel table processing (#165), this would reduce schema diff time from minutes to seconds.

Precedent

This follows the same pattern proven in PR #158:

• MySQL CHECKSUM TABLE skips identical tables for data diffs
• PostgreSQL streaming sorted-merge uses md5(row_hash) in Phase 1 to avoid transferring full row data

Applying it to schema metadata is a natural extension.

Related

• feat: Streaming sorted-merge diff for PostgreSQL, SQLite sha3 hash, MySQL CHECKSUM pre-scan #158 — Streaming sorted-merge diff (data checksum pre-scan, merged)
• Feature: parallel / threaded table processing #165 — Parallel / threaded table processing (open, complementary)
• Add Parallel threaded workers to speed processing. Add Checksum table comparison to skip tables. #133 / Add a optional checksum comparison and threaded processes #134 — Original checksum + threading proposal

Relevant database(s)

All

Alternatives considered

No response