Context
We just moved our sql database from non-partitioned tables to partitioned ones with the help of psqlextra.
We decided to do so since our application was experiencing some performance issues when it came to making queries, and reducing the amount of data we have was not an option. The partitioning was successful, however as expected queries not made on our partition key became much slower than before and the ones using said partition key have greatly improved in performances.
For the sake of explanation, the partitioning key will be referred to as created_on and the queries that are now slow are being made to the primary or foreign key of our models.
What we want: For queries to be faster
What we had in mind
We decided to implement mapping tables that would map for a given entry in our tables the id to the created_on value.
The idea being that if a request is made that does not contain the created_on field but does have the primary key we could speed up the request by making a first request to get the partitioning get and then make a second request using that to greatly improve performances.
The current problem: Foreign keys
What we are currently trying to do is force Django to filter the field of the other table from a foreign key, e.g. TableA and TableB, Q(table_b__id=123) which will do a WHERE TableA.table_b = 123, but we want to do WHERE TableB.id = 123, how could we achieve that?