Subject: Re: Loading table with indexed jsonb field is stalling
To: Will Hartung <willhartung@gmail.com>
Cc: pgsql-general@lists.postgresql.org
References: 
 <CAKMEDdxE95SC76wadMzrH454HpvCV4tOdEmuZRO=pwArEw6YVg@mail.gmail.com>
 <20505.1558202555@sss.pgh.pa.us>
 <90835D2B-1FBA-4F26-8157-7C831C24B266@gmail.com>
 <cdc049f0-1980-f5a8-726a-629345b41ef0@aklaver.com>
 <3A35A039-D8F5-4B40-9B41-42E70BEF2C2E@gmail.com>
 <806d4915-f05d-8e75-0a42-e11b48596ab5@gmail.com>
 <12E9FEDD-37E9-4D51-9BA7-94FF5039BB1B@gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <563e995f-d7b1-7e2f-c1f1-b00db7a18589@gmail.com>
Date: Mon, 20 May 2019 16:55:42 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.6.1
MIME-Version: 1.0
In-Reply-To: <12E9FEDD-37E9-4D51-9BA7-94FF5039BB1B@gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

On 5/20/19 4:48 PM, Will Hartung wrote:
>> On May 20, 2019, at 2:36 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>> I think you answered this earlier, but does the same stalling happen when indexes are dropped?
> No, the data loads fine.
>
> The way I originally stumbled upon this was that I had off loaded the data for some other kinds of testing.
>
> There are several indexes in production, but for my local testing, I only had one on the pk.

And it pathologically loads even when there's just a PK on the numeric field?

> Then, one day, I wanted to look for something, and decided I’ll build the json index “real quick”.
>
> Being me, it wasn’t until a few days later I’m like “why is my machine busy, what’s that grinding sound” (I rarely hear my drive, most work is done on SSD).
>
> Then I realized that the index was still being built, several days later.
>
> “Huh”
>
> And I left it again.
>
> I was, again, not really paying attention, and didn’t realize until 2 weeks after I had started that it was STILL running.
>
> At that point I killed it.
>
> Then I decided to break the file up and load it in chunks, see “how far” it was getting, etc. It did not take long for it to get stuck.
>
> I tried it on different versions of PG, now on different OS’s (albeit one is in a VM). It wasn’t a crisis, just a curiosity.
>
> But we had a scary event couple of weeks ago that may have forced us to reload the table, but, thankfully, didn’t and we recovered. But got me to thinking about poking at it again.

Drop all indexes, load data, recreate indexes?

> A colleague is trying to pg_restore our production DB for a conversion test to AWS Aurora, and it’s been stuck for 43 hours on 68M row loaded table. So, it’s systemic with our data, and I believe that we can not reload this table currently. Which is scary.

But pg_restore only creates indexes AFTER the data loads.

-- 
Angular momentum makes the world go 'round.