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.

https://www.postgresql.org/docs/current/ddl-partitioning.html
5.11. Table Partitioning
postgresql.org

> On 22. May 2023, at 9.55, Phani Prathyush Somayajula <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