0

For creating partitioned table where we are expecting more than 800 million rows, I am trying to create partition based on hash value of natural key. Please find below more details on this:

CREATE TABLE IF NOT EXISTS role_player_p2
(
    role_player_id bigint NOT NULL,
    source_record_id bigint NOT NULL,
    role_player_key character varying(512) COLLATE pg_catalog."default" NOT NULL,
    legal_entity_id integer,
    subject_type_dnb_cd integer,
    senior_principal_indc boolean,
    role_player_type_dnb_cd integer NOT NULL,
    row_modified_tmst timestamp with time zone NOT NULL,
    row_created_tmst timestamp with time zone NOT NULL,
    row_created_identifier_txt character varying(256) COLLATE pg_catalog."default" NOT NULL,
    row_modified_identifier_txt character varying(256) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT role_player_p2_pk PRIMARY KEY (role_player_id)
) PARTITION BY HASH (role_player_id);

Here role_player_key(512) is natural key.

I have created 100 partitions of this table using similar SQL statements:

 CREATE TABLE midas_user.role_player_p2_1 PARTITION OF midas_user.role_player_p2
 FOR VALUES WITH (modulus 100, remainder 0)

After this, I moved data from current non partitioned table to partitioned table, using hashtextextended(role_player_key,0) as role_player_id, basically creating primary key using hash value of natural key.

insert into role_player_p2
SELECT hashtextextended(role_player_key,0) as role_player_id,
source_record_id, role_player_key, legal_entity_id, subject_type_dnb_cd, senior_principal_indc,
role_player_type_dnb_cd, row_modified_tmst, row_created_tmst, row_created_identifier_txt, 
row_modified_identifier_txt FROM role_player_p

With this change, now where condition for the queries to this table needs to be modified to include hashtextextended function:

select * from role_player_p2
where role_player_id = hashtextextended('Test:070122728:24965',0)

I am not sure if this is the right approach as using function in where clause can impact the performance of this query. Alternatively, I can also use role_player_key as partition key but I think data length of this key - varchar(256) will also have impact to index size and query.

Any suggestion on which of these is the better approach (considering hash value of natural key as partition key and let all queries use hash function OR use natural key of string length up to 256 characters as partition key and primary key.

5
  • The correct way would be INSERT INTO role_player_p2 SELECT * FROM role_player_p;. The hashing happens automatically, under the hood. May 15, 2023 at 7:30
  • Hi Laurenz, I tried that but it seems PostgreSQL does not allow partition on generated columns (in this case, role_player_id is generated from hashtextextended(role_player_key). Also, I want to check if there is any issue considering role_player_key - varchar(512) as partition and primary key over this hash value.
    – Siddharth
    May 15, 2023 at 11:22
  • There is no generated column anywhere. I don't understand. May 15, 2023 at 11:31
  • @LaurenzAlbe, role_player_id is not available in role_player_p table from where I am selecting data to insert into role_player_p2 partitioned table. Thats why using hash function in the Insert condition.
    – Siddharth
    May 15, 2023 at 12:24
  • Hi @LaurenzAlbe, appreciate your feedback to the question. If you think whatever I am doing does not makes sense, do let me know please.
    – Siddharth
    May 16, 2023 at 12:11

1 Answer 1

0

I think I begin to understand. You made a mistake by adding the column role_player_id to the partitioned table. Duplicating data that way is rarely a good idea. Really, the solution should be much simpler:

CREATE TABLE IF NOT EXISTS role_player_p2
(
    source_record_id bigint NOT NULL,
    role_player_key character varying(512) PRIMARY KEY,
    legal_entity_id integer,
    subject_type_dnb_cd integer,
    senior_principal_indc boolean,
    role_player_type_dnb_cd integer NOT NULL,
    row_modified_tmst timestamp with time zone NOT NULL,
    row_created_tmst timestamp with time zone NOT NULL,
    row_created_identifier_txt character varying(256) NOT NULL,
    row_modified_identifier_txt character varying(256) NOT NULL,
    CONSTRAINT role_player_p2_pk PRIMARY KEY (role_player_id)
) PARTITION BY HASH (role_player_key);

The partitioning key doesn't have to be a number, and you don't have to apply the hash function yourself. PostgreSQL will just take the strings and feed them into hashtextextended('...', 8816678312871386365) to get the hash value.

1
  • Thanks @Laurent Albe for your feedback. I thought the join query performance with bigint will be better than varchar(512). I will do the change you have suggested.
    – Siddharth
    May 16, 2023 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.