I am developing a Hospital Information Management System (HIMS), and the project is almost 80% complete. I have been using MySQL as the database, but now I am wondering whether I made the right choice or whether I should have chosen PostgreSQL from the beginning. My main concern is long-term scalability. I expect the database to grow to several GBs or potentially much larger as the hospital generates more patient records, laboratory results, prescriptions, billing transactions, and other medical data.
I have previously experienced reporting and query-performance issues when working with databases containing several GBs of data. I want to avoid facing similar problems when this HIMS goes into production and the data volume increases.
I have two main questions:
- Database migration: Is there a reliable, fast, and relatively straightforward way to migrate an existing MySQL database to PostgreSQL, including tables, relationships, indexes, data, and other database objects, without manually rebuilding everything?
- Database choice: Since my project is already 80% complete, would it be better to continue with MySQL and optimize it properly, or would migrating to PostgreSQL now be a better long-term decision?
I would appreciate advice from developers who have worked on large healthcare systems or enterprise applications with millions of records.
My priority is reliable reporting, query performance, data integrity, scalability, and maintainability over the next 5–10 years.
What would you recommend in my situation, and why?