From: Piotr Włodarczyk <piotrwlodarczyk89@gmail.com>
To: Michael Lewis <mlewis@entrata.com>
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: Piotr Włodarczyk <piotr.wlodarczyk@gnb.pl>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: ODP: Planner performance in partitions
Date: Tue, 13 Aug 2019 08:29:06 +0200
Message-ID: <5d5258b0.1c69fb81.3d155.d4f8@mx.google.com> (raw)
In-Reply-To: <CAHOFxGr3TG4OAHGvkXKhHbF-h2JaXR5+djDkA6o=MsquXkLAsQ@mail.gmail.com>
References: <5675f9f771f04d9f8b5cffe16f4a4175@EX13MBX02.gb.ad.corp>
<CAHOFxGrPiuFFNETAbV3rcw1COns8vJbgPm5zrGiSeO9GZPRshQ@mail.gmail.com>
<458dfc43-b097-29f3-4e0e-857b9c30b427@sqlexec.com>
<5d51c622.1c69fb81.4032d.34d9@mx.google.com>
<CAKJS1f9DEHYHu=hJPE_SYj9eheWK4F8iPNQGBApT4AHKgxVywQ@mail.gmail.com>
<CAHOFxGr3TG4OAHGvkXKhHbF-h2JaXR5+djDkA6o=MsquXkLAsQ@mail.gmail.com>
As you wrote we have about 400/500 partitions in real life. So time problem is much smaller, but still it is and in one place of aur application we have decided to help DB and we're indicating in query exact partition we need. What pushed me to do this test? Just curiosity I think. After I saw in pg_locks that all partitions which was selected in uncommitted transaction have ACCESS SHARED i've started thinking about efficiency. And that way here we are. Why we need some hundred partitions? It’s because our main table (public.book) have hundreds of millions records. It’s not maintainable. VACUUM never ends, space on device is huge and we cannot take database down for longer that 2-3 hours, what is too short to maintain them manually. So we've partitioned them on two levels. First on id_owner (which is in every query) and the second level based on date. It’ll help as detach partitions with old data we no longer need.
Pozdrawiam,
Piotr Włodarczyk
Od: Michael Lewis
Wysłano: wtorek, 13 sierpnia 2019 00:37
Do: David Rowley
DW: Piotr Włodarczyk; MichaelDBA; Piotr Włodarczyk; pgsql-performance@lists.postgresql.org
Temat: Re: Planner performance in partitions
Was there a reason to exceed 100-500 partitions in real life that pushed you to do this test? Is there some issue you see when using 100 partitions that is solved or reduced in severity by increasing to 1200 or 6000 partitions?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: piotrwlodarczyk89@gmail.com, mlewis@entrata.com, david.rowley@2ndquadrant.com, MichaelDBA@sqlexec.com, piotr.wlodarczyk@gnb.pl, pgsql-performance@lists.postgresql.org
Subject: Re: ODP: Planner performance in partitions
In-Reply-To: <5d5258b0.1c69fb81.3d155.d4f8@mx.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox