Not sure I would have opted for Bigtable here over sharded PostgreSQL because it unduly limits flexibility but within this constrained use case it works fine.
The big thing to remember which is covered by this article is your only real performant option to return multiple rows for BT is range scans so your keys should be setup to support this. If you need more than 1 index you are essentially shit out of luck - hence my preference to stay with PG as long as possible, even if that means sharding to multiple servers.
Good points - sharded Postgres is more likely to be a better choice in most instances. I wouldn't be surprised even if sharded Postgres would have worked well for us, but as you mentioned, for this constrained use case, Bigtable works fine.
What is the recommended way to manage and interact with a shared Postgres cluster? Is it a fully connected mesh where every node running business logic talks to every Postgres node?
We did consider sharding PostgreSQL and opted for something that would be fully managed for us to minimize our management overhead. Managing sharded databases can be complicated business and that wasn't something we wanted to take on if we could avoid it.
Comments
Not sure I would have opted for Bigtable here over sharded PostgreSQL because it unduly limits flexibility but within this constrained use case it works fine.
The big thing to remember which is covered by this article is your only real performant option to return multiple rows for BT is range scans so your keys should be setup to support this. If you need more than 1 index you are essentially shit out of luck - hence my preference to stay with PG as long as possible, even if that means sharding to multiple servers.
Good points - sharded Postgres is more likely to be a better choice in most instances. I wouldn't be surprised even if sharded Postgres would have worked well for us, but as you mentioned, for this constrained use case, Bigtable works fine.
What is the recommended way to manage and interact with a shared Postgres cluster? Is it a fully connected mesh where every node running business logic talks to every Postgres node?
Would you use chain replication or a hash ring?
We did consider sharding PostgreSQL and opted for something that would be fully managed for us to minimize our management overhead. Managing sharded databases can be complicated business and that wasn't something we wanted to take on if we could avoid it.
Planetscale does a good job at that, but for MySQL