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

[Matti Linnanvuori <matti.linnanvuori@xxxxxxxxxxxxx>]

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.

  
  

  

  5.11. Table Partitioning

  postgresql.org
  
  

On 22. May 2023, at 9.55, Phani Prathyush Somayajula <phani.somayajula@xxxxxxxxxxxxxxxxx> wrote:

You don't often get email from phani.somayajula@xxxxxxxxxxxxxxxxx. Learn why this is important

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@xxxxxxxxxxxxxxxxx> 
Sent: Monday, May 22, 2023 12:24 PM
To: pgsql-admin@xxxxxxxxxxxxxxxxxxxx
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