On Wed, May 4, 2016 at 3:45 AM, Jeff Janes <jeff.janes@xxxxxxxxx> wrote:
On Tue, May 3, 2016 at 3:09 AM, Maxim Boguk <maxim.boguk@xxxxxxxxx> wrote:
> Hi,
>
> I started with empty table with index over
> custom_fields | jsonb
> field
> defined as:
> "idx_learners_custom_fields" gin (custom_fields)
> Globally gin_pending_list_limit set to 2MB.
> Database version is 9.5.2.
>
> Now question:
> If table populated with 1M records in single transaction then the final size
> of the GIN index is:
> 4265 MB
> but after I performed reindex index idx_learners_custom_fields;
> the index size had been reduced 15x to 295 MB.
>
> Is this behavior expected?
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.
--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.com/
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim.boguk@xxxxxxxxx
"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."
Senior Postgresql DBA
http://www.postgresql-consulting.com/
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim.boguk@xxxxxxxxx
"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."
