Working with data distribution policies in Yandex MPP Analytics for PostgreSQL
In Yandex MPP Analytics for PostgreSQL, all database tables are distributed row by row across the cluster's segment hosts. Each table is assigned a data distribution policy that defines the specific rule used to allocate rows to segments. For more on distribution policies, see Data distribution in Yandex MPP Analytics for PostgreSQL.
When working with data distribution policies, you can:
- Define a distribution policy for a new table.
- Modify the distribution policy and key for an existing table.
- Manually redistribute data across segments.
Defining a distribution policy
To define a distribution policy, run this command:
-
For hash distribution:
CREATE TABLE <table_name> ( <column_descriptions> ) DISTRIBUTED BY (<distribution_key>);Where
<distribution_key>is a single column or a comma-separated list of columns. -
For random distribution:
CREATE TABLE <table_name> ( <column_descriptions> ) DISTRIBUTED RANDOMLY; -
For replicated distribution:
CREATE TABLE <table_name> ( <column_descriptions> ) DISTRIBUTED REPLICATED;
Modifying a distribution policy
Warning
If a table has a primary key or a unique constraint, you cannot change its distribution policy from hash distribution to random distribution.
To modify a distribution policy or key, run this command:
-
Changing to hash distribution or selecting a new distribution key:
ALTER TABLE <table_name> SET DISTRIBUTED BY (<distribution_key>);Where
<distribution_key>is a single column or a comma-separated list of columns. -
Changing to random distribution:
ALTER TABLE <table_name> SET DISTRIBUTED RANDOMLY; -
Changing to replicated distribution:
ALTER TABLE <table_name> SET DISTRIBUTED REPLICATED;
When you modify a policy, data is automatically redistributed across segments, except in the following cases:
- When changing the policy from hash distribution to random distribution.
- When the
DISTRIBUTEDclause specifies the current distribution policy settings.
If needed, you can manually redistribute data.
Redistributing data manually
To redistribute data, e.g., when changing the policy from hash distribution to random distribution, run this command:
ALTER TABLE <table_name>
SET WITH (REORGANIZE=TRUE);
When expanding a Yandex MPP Analytics for PostgreSQL cluster, data is redistributed automatically, so you do not need to run this command manually.