[jd3]

A week before being laid off last month, I solved a decade+ old open problem at our company which first occurred since Django doesn't natively support CTE's, leading to years of technical debt from the ersatz sql/query plans produced by our fragile queries.

I ended up manually overloading get_extra_restriction on a custom ForeignKey class (we couldn't use FilteredRelation b/c we were still on django 1.11), which ensured that the JOIN ON ... clause limited the tables being joined to their correct partition/schema while being accessed through a view

The view thing is a long story — it was a legacy PAC codebase from the '90s which used 13+ schemas in a mysql db that was then being synced to our postgres db through Amazon DMS. All of the tables on each view contain identical source_schema/CompanyID columns, hence the

    '%(remote_alias)s. "source_schema" = %(fk_alias)s. "source_schema" AND '
    '%(remote_alias)s. "CompanyID" = %(fk_alias)s. "CompanyID"

etc. approach

before/after query plan in depesz: https://imgur.com/a/HQbNSIL

[tankenmate]

Good call.

As an aside why not use postgres's mysql foreign data wrapper instead of syncing with the mysql database?

[jd3]

I was not tech lead on that part of the project and did not contribute to any of its initial research/implementation, sadly; I was brought in post-hoc to help with perf issues/cleanup.

To directly answer your question, though, without going into too much detail, there were pci compliance issues with some tables/columns in the mysql db, so if I had to guess, perhaps DMS had the capability to assuage that concern more elegantly than mysql_fdw[0], though the mysql_fwd EXCEPT parameter seems to also do the same thing, so honestly, I have no idea.

    By default, all views and tables existing in a particular database on the MySQL host are imported. Using this option, you can exclude specified foreign tables from the import.[1]

[0]: https://github.com/EnterpriseDB/mysql_fdw

[1]: https://www.enterprisedb.com/docs/mysql_data_adapter/latest/...