Schema sync discovery: per-table constraint query takes 60–90s each on PostgreSQL 18, deploys take 30–40 minutes

Description

Entity-driven schema sync (db.get_schema_registry(...) + registry.sync()) spends 30–40 minutes in schema discovery on PostgreSQL 18.

Setup: a single PostgreSQL database shared by 3 applications, each in its own schema with its own database user. Each application runs sea-orm schema sync against its own schema on deploy. All 3 are affected.

The time goes into sea-schema's constraint discovery query (SchemaQueryBuilder::query_table_constraints — the information_schema.table_constraints join with referential_constraints_subquery):

• each execution takes 60–96 seconds while returning only 9–14 rows (e.g. elapsed=96.377s rows_returned=14 in our slow-statement log),

• SchemaDiscovery::discover() re-runs it once per table, sequentially, so total sync time ≈ 90 s × table count.

The catalog is small and healthy, so this is not a data-size problem: pg_constraint has 1017 rows total, pg_inherits has 15, and there is no catalog bloat. pg_stat_activity shows wait_event = NULL for the whole duration — the query is burning CPU inside the information_schema views, not waiting on locks.

Two things in the generated query make it structurally expensive in a multi-schema database: the constraint_column_usage join has no schema qualifier (only constraint_name), and the OR'd join condition on position_in_unique_constraint prevents hash/merge joins, forcing a nested loop over the view results.

Steps to Reproduce

1. PostgreSQL 18; a single database containing multiple schemas owned by different users (~1000 rows in pg_constraint overall).

2. Run entity schema sync: db.get_schema_registry("crate::*") then registry.sync(&db), with entities that have FK relations.

3. Observe statement timings (e.g. sqlx slow-statement logging with a 1s threshold).

Expected Behavior

Discovery queries against a ~1000-constraint catalog complete in milliseconds; syncing an already-converged schema takes seconds.

Actual Behavior

Every per-table constraint discovery query takes 60–96 seconds. With a few dozen tables per schema, each deploy spends 30–40 minutes in registry.sync().

Reproduces How Often

Always — every sync, every table, on all 3 applications sharing the database.

Workarounds

Ruled out so far:

• catalog bloat — none (checked);
• jit — already off;
• ANALYZE on the pg_catalog tables — no change;
• lock contention — wait_event stays NULL, so it is pure execution cost.

Regardless of the planner details, a structural fix on the discovery side would remove the problem entirely — e.g. querying pg_catalog directly, or fetching constraints for the whole schema in a single pass instead of per table, and schema-qualifying the constraint_column_usage join.

Versions

sea-orm    2.0.0-rc.41                                                                                                                                                                                                                                                                                                                    
sea-schema 0.18.0                                                                                                                                                                                                                                                                                                                         
sea-query  1.0.1                                                                                                                                                                                                                                                                                                                          
sqlx       0.9                                                                                                                                                                                                                                                                                                                            

Start at SchemaQueryBuilder::query_table_constraints and SchemaDiscovery::discover, then reproduce entity schema sync with PostgreSQL 18 while observing the slow-statement logs. Compare the per-table constraint discovery behavior with the reported multi-schema setup; done means discovery completes in milliseconds and an already-converged sync finishes in seconds.