From: Justin Pryzby <pryzby@telsasoft.com>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Gunther <raj@gusw.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.
Date: Sun, 24 Feb 2019 16:43:17 -0600
Message-ID: <20190224224317.GL28750@telsasoft.com> (raw)
In-Reply-To: <CADkLM=cx5b_gEt9SMoZFiM57xvr_eMbp94QjmXcFhS0o3c-dFg@mail.gmail.com>
References: <33bae6e3-1c5a-77f0-ee99-408fbf0fbb83@gusw.net>
<CAKJS1f_kxF=3K=D8MXa2NwdycwKJsNqVXNXyeDWv3t9f3p6J2A@mail.gmail.com>
<c044fc83-4816-e846-e78c-b4d6961efb6f@gusw.net>
<CADkLM=cx5b_gEt9SMoZFiM57xvr_eMbp94QjmXcFhS0o3c-dFg@mail.gmail.com>
On Sun, Feb 24, 2019 at 04:34:34PM -0500, Corey Huinker wrote:
> I think your solution may be something like this:
> 1. Create a new table, same columns, partitioned on the pending column.
> 2. Rename your existing queue table old_queue to the partitioned table as a
> default partition.
> 3. Rename new table to queue
> 4. add old_queue as the default partition of queue
> 5. add a new partition for pending = true rows, set the fillfactor kind of
FYI, the "default partition" isn't just for various and sundry uncategorized
tuples (like a relkind='r' inheritence without any constraint). It's for
"tuples which are excluded by every other partition". And "row migration"
doesn't happen during "ALTER..ATTACH", only UPDATE. So you'll be unable to
attach a partition for pending=true if the default partition includes any such
rows:
|ERROR: updated partition constraint for default partition "t0" would be violated by some row
I think you'll need to schedule a maintenance window, create a new partitioned
heirarchy, and INSERT INTO queue SELECT * FROM old_queue, or similar.
Justin
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: pryzby@telsasoft.com, corey.huinker@gmail.com, raj@gusw.net
Subject: Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.
In-Reply-To: <20190224224317.GL28750@telsasoft.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