Thread: Clarification on the release notes of postgresql 12 regarding pg_upgrade
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?
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
Re: Clarification on the release notes of postgresql 12 regarding pg_upgrade
From
Josef Šimánek
Date:
But the problem in this case is probably this note:
> This means that a REINDEX 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:
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
=?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> 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
Re: Clarification on the release notes of postgresql 12 regarding pg_upgrade
From
Peter Geoghegan
Date:
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
Re: Clarification on the release notes of postgresql 12 regardingpg_upgrade
From
Bruce Momjian
Date:
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 +