From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Indirect indexes
Date: Fri, 21 Oct 2016 20:04:10 -0300
Message-ID: <20161021230410.kyngu5dzssvi44wk@alvherre.pgsql> (raw)
In-Reply-To: <CA+TgmoajGNsjTg7B3KjdpuDEuaOiMXWoh6ewc9jkhuCuv2deFQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Robert Haas wrote:
> So, I think that this is a really promising direction, but also that
> you should try very hard to try to get out from under this 6-byte PK
> limitation. That seems really ugly, and in practice it probably means
> your PK is probably going to be limited to int4, which is kind of sad
> since it leaves people using int8 or text PKs out in the cold.
I think we could just add a new type, unsigned 6 byte int, specifically
for this purpose. Little in the way of operators, as it's pointless to
try to do arithmetic with object identifiers. (It'd be similar to UUID
in spirit, but I wouldn't try to do anything too smart to generate them.)
> I believe Claudio Freire is on to something when he suggests storing
> the PK in the index tuple; one could try to skip storing the TID, or
> always store it as all-zeroes.
That bloats the index a bit. But then, maybe that is fine ...
> The VACUUM problems seem fairly serious. It's true that these indexes
> will be less subject to bloat, because they only need updating when
> the PK or the indexed columns change, not when other indexed columns
> change. On the other hand, there's nothing to prevent a PK from being
> recycled for an unrelated tuple. We can guarantee that a TID won't be
> recycled until all index references to the TID are gone, but there's
> no such guarantee for a PK. AFAICT, that would mean that an indirect
> index would have to be viewed as unreliable: after looking up the PK,
> you'd *always* have to recheck that it actually matched the index
> qual.
Yes, recheck is always needed.
As for vacuum, I was thinking this morning that perhaps the answer to
that is just to not vacuum the index at all and instead rely on the
killtuple interface (which removes tuples during scan). So we don't
need to spend precious maint_work_mem space on a large list of PK
values.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, robertmhaas@gmail.com
Subject: Re: Indirect indexes
In-Reply-To: <20161021230410.kyngu5dzssvi44wk@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox