Details
-
Type:
RFC
-
Status: Implemented
-
Resolution: Done
-
Component/s: DM
-
Labels:None
Description
The LDF has been administrating the consolidated DB environment. Since the beginning of the LDF, Oracle has been the database of choice for the consolidated DB. Oracle was chosen based on scalability of the data and admin domain knowledge based on previous astronomy projects at the LDF. NCSA still believes that as of operations year 4-5 with the current data design that most databases will have scalability issues, but that problem can be addressed closer to that time. Many scalability issues can be solved by better hardware such as NVMe disk drives or SSDs and the like. Paying for a costly DB that might solve problems that might happen in 3-4 years depending on design decisions and hardware along with the current constant workarounds that are required for Oracle has become a large time sink for the project. Time of the middleware developers can be used best elsewhere instead of trying to figure out why it works for other DBs and not for Oracle.
Since the Butler/Gen3 environment is not using the consolidated DB with real data and the rest of the project is basically using SQLite, this is a perfect time to move to a different DB before Oracle gets set in stone requiring a transition plan to move to a different DB . The Postgres database has always been tested along side of the Oracle consolidated DB for all Butler/G3 processes and is a natural DB to use due to its open nature, and it is well-supported on many platforms. The hardware already dedicated to the Oracle platform at the LDF can be moved easily to Postgres, and this saves the project from having to purchase the Oracle scaling software called "RAC" each year.
Please leave comments here if you think that the choice of moving to Postgres over Oracle will affect the project in any way negatively.
Attachments
Issue Links
Activity
Strongly support. Besides the reasons mentioned in the RFC, using Postgres in our technical baseline increases the portability of our services and allows us to use without modification code from CADC that requires spherical geometry (through pgsphere). SQuaRE already uses Postgres for small ad-hoc databases (eg. the nublado session database) and we have helm charts etc. for their deployment.
Using Postgres in our technical baseline is also consistent with DM's commitment to open source software. In the event where dual database testing is required for compatibility, I suggest we use the other leading open source contender, MariaDB.
I would have been happy to see an RFC on the technical merits, even putting aside the cost - the latter makes it a no-brainer.
To add to what Frossie said, this is also an improvement from a security standpoint. Oracle is notoriously uninformative about security patches and vulnerability disclosure, while PostgreSQL is a good open source software citizen, receives excellent security support, and is very widely used and thus widely studied with a lot of best practices about secure configurations.
It is not clear to me how the existing hardware will be configured for Postgres. As I understand it, there are multiple machines in a RAC cluster. Will each machine host a different database server, or will they be clustered in some way?
While existing Gen3 Butler performance issues are almost certainly resolvable by software changes, I'd like to make sure that we have adequate headroom for future growth.
Flagging for the DMCCB, since this is a change to the baseline. I expect that to be a formality, though: please don't let it distract you if you have a useful technical comment to add.