Re: Visibility map and hint bits

On Thu, May 5, 2011 at 12:59 PM, Bruce Momjian <bruce@momjian.us> wrote:
> I wonder if we could fix both of these at the same time.  Once the
> visibility map is reliable, can we use that to avoid updating the hint
> bits on all rows on a page?

I don't think so.  There are two problems:

1. If there is a long-running transaction on the system, it will not
be possible to set PD_ALL_VISIBLE, but hint bits can still be set.  So
there could be a significant performance regression if we don't set
hint bits in that case.

2. Making the visibility map crash-safe will mean making setting hint
bits emit XLOG records, so it can't be done on Hot Standby servers at
all, and it's much more expensive than just setting a hint bit on the
master.

> For bulk loads, all the pages are going have the same xid and all be
> visible, so instead of writing the entire table, we just write the
> visibility map.
>
> I think the problem is that we have the PD_ALL_VISIBLE page flag, which
> requires a write of the page as well.  Could we get by with only the
> visibility bits and remove PD_ALL_VISIBLE?

In some ways, that would make things much simpler.  But to make that
work, every insert/update/delete to a page would have to pin the
visibility map page and clear PD_ALL_VISIBLE if appropriate, so it
might not be good from a performance standpoint, especially in
high-concurrency workloads.  Right now, if PD_ALL_VISIBLE isn't set,
we don't bother touching the visibility map page, which seems like a
possibly important optimization.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company