Re: 5.11.1 Table Partitioning Overview - "...the size of the table should exceed the physical memory.."

From: Erik Wienhold <ewie(at)ewie(dot)name>
To: vasiliauskas(dot)mg(at)gmail(dot)com, pgsql-docs(at)lists(dot)postgresql(dot)org
Subject: Re: 5.11.1 Table Partitioning Overview - "...the size of the table should exceed the physical memory.."
Date: 2023-05-30 19:16:23
Message-ID: 585296650.518007.1685474183992@office.mailbox.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

> On 30/05/2023 07:06 CEST PG Doc comments form <noreply(at)postgresql(dot)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

In response to

Browse pgsql-docs by date

  From Date Subject
Next Message Peter Eisentraut 2023-05-31 11:16:56 Re: confusing positioning of notes in connection settings
Previous Message PG Doc comments form 2023-05-30 05:06:58 5.11.1 Table Partitioning Overview - "...the size of the table should exceed the physical memory.."