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.96) (envelope-from ) id 1vOIgM-001f7l-14 for pgsql-general@arkaria.postgresql.org; Wed, 26 Nov 2025 16:47:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vOIgJ-000cFs-2y for pgsql-general@arkaria.postgresql.org; Wed, 26 Nov 2025 16:47:52 +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.96) (envelope-from ) id 1vOIgJ-000cFj-1T for pgsql-general@lists.postgresql.org; Wed, 26 Nov 2025 16:47:51 +0000 Received: from mail-oi1-x22f.google.com ([2607:f8b0:4864:20::22f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vOIgH-001bUX-0c for pgsql-general@lists.postgresql.org; Wed, 26 Nov 2025 16:47:50 +0000 Received: by mail-oi1-x22f.google.com with SMTP id 5614622812f47-4500a6fb71aso2243219b6e.3 for ; Wed, 26 Nov 2025 08:47:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1764175669; x=1764780469; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=26cpSYjp9J0XPU6fvlGHKDyw7TxcHwG0/BiKWGICGJ8=; b=hyz60Y9+mZvmmtaHTW2KQ+HWQ1SmCxMoNFoTdyAjNsxYOgUAH7sU6/xUwRQVpCu+Il mdHlFqv5Jldm7o2t88L7Pu8CYLgNhs6qogn/p7EemYnK9Zqn44hH3wJd8CxZ8JKl320X VU2aM1T+zGfX2OnSKKW9KRYuvb+byUvMtBYhDiLJYU3qLyhn6PAB8+43vkMnTRMwoKeE FGjDqP2sNyemAhYRxrC2fBgKdgNRWRutxGDwDMnhS504IMQ8hUfohfVB/8cvL1j/mB/e z0uYAmuu27nQ6YOytkQKXsKrD4mc4EVUFRl7Wo5CH/nn30cIk/E5XEs+z2DAFxc2iNcQ Ip3w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1764175669; x=1764780469; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=26cpSYjp9J0XPU6fvlGHKDyw7TxcHwG0/BiKWGICGJ8=; b=PiH4QkCTAp+n4oTsRkx7Mgj1bsX+eMKPuAgEM1rYmP+InMtwW7n+y9mj8koi1OB94I 9dUCkNmbNzaxgx4kNwgRdEEpDjbm92CyyW1YM9CUBYyJ1t5Mlheny98nOI5c+n7PqNsU CIEzcf7WWC9glmYGHU9fU7rtv7w6ujXXQe7g4TAhJAzdOgQWj/XGF+A/DdDUhzVek9MJ vwp3ifHz5JD7u0Dnrzk82wwMFFbZmDcYmCrLI915ejhWf+obBTwXibAPRgCAvnreb5S9 9ZPECtGyGINp+p/zAZMkANG3iZCGPlb+rEdCRRkVWLUB97QdTzJ6+OruyZmoIlyCqA0H 3ZeA== X-Gm-Message-State: AOJu0YyNrMg5Cm3JwFMYqv5xv6OZ+SqVhUFgsEtUdcAkVPrlMYAop8Uy Qd49rwq62BlBE3jvWUrvYFxe8rxniPN6YrpcGyfH4jCwrW52dAn203NV/aVjUu9dBO4aeHi3gGw fXvO+6VX1+jYZP4kyIBdTwFySe1DMa7Au/A== X-Gm-Gg: ASbGncsHJX+fWYfDh034AqSv6EcLaWQ5t0s73GBlDFDOvu2v4blHAzjeYcQyVRWoq77 YRQVSLNMBvER3FzDmiFrJhfxpj+GH4TW/+ux/WmRbCgsY7mGhJP33EQ1LEs2ys49WK2vyHKWT6I r+zBpP3Xh7NkdjXZPW7cxG0oZAHrhUAQNqWNAz3S+9u3H4DWuGN/txKKTkHAuSN81S6Ud5XkMSJ 96ChbzWBngsG6hYgsJeyro2m+IN/RQxqiGsaZ/kVmzYFZd2YcFNQGcsPaoNMsDTvI4ht+v8 X-Google-Smtp-Source: AGHT+IHeBTUQa+UPlUGzeBhQuHU5AB81mIzQfdjXWIQUwBkpaycy28yaBaAWGoec3Xhf3DrfXLIeACyWz3tnjAFyeS4= X-Received: by 2002:a05:6808:178d:b0:437:eb1d:cdde with SMTP id 5614622812f47-4514e7a239amr3183022b6e.33.1764175668936; Wed, 26 Nov 2025 08:47:48 -0800 (PST) MIME-Version: 1.0 References: <60a4dc2711441931d29df7a43ba7447bbedb402f.camel@cybertec.at> In-Reply-To: From: Ron Johnson Date: Wed, 26 Nov 2025 11:47:38 -0500 X-Gm-Features: AWmQ_bkpt7sOnB_5V62ht38T1gQu-8yMpPDc6PTdQKQWXPbZEjZ-7ueOmQgmvYA Message-ID: Subject: =?UTF-8?Q?Re=3A_Question_on_PostgreSQL_Table_Partitioning_=E2=80=93_Pe?= =?UTF-8?Q?rformance_of_Queries_That_Do_Not_Use_the_Partition_Key?= To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000001184b4064482286e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001184b4064482286e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable An old greybeard COBOL programmer would say that a critical *OLTP* table should *only* be accessed via one index (customer_id, sale_id, PK, etc), and there should be as few indices as possible on the table. The DBA would then partition based on that index. Any reports should be run from a separate table which is populated from the OLTP table. (This is why reporting databases and their big brothers "data warehouses" were developed: so you aren't querying the whole critical OLTP table.) In your case, right now, my first DBA task would be to ensure that the table is regularly vacuumed and analyzed. On Wed, Nov 26, 2025 at 11:29=E2=80=AFAM atma ram wro= te: > Hi Laurenz, > > Thank you for your reply. > > The table size is 1.6TB and not GB. My bad. > > The queries using primary key or partition key will be > definitely improved. My question is 20 queries that do not use partition > key and use only index. Since this is a critical OLTP system, even if the= re > is a slight chance that those 20 queries will degrade performance, then w= e > may not go with partition and find any alternative ways. Hence the > question. > > We can do a POC and find out. But wanted to check if there are any > existing benchmarking, explanations or details available before we do POC= . > Is it worth it? > > Thanks, > Atma > > On Wed, Nov 26, 2025 at 9:05=E2=80=AFPM Laurenz Albe > wrote: > >> On Wed, 2025-11-26 at 18:50 +0530, atma ram wrote: >> > We have a table that is approximately 1.6 GB in size. Query performanc= e >> has started to degrade. >> > Although we have multiple indexes, the large table size is still >> causing performance issues. >> >> 1.6 GB is too small for partitioning. >> >> > We are planning to partition the table on the primary key. This is an >> OLTP system, and there >> > are around 100 queries that access this table. About 80 of these >> queries use the primary key >> > and will therefore benefit directly from the partition key once we >> implement partitioning. >> >> If a table uses the primary key, I cannot see how it could cause >> performance issues. >> >> I expect that these queries will become slightly *slower* if you >> partition the table, >> because of the overhead of partition pruning. >> >> > However, the remaining 20 queries do not use the primary key; they rel= y >> on other indexed columns. >> > Our question is: after partitioning the table, and after creating the >> necessary indexes on >> > each partition, what happens to the performance of those 20 queries >> that do not use the partition key? >> > =E2=80=93 Will their performance degrade? >> >> Very likely yes, though perhaps only slightly. >> >> > =E2=80=93 Will it remain the same as before partitioning? >> > =E2=80=93 Is there any chance it will improve? >> >> That is unlikely, but possible, for example if the query can choose a >> faster sequential >> scan on some partitions, instead of a slower index scan on the whole >> table. >> >> > Is there any benchmarking, documentation, or reference material that >> can help demonstrate >> > how partitioning will affect the performance of the 20 queries that do >> not use the partition key? >> > This information is critical for us before proceeding with the >> partitioning strategy. >> >> The only good way to tell is to implement it on a test database and try >> it. >> >> But as I said initially, with a 1.6 GB table patritioning is pointless. >> >> Examine the performance bottleneck with EXPLAIN (ANALYZE, BUFFERS) and >> try to improve >> your queries. >> >> Yours, >> Laurenz Albe >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000001184b4064482286e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
An old greybeard COBOL programmer would say=C2=A0that= a critical OLTP table should only=C2=A0be accessed via one i= ndex (customer_id, sale_id, PK, etc), and there should be as few indices as= possible on the table.=C2=A0 The DBA would then partition based on that in= dex.=C2=A0 Any reports should be run from a separate table which is populat= ed from the OLTP table.=C2=A0 (This is why reporting databases and their bi= g brothers "data warehouses" were developed: so you aren't qu= erying the whole critical OLTP table.)

In your cas= e, right now, my first DBA task would be to ensure that the table is regula= rly vacuumed=C2=A0and analyzed.

On Wed= , Nov 26, 2025 at 11:29=E2=80=AFAM atma ram <atmaramkp@gmail.com> wrote:
Hi=C2=A0Laurenz,

Thank you for your=C2= =A0reply.

The table size is 1.6TB and not GB. My b= ad.

The queries using primary key or partition key= will be definitely=C2=A0improved. My question is 20 queries that do not us= e partition key and use only index. Since this is a critical OLTP system, e= ven if there is a slight chance that those 20 queries will degrade performa= nce, then we may not go with partition and find any alternative ways. Hence= the question.=C2=A0

We can do a POC and find out.= But wanted to check if there are any existing benchmarking, explanations o= r details available before we do POC. Is it worth it?

<= div>Thanks,
Atma

On Wed, Nov 26, 2025 at 9:05=E2=80=AFPM Lau= renz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2025-11-26 at 18:50 +0530, atma ram wrote:=
> We have a table that is approximately 1.6 GB in size. Query performanc= e has started to degrade.
> Although we have multiple indexes, the large table size is still causi= ng performance issues.

1.6 GB is too small for partitioning.

> We are planning to partition the table on the primary key. This is an = OLTP system, and there
> are around 100 queries that access this table. About 80 of these queri= es use the primary key
> and will therefore benefit directly from the partition key once we imp= lement partitioning.

If a table uses the primary key, I cannot see how it could cause performanc= e issues.

I expect that these queries will become slightly *slower* if you partition = the table,
because of the overhead of partition pruning.

> However, the remaining 20 queries do not use the primary key; they rel= y on other indexed columns.
> Our question is: after partitioning the table, and after creating the = necessary indexes on
> each partition, what happens to the performance of those 20 queries th= at do not use the partition key?
> =E2=80=93 Will their performance degrade?

Very likely yes, though perhaps only slightly.

> =E2=80=93 Will it remain the same as before partitioning?
> =E2=80=93 Is there any chance it will improve?

That is unlikely, but possible, for example if the query can choose a faste= r sequential
scan on some partitions, instead of a slower index scan on the whole table.=

> Is there any benchmarking, documentation, or reference material that c= an help demonstrate
> how partitioning will affect the performance of the 20 queries that do= not use the partition key?
> This information is critical for us before proceeding with the partiti= oning strategy.

The only good way to tell is to implement it on a test database and try it.=

But as I said initially, with a 1.6 GB table patritioning is pointless.

Examine the performance bottleneck with EXPLAIN (ANALYZE, BUFFERS) and try = to improve
your queries.

Yours,
Laurenz Albe


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--0000000000001184b4064482286e--