Partitioning Postgres table

Question

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.

Database Tables

"I would also like to partition the patient table; a partition of patients by each doctor." How does this work when the same patient can be assigned to multiple doctors? And what do you want to achieve by partitioning the table? — Thilo, Commented Jul 14, 2019 at 7:43
How many patients and doctors do you expect? Partitioning is not necessary (nor helpful) if you only have a few million rows in your table — user330315, Commented Jul 14, 2019 at 7:49

Laurenz Albe · Accepted Answer · 2019-07-14 04:47:32Z

1

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?

answered Jul 14, 2019 at 4:47

Laurenz Albe

250k21 gold badges298 silver badges373 bronze badges

Reading the answers i see that use partitioning is not convenient in this case, @LaurenzAlbe can you please share me a correct maner to do many-to-many relations? Thank you!
– bdw
Commented Jul 14, 2019 at 16:56
I can show you what I think is good. For example in doctor_patient, remove id_doctor_patient and instead declare the primary key on (id_doctor, id_patient) (or the other way around). That saves you a column, and it ensures that there can be only one entry per doctor-patient combination.
– Laurenz Albe
Commented Jul 14, 2019 at 17:02

Add a comment |

Collectives™ on Stack Overflow

Partitioning Postgres table

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related