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 1q0zjc-0007iI-2q for pgsql-admin@arkaria.postgresql.org; Mon, 22 May 2023 07:13:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q0zja-00062u-6H for pgsql-admin@arkaria.postgresql.org; Mon, 22 May 2023 07:13:34 +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 1q0zjZ-00062k-Nd for pgsql-admin@lists.postgresql.org; Mon, 22 May 2023 07:13:33 +0000 Received: from mail-yb1-xb35.google.com ([2607:f8b0:4864:20::b35]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q0zjW-001AF0-TJ for pgsql-admin@lists.postgresql.org; Mon, 22 May 2023 07:13:32 +0000 Received: by mail-yb1-xb35.google.com with SMTP id 3f1490d57ef6-ba82956d3e0so4776679276.0 for ; Mon, 22 May 2023 00:13:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1684739610; x=1687331610; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=w1hqJmiotobA9VoAGlI46uxWxaoKshu/pqXj350sgs4=; b=JEmt9TgT7gz1pfNNyTDCWyg/ynZP18kMmarAMlicnBRaEt+Ic/iidUoji8WXCwQPcG GJHieKlgjDIN2GaUWwPuv+JNxqNtu1C6UYESJJ30BZi8IueiuvBGNHeK25O6zPMlMdfn v7+yR09dHo8GrQZ8rwX8v64c/ja5coAA6AjglHcExxp5yskERGu0doxLh1h7QP7WPKB6 7lLL7wME9U6aSTXXOtH3L3BqupdJ6ZeEaFJjAtHuKCUlq02tngP8o1sAaYfw/uCCUIOd kkcpkfA05xTfywy8w4jXMUZts222zYeROuyipb5z5YJWYVslS///OhJbgNQKH1z0iFkI enwA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1684739610; x=1687331610; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=w1hqJmiotobA9VoAGlI46uxWxaoKshu/pqXj350sgs4=; b=chtvxJsn7u8rFk/+IXYBDn2xSDu3ZQPO+1lj/ZhnqE4Lp+hM8gWl5ULLaDsPwyH28D lJhY3fUPcvj/ypMq0eF11k1cSFXAW696i2ufgVLofBycNRwvt7T+QDHciDAT0uK8aCKQ 8nfzFIgtSokr7ZAaYyDDfbzvnjaQt4YGyKik8EMXgW/OaGOtqBYOUpxZ4OwgVFeNBpQt UTfDXCBnl4LjZ/KcYdet76IkrbSSDTxX4UrxbkBK3IEz4PkZ4EgK6b5tr5N8qfozbCpH aXnQoMqJSw+6LQL+w9EkW1+bTakMudDCM51KvXSKsPN4aTy+F9P7SL65xhjjzKeNvtJF BWew== X-Gm-Message-State: AC+VfDy4LzLqxG+tvl7XYEcz1YQ2ckDi4rN7cP2m3cA59z2owk5pZdue dvDTOozSmT9PqgywfCIT8aooKkIwWNo= X-Google-Smtp-Source: ACHHUZ67qe1T1ecwiqkcZlw0e5elwm8OUWHeTOoeibhLgTro9LFc9zxXo2CaDWnkwgxbMtvkczmsWQ== X-Received: by 2002:a25:acc5:0:b0:ba8:24c4:ec02 with SMTP id x5-20020a25acc5000000b00ba824c4ec02mr10997177ybd.25.1684739609907; Mon, 22 May 2023 00:13:29 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:6922:b189:696b:eba2? ([2600:1700:bd2c:b810:6922:b189:696b:eba2]) by smtp.googlemail.com with ESMTPSA id v15-20020a5b084f000000b00b9dfcc7a1fasm1328422ybq.7.2023.05.22.00.13.29 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 22 May 2023 00:13:29 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------bJb6tlH8Za9Ghty5l38US2eZ" Message-ID: Date: Mon, 22 May 2023 02:13:29 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS Content-Language: en-US To: "pgsql-admin@lists.postgresql.org" References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------bJb6tlH8Za9Ghty5l38US2eZ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > *Sent:* Monday, May 22, 2023 12:34 PM > *To:* pgsql-admin@lists.postgresql.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. --------------bJb6tlH8Za9Ghty5l38US2eZ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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@gmail.com>
Sent: Monday, May 22, 2023 12:34 PM
To: pgsql-admin@lists.postgresql.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.
--------------bJb6tlH8Za9Ghty5l38US2eZ--