Re: Insert only table and size of GIN index JSONB field.

On Wed, May 4, 2016 at 1:39 AM, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> wrote:

>> This sounds like a known issue, fixed in 9.6, where the pages used for
>> the pending list do not eligible for recycling until the table is
>> vacuumed. Autovacuum does not run on insert only tables, so they just
>> accumulate as empty pages in the index.
>>
>> Cheers,
>>
>> Jeff
>
>
>
> Hi Jeff,
>
> Yes it's look like a cause.
> Is there any workaround possible for 9.5.2?
> At this moment I see palliative (because it doesn't reduce already bloated
> index) cure via performing manual vacuum on the table after each batch
> insert
> or very slow (for the large table) full scale fix via create new index/drop
> old index.

A full workaround would be turn off "fastupdate" on the index.

In my workloads, doing that doesn't even slow inserts down by a
meaningful amount, so now I just habitually turn it off.

If you can't do that, you could run manual vacuum repeatedly during
the bulk load so the pages can be recycled within the same batch,
although that might slow things down more than just turning fastupdate
off does.

Or, you could just live with the bloat. It is 15x when starting from
an empty table. But if you are doing repeated batch inserts which
don't each start with an empty table, it won't be nearly as bad (as
long as you vacuum in between) on a ratio basis. (And if you do
always start out with an empty table, you should build the index at
the end, not the beginning, of the inserts)

Cheers,

Jeff