Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q0zX9-000785-Ms for pgsql-admin@arkaria.postgresql.org; Mon, 22 May 2023 07:00:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q0zX8-0002Qu-J4 for pgsql-admin@arkaria.postgresql.org; Mon, 22 May 2023 07:00:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q0zX7-0002QF-My for pgsql-admin@lists.postgresql.org; Mon, 22 May 2023 07:00:42 +0000 Received: from helsinki.portalify.com ([217.30.185.41]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q0zX2-0019zu-5o for pgsql-admin@lists.postgresql.org; Mon, 22 May 2023 07:00:39 +0000 Received: from smtpclient.apple (dhcp-wlan-10-0-112-223.portalify.com [10.0.112.223]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by helsinki.portalify.com (Postfix) with ESMTPSA id DF40F21FAFAD; Mon, 22 May 2023 10:00:30 +0300 (EEST) From: Matti Linnanvuori Message-Id: <404AADDB-83F9-4A4A-B6D7-E736C278EFDA@portalify.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_3711C559-60B5-4781-9F50-5F66E0E8D71A" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.600.7\)) Subject: Re: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS Date: Mon, 22 May 2023 10:00:20 +0300 In-Reply-To: Cc: "pgsql-admin@lists.postgresql.org" To: Phani Prathyush Somayajula References: X-Mailer: Apple Mail (2.3731.600.7) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_3711C559-60B5-4781-9F50-5F66E0E8D71A Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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=EF=BF=BC 5.11. Table Partitioning postgresql.org > On 22. May 2023, at 9.55, Phani Prathyush Somayajula = wrote: >=20 > You don't often get email from phani.somayajula@pragmaticplay.com = . Learn why this is important = =09 > Furthermore, the explain plan(without partition) looks like this :=20 >=20 > Finalize Aggregate (cost=3D20494220.72..20494220.75 rows=3D1 width=3D8)= > -> Gather (cost=3D20494220.67..20494220.70 rows=3D6 width=3D8) > Workers Planned: 6 > -> Partial Aggregate (cost=3D20494210.67..20494210.70 rows=3D1= width=3D8) > -> Parallel Bitmap Heap Scan on ermabet e = (cost=3D420824.55..20494210.60 rows=3D26 width=3D0) > Recheck Cond: ((placedon >=3D '2023-03-28 = 08:20:23.927+05:30'::timestamp with time zone) AND (placedon <=3D = '2023-03-29 08:19:23.927+05:30'::timestamp with time zone)) > Filter: (((brandid)::text =3D 'pp'::text) AND = ((playerid)::text =3D 'Peri=C5=A1a_80'::text)) > -> Bitmap Index Scan on idx_ermabetbet_pcdon = (cost=3D0.00..420824.51 rows=3D27478794 width=3D0) > Index Cond: ((placedon >=3D '2023-03-28 = 08:20:23.927+05:30'::timestamp with time zone) AND (placedon <=3D = '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 > =20 > Regards, > Phani Pratz > PPBET-DBA > =20 > From: Phani Prathyush Somayajula >=20 > 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 > =20 > Hi All, > =20 > I=E2=80=99ve 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).=20 > I can=E2=80=99t create a partition by range(date) and the query is = taking more than 5 mins, whereas we=E2=80=99re expecting the query to = run less than 300ms. > =20 > My Desired Partitions:=20 >=20 > 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); >=20 > for which I get the error :=20 > 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. > =20 > =20 > Error position: >=20 >=20 >=20 > error less partition creation is :=20 >=20 > -- 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); > =20 > -- 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 > =20 > -- 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 > =20 > =20 > But I don=E2=80=99t want PLACEDON column part of the primary key.=20 > =20 > Any suggestions? > =20 > Regards, > Phani Pratz > PPBET-DBA --Apple-Mail=_3711C559-60B5-4781-9F50-5F66E0E8D71A Content-Type: multipart/related; type="text/html"; boundary="Apple-Mail=_7D180187-553D-45EE-AE97-9FEF9D377DFC" --Apple-Mail=_7D180187-553D-45EE-AE97-9FEF9D377DFC Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

Hello!

The following = limitations apply to partitioned tables: