Re: Rearchitecting for storage

Greetings,

* Matthew Pounsett (matt(at)conundrum(dot)com) wrote:
> On Fri, 19 Jul 2019 at 11:25, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
> > On 2019-07-19 10:41:31 -0400, Matthew Pounsett wrote:
> > > Okay. So I guess the short answer is no, nobody really knows how to
> > > judge how much space is required for an upgrade? :)
> >
> > As I understand it, a pg_upgrade --link uses only negligible extra
> > space. It duplicates a bit of householding information, but not your
> > data tables or indexes. Your 18 TB table will definitely not be duplicated
> > during the upgrade if you can use --link.
>
> The documentation for pg_upgrade --link says that the old copy is no longer
> usable,

That's not entirely true- the old copy is only no longer usable *after*
you've started the new version of the DB against those data files. If
you haven't started the new major version of PG yet, then you can go
back to using the old version against those files.

> which means it's modifying files that are linked.

No, it doesn't.

> If it were only
> modifying small housekeeping files, then it would be most efficient not to
> link those, which would keep both copies of the db usable.

The catalog tables *aren't* linked. Both copies of the DB are usable-
but only until you start the DB against one of the versions. Once
you've started either the old version or the new version, you can't
switch. If you started the old version, then you could do another
pg_upgrade, of course, but you can't use the new version as there will
have been changes made to the catalog tables and control file (which
aren't linked) that would have to be accounted for in the new version's
catalog by pg_upgrade.

> That seems
> incompatible with your suggestion that it doesn't need to modify the data
> files. Depending on how it goes about doing that, it could mean a
> significant short-term increase in storage requirements while the data is
> being converted.

No, that's not the case- link mode doesn't copy the data files, it just
rebuilds the catalog tables and fixes up things in the new database
cluster (clog, wal, et al, not the user data tables/indexes).

> Going back to our recent 'reindex database' attempt, pgsql does not
> necessarily do these things in the most storage-efficient manner; it seems
> entirely likely that it would choose to use links to duplicate the data
> directory, then create copies of each data file as it converts them over,
> then link that back to the original for an atomic replacement. That could
> eat up a HUGE amount of storage during the conversion process without the
> start and end sizes being very different at all.

No, that isn't how pg_upgrade works.

Thanks,

Stephen