Hey @mrbabbage! Retool customer here! Great service :)
Non distributed RDBMS is a great (yet underrated) choice. Thank you for the good writeup.
I was thinking you could have a much less delicate migration experience next time (some years from now). So you can go for a quicker parallel "dump and restore" migration.
For example:
- Client side sharding in the application layer: you could shard your customers' data across N smaller DB instances (consistent hashing on customer ID)
- Moving the append-only data somewhere else than postgres prior to the upgrade. You don't need RDBMS capabilities for that stuff anyway. Look at Elasticsearch, Clickhouse, or any DB oriented to time series data.
The second bullet point is underway! Getting audit events out of the main database will be a major headache saver.
The first bullet point is on our radar for the near term. We have a very natural shard key in our schema (the customer ID), with AFAIK no relationships across that shard key. And once we start horizontally sharding, we can do cool things like putting your data in a shard geographically close to you, which will greatly increase app performance for our non US customers. Exciting stuff coming down the pike!
Non distributed RDBMS is a great (yet underrated) choice. Thank you for the good writeup.
I was thinking you could have a much less delicate migration experience next time (some years from now). So you can go for a quicker parallel "dump and restore" migration.
For example:
- Client side sharding in the application layer: you could shard your customers' data across N smaller DB instances (consistent hashing on customer ID)
- Moving the append-only data somewhere else than postgres prior to the upgrade. You don't need RDBMS capabilities for that stuff anyway. Look at Elasticsearch, Clickhouse, or any DB oriented to time series data.
WDYT?