Date: Sun, 24 Feb 2019 16:43:17 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Gunther <raj@gusw.net>, pgsql-performance@postgresql.org
Subject: Re: Massive parallel queue table causes index deterioration, but
 REINDEX fails with deadlocks.
Message-ID: <20190224224317.GL28750@telsasoft.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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CADkLM=cx5b_gEt9SMoZFiM57xvr_eMbp94QjmXcFhS0o3c-dFg@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

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