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@gf.microolap.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers