The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation:tested, passed
We're using Postgres with this patch for some time.
In our use case we've got a quickly growing large table with events from our
users.
Table has a structure of (user_id, ts, ). Events are append only,
each user generates events in small predictable time frame, mostly each second.
From time to time we need to read this table in fashion of WHERE ts BETWEEN a
AND b AND user_id=c.
Such query leads to enormous amount of seeks, as records of each user are
scattered across relation and there are no pages that contain two events from
same user.
To fight it, we created a btree index on (user_id, ts, ). Plan
switched to index only scans, but heap fetches and execution times were still
the same.
Manual
We noticed that autovacuum skips scanning the relation and freezing the
Visibility Map.
We started frequently performing VACUUM manually on the relation. This helped
with freezing the Visibility Map.
However, we found out that VACUUM makes a full scan over the index.
As index does not fit into memory, this means that each run flushes all the
disk caches and eats up Amazon IOPS credits.
With this patch behavior is much better for us - VACUUM finishes real quick.
As a future improvement, a similar improvement for other index types will be
useful.
After it happens, I'm looking forward to autovacuum kicking in on append-only
tables, to freeze the Visibility Map.
The new status of this patch is: Ready for Committer
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
