Re: Loading table with indexed jsonb field is stalling

On 5/20/19 4:49 PM, Will Hartung wrote:
>
>> On May 20, 2019, at 4:39 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> So the issue is building the index(s) not loading the data, correct?
>
> Correct.
>
> Since I have been exploring this, I have not been able to successfully
> create a gin jsonb_path_ops index on a jsonb field either as a new index
> on a table with existing data, or as a pre-existing index on an empty
> table and loading the data in.
>
> The create index does not finish on the existing data, and loading data
> does not finish when the index pre-exists.

Well looks like you are down to Tom's suggestion of creating a test
case. Given that it seems confined to the jsonb field and corresponding
index, I would think that is all that is needed for the test case. Start
with some smaller subset, say 10,000 rows and work up till you start
seeing an issue.

>
> We currently have a 68M row table loaded in an Aurora instance on AWS (I
> do not know the instance/machine size), and the index has been building
> for 48 hours now.
>
> The pg_restore is currently working on that index:
>
>
> CREATE INDEX eis_ent_traits
>   ON public.eis_entity
>   USING gin
>   (traits jsonb_path_ops);
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com