Partitioning and Replicating dynamically

Hi, I’m very new to MemSQL and primarily have a RDBMS background. I’m running MemSQL with high_availability set to false, and would like to see performance based on different partitioning and replication schemes.

However, in trying to dynamically replicate and partition, I cannot find any documentation - as most replication documentations are about replicating to entire clusters (and not nodes), and most partitioning documents indicate to setting partitions during schema definition.

Could someone please advise on how it would be possible to do something like this, which can be done in PostgreSQL:

Replication of a table


Partitioning of a table



You can create a table that is replicated on each cluster node by using a reference table.

You can create a table that is split across leaf nodes via hash partitioning by using a shard key.

We support disaster recovery (to protect against losing a whole cluster for any reason) using our REPLICATE DATABASE feature.

We support high availability within a single cluster using redundancy-2.

1 Like

Hi Hanson,

Thank you for your reply as the resources were indeed very helpful. However, I would still like to perform these tasks dynamically instead of during DDL. What would be the best practice to e.g. change the shard key for a relation?

We don’t currently support directly changing the shard key of a table, though we’re considering it for the future. The way to do it now is as follows. Suppose you have table t, sharded on column x, but you want it to be sharded on column y. You would do this:

create table t2(...., shard(y)); -- t2 otherwise has same schema as t
insert into t2 select * from t;
-- check t2 to make sure it has what you want in it
drop table t;
alter table t2 rename t;

This is an offline process, but can be pretty fast even for large tables.

1 Like

Thanks Hanson! Much appreciated.

Hi Hanson,
In addition to dropping and renaming the table, would I have to recreate the indexes on the tables as well?

Maybe. The KEY(…) definitions in your create table statement may already have created the indexes you need, depending on the table type and kind of key. If not, you’d need to run CREATE INDEX statements too, as appropriate.

1 Like