4 ms·
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, le
by jd3 2y ago
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 https://imgur.com/a/HQbNSIL
- tankenmate 2y agoGood call. As an aside why not use postgres's mysql foreign data wrapper instead of syncing with the mysql database?
- jd3 2y agoI 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 https://github.com/EnterpriseDB/mysql_fdw [1]: https://www.enterprisedb.com/docs/mysql_data_adapter/latest/configuring/#parameters-4 https://www.enterprisedb.com/docs/mysql_data_adapter/latest/...