Re: GUC for cleanup indexes threshold.

Hello, Darafei.

You wrote:

DP> The following review has been posted through the commitfest application:
DP> make installcheck-world: tested, passed
DP> Implements feature: tested, passed
DP> Spec compliant: tested, passed
DP> Documentation: tested, passed

DP> We're using Postgres with this patch for some time.

DP> In our use case we've got a quickly growing large table with events from our users.
DP> Table has a structure of (user_id, ts, <event data>). Events are
DP> append only, each user generates events in small predictable time frame, mostly each second.
DP> From time to time we need to read this table in fashion of WHERE
DP> ts BETWEEN a AND b AND user_id=c.
DP> Such query leads to enormous amount of seeks, as records of each
DP> user are scattered across relation and there are no pages that
DP> contain two events from same user.

DP> To fight it, we created a btree index on (user_id, ts,
DP> <event_data>). Plan switched to index only scans, but heap fetches
DP> and execution times were still the same.
DP> Manual
DP> We noticed that autovacuum skips scanning the relation and freezing the Visibility Map.

DP> We started frequently performing VACUUM manually on the relation.
DP> This helped with freezing the Visibility Map.
DP> However, we found out that VACUUM makes a full scan over the index.
DP> As index does not fit into memory, this means that each run
DP> flushes all the disk caches and eats up Amazon IOPS credits.

DP> With this patch behavior is much better for us - VACUUM finishes real quick.

DP> As a future improvement, a similar improvement for other index types will be useful.
DP> After it happens, I'm looking forward to autovacuum kicking in on
DP> append-only tables, to freeze the Visibility Map.

DP> The new status of this patch is: Ready for Committer

Seems like, we may also going to hit it and it would be cool this
vacuum issue solved for next PG version.

--
With best wishes,
Pavel mailto:pavel(at)gf(dot)microolap(dot)com