Overview
1 Relational Database - PostgreSQL
PostgreSQL also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.
1.1 Self hosted PostgreSQL
Assumptions
- Data integrity is absolutely paramount (i.e., for financial applications, defense and security, and private health information)
- Highly structured data
PostgreSQL can be deployed in these 5 modes
Depending on the project lifecycle and scale, the deployments could be of the following types
- dSmall - A small development server
- dMedium - A medium development server
- pSmall - A small production server ideal to small projects.
- pMedium - A medium production server ideal for a millions of rows
- pLarge - Large production server with scale spanning to billions of rows and hundreds of tables.
Use Cases
- Development with local database. dSmall
- Development with database on the server with database size less than 5GB or table rows less than a million. dSmall
- Development with database on the server with database size > 5GB or table rows more than a million. dMedium
- Production database with single microservice depending on it. Size < 5GB and max rows < million. pSmall
- Production database with dashboards and graphs attached to it. Connection count < 50. pSmall
- Production database which needs to serve APIs through microservices and there is no limit to the amount of connections to it and has dashboards and graphs attached to it. pMedium
- Production datababase with all the things on point 6 data size upto 2TB. pMedium
- Production database with all the things on point 6 and data size > 2TB. pLarge
Comparison of performance
Indicator | dSmall | dMedium | pSmall | pMedium | pLarge |
---|---|---|---|---|---|
Max Database Size (GB) | 1 | 5 | 2000 | 2000 | No limits |
Read Only Replica | ✗ | ✗ | ✓ | ✓ | ✓ |
Connection Pooler | ✓ | ✓ | ✓ | ✓ | ✓ |
Production Ready | ✗ | ✗ | ✓ | ✓ | ✓ |
Cost per GB | x | 2x | 4x | 7x | 7x |
Sharding Capability | ✗ | ✗ | ✗ | ✗ | ✓ |
Archiving and PITR | ✗ | ✗ | ✗ | ✓ | ✓ |
Backups | ✗ | ✗ | ✓ | ✓ | ✓ |
Disaster Recovery | ✗ | ✗ | ✗ | ✓ | ✓ |
Setup Difficulty | x | x | 3x | 5x | 10x |
Maintenance Cost (Dev hours) | - | - | x | 2x | 3x |
1.2 Managed PostgreSQL
Assumptions
- Data integrity is absolutely paramount (i.e., for financial applications, defense and security, and private health information)
- Highly structured data
PostgreSQL can be deployed in these modes
Use Cases
- Development with local database. A
- Development with database on the server with database size less than 5GB or table rows less than a million. A
- Development with database on the server with database size > 5GB or table rows more than a million. B
- Production database with single microservice depending on it. Size < 5GB and max rows < million. B
- Production database with dashboards and graphs attached to it. Connection count < 50. C
- Production database which needs to serve APIs through microservices and there is no limit to the amount of connections to it and has dashboards and graphs attached to it. D
- Production datababase with all the things on point 6 data size upto 2TB. E
- Production database with all the things on point 6 and data size > 2TB. E
Comparison of performance
Indicator | A | B | C | D | E |
---|---|---|---|---|---|
Max Database Size (GB) | - | - | - | - | - |
Read Only Replica | - | - | - | - | - |
Connection Pooler | - | - | - | - | - |
Production Ready | - | - | - | - | - |
Cost per GB | - | - | - | - | - |
Sharding Capability | - | - | - | - | - |
Archiving and PITR | - | - | - | - | - |
Backups | - | - | - | - | - |
Disaster Recovery | - | - | - | - | - |
Setup Difficulty | - | - | - | - | - |
Maintenance Cost (Dev hours) | - | - | - | - | - |