RE: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS

Hello @Matti Linnanvuori<mailto:matti(dot)linnanvuori(at)portalify(dot)com>, yes, I am aware of this limitation. Thus, the request to find a workaround.

Regards,
Phani Pratz
PPBET-DBA

From: Matti Linnanvuori <matti(dot)linnanvuori(at)portalify(dot)com>
Sent: Monday, May 22, 2023 12:30 PM
To: Phani Prathyush Somayajula <phani(dot)somayajula(at)pragmaticplay(dot)com>
Cc: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Re: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS

Hello!

The following limitations apply to partitioned tables:

* To create a unique or primary key constraint on a partitioned table, the partition keys must not include any expressions or function calls and the constraint's columns must include all of the partition key columns.

[cid:image001(dot)png(at)01D98CA9(dot)B3E3C230]
5.11. Table Partitioning<https://www.postgresql.org/docs/current/ddl-partitioning.html>
postgresql.org<https://www.postgresql.org/docs/current/ddl-partitioning.html>

On 22. May 2023, at 9.55, Phani Prathyush Somayajula <phani(dot)somayajula(at)pragmaticplay(dot)com<mailto:phani(dot)somayajula(at)pragmaticplay(dot)com>> wrote:

You don't often get email from phani(dot)somayajula(at)pragmaticplay(dot)com<mailto:phani(dot)somayajula(at)pragmaticplay(dot)com>. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification>
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(dot)somayajula(at)pragmaticplay(dot)com<mailto:phani(dot)somayajula(at)pragmaticplay(dot)com>>
Sent: Monday, May 22, 2023 12:24 PM
To: pgsql-admin(at)lists(dot)postgresql(dot)org<mailto:pgsql-admin(at)lists(dot)postgresql(dot)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