From: | Heikki Linnakangas <hlinnaka(at)iki(dot)fi> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: FSM versus GIN pending list bloat |
Date: | 2015-08-04 07:18:22 |
Message-ID: | 55C0673E.40302@iki.fi |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 08/04/2015 08:03 AM, Jeff Janes wrote:
> For a GIN index with fastupdate turned on, both the user backends and
> autoanalyze routine will clear out the pending list, pushing the entries
> into the normal index structure and deleting the pages used by the pending
> list. But those deleted pages will not get added to the freespace map
> until a vacuum is done. This leads to horrible bloat on insert only
> tables, as it is never vacuumed and so the pending list space is never
> reused. And the pending list is very inefficient in space usage to start
> with, even compared to the old style posting lists and especially compared
> to the new compressed ones. (If they were aggressively recycled, this
> inefficient use wouldn't be much of a problem.)
Good point.
> The attached proof of concept patch greatly improves the bloat for both the
> insert and the update cases. You need to turn on both features: adding the
> pages to fsm, and vacuuming the fsm, to get the benefit (so JJ_GIN=3). The
> first of those two things could probably be adopted for real, but the
> second probably is not acceptable. What is the right way to do this?
> Could a variant of RecordFreeIndexPage bubble the free space up the map
> immediately rather than waiting for a vacuum? It would only have to move
> up until it found a page with freespace already recorded in it, which the
> vast majority of the time would mean observing up one level and then not
> writing to it, assuming the pending list pages remain well clustered.
Yep, that sounds good.
- Heikki
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2015-08-04 07:20:39 | Re: WIP: SCRAM authentication |
Previous Message | Michael Paquier | 2015-08-04 06:41:16 | Re: tablecmds.c and lock hierarchy |