Working on apps that need data to be persisted, a decision on which storage technology needs to be adopted have to be made.
| Criteria | PostgreSQL | CosmosDb |
|---|---|---|
| Description | Azure Database for PostgreSQL is a relational DB. There is no free tier. | CosmosDb is a multi-purpose document database that can handle multiple APIs. There is one free tier database per Azure account with 1000 RUs and 25 Gb of storage. |
| Storage costs (costs to store the data based on 10 years data retention) |
Basic: $1/month ($0.10 GB/month). General Purpose: $1.15/month ($0.115 GB/month). |
Free, if the free tier can be used. Otherwise: 2.5 USD / month based on assumptions below |
| Operation costs (costs per utilization, querying) |
Basic (1 vCore, 2GiB memory) $24.82/month. General Purpose (2 vCore, 10GiB memory) $127.896/month |
Free, if the free tier can be used. Otherwise: 400RU/s, 23.36 USD/month based on assumptions below |
| Complexity (schema migrations, deployment automation) |
Requires you to create and maintain a schema. Local emulator (local install or Docker). | Does not require you to maintain a schema. We recommend using the SQL API (performance, support). Local emulator for all platforms Windows: local install. Other platforms: Docker. |
| Infrastructure (deployment through CI) |
Supported by Terraform. | Supported by Terraform. |
| Disaster recovery (Backup, SLA, Availability Zones, Multi region support) |
It supports data backup and recovery. More | Backup: Online backup SLA: 99.99% to 99.999% Availability Zones: In selected regions Multi region support: yes |
| Team/Developer experience (can run/test locally) |
Team has experience. Currently, no Docker experience in team. | No experience with CosmosDb in team currently, but NoSQL databases are known. Currently, no Docker experience in team. |
| Feature: multi results querying (get pending payments, get notified customers) |
Yes | Yes (need to pay attention to partition strategy) |
| Feature: create/update/get item by key (get a payment, get a notified customer) |
Yes | Yes |
| Feature: Power BI integration (Power BI can connect to this data source) |
PostreSQL Connector | CosmosDb Connector |
| Feature: Security (encryption transit/at rest, network isolation, ip firewall) |
Supports Firewall and Encryption | encryption (transit/at rest) Encrypted in transit, at rest and in backup network isolation: use virtual network service tags to achieve network isolation ip firewall: supports policy driven IP-based access controls for inbound firewall support |
| Feature: time to live (delete data after X amount of time) |
Not built-in. Can be implemented as a trigger | Provides the ability to delete items automatically from a container after a certain time period. Deletion of expired items is a background task that consumes left-over Request Units |