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, <event data>). Events are append only, each user generates events in small
predictabletime 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
pagesthat contain two events from same user.
To fight it, we created a btree index on (user_id, ts, <event_data>). Plan switched to index only scans, but heap
fetchesand 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