Hello Matej,
I found some interesting implementation of postgres_XL at TenCent(WeChat) and Javelin. You can find video capture of conferences of IT people from these companies. Moreover, I attended to PgConf.eu at Warsaw in October, and The ESA (European Space Agency) made a lightning talk on their Gaïa project using a 8-datanode cluster.
I agree with you about the SPOF for the master on the citusdata architecture. Yet, implementation is very easy, and it's an extension in Pg10! But I had to fire many foreign into my data model to be able to install my tables into citusdata.
2 years ago, I was looking for a partitioning extension, pg_partman was mature, whereas pg_pathman was in version 0.4 and many issues in their github were written in Cyrillic, and I'm French ;-)... So I had a closer look at pg_partman.
I'm using pg_partman in production now.
2018-01-29 16:49 GMT+01:00 Matej <gmatej@xxxxxxxxx>:
Hi Thomas.Thanks.Also looked at those solutions:- PGXL Am a ltille afraid we would be the test dummies. Did not hear of many production installs.- Citus seems heavily limited scalability vise, because of the master node design.Regarding partitioning we are considering ourselves pg_pathman. Was hoping on PG10 partitioning but currently not really many changes performance vise.Overall we are still considering manual APP/sharding as this seems to be the most scalable approach which least added latency. The builtin solutions seems to introduce extra lag and I am afraid of what to do when something goes wrong. then it's not a black box anymore and you have to study the details.For node loss we plan a master -slave setup, and there will not be so many cross shard queries.BRMatej2018-01-29 16:15 GMT+01:00 Thomas Boussekey <thomas.boussekey@xxxxxxxxx>:Hello,Facing the same situation, I'm considering 3 solutions:- Sharding with postgres_xl (waiting for a Pg10 release)- Sharding with citusdata (Release 7.2, compatible with Pg10 and pg_partman, seems interesting)- Partitioning with PG 10 native partitioning or pg_partmanWith colleagues, we have tested the 3 scenarios.Sharding looks interesting, but you have to apprehend its behaviour in case of node loss, or cross-node queries.Thomas2018-01-29 15:44 GMT+01:00 Melvin Davidson <melvin6925@xxxxxxxxx>:On Mon, Jan 29, 2018 at 9:34 AM, Matej <gmatej@xxxxxxxxx> wrote:Hi Everyone.We are looking at a rather large fin-tech installation. But as scalability requirements are high we look at sharding of-course.I have looked at many sources for Postgresql sharding, but we are a little confused as to shared with schema or databases or both.So far our understanding:SCHEMA.PROS:- seems native to PG- backup seems easier- connection pooling seems easier, as you can use same connection between shard.CONS:- schema changes seems litlle more complicated- heard of backup and maintenance problems- also some caching problems.DATABASE:PROS:- schema changes litlle easier- backup and administration seems more robustCONS:- heard of vacuum problems- connection pooling is hard, as 100 shards would mean 100 poolsSo what is actually the right approach? If anyone could shed some light on my issue.ThanksYou might also want to consider GridSQL. IIRC it was originally developed by EnterpriseDB. I saw a demo of it a few years ago and it was quite impressive,but I've had no interaction with it since, so you will have to judge for yourself.
--Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
