Content-Type: text/plain; charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\))
Subject: Re: Loading table with indexed jsonb field is stalling
From: Will Hartung <willhartung@gmail.com>
In-Reply-To: <806d4915-f05d-8e75-0a42-e11b48596ab5@gmail.com>
Date: Mon, 20 May 2019 14:48:05 -0700
Cc: pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <12E9FEDD-37E9-4D51-9BA7-94FF5039BB1B@gmail.com>
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>
To: Ron <ronljohnsonjr@gmail.com>
Precedence: bulk


> 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.

Then, one day, I wanted to look for something, and decided I=E2=80=99ll =
build the json index =E2=80=9Creal quick=E2=80=9D.

Being me, it wasn=E2=80=99t until a few days later I=E2=80=99m like =
=E2=80=9Cwhy is my machine busy, what=E2=80=99s that grinding sound=E2=80=9D=
 (I rarely hear my drive, most work is done on SSD).

Then I realized that the index was still being built, several days =
later.

=E2=80=9CHuh=E2=80=9D

And I left it again.

I was, again, not really paying attention, and didn=E2=80=99t 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 =E2=80=9Cho=
w far=E2=80=9D 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=E2=80=99s =
(albeit one is in a VM). It wasn=E2=80=99t 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=E2=80=99t and we recovered. But =
got me to thinking about poking at it again.

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

And here we are.