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:pa...@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
