[inigyou]

That's the idea of sharding. If you read the pgdog docs, you'll notice you need to tell it which shard server to route your request to - it doesn't just magically work. It's still providing value by reusing connections, which are particularly expensive in postgres.

Because it's not magic, you do still have to know what's going on under the hood, e.g. no cross-shard transactions.

I'd see if my application can benefit from read replicas before doing sharding, because sharding is difficult (if you care about data consistency). With replicas, each replica does have a full copy of the data and you only write to the master - you have to decide which transactions are suitable for running against replicas, which can lag slightly behind realtime. E.g. reading data to build a webpage is probably safe to do from a replica - any read-modify-write is not.

[levkk]

fwiw, we support cross-shard transactions. They are not magic though, just good old 2pc and a bit of coordination.

[inigyou]

2pc is only safe if every part of the system has guaranteed uptime, which it never does. Assume that cross-shard transactions only work in the happy case and may result in inconsistent data otherwise.

They also reduce the benefit of sharding, possibly down to worse performance than a non-sharded DB.

[levkk]

For sure. They should be used for "metadata"-style tables only. High throuput writes should be direct-to-shard.