Clarification on the release notes of postgresql 12 regarding pg_upgrade

On Fri, 2019-10-04 at 10:00 -0300, Marcelo Lacerda wrote:

There are a few instances where the release notes seem to indicate
that the administrator should use pg_dump to upgrade a database so
that improvements on btree can be available.

Here are they:

1.

In new btree indexes, the maximum index entry length is reduced by

eight bytes, to improve handling of duplicate entries (Peter
Geoghegan)

This means that a REINDEX operation on an index pg_upgrade'd from a

previous release could potentially fail.

2.

Improve performance and space utilization of btree indexes with many

duplicates (Peter Geoghegan, Heikki Linnakangas)

...
Indexes pg_upgrade'd from previous releases will not have these

benefits.

3.

Allow multi-column btree indexes to be smaller (Peter Geoghegan,

Heikki Linnakangas)

...
Indexes pg_upgrade'd from previous releases will not have these

benefits.

My questions are:

1. Is this a current limitation of pg_upgrade that will be dealt
afterwards?

2. Are we going to see more of such cases were pg_upgrade leaves the
database incompatible with newer features.

3. What's the recommendation for administrators with databases that
are too large to be upgraded with pg_dump?

pg_upgrade doesn't touch the index data, so it cannot rewrite indexes
to take advantage of these improvements.

There is no incompatibility involved.

You can always REINDEX some indexes later.
Now that we have REINDEX CONCURRENTLY, it shouldn't hurt as much.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

But the problem in this case is probably this note:

This means that a REINDEX

<https://www.postgresql.org/docs/12/sql-reindex.html&gt; operation on an
index pg_upgrade'd from a previous release could potentially fail.

You can't REINDEX safely regarding that note.

pá 4. 10. 2019 v 16:06 odesílatel Laurenz Albe <laurenz.albe@cybertec.at>
napsal:

=?UTF-8?B?Sm9zZWYgxaBpbcOhbmVr?= <josef.simanek@gmail.com> writes:

But the problem in this case is probably this note:

This means that a REINDEX
<https://www.postgresql.org/docs/12/sql-reindex.html&gt; operation on an
index pg_upgrade'd from a previous release could potentially fail.

You can't REINDEX safely regarding that note.

Actually running into that problem is quite unlikely; and if you did
hit it, it'd just mean that the REINDEX fails, not that you have any
urgent problem to fix. I'd encourage you to just go ahead and REINDEX,
if you have indexes that could benefit from the other changes.

regards, tom lane

On Fri, Oct 4, 2019 at 9:09 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

You can't REINDEX safely regarding that note.

Actually running into that problem is quite unlikely; and if you did
hit it, it'd just mean that the REINDEX fails, not that you have any
urgent problem to fix. I'd encourage you to just go ahead and REINDEX,
if you have indexes that could benefit from the other changes.

Right. It is hard to imagine an application that evolved to fully rely
on the previous slightly higher limit, and cannot tolerate a reduction
in the limit by only 8 bytes. The limit applies to a tuple *after*
TOAST compression has been applied.

--
Peter Geoghegan

On Fri, Oct 4, 2019 at 09:43:31AM -0700, Peter Geoghegan wrote:

On Fri, Oct 4, 2019 at 9:09 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

You can't REINDEX safely regarding that note.

Actually running into that problem is quite unlikely; and if you did
hit it, it'd just mean that the REINDEX fails, not that you have any
urgent problem to fix. I'd encourage you to just go ahead and REINDEX,
if you have indexes that could benefit from the other changes.

Right. It is hard to imagine an application that evolved to fully rely
on the previous slightly higher limit, and cannot tolerate a reduction
in the limit by only 8 bytes. The limit applies to a tuple *after*
TOAST compression has been applied.

Right. Pg_upgrade is fast, but we don't want it limiting file format
changes that can improve Postgres. Allowing REINDEX to fix things is
the best of both worlds --- fast upgrades, and after some REINDEX-ing,
faster Postgres.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +