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

On Mon, May 22, 2023 at 2:54 AM Phani Prathyush Somayajula <
phani(dot)somayajula(at)pragmaticplay(dot)com> 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).
>
> 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.
>

This is confusing. Is your main question about performance, or about
designing a primary key, or about designing partitioning? They are pretty
different things. If you fix the performance problem the right way, do you
need partitioning at all? An 80 million row table is not all that large, I
would normally not think partitioning it would be very important.

-- Step 3: Create indexes on child tables
>
> CREATE INDEX ermabet_bstatus_idx ON bets.ermabet_2022 USING btree
> (betstatus);
>
>
>
If you create indexes on the parent table, they will automatically be
created on each child. Only if you want different children to have
different indexes should you create them separately on the children.

Based on your plan shared in a different email, you need an index on
(brandid, playerid, placedon). With the right index, I doubt partitioning
would be needed.

Cheers,

Jeff

>