Which Database?

[nelsonic]

We need to carefully select a Database for this, because it's the single "critical success factor". The two that I am currently considering are:

• PostgreSQL: because it's "general purpose" and we can easily map the fields to an SQL Schema https://blog.andyet.com/2016/02/04/postgres-9.5-document-store-hybrid
• CouchDB: because it has "built-in" revision history and excellent clustering fore resiliance. https://medium.com/linagora-engineering/setting-up-a-couchdb-2-cluster-on-centos-7-8cbf32ae619f https://hackernoon.com/running-a-couchdb-2-0-cluster-in-production-on-aws-with-docker-50f745d4bdbc

Other options could include:

• cockroachdb: https://github.com/cockroachdb/cockroach (but I have zero experience with it)

@SimonLab are you up for doing a "spike" to investigate cockroachdb ?

[nelsonic]

https://www.youtube.com/watch?v=p8aJuk7TJJA

[nelsonic]

On the basis that: a) there is no support for CockroachDB on Travis-CI (yet...!): https://docs.travis-ci.com/user/database-setup/ b) we already know PostgreSQL inside-out from our time using Phoenix and have the necessary tools on our localhost. c) we can "move fast" because there is nothing to learn/configure so we can ship an MVP in a week!

I suggest that we stick with a "standard" Phoenix setup for this app (i.e. use PostgreSQL).

Given the relative simplicity of the data we are storing I don't envisage us having too much "difficulty" migrating to CockroachDB once we have a reasonable "scale". See: https://www.cockroachlabs.com/docs/stable/frequently-asked-questions.html#can-a-postgresql-or-mysql-application-be-migrated-to-cockroachdb

@SimonLab what are your thoughts on this?

Note: we will almost definately need to do the CockroachDB "Spike" in the near future ... if/when the the app is "successful". 🤞 meanwhile we can "wait and see" what happens with this issue: https://github.com/cockroachdb/cockroach/issues/5582 ("Elixir Connection Issues")

My suggested "architecture" is to:

• Store all the metadata in the PostgreSQL Database
• Store the revisions of the Comments as text in a file in S3 named according to the comment id + revision number which we then reference in the DB row. This will keep the DB size low so it means we can run the service sustainably up to a few million repositories (assuming 1k issues per repo and an average of 10 revisions per issue) without any issues.

[nelsonic]

Discussed this morning in standup. Collectively Decided: PostgreSQL for MVP.