Determine which database technology to use about jwql

[gkanarek]

We can start with this, perhaps? https://db-engines.com/en/system/Microsoft+SQL+Server%3BMySQL%3BPostgreSQL

[bourque]

I agree completely, but I figured I'd give MSS its due diligence ;)

[bourque]

My thoughts:

1. We should throw out the possibility of MSS right away since (1) it has a commercial license as @gkanarek points out and (2) I don't think anyone on our team is familiar with it?
2. The fact that PostgreSQL supports array types is very intriguing and could prove to be useful down the line
3. With ITSD using PostgreSQL for confluence and jira (and perhaps other things) we know there are experts in the building that could help us if needed.
4. From various articles I have read there doesn't seem to be a drastic difference in performance between MySQL and PostgreSQL
5. SQLAlchemy, which is likely what we will use to implement the database schema, supports both MySQL and PostgreSQL
6. Thanks to SQLAlchemy, if we ever find the need to switch from PostgreSQL to MySQL or vice-versa, it comes down to literally changing one line of code.

With these in mind, I think my official vote would be to use PostgreSQL. I would be interested to hear if anyone else has any thoughts on this.

[catherine-martlin]

There are a lot of possible comparisons done by others that could be worth investigating, for example: http://www.postgresqltutorial.com/postgresql-vs-mysql/ , https://www.pg-versus-ms.com/

We will need to be careful to decide on how much we do or don't trust the sources/agree with their premises and how they relate to our needs.

[hover2pi]

I can help with this. We should make estimates of the data types, file sizes, query volume, etc. to inform our research.

[bourque]

I have started creating a draft of an evaluation matrix with the metrics that we care about: https://stsci.box.com/s/s8q5so5qjy2tmkakj99zmtnqaktjws3y

Feel free to add/edit

[gkanarek]

For what it's worth, I vote that we stay away from MSS, so that everything can be open source.

[bourque]

FWIW, I had a conversation with Alex Yermolaev about this. He said that they are using PostgresSQL for confluence and jira and it has been working very well and much better than MySQL has. He also noted that PostgreSQL has strength in the industry right now (lots of commitment to R&D) and voiced concerns that Oracle is slowly killing support for MySQL.

[gkanarek]

That all sounds good to me.

Speaking of database schema implementations, I've used both SQLAlchemy and pyDAL, and found the latter to be more significantly more intuitive. However, it's definitely not as mature as SQLAlchemy, so that's something to consider.

[SaOgaz]

@bourque we might want to also make sure there are no known bugs in SQLAlchemy for PostrgresSQL that aren't in MySQL (given that Matt and I have had the most experience using SQLAlchemy to connect to MySQL). Not sure if this will be easy to even look up given the level of documentation for SQLAlchemey, but maybe their github has better bug tracking.

[SaOgaz]

So far everything looks good, but it does seems like we want to use the psycopg underlayer in SQLAlchemy. I guess there are several Python to PostgreSQL adapters that SQLAlchemy can use, but Psycopg is the most developed. I will keep looking into things.

[SaOgaz]

All right, I've done a lot of poking around and nothing major has come up. So I'll e-mail the list that we're good to go!

[bourque]

Thank you for your research @SaraOgaz. Based on all of this and our previous findings, we have decided that we are going to use PostgreSQL!