Date: Tue, 30 May 2023 21:16:23 +0200 (CEST)
From: Erik Wienhold <ewie@ewie.name>
To: vasiliauskas.mg@gmail.com, pgsql-docs@lists.postgresql.org
Message-ID: <585296650.518007.1685474183992@office.mailbox.org>
In-Reply-To: 
 <168542321808.1230020.11749247868403452374@wrigleys.postgresql.org>
References: 
 <168542321808.1230020.11749247868403452374@wrigleys.postgresql.org>
Subject: Re: 5.11.1 Table Partitioning Overview - "...the size of the table
 should exceed the physical memory.."
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Importance: Normal
Archived-At: 
 <https://www.postgresql.org/message-id/585296650.518007.1685474183992%40office.mailbox.org>
Precedence: bulk

> On 30/05/2023 07:06 CEST PG Doc comments form <noreply@postgresql.org> wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/ddl-partitioning.html
> Description:
>
> Is this rule of thumb a typo?
>
> "The exact point at which a table will benefit from partitioning depends on
> the application, although a rule of thumb is that the size of the table
> should exceed the physical memory of the database server."
>
> I believe it should say the "a rule of thumb is that the size of the table
> should NOT exceed the physical memory of the database server."

That rule of thumb is correct (or at least not wrong).  The reasoning behind it
is that table partitioning provides a benefit if individual partitions fit into
physical memory and queries target just a few partitions instead of the entire
table.  Subsequent queries will benefit if the necessary tables have already
been loaded into memory.  If a table does not fit into memory, Postgres will
have to constantly swap out memory.  Also work_mem may be exceeded sooner with
larger tables and Postgres has to eventually write temporary files to disk to
store intermediate results, e.g. for sorting and hashing.

Physical memory means RAM, not disk, if that's your issue here.

--
Erik