EDW and Data marts

[SeihaHoy]

I am planning to integrate an EDW and data marts in cloudberry. In this case should I create two databases for each because I am concerning about the segmentations. If we create segmentations on a database, will it transfer the same settings to the other database. Please explain the segmentations configurations when there are multiple databases. Thanks in advance!

[2shar-p]

By "segmentation" If you mean distribution of data across all the segments, then the short answer is yes.
All tables will be distributed by a key, which is either a single column or group of columns. You could also choose to distribute randomly, and that will still distribute data for each table across all segments. This applies to each table in the Cloudberry deployment - each database, each schema, each table, each partition... all that has data in it will be distributed across all segments and each segment will have it's own portion of data. Now, one could choose to make tables replicated instead of distributed, in which case, the same copy of data will be on each and every segment, still data will be on every segment of the Cloudberry's "shared nothing MPP cluster" -- happy to help chat more; I am on Cloudberry Slack, mostly available during US hours.

[SeihaHoy]

For example if a segment node has 4 segment instances and 4 mirror instances, then it will apply to all the databases created?

[2shar-p]

The primary instances and mirror instances will also apply the same way as data distribution - it works across all objects/relations managed by the Cloudberry database.

And to be specific about primary:mirror, the mirror as the name suggests just mirrors the data using WAL replication and is quietly standing by until the primary instance fails. It will not be queryable until primary segment becomes unavailable and fault tolerant system marks the primary down and make the mirror available for use.

e.g. IF you have 4 segments and a table with 40 rows, ideally you would have evenly distributed data making it 10 rows of data in each of the 4 segments... let's call it p1 thru p4. Now you want to make sure the mirror of these m1 thru m4 are on different physical nodes so if/when there is an issue with underlying infrastructure/hardware, the mirrors running on stable servers will become active and the Cloudberry database stays available without downtime.

Hope this helps.

[SeihaHoy]

Thanks a lot for your help!