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

Question whether you need to partition the table. – not really, if the query gives output within 300ms

SELECT COUNT(1)
FROM ermabet e
WHERE brandid = 'pp'
AND playerid = 'Periša_80'
AND placedon BETWEEN '2023-03-28 08:20:23.927 +0530' AND '2023-03-29 08:19:23.927 +0530';

Meaning the count of bets placed in the last 24hours. Predicate values will change according to the time.

But this query is taking at least 7 mins to complete(without partition) and 1:30s(With partition)

Regards,
Phani Pratz
PPBET-DBA

From: Ron <ronljohnsonjr(at)gmail(dot)com>
Sent: Monday, May 22, 2023 12:43 PM
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Re: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS

Adverse affects, huh? Us, too. Had to departition quite a few tables for performance reasons.

Honestly, 80M rows isn't a lot, unless those jsonb fields are big. Question whether you need to partition the table.
On 5/22/23 02:08, Phani Prathyush Somayajula wrote:
If I had the partition column in my PK, other services using this table, are affected adversely – that’s the catch.
Apologies for not being completely transparent.

Regards,
Phani Pratz
PPBET-DBA

From: Ron <ronljohnsonjr(at)gmail(dot)com><mailto:ronljohnsonjr(at)gmail(dot)com>
Sent: Monday, May 22, 2023 12:34 PM
To: pgsql-admin(at)lists(dot)postgresql(dot)org<mailto:pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Re: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS

On 5/22/23 01:53, Phani Prathyush Somayajula wrote:

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).
[snip]

But I don’t want PLACEDON column part of the primary key.

And I didn't want partition_date in my PK, either, but I had to add so as to partition on the part_date column.
--
Born in Arizona, moved to Babylonia.

--
Born in Arizona, moved to Babylonia.