Investigate Database Locality
Currently, the plan is to use Postgres for the database. This is a very sound database choice. However, there are some considerations that might warrant using something else instead.
Ideally, the database setup for DigitalShed is somewhat federated. There needs to be some globally consistent idea of user accounts and sheds. But the data of the individual sheds could be split up into different regions for low latency. This would allow sub-100ms requests even for people in Australia or Asia that might otherwise experience slower usage.
In summary, this means that we need basically two database layers:
- One globally consistent database (using something like Raft or Paxos as a consensus protocol) to store user accounts and list of sheds
- One local database, storing the shed data. The global store specifies which region a shed is in, and the local database acts as the master for these shed's data.
A user furthermore needs to have the ability to migrate a shed from one location to another (although, this might not be necessary).
One approach would be to use CockroachDB for this, as it offers row-level regionality. Another approach would be to use multiple Postgres clusters that are hooked up to each other using external database connectors.
Approaches:
- Multiple Postgres
- CochroachDB
- Neon