Skip to main content
Add 'database-design' tag; correct wording
Source Link
Michael Green
  • 25.1k
  • 13
  • 53
  • 98

I have a main database that runruns on a server with good resources and let, let's say, a dozens ofdozen satellites that replicate data. Satellites have less resources than main server.

In that database there is a BIG table, that I want partitioned in rangeranges because in fact each satellite carecares about his own specific range.

PARTITION BY RANGE( ID ) (
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p3 VALUES LESS THAN (4000000),
    PARTITION p4 VALUES LESS THAN ...
);

LetLet's say satellite 1 carecares about table IDIDs that start with 1, satellite 2 carecares about IDIDs that start with 2 and so on. (all IDIDs are numbernumbered in millionmillions).

Is it possible to replicate on each satellite only data that are relevant to the satellite.? I mean I don't want p2 and p3 on satellite 1, only p1 is relevant for satellite 1. But I don't know how to say to the replication engine that I want only such a part of the data.

isIs it only possible to replicate only one partition of the partition scheme  ?

thank you for your indications or comments.

I have a main database that run on a server with good resources and let say a dozens of satellites that replicate data. Satellites have less resources than main server.

In that database there is a BIG table, that I want partitioned in range because in fact each satellite care about his own specific range.

PARTITION BY RANGE( ID ) (
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p3 VALUES LESS THAN (4000000),
    PARTITION p4 VALUES LESS THAN ...
);

Let say satellite 1 care about table ID that start with 1, satellite 2 care about ID that start with 2 and so on. (all ID are number in million).

Is it possible to replicate on each satellite only data that are relevant to the satellite. I mean I don't want p2 and p3 on satellite 1, only p1 is relevant for satellite 1. But I don't know how to say to replication engine that I want only such a part of the data.

is it only possible to replicate only one partition of the partition scheme  ?

thank you for your indications or comments.

I have a main database that runs on a server with good resources and, let's say, a dozen satellites that replicate data. Satellites have less resources than main server.

In that database there is a BIG table, that I want partitioned in ranges because in fact each satellite cares about his own specific range.

PARTITION BY RANGE( ID ) (
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p3 VALUES LESS THAN (4000000),
    PARTITION p4 VALUES LESS THAN ...
);

Let's say satellite 1 cares about table IDs that start with 1, satellite 2 cares about IDs that start with 2 and so on. (all IDs are numbered in millions).

Is it possible to replicate on each satellite only data that are relevant to the satellite? I mean I don't want p2 and p3 on satellite 1, only p1 is relevant for satellite 1. But I don't know how to say to the replication engine that I want only such a part of the data.

Is it only possible to replicate only one partition of the partition scheme?

Source Link
dominix
  • 123
  • 6

database design question with Partition and Replication

I have a main database that run on a server with good resources and let say a dozens of satellites that replicate data. Satellites have less resources than main server.

In that database there is a BIG table, that I want partitioned in range because in fact each satellite care about his own specific range.

PARTITION BY RANGE( ID ) (
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p3 VALUES LESS THAN (4000000),
    PARTITION p4 VALUES LESS THAN ...
);

Let say satellite 1 care about table ID that start with 1, satellite 2 care about ID that start with 2 and so on. (all ID are number in million).

Is it possible to replicate on each satellite only data that are relevant to the satellite. I mean I don't want p2 and p3 on satellite 1, only p1 is relevant for satellite 1. But I don't know how to say to replication engine that I want only such a part of the data.

is it only possible to replicate only one partition of the partition scheme ?

thank you for your indications or comments.