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

What's wrong with partitioning by placedon, with a primary key of (betid, placedon)? - this is because the business need to run group by queries based on these brands. For now, there are 4 brands and in future it could go up to 7.

Yes, I agree there could be a collision with the current pk structure, which got me to a thought - " Can we create pk of (brandid,placedon, betid)" ? coz I'ven't check it yet. Got to test first.

Regards,
Phani Pratz
PPBET-DBA

-----Original Message-----
From: Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
Sent: Monday, May 22, 2023 8:46 PM
To: Phani Prathyush Somayajula <phani(dot)somayajula(at)pragmaticplay(dot)com>
Cc: pgsql-admin(at)lists(dot)postgresql(dot)org; Matti Linnanvuori <matti(dot)linnanvuori(at)portalify(dot)com>
Subject: Re: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS

> On May 22, 2023, at 8:07 AM, Phani Prathyush Somayajula <phani(dot)somayajula(at)pragmaticplay(dot)com> wrote:
>
> However, since I was telling other ripple effects, we're trying to test creating subpartitions by placedon and partitions by brandid like below.
> The reason being : we'll have to archive or purge the data post 90 days.

You could still partition by placedon... Partitioning by brandid is not much help with rotating out old data.

Why the change in primary key? In fact, your primary key in that example is not guaranteed unique--if I'm reading this correctly there is a chance of collision.

What's wrong with partitioning by placedon, with a primary key of (betid, placedon)?