Subject Re: [Firebird-Architect] Transaction tagged indexes
Author Ann Harrison
> On Aug 22, 2015, at 5:08 AM, Dmitry Yemanov firebird2@... [Firebird-Architect] <> wrote:
>> Postgres went the other
>> way. They store their record versions oldest to newest (rather than
>> vice versa) and (IIRC) their indexes point to the version, not the
>> primary record.
> Correct. This adds the index maintenance overhead for updates/deletes,
> but allows faster GC without storage overhead for TIDs. Do you think
> this approach is worth adopting?

No, not at all. Storing record versions newest first requires shuffling back versions around, but allows storing deltas rather than whole record versions. That saves a lot of space. Firebird can also move back versions as necessary, which PGSQL can't - at least not off page. More important, changing a non-key field in FBB doesn't require any index changes at all. AFAIK, Postgres has to make an entry in each index on a table for each record versions. While their index GC may be simpler, they have to do a lot more of it.

> This does not solve the index coverage, however. As a workaround, PGSQL
> now uses the visibility bitmap to avoid fetching a data page if all its
> rows are guaranteed to be visible to everyone.

> Kinda compromise solution
> that surely works fine for the "old" data but unlikely to give any
> advantage for the hot data pages in the oltp load.

Right. And one more hotspot for page management.