Обсуждение: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS

Hi All,

I’ve a table ermabet having 80mil records. My business need is to check the bet placed by a player(users), which queries the table, and enable streaming for the player only if he has placed his bet in the last 24 hrs( granular to the millisecond).

I can’t create a partition by range(date) and the query is taking more than 5 mins, whereas we’re expecting the query to run less than 300ms.

My Desired Partitions:

CREATE TABLE bets.ermabet_partition (

    betid varchar(50) NOT NULL,

    brandid varchar(50) NOT NULL,

    channelid varchar(50) NULL,

    playerid varchar(50) NULL,

    bet jsonb NULL,

    posid varchar(50) NULL,

    agentid varchar(50) NULL,

    bettype varchar(50) NULL,

    betclass varchar(20) NULL,

    betstatus varchar(15) NULL,

    placedon timestamptz NULL,

    settledon timestamptz NULL,

    unitcount int4 NULL,

    unitstake float8 NULL,

    totalstake float8 NULL,

    potentialreturn float8 NULL,

    legcount int4 NULL,

    openlegcount int4 NULL,

    selectionids _text NULL,

    marketids _text NULL,

    eventids _text NULL,

    competitionids _text NULL,

    sportids _text NULL,

    createdon timestamptz NULL,

    marketselectionids _text NULL,

    originalreturn float8 NULL,

    changelog _jsonb NULL,

    tags jsonb NULL,

    CONSTRAINT pk_ermabet PRIMARY KEY (betid, brandid)

) PARTITION BY RANGE (placedon);

for which I get the error :
SQL Error [0A000]: ERROR: unique constraint on partitioned table must include all partitioning columns

  Detail: PRIMARY KEY constraint on table "ermabet_part_test" lacks column "placedon" which is part of the partition key.

Error position:



error less partition creation is :

-- Step 1: Create the parent table

CREATE TABLE bets.ermabet (

    betid varchar(50) NOT NULL,

    brandid varchar(50) NOT NULL,

    channelid varchar(50) NULL,

    playerid varchar(50) NULL,

    bet jsonb NULL,

    posid varchar(50) NULL,

    agentid varchar(50) NULL,

    bettype varchar(50) NULL,

    betclass varchar(20) NULL,

    betstatus varchar(15) NULL,

    placedon timestamptz NULL,

    settledon timestamptz NULL,

    unitcount int4 NULL,

    unitstake float8 NULL,

    totalstake float8 NULL,

    potentialreturn float8 NULL,

    legcount int4 NULL,

    openlegcount int4 NULL,

    selectionids _text NULL,

    marketids _text NULL,

    eventids _text NULL,

    competitionids _text NULL,

    sportids _text NULL,

    createdon timestamptz NULL,

    marketselectionids _text NULL,

    originalreturn float8 NULL,

    changelog _jsonb NULL,

    tags jsonb NULL,

    CONSTRAINT pk_ermabet PRIMARY KEY (betid, brandid, placedon)

) PARTITION BY RANGE (placedon);

-- Step 2: Create child tables for each partition

CREATE TABLE bets.ermabet_2022 PARTITION OF bets.ermabet

    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

-- Add more child tables for other date ranges as needed

-- Step 3: Create indexes on child tables

CREATE INDEX ermabet_bstatus_idx ON bets.ermabet_2022 USING btree (betstatus);

CREATE INDEX ermabet_mktids_idx ON bets.ermabet_2022 USING gin (marketids);

CREATE INDEX ermabet_tgs_idx ON bets.ermabet_2022 USING gin (tags);

CREATE INDEX idx_ermabet_stdon ON bets.ermabet_2022 USING btree (settledon);

CREATE INDEX idx_ermabetbet_pcdon ON bets.ermabet_2022 USING btree (placedon);

-- Repeat the index creation for other child tables as needed

But I don’t want PLACEDON column part of the primary key.

Any suggestions?

Regards,

Phani Pratz

PPBET-DBA

Furthermore, the explain plan(without partition) looks like this :

Finalize Aggregate  (cost=20494220.72..20494220.75 rows=1 width=8)

  ->  Gather  (cost=20494220.67..20494220.70 rows=6 width=8)

        Workers Planned: 6

        ->  Partial Aggregate  (cost=20494210.67..20494210.70 rows=1 width=8)

              ->  Parallel Bitmap Heap Scan on ermabet e  (cost=420824.55..20494210.60 rows=26 width=0)

                    Recheck Cond: ((placedon >= '2023-03-28 08:20:23.927+05:30'::timestamp with time zone) AND (placedon <= '2023-03-29 08:19:23.927+05:30'::timestamp with time zone))

                    Filter: (((brandid)::text = 'pp'::text) AND ((playerid)::text = 'Periša_80'::text))

                    ->  Bitmap Index Scan on idx_ermabetbet_pcdon  (cost=0.00..420824.51 rows=27478794 width=0)

                          Index Cond: ((placedon >= '2023-03-28 08:20:23.927+05:30'::timestamp with time zone) AND (placedon <= '2023-03-29 08:19:23.927+05:30'::timestamp with time zone))

JIT:

  Functions: 9

  Options: Inlining true, Optimization true, Expressions true, Deforming true

Hi All,

I’ve a table ermabet having 80mil records. My business need is to check the bet placed by a player(users), which queries the table, and enable streaming for the player only if he has placed his bet in the last 24 hrs( granular to the millisecond).

I can’t create a partition by range(date) and the query is taking more than 5 mins, whereas we’re expecting the query to run less than 300ms.

My Desired Partitions:

CREATE TABLE bets.ermabet_partition (

    betid varchar(50) NOT NULL,

    brandid varchar(50) NOT NULL,

    channelid varchar(50) NULL,

    playerid varchar(50) NULL,

    bet jsonb NULL,

    posid varchar(50) NULL,

    agentid varchar(50) NULL,

    bettype varchar(50) NULL,

    betclass varchar(20) NULL,

    betstatus varchar(15) NULL,

    placedon timestamptz NULL,

    settledon timestamptz NULL,

    unitcount int4 NULL,

    unitstake float8 NULL,

    totalstake float8 NULL,

    potentialreturn float8 NULL,

    legcount int4 NULL,

    openlegcount int4 NULL,

    selectionids _text NULL,

    marketids _text NULL,

    eventids _text NULL,

    competitionids _text NULL,

    sportids _text NULL,

    createdon timestamptz NULL,

    marketselectionids _text NULL,

    originalreturn float8 NULL,

    changelog _jsonb NULL,

    tags jsonb NULL,

    CONSTRAINT pk_ermabet PRIMARY KEY (betid, brandid)

) PARTITION BY RANGE (placedon);

for which I get the error :
SQL Error [0A000]: ERROR: unique constraint on partitioned table must include all partitioning columns

  Detail: PRIMARY KEY constraint on table "ermabet_part_test" lacks column "placedon" which is part of the partition key.

Error position:



error less partition creation is :

-- Step 1: Create the parent table

CREATE TABLE bets.ermabet (

    betid varchar(50) NOT NULL,

    brandid varchar(50) NOT NULL,

    channelid varchar(50) NULL,

    playerid varchar(50) NULL,

    bet jsonb NULL,

    posid varchar(50) NULL,

    agentid varchar(50) NULL,

    bettype varchar(50) NULL,

    betclass varchar(20) NULL,

    betstatus varchar(15) NULL,

    placedon timestamptz NULL,

    settledon timestamptz NULL,

    unitcount int4 NULL,

    unitstake float8 NULL,

    totalstake float8 NULL,

    potentialreturn float8 NULL,

    legcount int4 NULL,

    openlegcount int4 NULL,

    selectionids _text NULL,

    marketids _text NULL,

    eventids _text NULL,

    competitionids _text NULL,

    sportids _text NULL,

    createdon timestamptz NULL,

    marketselectionids _text NULL,

    originalreturn float8 NULL,

    changelog _jsonb NULL,

    tags jsonb NULL,

    CONSTRAINT pk_ermabet PRIMARY KEY (betid, brandid, placedon)

) PARTITION BY RANGE (placedon);

-- Step 2: Create child tables for each partition

CREATE TABLE bets.ermabet_2022 PARTITION OF bets.ermabet

    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

-- Add more child tables for other date ranges as needed

-- Step 3: Create indexes on child tables

CREATE INDEX ermabet_bstatus_idx ON bets.ermabet_2022 USING btree (betstatus);

CREATE INDEX ermabet_mktids_idx ON bets.ermabet_2022 USING gin (marketids);

CREATE INDEX ermabet_tgs_idx ON bets.ermabet_2022 USING gin (tags);

CREATE INDEX idx_ermabet_stdon ON bets.ermabet_2022 USING btree (settledon);

CREATE INDEX idx_ermabetbet_pcdon ON bets.ermabet_2022 USING btree (placedon);

-- Repeat the index creation for other child tables as needed

But I don’t want PLACEDON column part of the primary key.

Any suggestions?

Regards,

Phani Pratz

PPBET-DBA

Hello @Matti Linnanvuori, yes, I am aware of this limitation. Thus, the request to find a workaround.

Hello!

The following limitations apply to partitioned tables:

If I had the partition column in my PK, other services using this table, are affected adversely – that’s the catch.

Apologies for not being completely transparent.

On 5/22/23 01:53, Phani Prathyush Somayajula wrote:

Hi All,

 

I’ve a table ermabet having 80mil records. My business need is to check the bet placed by a player(users), which queries the table, and enable streaming for the player only if he has placed his bet in the last 24 hrs( granular to the millisecond).

[snip]

 

 

But I don’t want PLACEDON column part of the primary key.

And I didn't want partition_date in my PK, either, but I had to add so as to partition on the part_date column.

We don’t have index on playerid, Ron

Where's the playerid index?

Furthermore, the explain plan(without partition) looks like this :

Finalize Aggregate  (cost=20494220.72..20494220.75 rows=1 width=8)

  ->  Gather  (cost=20494220.67..20494220.70 rows=6 width=8)

        Workers Planned: 6

        ->  Partial Aggregate  (cost=20494210.67..20494210.70 rows=1 width=8)

              ->  Parallel Bitmap Heap Scan on ermabet e  (cost=420824.55..20494210.60 rows=26 width=0)

                    Recheck Cond: ((placedon >= '2023-03-28 08:20:23.927+05:30'::timestamp with time zone) AND (placedon <= '2023-03-29 08:19:23.927+05:30'::timestamp with time zone))

                    Filter: (((brandid)::text = 'pp'::text) AND ((playerid)::text = 'Periša_80'::text))

                    ->  Bitmap Index Scan on idx_ermabetbet_pcdon  (cost=0.00..420824.51 rows=27478794 width=0)

                          Index Cond: ((placedon >= '2023-03-28 08:20:23.927+05:30'::timestamp with time zone) AND (placedon <= '2023-03-29 08:19:23.927+05:30'::timestamp with time zone))

JIT:

  Functions: 9

  Options: Inlining true, Optimization true, Expressions true, Deforming true

 

Regards,

Phani Pratz

PPBET-DBA

 

From: Phani Prathyush Somayajula <phani.somayajula@pragmaticplay.com>
Sent: Monday, May 22, 2023 12:24 PM
To: pgsql-admin@lists.postgresql.org
Subject: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS

 

Hi All,

 

I’ve a table ermabet having 80mil records. My business need is to check the bet placed by a player(users), which queries the table, and enable streaming for the player only if he has placed his bet in the last 24 hrs( granular to the millisecond).

I can’t create a partition by range(date) and the query is taking more than 5 mins, whereas we’re expecting the query to run less than 300ms.

 

My Desired Partitions:

CREATE TABLE bets.ermabet_partition (

    betid varchar(50) NOT NULL,

    brandid varchar(50) NOT NULL,

    channelid varchar(50) NULL,

    playerid varchar(50) NULL,

    bet jsonb NULL,

    posid varchar(50) NULL,

    agentid varchar(50) NULL,

    bettype varchar(50) NULL,

    betclass varchar(20) NULL,

    betstatus varchar(15) NULL,

    placedon timestamptz NULL,

    settledon timestamptz NULL,

    unitcount int4 NULL,

    unitstake float8 NULL,

    totalstake float8 NULL,

    potentialreturn float8 NULL,

    legcount int4 NULL,

    openlegcount int4 NULL,

    selectionids _text NULL,

    marketids _text NULL,

    eventids _text NULL,

    competitionids _text NULL,

    sportids _text NULL,

    createdon timestamptz NULL,

    marketselectionids _text NULL,

    originalreturn float8 NULL,

    changelog _jsonb NULL,

    tags jsonb NULL,

    CONSTRAINT pk_ermabet PRIMARY KEY (betid, brandid)

) PARTITION BY RANGE (placedon);

for which I get the error :
SQL Error [0A000]: ERROR: unique constraint on partitioned table must include all partitioning columns

  Detail: PRIMARY KEY constraint on table "ermabet_part_test" lacks column "placedon" which is part of the partition key.

 

 

Error position:



error less partition creation is :

-- Step 1: Create the parent table

CREATE TABLE bets.ermabet (

    betid varchar(50) NOT NULL,

    brandid varchar(50) NOT NULL,

    channelid varchar(50) NULL,

    playerid varchar(50) NULL,

    bet jsonb NULL,

    posid varchar(50) NULL,

    agentid varchar(50) NULL,

    bettype varchar(50) NULL,

    betclass varchar(20) NULL,

    betstatus varchar(15) NULL,

    placedon timestamptz NULL,

    settledon timestamptz NULL,

    unitcount int4 NULL,

    unitstake float8 NULL,

    totalstake float8 NULL,

    potentialreturn float8 NULL,

    legcount int4 NULL,

    openlegcount int4 NULL,

    selectionids _text NULL,

    marketids _text NULL,

    eventids _text NULL,

    competitionids _text NULL,

    sportids _text NULL,

    createdon timestamptz NULL,

    marketselectionids _text NULL,

    originalreturn float8 NULL,

    changelog _jsonb NULL,

    tags jsonb NULL,

    CONSTRAINT pk_ermabet PRIMARY KEY (betid, brandid, placedon)

) PARTITION BY RANGE (placedon);

 

-- Step 2: Create child tables for each partition

CREATE TABLE bets.ermabet_2022 PARTITION OF bets.ermabet

    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

-- Add more child tables for other date ranges as needed

 

-- Step 3: Create indexes on child tables

CREATE INDEX ermabet_bstatus_idx ON bets.ermabet_2022 USING btree (betstatus);

CREATE INDEX ermabet_mktids_idx ON bets.ermabet_2022 USING gin (marketids);

CREATE INDEX ermabet_tgs_idx ON bets.ermabet_2022 USING gin (tags);

CREATE INDEX idx_ermabet_stdon ON bets.ermabet_2022 USING btree (settledon);

CREATE INDEX idx_ermabetbet_pcdon ON bets.ermabet_2022 USING btree (placedon);

-- Repeat the index creation for other child tables as needed

 

 

But I don’t want PLACEDON column part of the primary key.

 

Any suggestions?

 

Regards,

Phani Pratz

PPBET-DBA

 

Question whether you need to partition the table. – not really, if the query gives output within 300ms

SELECT COUNT(1)

FROM ermabet e

WHERE brandid = 'pp'

AND playerid = 'Periša_80'

AND placedon BETWEEN '2023-03-28 08:20:23.927 +0530' AND '2023-03-29 08:19:23.927 +0530';

Meaning the count of bets placed in the last 24hours. Predicate values will change according to the time.

But this query is taking at least 7 mins to complete(without partition) and 1:30s(With partition)

Adverse affects, huh?  Us, too.  Had to departition quite a few tables for performance reasons.

Honestly, 80M rows isn't a lot, unless those jsonb fields are big.  Question whether you need to partition the table.

If I had the partition column in my PK, other services using this table, are affected adversely – that’s the catch.

Apologies for not being completely transparent.

 

Regards,

Phani Pratz

PPBET-DBA

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: Monday, May 22, 2023 12:34 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS

 

On 5/22/23 01:53, Phani Prathyush Somayajula wrote:

Hi All,

 

I’ve a table ermabet having 80mil records. My business need is to check the bet placed by a player(users), which queries the table, and enable streaming for the player only if he has placed his bet in the last 24 hrs( granular to the millisecond).

[snip]

 

 

But I don’t want PLACEDON column part of the primary key.

And I didn't want partition_date in my PK, either, but I had to add so as to partition on the part_date column.

--
Born in Arizona, moved to Babylonia.

Apologies for not replying on the full thread. We did the 3rd suggestion @Scott, it worked with great improvement. The query is now running less than 300ms when created a composite index on brandid,playerid,placedon - however, we had to change the application code to run the query in the same order(which is ok for the app).

However, since I was telling other ripple effects, we're trying to test creating subpartitions by placedon and partitions by brandid like below.

The reason being : we'll have to archive or purge the data post 90 days.

CREATE TABLE bets.emb (

                betid varchar(50) NOT NULL,

                brandid varchar(50) NOT NULL,

                channelid varchar(50) NULL,

                playerid varchar(50) NULL,

                bet jsonb NULL,

                posid varchar(50) NULL,

                agentid varchar(50) NULL,

                bettype varchar(50) NULL,

                betclass varchar(20) NULL,

                betstatus varchar(15) NULL,

                placedon timestamptz NULL,

                settledon timestamptz NULL,

                unitcount int4 NULL,

                unitstake float8 NULL,

                totalstake float8 NULL,

                potentialreturn float8 NULL,

                legcount int4 NULL,

                openlegcount int4 NULL,

                selectionids _text NULL,

                marketids _text NULL,

                eventids _text NULL,

                competitionids _text NULL,

                sportids _text NULL,

                createdon timestamptz NULL,

                marketselectionids _text NULL,

                originalreturn float8 NULL,

                changelog _jsonb NULL,

                tags jsonb NULL,

                CONSTRAINT pk_ermabet PRIMARY KEY (brandid,placedon)

) partition by list( brandid);

bets=> CREATE TABLE bets.emb_01 PARTITION OF bets.emb FOR VALUES IN ('pp') PARTITION BY RANGE(placedon);

CREATE TABLE

CREATE TABLE bets.emb_12_p2022 PARTITION OF bets.emb_01 FOR VALUES FROM ('2022-12-01 05:30:00+05:30') TO ('2023-01-01 05:30:00+05:30');

CREATE TABLE bets.emb_01_p2023 PARTITION OF bets.emb_01 FOR VALUES FROM ('2023-01-01 05:30:00+05:30') TO ('2023-02-01 05:30:00+05:30');

CREATE TABLE bets.emb_02_p2023 PARTITION OF bets.emb_01 FOR VALUES FROM ('2023-02-01 05:30:00+05:30') TO ('2023-03-01 05:30:00+05:30');

CREATE TABLE bets.emb_03_p2023 PARTITION OF bets.emb_01 FOR VALUES FROM ('2023-03-01 05:30:00+05:30') TO ('2023-04-01 05:30:00+05:30');

CREATE TABLE bets.emb_04_p2023 PARTITION OF bets.emb_01 FOR VALUES FROM ('2023-04-01 05:30:00+05:30') TO ('2023-05-01 05:30:00+05:30');

CREATE TABLE bets.emb_05_p2023 PARTITION OF bets.emb_01 FOR VALUES FROM ('2023-05-01 05:30:00+05:30') TO ('2023-06-01 05:30:00+05:30');

CREATE TABLE bets.emb_06_p2023 PARTITION OF bets.emb_01 FOR VALUES FROM ('2023-06-01 05:30:00+05:30') TO ('2023-07-01 05:30:00+05:30');

bets=> CREATE TABLE bets.emb_02 PARTITION OF bets.emb FOR VALUES IN ('nitro') PARTITION BY RANGE(placedon);

CREATE TABLE

CREATE TABLE bets.nitro_12_p2022 PARTITION OF bets.emb_02 FOR VALUES FROM ('2022-12-01 05:30:00+05:30') TO ('2023-01-01 05:30:00+05:30');

CREATE TABLE bets.nitro_01_p2023 PARTITION OF bets.emb_02 FOR VALUES FROM ('2023-01-01 05:30:00+05:30') TO ('2023-02-01 05:30:00+05:30');

CREATE TABLE bets.nitro_02_p2023 PARTITION OF bets.emb_02 FOR VALUES FROM ('2023-02-01 05:30:00+05:30') TO ('2023-03-01 05:30:00+05:30');

CREATE TABLE bets.nitro_03_p2023 PARTITION OF bets.emb_02 FOR VALUES FROM ('2023-03-01 05:30:00+05:30') TO ('2023-04-01 05:30:00+05:30');

CREATE TABLE bets.nitro_04_p2023 PARTITION OF bets.emb_02 FOR VALUES FROM ('2023-04-01 05:30:00+05:30') TO ('2023-05-01 05:30:00+05:30');

CREATE TABLE bets.nitro_05_p2023 PARTITION OF bets.emb_02 FOR VALUES FROM ('2023-05-01 05:30:00+05:30') TO ('2023-06-01 05:30:00+05:30');

CREATE TABLE bets.nitro_06_p2023 PARTITION OF bets.emb_02 FOR VALUES FROM ('2023-06-01 05:30:00+05:30') TO ('2023-07-01 05:30:00+05:30');

bets=> CREATE TABLE bets.emb_03 PARTITION OF bets.emb FOR VALUES IN ('21com') PARTITION BY RANGE(placedon);

CREATE TABLE bets.com21_12_p2022 PARTITION OF bets.emb_03 FOR VALUES FROM ('2022-12-01 05:30:00+05:30') TO ('2023-01-01 05:30:00+05:30');

CREATE TABLE bets.com21_01_p2023 PARTITION OF bets.emb_03 FOR VALUES FROM ('2023-01-01 05:30:00+05:30') TO ('2023-02-01 05:30:00+05:30');

CREATE TABLE bets.com21_02_p2023 PARTITION OF bets.emb_03 FOR VALUES FROM ('2023-02-01 05:30:00+05:30') TO ('2023-03-01 05:30:00+05:30');

CREATE TABLE bets.com21_03_p2023 PARTITION OF bets.emb_03 FOR VALUES FROM ('2023-03-01 05:30:00+05:30') TO ('2023-04-01 05:30:00+05:30');

CREATE TABLE bets.com21_04_p2023 PARTITION OF bets.emb_03 FOR VALUES FROM ('2023-04-01 05:30:00+05:30') TO ('2023-05-01 05:30:00+05:30');

CREATE TABLE bets.com21_05_p2023 PARTITION OF bets.emb_03 FOR VALUES FROM ('2023-05-01 05:30:00+05:30') TO ('2023-06-01 05:30:00+05:30');

CREATE TABLE bets.com21_06_p2023 PARTITION OF bets.emb_03 FOR VALUES FROM ('2023-06-01 05:30:00+05:30') TO ('2023-07-01 05:30:00+05:30');

Regards,

Phani Pratz

PPBET-DBA

-----Original Message-----
From: Scott Ribe <scott_ribe@elevated-dev.com>
Sent: Monday, May 22, 2023 6:55 PM
To: Phani Prathyush Somayajula <phani.somayajula@pragmaticplay.com>
Cc: Matti Linnanvuori <matti.linnanvuori@portalify.com>; pgsql-admin@lists.postgresql.org
Subject: Re: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS

> On May 22, 2023, at 7:17 AM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:

>

> 3) don't partition on it, index (brandid, playerid, placedon)

Oops, given that you're looking for latest placedon, not a specific one, make that (placedon, playerid, brandid)