4 ms·

Correct me if my knowledge is outdated, Vitess deployment comes at a cost: 1. You can't have single database view of all shards 2. No distributed ACID 3. No d

Correct me if my knowledge is outdated, 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.

harshitgangal 4y ago
Disclaimer: I am a Vitess Maintainer. 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.
- truth_seeker 4y ago
  Thanks for info.
- TedShiller 4y ago
mattlord 4y ago
> 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. 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. :-)
- truth_seeker 4y ago
- paulryanrogers 4y ago
  There is also Aurora Postgres, though I've heard it still suffers from vacuum issues for some workloads. 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.
gen220 4y ago
Postgres FDW [1] seems to me to be a crazy cool (i.e. unexpected and good) feature of Postgres, but I haven't seen much chatter about it on HN in the rdbms stories. Has anyone here used it? What is/was your experience like? [1]: https://www.postgresql.org/docs/current/postgres-fdw.html https://www.postgresql.org/docs/current/postgres-fdw.html
- sroussey 4y ago
  That’s a great feature but not al all comparable for a deployment. The closest thing for PostgreSQL would be Citus, I imagine.
aeyes 4y ago
FDW is not nearly as usable as you think. Joins of even 2 or 3 remote tables with more than a few hundred rows in the join are enough to make simple queries take minutes to return even if you tune all the settings and have statistics of remote tables. Also, how do you reshard without downtime?
- truth_seeker 4y ago
  (Plz excuse the brevity. I am using my mobile phone to reply) Have you read this page carefully especially the section "remote execution option" https://www.postgresql.org/docs/current/postgres-fdw.html 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-enhancement-in-postgresql-14/ 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.
  - aeyes 4y ago
    Yes, I read it and that is what I meant with "even if you tune all the settings and have statistics of remote tables". I have tried all the options, if you have any remote join which is more than a few rows you'll have a bad time. 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.

harshitgangal 4y ago

Disclaimer: I am a Vitess Maintainer. 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.

truth_seeker 4y ago
Thanks for info.
TedShiller 4y ago

mattlord 4y ago

> 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. 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. :-)

truth_seeker 4y ago
paulryanrogers 4y ago
There is also Aurora Postgres, though I've heard it still suffers from vacuum issues for some workloads. 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.

gen220 4y ago

Postgres FDW [1] seems to me to be a crazy cool (i.e. unexpected and good) feature of Postgres, but I haven't seen much chatter about it on HN in the rdbms stories. Has anyone here used it? What is/was your experience like? [1]: https://www.postgresql.org/docs/current/postgres-fdw.html https://www.postgresql.org/docs/current/postgres-fdw.html

sroussey 4y ago
That’s a great feature but not al all comparable for a deployment. The closest thing for PostgreSQL would be Citus, I imagine.

aeyes 4y ago

FDW is not nearly as usable as you think. Joins of even 2 or 3 remote tables with more than a few hundred rows in the join are enough to make simple queries take minutes to return even if you tune all the settings and have statistics of remote tables. Also, how do you reshard without downtime?

truth_seeker 4y ago
(Plz excuse the brevity. I am using my mobile phone to reply) Have you read this page carefully especially the section "remote execution option" https://www.postgresql.org/docs/current/postgres-fdw.html 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-enhancement-in-postgresql-14/ 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.
- aeyes 4y ago
  Yes, I read it and that is what I meant with "even if you tune all the settings and have statistics of remote tables". I have tried all the options, if you have any remote join which is more than a few rows you'll have a bad time. 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.