Please find my answer inline
1. You can't have single database view of all shards
- Vitess does query planner and routing for the application so application does not have to know about shards. Yes, Vitess gives a view of a single database.
2. No distributed ACID
- This has been talked a lot feature and it is in our roadmap to provide. Currently, we have a twoPC implementation which we do not recommend for production use. Right now the users of vitess either choose their sharding key in such a way that all the queries in a transaction goes to single shard or the application can handle best effort multi shard transaction.
3. No distributed JOINs
- Vitess does support distributed joins, aggregation, subqueries and other complex queries. Vitess thrive to be 100% Mysql compatible. If anyone encouter such query, a github issue helps in knowing and prioritizing.
I wish you all the best in operating that at scale and providing a reliable general purpose PostgreSQL DBaaS (even if only internally) around it. :-) PostgreSQL is great, not going to argue otherwise -- every solution has tradeoffs and if the tradeoffs of one solution fit your usage best then go with it. If you need to scale out and manage a large cluster of PostgreSQL instances as if it was a single logical database then I'd recommend something that's in a similar space as Vitess and PlanetScale -- Citus and Citus on Azure respectively. At the hand waving level it's always easy to "do this, and that", but it's a whole other matter when you develop a general purpose production-grade product that anyone can use and operate at virtually infinite scale. :-)
In my limited experience DB scaling usually necessitates knowing some specific quirks of whatever implementation is chosen. Otherwise their side effects or sharp edges will cause problems.
Has anyone here used it? What is/was your experience like?
[1]: https://www.postgresql.org/docs/current/postgres-fdw.html
The closest thing for PostgreSQL would be Citus, I imagine.
Also, how do you reshard without downtime?
Have you read this page carefully especially the section "remote execution option"
https://www.postgresql.org/docs/current/postgres-fdw.html
If not give it a try.
Also this one might help too.
https://www.percona.com/blog/2021/06/02/postgres_fdw-enhance...
Now, about re-sharding:
I know this is not as care free as in NewSQL dbs like CockroachDB but its not that difficult either.
Choosing a shard key ultimately comes down to breaking down your ER diagram and inspecting the incoming write flow to certain tables which are bound to grow.
In any of the cases you don't have to let your user suffer from downtime. At least I have not. Even if you chose wrong shard key, You can gracefully transfer or re-distribute the data records by creating new tables and renaming then with old ones. The window of appearant downtime here is few milliseconds. This is not something which will happen frequently.
I'm not even talking about changing the shard key, even growing the cluster isn't easily done and much less with "millisecond latency". Unless you use Citus which technically isn't a pure PostgreSQL solution.
Vitess deployment comes at a cost:
1. You can't have single database view of all shards 2. No distributed ACID 3. No distributed JOINs
If I were to do it in PostgreSQL 14 natively without any other third party plugins, I can get help of Table Partitioning (say root server) + Tables on Foreign Servers (say leaf servers) through FDW. Parallel scans and JOINs are allowed. Also, PostgreSQL 15 (in beta now) will allow parallel commits to foreign server tables through FDW.