3 ms·

I think my #1 rule for SQL these days is to abuse common table expressions as much as possible. No amount of whitespace cleanliness can compensate for a poorly

I think my #1 rule for SQL these days is to abuse common table expressions as much as possible. No amount of whitespace cleanliness can compensate for a poorly organized problem. There is (in my mind) no longer an excuse for trying to join 10+ tables all at once in a single heroic attempt. Decompose the problem and let the query planner figure that shit out for you, just as you would with a compiler and code. With CTEs you can offload sophisticated joins and constraints in such a way that less experienced developers can follow behind more easily. Once you find multiple queries using the same WITH clauses, you can create more permanent views that further centralize and optimize these concerns.

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 ago
  Good call. As an aside why not use postgres's mysql foreign data wrapper instead of syncing with the mysql database?
  - jd3 2y ago
    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 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/...
prudentpomelo 2y ago
CTEs would be such a blessing. I am stuck using mysql 5.6. So many queries would just get simpler.

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 ago
Good call. As an aside why not use postgres's mysql foreign data wrapper instead of syncing with the mysql database?
- jd3 2y ago
  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 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/...

prudentpomelo 2y ago

CTEs would be such a blessing. I am stuck using mysql 5.6. So many queries would just get simpler.