Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s9qEn-005Sgl-FO for pgsql-admin@arkaria.postgresql.org; Wed, 22 May 2024 17:58:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s9qEn-00202u-H9 for pgsql-admin@arkaria.postgresql.org; Wed, 22 May 2024 17:58:53 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s9qEn-00202m-5K for pgsql-admin@lists.postgresql.org; Wed, 22 May 2024 17:58:53 +0000 Received: from qs51p00im-qukt01080101.me.com ([17.57.155.18]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s9qEj-001UR0-MG for pgsql-admin@lists.postgresql.org; Wed, 22 May 2024 17:58:51 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=1a1hai; t=1716400728; bh=tSNMqtVurTVLgX7fvj1hgcR+iPufFHqqByxbZ9kjk+A=; h=From:Message-Id:Content-Type:Mime-Version:Subject:Date:To; b=AiFOekGcQSbuuMqGxIKjEBbwbVxTeskorL3rLbaCPKFkZVJ8m3D8pO+zt2mqxc2aM ew9F0SWb7c5wfODTed8X4zWQZT2h3WK6XK+WbNmDPXC8CNK00awTWAEOkLOMN3HpIF AK+Bq21Z2mNcP7dKNbM6r4o8CYV23wluw8tHm/qR6oM9LIpE3BsZGjIS5JuJpdsxZf c1ui0vm+fxJMffcdKwDdGWNDEvUHf2N5WoygO/TLwUTdyzS67mGGTHFWZQwhYuxfDr JhSvhpukKh8Y9apyLyWqDji94zD/9/rv/g37y/hSiIce4RokdAigdGfH4hzfBE/280 a4Y0L7cPipPjw== Received: from smtpclient.apple (qs51p00im-dlb-asmtp-mailmevip.me.com [17.57.155.28]) by qs51p00im-qukt01080101.me.com (Postfix) with ESMTPSA id 8712C618019F; Wed, 22 May 2024 17:58:47 +0000 (UTC) From: Rui DeSousa Message-Id: <2EB08EEC-56F6-4E96-A14A-797361C07C5A@icloud.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_C71AF3C2-91CF-408F-8DA8-FCBDCCB8784E" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.8\)) Subject: Re: Need guidance on partioning Date: Wed, 22 May 2024 13:58:46 -0400 In-Reply-To: Cc: "Wetmore, Matthew (CTR)" , srinivasan s , "pgsql-admin@lists.postgresql.org" To: M Sarwar References: <58ba1eea470d440faea4100910529787@evernorth.com> X-Mailer: Apple Mail (2.3696.120.41.1.8) X-Proofpoint-ORIG-GUID: jqzzCww6NgU2CUIB_XzDlUt0nzJqXDz8 X-Proofpoint-GUID: jqzzCww6NgU2CUIB_XzDlUt0nzJqXDz8 X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.272,Aquarius:18.0.1039,Hydra:6.0.650,FMLib:17.12.28.16 definitions=2024-05-22_10,2024-05-22_01,2024-05-17_01 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 phishscore=0 malwarescore=0 suspectscore=0 clxscore=1011 adultscore=0 spamscore=0 bulkscore=0 mlxlogscore=777 mlxscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.19.0-2308100000 definitions=main-2405220124 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_C71AF3C2-91CF-408F-8DA8-FCBDCCB8784E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On May 22, 2024, at 11:10 AM, M Sarwar wrote: >=20 > Is it a good idea to partition the table when it reaches 750 MB in = size or 1 B rows or any other general rule? >=20 750MB is not a really big table; Postgres default segment size is 1GB, I = normally set it 256GB instead. 1GB segment size is holdover from signed = 32bit filesystems where the largest file size was 2GB.=20 I=E2=80=99ve had tables that where hundreds of GB and with billions of = records. In my experiences partitioning usually degrades performance = unless it well planned out as there is overhead associated with = partitioning. I don=E2=80=99t think there is a hard and fast rule for when to = partition. It really depending on the given issues at hand. One good = reason to partition large tables is for table maintenance, i.e. aging = out and archiving out large chunks of data. What is a large table? That = might also depend on hardware; a big table on spinning rust might feel = like small table on solid state. There is also a limit to how many partitions one should create. Too = many and performance tanks. I once inherited a system that partition by = day, what a disaster, I completely eliminated the partitioning from = system as it didn=E2=80=99t need it. Point is partitioning is a tool = and you=E2=80=99ll know when you need it. As with a tool =E2=80=94 if = you have a hammer in hand and the fastener is a screw then that screw = really start to look like nail.= --Apple-Mail=_C71AF3C2-91CF-408F-8DA8-FCBDCCB8784E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On May 22, 2024, at 11:10 AM, M Sarwar <sarwarmd02@outlook.com> wrote:

Is it  a good = idea to partition the table when it reaches 750 MB in size or 1 B rows = or any other general rule?


750MB is not a really big = table; Postgres default segment size is 1GB, I normally set it 256GB = instead.  1GB segment size is holdover from signed 32bit = filesystems where the largest file size was 2GB. 

I=E2=80=99ve had tables = that where hundreds of GB and with billions of records.  In my = experiences partitioning usually degrades performance unless it well = planned out as there is overhead associated with partitioning.

I don=E2=80=99t think = there is a hard and fast rule for when to partition.  It really = depending on the given issues at hand.  One good reason to = partition large tables is for table maintenance, i.e. aging out and = archiving out large chunks of data.  What is a large table? That = might also depend on hardware; a big table on spinning rust might feel = like small table on solid state.

There is also a limit to how many = partitions one should create.  Too many and performance tanks. =  I once inherited a system that partition by day, what a disaster, = I completely eliminated the partitioning from system as it didn=E2=80=99t = need it.  Point is partitioning is a tool and you=E2=80=99ll know = when you need it.  As with a tool =E2=80=94 if you have a hammer in = hand and the fastener is a screw then that screw really start to look = like nail.
= --Apple-Mail=_C71AF3C2-91CF-408F-8DA8-FCBDCCB8784E--