From: Matti Linnanvuori <matti.linnanvuori@portalify.com>
To: Phani Prathyush Somayajula <phani.somayajula@pragmaticplay.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS
Date: Mon, 22 May 2023 10:00:20 +0300
Message-ID: <404AADDB-83F9-4A4A-B6D7-E736C278EFDA@portalify.com> (raw)
In-Reply-To: <VI1PR10MB7671180739AB51DF1455A1A48D439@VI1PR10MB7671.EURPRD10.PROD.OUTLOOK.COM>
References: <VI1PR10MB76711B1B17D38E3CFF4E73008D439@VI1PR10MB7671.EURPRD10.PROD.OUTLOOK.COM>
<VI1PR10MB7671180739AB51DF1455A1A48D439@VI1PR10MB7671.EURPRD10.PROD.OUTLOOK.COM>
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.somayajula@pragmaticplay.com> wrote:
>
> You don't often get email from phani.somayajula@pragmaticplay.com <mailto:phani.somayajula@pragmaticplay.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.somayajula@pragmaticplay.com <mailto:phani.somayajula@pragmaticplay.com>>
> Sent: Monday, May 22, 2023 12:24 PM
> To: pgsql-admin@lists.postgresql.org <mailto: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
Attachments:
[image/png] elephant.png (49.1K, ../404AADDB-83F9-4A4A-B6D7-E736C278EFDA@portalify.com/3-elephant.png)
download | view image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: matti.linnanvuori@portalify.com, phani.somayajula@pragmaticplay.com, pgsql-admin@lists.postgresql.org
Subject: Re: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS
In-Reply-To: <404AADDB-83F9-4A4A-B6D7-E736C278EFDA@portalify.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox