[ADR] Core APIs database technology

[StevenMalaihollo]

There are 2 types or databases that are considered:

1. Relational
2. Document
   • MongoDB
   • Firestore
   • CosmosDB
   • ArangoDB

Considerations

1. Costs
2. Security
3. Governance (offboarding of team members)
4. Scaling
5. Community
6. Learning curve
7. Redundancy
8. Development speed (migrations, containerized)
9. Data integrity
10. Performance
11. Querying features

Database Comparison

Relational

Pros

• Guaranteed data integrity
• Easy to learn and widely used
• Large community
• Full sorting and filter capabilities
• Likely to be more performant for join operations

Cons

• Azure SQL Database is the only mainstream serverless solution
• Time consuming migrations

Document

Pros

• Existing experience with MongoDB
• High development speed
• Most support serverless scaling
• Learning curve is not steep
• Likely to be more performant for simple read/write operations

Cons

• Easily lose data integrity
• Tooling is not standardized (querying, emulating)

Summary

After lining up the considerations for Relational and Document DBs we come to our final candidates:

1. Azure SQL Database serverless
2. MongoDB (Serverless is in preview and not governed)
3. CosmosDB with Mongo API (Not full support of mongo API, weak data integrity)
4. Firestore (Weak data integrity, limited querying)
5. ArangoDB (Not serverless, small community)
6. CockroachDB, PlanetScale (Not mainstream, lacks governance)
7. Google SQL

Decision

1. For the source of truth APIs we're going to use Google Cloud SQL
   1. Mainstream solution
   2. Forces good data integrity
2. For query API we're suggest going for a document database with strong search and querying capabilities
   1. MongoDB looks like a promising option, but we need more research to be sure
   2. Other options include ArangoDB, Elastic Search

Consequences

• Slower development, especially when migration is needed
• We need to test scaling performance
• Team needs to get familiar with SQL

Alternatives

Document databases where not chosen for the source of truth APIs because;

• they risk losing data integrity
• they often lack serverless capabilities
• sometimes lack community

CockroachDB / PlanetScale

• Are not mainstream providers
• No big name governance system

[github-actions]

Remember that ADRs are publicly available hence do not include any confidential information in the issue description!
To read more about ADR please refer to documentation.

[piotrczyz]

I'm quite surprise over CosmoDb with Mongo API https://docs.microsoft.com/en-us/azure/cosmos-db/mongodb/mongodb-introduction. Maybe you can consider that for you APIs.

Why wouldn't you use Document database as a source of truth? @StevenMalaihollo

[JakubC-projects]

I'm quite surprise over CosmoDb with Mongo API https://docs.microsoft.com/en-us/azure/cosmos-db/mongodb/mongodb-introduction. Maybe you can consider that for you APIs.

Why wouldn't you use Document database as a source of truth? @StevenMalaihollo

CosmosDB wasn't chosen because it doesn't fully support Mongo API, notably lacks ability to define a collection schema. We feel that, for those basic APIs, data integrity is a top priority, so missing that is a big negative. However if it turns out that the serverless sql doesn't scale well enough, we are ready to switch to a Document database

[andreasgangso]

Use postgres on azure

[andreasgangso]

Why not google sql?

[piotrczyz]

Can we do the anaysis here? #7

[andreasgangso]

Likely to be more performant for simple read/write operations

I think this is false

https://www.enterprisedb.com/news/new-benchmarks-show-postgres-dominating-mongodb-varied-workloads

PostgreSQL 11 was found to be faster than MongoDB 4.0 in almost every benchmark.
Throughput was higher, ranging from dozens of percent points up to one and even two
orders of magnitude on some benchmarks. Latency, when measured by the benchmark,
was also lower on PostgreSQL.

https://info.enterprisedb.com/rs/069-ALB-339/images/PostgreSQL_MongoDB_Benchmark-WhitepaperFinal.pdf

[StevenMalaihollo]

Likely to be more performant for simple read/write operations

I think this is false

https://www.enterprisedb.com/news/new-benchmarks-show-postgres-dominating-mongodb-varied-workloads

PostgreSQL 11 was found to be faster than MongoDB 4.0 in almost every benchmark.
Throughput was higher, ranging from dozens of percent points up to one and even two
orders of magnitude on some benchmarks. Latency, when measured by the benchmark,
was also lower on PostgreSQL.

https://info.enterprisedb.com/rs/069-ALB-339/images/PostgreSQL_MongoDB_Benchmark-WhitepaperFinal.pdf

Interesting, our source for this was this whitepaper, it compares MySQL instead of PostgreSQL with MongoDB:

link

[JakubC-projects]

Why not google sql?

After looking at Azure Serverless SQL pricing model, it isn't really that good, and the SQL flavor is Microsoft specific.
We would like to use a mainstream SQL flavor (like Postgres or MySQL).
Therefore we're going to try GCP's SQL offer (probably postgres flavor).

[u12206050]

I've been using GCP Sql mysql flavor and am very satisfied with it. It does cost bit for an instance, but I can run multiple databases on the same instance. In production I've seen and worked on mysql databases that are at least 10 times BCC's size in terms of traffic and customers, so as a technology I think it can work very well as the source of truth. Other databases can be synced/indexed from the source of truth for improved performance. Eg. State&transactions stores: document database (firestore, mongodb) Search: elasticseaech or dedicated search service Cache: redis database Audit and Logging: elastic or big query Relations heavy queries: neo4j (graph database)

…

On Fri, 1 Apr 2022, 11:26 Jakub Czyż, ***@***.***> wrote: Why not google sql? After looking at Azure Serverless SQL pricing model, it isn't really that good, and the SQL flavor is Microsoft specific. We would like to use a mainstream SQL flavor (like Postgres or MySQL). Therefore we're going to try GCP's SQL offer (probably postgres flavor). — Reply to this email directly, view it on GitHub <#39 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABSVMWUORGEAYUCFJIVAVADVC26NLANCNFSM5SFPTPJQ> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>