Partitioning Postgres table
I am building a database in Postgres 11, and I would like to segment the information by partitioning tables. The appointment table is already partitioned by date ranges, and I would also like to partition the patient table; a partition of patients by each doctor.
The question is: How can I partition the patient table with list partitioning? That is to say, for this table I would have to make a direct partition relationship with the doctor table or I would have to use the intermediate table, since between the two mentioned tables there is a relationship of many to many.
Attached is an illustrative image.
For a many-to-many relationship you will need a mapping table, partitioning or not.
I wouldn't use an artificial primary key for the mapping table, but the combination of id_doctor and id_patient (they are artificial anyway). The same holds for the appointment table.
Since id_doctor is not part of the patient table (and shouldn't be), you cannot partition the patient table per doctor. Why would you want to do that? Partitioning is mostly useful for mass deletions (and to some extent for speeding up sequential scans) — is that your objective?
There is a wide-spread assumption that bigger tables should be partitioned just because they are big, but that is not the case. Index access to a partitioned table is — if anything — slightly slower than index access to a non-partitioned table. Do you have billions of patients?