PoC: true in-place major version upgrades
pg_upgrade has worked for many years, but it still has well-known
limitations, in particular:
- Does not scale with big schemas, leading to hours of downtime
- No PITR across major versions
I think it's time for some innovation. The attached series is a
"tracer bullet", _rough_, proof of concept of in-place upgrade. With
the exception of 0001/2, the patches are not quite ready for code
review, but it's far enough along to demonstrate and get feedback on
architecture and orchestration. An earlier prototype showed this
should be much faster for big-schema databases, but it's not fair to
measure yet since it's not fully functional.
I have fleshed out a start-to-finish demo in the attached v1-demo.txt.
To give you a preview, the orchestration basically goes like this
(prepare, swap, golive):
$NEW/pg_inplace_upgrade -D $DEMO/primary -b $OLD -B $NEW \
--standby 'host=/tmp port=6001 dbname=postgres' prepare
$NEW/pg_inplace_upgrade -D $DEMO/primary -b $OLD -B $NEW -l
$DEMO/primary.log swap
$NEW/pg_inplace_upgrade -D $DEMO/primary -b $OLD -B $NEW golive
$NEW/pg_inplace_upgrade -D $DEMO/standby -b $OLD -B $NEW -l
$DEMO/standby.log swap
Here's what it does in a nutshell:
The 'prepare' stage runs some checks, freezes the catalogs, and shuts
down. The shutdown checkpoint is flagged as an upgrade boundary. The
standby will replay up to the primary's shutdown checkpoint and pause.
The 'swap' stages start the new binary. On the standby, it first shuts
down the old binary. On the primary, the startup process 'takes over'
the server by converting pg_control to the new format and writing a
start checkpoint. In a new 'upgrade' mode, a postmaster child migrates
the catalogs. (In the PoC, the catalog layout and data don't actually
change, so this simply rewrites the old catalogs' rows unchanged into
new files. It builds new indexes, and arranges for the relmapper and
pg_class to find the new files.) The catalog writes are WAL-logged,
but WAL archiving, sending, and recycling are suppressed for the
duration.
Up until 'golive', rollback is possible. This entails unlinking newly
created files (including catalog relations and pg_xact segments) and
restoring e.g. the control file and relmapper.
The 'golive' stage writes a "go-live" checkpoint, and resumes normal
WAL streaming and archiving. Rollback is now impossible.
The 'swap' stage on the standby allows it to replay the new-version
WAL generated during the migration, so the standby's catalog migration
is just applying normal FPIs. After replay passes the go-live
checkpoint, the standby can accept connections again.
*** Catalogs (TODO)
While the PoC sidesteps this entirely for now, this is actually how I
started. About a year and half ago, I experimented with catalog header
annotations so that the usual couple hundred lines of Perl would
generate the code that transforms the catalog tuples. Earlier this
year, I turned that into a throwaway "offline upgrade" (i.e. no WAL)
prototype. That exercise indicated that this method could feasibly
handle all catalog schema changes since PG12. Example from pg_class:
```
/* # of tuples (not always up-to-date; -1 means "unknown") */
float4 reltuples BKI_DEFAULT(-1) CHANGEDVALUE(14,
FUNC(reltuples_zero_to_unknown));
[...]
/* # of all-frozen blocks (not always up-to-date) */
int32 relallfrozen BKI_DEFAULT(0) FIRSTVERSION(18, CONST(0));
```
This says:
- "when upgrading from before PG14, call this function", which changes
'0' to '-1', otherwise takes reltuples as-is.
- "when generating tuple descriptors, leave out relallfrozen for
versions before PG18, and fill in a '0' for the new tuples"
There were some more difficult cases, but not many. The most difficult
aspect is node trees. I don't think it's possible to transform those
on-the-fly. My offline prototype dumped the deparsed node trees into
SQL in the usual way, which were then "recompiled" to node trees into
the new version's catalogs.
It should be possible for the catalog migration to bring the planner
statistics over as a matter of course.
*** Next steps
In order for in-place upgrade to actually work as a feature,
1) the old version needs to have 0001/2, so in practice that means the
upgraded-from version is PG20 at the earliest, with the rest of the
effort for PG21+.
2) At some point, catalog OIDs (e.g. between 10k and 12k) that now
vary between versions should be made immutable. Technically, it's not
necessary, but it makes it a lot nicer.
To justify committing those first two pieces during the PG20 cycle,
I'll need to progress the entire series to the point where there are
no serious architectural questions, and can be plausibly finished in
PG21+ with "just work". That will be a challenge, but I think it's
possible.
*** AI Disclaimer
I have used Claude extensively in the course of developing this. I was
skeptical about use of agents in software development until fairly
recently, but when given the right adult supervision they can be a
*huge* aid for complex software projects. I've re-written parts by
hand, but there's a lot more to clean up and harden. There are
operational gotchas and flaws, some of them left there on-purpose --
making this prematurely robust would be distracting and not a good use
of time before the architecture settles. The more boilerplate-y parts
have not had much scrutiny at all, but the important functionality
has.
--
John Naylor
Amazon Web Services
Attachments:
t254296_1v1-demo.txttext/plain; charset=US-ASCII; name=v1-demo.txtDownload
v1-0016-pg_inplace_upgrade-Add-the-golive-command.patchapplication/x-patch; name=v1-0016-pg_inplace_upgrade-Add-the-golive-command.patchDownload+965-26
v1-0015-pg_inplace_upgrade-Add-the-rollback-command.patchapplication/x-patch; name=v1-0015-pg_inplace_upgrade-Add-the-rollback-command.patchDownload+707-6
v1-0013-Take-btree-support-procedures-from-the-index-rela.patchapplication/x-patch; name=v1-0013-Take-btree-support-procedures-from-the-index-rela.patchDownload+47-26
v1-0017-Cross-a-standby-to-the-new-version-of-an-in-place.patchapplication/x-patch; name=v1-0017-Cross-a-standby-to-the-new-version-of-an-in-place.patchDownload+1165-274
v1-0014-Migrate-the-catalogs-for-an-in-place-upgrade.patchapplication/x-patch; name=v1-0014-Migrate-the-catalogs-for-an-in-place-upgrade.patchDownload+1278-8
v1-0009-Infrastructure-for-migrating-catalogs.patchapplication/x-patch; name=v1-0009-Infrastructure-for-migrating-catalogs.patchDownload+1369-110
v1-0012-Export-the-btree-spool-interface-of-nbtsort.c.patchapplication/x-patch; name=v1-0012-Export-the-btree-spool-interface-of-nbtsort.c.patchDownload+37-10
v1-0008-Suppress-checkpoints-and-archiving-during-an-in-p.patchapplication/x-patch; name=v1-0008-Suppress-checkpoints-and-archiving-during-an-in-p.patchDownload+150-23
v1-0011-Generate-a-description-of-every-catalog-s-relatio.patchapplication/x-patch; name=v1-0011-Generate-a-description-of-every-catalog-s-relatio.patchDownload+278-10
v1-0010-pg_inplace_upgrade-Add-the-swap-command.patchapplication/x-patch; name=v1-0010-pg_inplace_upgrade-Add-the-swap-command.patchDownload+264-11
v1-0005-Add-pg_inplace_upgrade-the-program-that-drives-an.patchapplication/x-patch; name=v1-0005-Add-pg_inplace_upgrade-the-program-that-drives-an.patchDownload+1963-1
v1-0003-Declare-pg_inplace_upgrade_golive-in-the-old-vers.patchapplication/x-patch; name=v1-0003-Declare-pg_inplace_upgrade_golive-in-the-old-vers.patchDownload+22-1
v1-0004-Stamp-HEAD-as-21devel.patchapplication/x-patch; name=v1-0004-Stamp-HEAD-as-21devel.patchDownload+13-14
v1-0006-Mock-bumping-magic-numbers-for-mechanically-testi.patchapplication/x-patch; name=v1-0006-Mock-bumping-magic-numbers-for-mechanically-testi.patchDownload+172-5
v1-0007-Take-over-the-primary-at-startup-for-an-in-place-.patchapplication/x-patch; name=v1-0007-Take-over-the-primary-at-startup-for-an-in-place-.patchDownload+1012-47
v1-0002-Pause-archive-recovery-at-an-upgrade-boundary.patchapplication/x-patch; name=v1-0002-Pause-archive-recovery-at-an-upgrade-boundary.patchDownload+446-6
v1-0001-Allow-marking-a-shutdown-checkpoint-as-an-upgrade.patchapplication/x-patch; name=v1-0001-Allow-marking-a-shutdown-checkpoint-as-an-upgrade.patchDownload+411-4
On Wed, Oct 21, 2026 at 07:00:10AM +0700, John Naylor wrote:
*** Catalogs (TODO)
While the PoC sidesteps this entirely for now, this is actually how I
started. About a year and half ago, I experimented with catalog header
annotations so that the usual couple hundred lines of Perl would
generate the code that transforms the catalog tuples. Earlier this
year, I turned that into a throwaway "offline upgrade" (i.e. no WAL)
prototype. That exercise indicated that this method could feasibly
handle all catalog schema changes since PG12. Example from pg_class:```
/* # of tuples (not always up-to-date; -1 means "unknown") */
float4 reltuples BKI_DEFAULT(-1) CHANGEDVALUE(14,
FUNC(reltuples_zero_to_unknown));[...]
/* # of all-frozen blocks (not always up-to-date) */
int32 relallfrozen BKI_DEFAULT(0) FIRSTVERSION(18, CONST(0));
```
As I understand it, instead of recreating the catalogs from pg_dump
--schema-only, you are modifying the actual catalogs. Yes, this will
certainly be faster than what we do now. I never tried this since it
would require changes/testing for each major release, and I didn't think
that would be reasonable. It might now be possible given our resources.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
John Naylor <johncnaylorls@gmail.com> writes:
*** Catalogs (TODO)
While the PoC sidesteps this entirely for now, this is actually how I
started. About a year and half ago, I experimented with catalog header
annotations so that the usual couple hundred lines of Perl would
generate the code that transforms the catalog tuples. Earlier this
year, I turned that into a throwaway "offline upgrade" (i.e. no WAL)
prototype. That exercise indicated that this method could feasibly
handle all catalog schema changes since PG12. Example from pg_class:
I'm just really, really skeptical that this is practical for
anything except the most trivial changes (such as your two examples).
You already mentioned that stored expressions (node trees) won't work,
but what about cases where we move information across catalogs, or
create things that just weren't there before? A handy example is the
recent changes to make pg_constraint entries for column NOT NULL
constraints. Adding pg_depend entries for linkages where we didn't
formerly require one seems like another plausible-and-hard-to-implement
case. So I'm afraid where this is going to lead us is either a
straitjacket on what we can change in the catalog representation,
or an additional requirement for even more complex, hard-to-test code
that has to be written in order to put forward such changes. We
built pg_upgrade the way we did precisely to avoid such problems,
and I don't want to give that up.
2) At some point, catalog OIDs (e.g. between 10k and 12k) that now
vary between versions should be made immutable. Technically, it's not
necessary, but it makes it a lot nicer.
The core reason why that's not so today is that the set of
pg_collation entries made by initdb depends on the platform. I don't
think you're going to be able to make that not be the case, at least
not without significant downsides.
regards, tom lane
On Sat, Oct 24, 2026 at 9:21 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'm just really, really skeptical that this is practical for
anything except the most trivial changes (such as your two examples).
You already mentioned that stored expressions (node trees) won't work,
but what about cases where we move information across catalogs, or
create things that just weren't there before? A handy example is the
recent changes to make pg_constraint entries for column NOT NULL
constraints. Adding pg_depend entries for linkages where we didn't
formerly require one seems like another plausible-and-hard-to-implement
case. So I'm afraid where this is going to lead us is either a
straitjacket on what we can change in the catalog representation,
or an additional requirement for even more complex, hard-to-test code
that has to be written in order to put forward such changes. We
built pg_upgrade the way we did precisely to avoid such problems,
and I don't want to give that up.
I agree. I actually had an idea like what John has done here over a
decade ago, and I think for many cases it would work fine, and the
cost would be that we would have to write transformation scripts for
new major releases. Back then, I was thinking about relistemp ->
relpersistence, which would be easy, since true and false would map to
't' and 'p', leaving 'u' as a new value. But it's unclear how we could
handle more complex cases. For example, imagine we remove a function
and, in a particular user's install, there's an object with a
dependency on the removed object. Keeping the old object isn't
feasible, and implementing DROP CASCADE in the middle of pg_upgrade is
a bad idea both because having a second copy of that logic that only
runs in mid-upgrade would be terrifying and also because the resulting
behavior might quite possibly not be what the user wants.
Today, such cases result in pg_upgrade erroring out, which isn't
great, but it's not clear what would be better. I think that this new
system would have to mimic that behavior, and it probably could, but
every major release would need a matching pg_upgrade patch to teach it
about all the transformations that are required, and when they should
error out, and what would happen if a particular transformation is so
complicated that we can't figure out how to code it up in the middle
of a catalog rewrite? Tom's example of the NOT NULL changes is a good
one: we need to allocate OIDs and we can't know if any given OID is
available and I think we might need to worry about constraint names
colliding and I suspect there are more problems. I think we'll find
that being able to do transformations like this without a
fully-running system is extremely difficult, and I suspect that's
enough reason to give up on this idea.
I don't really like that conclusion, because I've always felt that
pg_upgrade is a pretty half-baked version of upgrade-in-place. Both
the rather high possibility of failures requiring manual correction
and also the time it takes to run are real downsides, and I would like
to have something better. But I suspect this proposal is not the way.
I do wonder if some more limited version of it might make sense. For
example, what if we could directly write pg_class tuples for all the
relations we're porting over, and then re-add the rest of the objects
using SQL? But even here I see problems: pg_class entries for
partitioned tables store node trees, and pg_index entries can as well,
and right there things start to get complicated, because those node
trees might not be valid any more on the new version. We could try to
rescue the idea by explicitly excluding the node trees from what gets
reconstructed directly and add back partitioning expressions and index
predicates and expressions as a second step that uses SQL as a medium,
and maybe that would work. But there might be other problems there,
and I'm not even sure how much of the actual speed problem it would
fix. Regardless, the question I'd ask is whether there is some more
restrained proposal that could still deliver enough of the benefit to
be worth doing.
--
Robert Haas
Databricks
On Sun, Oct 25, 2026 at 8:21 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
John Naylor <johncnaylorls@gmail.com> writes:
*** Catalogs (TODO)
While the PoC sidesteps this entirely for now, this is actually how I
started. About a year and half ago, I experimented with catalog header
annotations so that the usual couple hundred lines of Perl would
generate the code that transforms the catalog tuples. Earlier this
year, I turned that into a throwaway "offline upgrade" (i.e. no WAL)
prototype. That exercise indicated that this method could feasibly
handle all catalog schema changes since PG12. Example from pg_class:I'm just really, really skeptical that this is practical for
anything except the most trivial changes (such as your two examples).
You already mentioned that stored expressions (node trees) won't work,
but what about cases where we move information across catalogs, or
create things that just weren't there before? A handy example is the
recent changes to make pg_constraint entries for column NOT NULL
constraints.
Okay, I misjudged where my effort should have gone when deciding what
part was worth getting into halfway functional shape and describing in
some detail. That's also useful feedback.
Yeah, that's one of the messy cases. The offline prototype only fully
exercised upgrading from 18 to 20devel (only punting on
pg_subscription), which didn't have to deal with that.
Adding pg_depend entries for linkages where we didn't
formerly require one seems like another plausible-and-hard-to-implement
case.
The offline prototype had to rebuild rule and function dependencies
for node trees, and that much wasn't hard. If you're concerned about
discovering when we'd need to add new entries, that's sounds like a
valid concern.
2) At some point, catalog OIDs (e.g. between 10k and 12k) that now
vary between versions should be made immutable. Technically, it's not
necessary, but it makes it a lot nicer.The core reason why that's not so today is that the set of
pg_collation entries made by initdb depends on the platform. I don't
think you're going to be able to make that not be the case, at least
not without significant downsides.
The platform collation (and information schema etc) entries are >12k.
The immutability would apply only to genbki-assigned oids for pg_amop,
pg_amproc, pg_cast, pg_opclass, pg_auth_members. We do have control
over those.
--
John Naylor
Amazon Web Services
On Mon, Oct 26, 2026 at 7:29 PM Robert Haas <robertmhaas@gmail.com> wrote:
But it's unclear how we could
handle more complex cases. For example, imagine we remove a function
and, in a particular user's install, there's an object with a
dependency on the removed object. Keeping the old object isn't
feasible, and implementing DROP CASCADE in the middle of pg_upgrade is
a bad idea both because having a second copy of that logic that only
runs in mid-upgrade would be terrifying and also because the resulting
behavior might quite possibly not be what the user wants.Today, such cases result in pg_upgrade erroring out, which isn't
great, but it's not clear what would be better. I think that this new
system would have to mimic that behavior, and it probably could, but
I don't know offhand exactly what pg_upgrade's error is, but the
offline prototype throws
ERROR: "an old row references pg_proc XYZ, which no longer exists in
this version"
...and you'd need to roll back and fix it.
every major release would need a matching pg_upgrade patch to teach it
about all the transformations that are required, and when they should
error out,
Most of the time, it's just going to be new columns containing '0' or
'null', isn't it? But maybe I'm missing something, but it seems to me
that most catalogs' form or semantics don't change all that much. As I
mentioned to Tom, the only upgrade that the prototype tried with the
(almost) full regression database was 18 -> 20devel.
and what would happen if a particular transformation is so
complicated that we can't figure out how to code it up in the middle
of a catalog rewrite?
In the offline prototype, if you're hacking on a feature, catalog
columns can be marked TODO, which will just cause the emitted
transform function to throw ERROR when called during an upgrade, but
everything else would be unaffected. It shouldn't interfere with
writing the feature itself, until it's time to commit. If you don't
replace the TODO then you'd get failures in the cross version upgrade
tests. If you mean if we can't figure it out _at all_, well then yes
we have a problem.
Tom's example of the NOT NULL changes is a good
one: we need to allocate OIDs and we can't know if any given OID is
available and I think we might need to worry about constraint names
colliding and I suspect there are more problems. I think we'll find
that being able to do transformations like this without a
fully-running system is extremely difficult, and I suspect that's
enough reason to give up on this idea.
For complex cases like that, you'd have to have a separate step when
the system is fully running, and that's after the main row-by-row
catalog migration has completed (i.e. the merge with the new binary's
bootstrap data) and after the node trees have been updated. So you can
allocate OIDs and call ChooseConstraintName() etc. So, it'd be a pain
in the rear at times, but I don't think it's going to be the same
level of sadness that some of our users encounter with pg_upgrade.
Perhaps we can arrange for the relevant info to be gathered during the
prepare step on the old version, so here the orchestrator would create
a .sql file with a bunch of
SELECT pg_upgrade_create_constraint_notnull(foo_rel, bar_col, ...);
...which would do the right thing (and not validate). Maybe that's
what your next paragraph was hinting at?
I don't really like that conclusion, because I've always felt that
pg_upgrade is a pretty half-baked version of upgrade-in-place. Both
the rather high possibility of failures requiring manual correction
and also the time it takes to run are real downsides, and I would like
to have something better. But I suspect this proposal is not the way.
I do wonder if some more limited version of it might make sense. For
example, what if we could directly write pg_class tuples for all the
relations we're porting over, and then re-add the rest of the objects
using SQL? But even here I see problems: pg_class entries for
partitioned tables store node trees, and pg_index entries can as well,
and right there things start to get complicated, because those node
trees might not be valid any more on the new version. We could try to
rescue the idea by explicitly excluding the node trees from what gets
reconstructed directly and add back partitioning expressions and index
predicates and expressions as a second step that uses SQL as a medium,
and maybe that would work.
That's pretty much what the offline prototype did for node trees. The
node trees are deparsed into SQL and saved to disk during the prepare
phase on the old version, and "recompiled" back into new-version node
trees in the new catalogs. It's not without challenges, I'll admit,
but it already works.
I don't really like "re-add the rest of the objects using SQL" as a
starting point, since that sounds like re-implementing most of dump
and restore, which is where the performance problems come from, but
SQL could be an escape hatch.
--
John Naylor
Amazon Web Services
On Wed, Oct 28, 2026 at 9:55 AM John Naylor <johncnaylorls@gmail.com> wrote:
every major release would need a matching pg_upgrade patch to teach it
about all the transformations that are required, and when they should
error out,Most of the time, it's just going to be new columns containing '0' or
'null', isn't it? But maybe I'm missing something, but it seems to me
that most catalogs' form or semantics don't change all that much. As I
mentioned to Tom, the only upgrade that the prototype tried with the
(almost) full regression database was 18 -> 20devel.
Yeah, I think most of the time it is, but I suspect it will take only
a few nasty counterexamples to ruin your day. If you can make this
work with every catalog change we've had in the last, idk, 7 years,
then maybe it's worth considering, but I have a feeling that there are
going to be some where it's pretty unpleasant. I'm willing to be
proven wrong.
For complex cases like that, you'd have to have a separate step when
the system is fully running, and that's after the main row-by-row
catalog migration has completed (i.e. the merge with the new binary's
bootstrap data) and after the node trees have been updated. So you can
allocate OIDs and call ChooseConstraintName() etc. So, it'd be a pain
in the rear at times, but I don't think it's going to be the same
level of sadness that some of our users encounter with pg_upgrade.
I think this only works if you can guarantee that this extra step
succeeds. Otherwise you'd have to roll back after starting the new
system.
--
Robert Haas
Databricks
On Mon, Oct 26, 2026 at 08:29:01AM -0400, Robert Haas wrote:
I don't really like that conclusion, because I've always felt that
pg_upgrade is a pretty half-baked version of upgrade-in-place. Both
the rather high possibility of failures requiring manual correction
and also the time it takes to run are real downsides, and I would like
to have something better. But I suspect this proposal is not the way.
I do wonder if some more limited version of it might make sense.
I'm a bit hesitant to chime in because I don't want to derail John's
thread, but pg_upgrade downtime is something I've thought about and worked
on a lot over the years. I've witnessed or at least heard about many
attempts at $subject, and thus far I'm not convinced it's viable. Sure,
it's just software and almost anything is possible, but in this case I
suspect it comes with a huge amount of complexity, ongoing maintenance
burden, and really weird trade-offs. Re-creating the catalogs with SQL
keeps things at least somewhat reasonable, at the expense of lots of
downtime for folks with big catalogs. (I should say that I would be very
happy for John to prove me wrong and get $subject committed.)
I've long wondered (and even prototyped at some point) if we can address
the downtime problem by doing the dump and restore while the old cluster is
running. The restore specifically contributes the majority of the
downtime, so allowing folks to use the old cluster up until the new
cluster's schema is ready would be a huge win. We'd need to block most
catalog changes (except for perhaps a few crucial ones like relfrozenxid
updates), so users wouldn't be able to run DDL commands, but that still
seems substantially better to me than being able to run _no_ commands.
Maybe that's similarly complicated and unmaintainable, but ISTM that it
could be a good incremental step that helps minimize downtime while
retaining most of the battle-tested infrastructure of pg_upgrade.
--
nathan
On Fri, Oct 30, 2026 at 12:02 AM Robert Haas <robertmhaas@gmail.com> wrote:
On Wed, Oct 28, 2026 at 9:55 AM John Naylor <johncnaylorls@gmail.com> wrote:
Yeah, I think most of the time it is, but I suspect it will take only
a few nasty counterexamples to ruin your day. If you can make this
work with every catalog change we've had in the last, idk, 7 years,
then maybe it's worth considering, but I have a feeling that there are
going to be some where it's pretty unpleasant. I'm willing to be
proven wrong.
The last 7 years of change has a variety of different problems, so
it's a good demo to do. As you say, though, the past does not
guarantee the future.
For complex cases like that, you'd have to have a separate step when
the system is fully running, and that's after the main row-by-row
catalog migration has completed (i.e. the merge with the new binary's
bootstrap data) and after the node trees have been updated. So you can
allocate OIDs and call ChooseConstraintName() etc. So, it'd be a pain
in the rear at times, but I don't think it's going to be the same
level of sadness that some of our users encounter with pg_upgrade.I think this only works if you can guarantee that this extra step
succeeds. Otherwise you'd have to roll back after starting the new
system.
Okay, here's an idea, continuing with the not-null constraint example:
The old way up to version N is a v1 constraint. The N+1 release that
introduces the v2 constraint must still recognize the v1 constraint,
but all new constraints will be created as v2 constraints. A future
release, at least N+2, can drop support for v1 constraints. When the
user tries to upgrade off of N+1, the check step complains "The system
still has v1 constraints. These must be converted to v2 constraints",
and we would have some documented method to do that. This way, complex
conversions steps don't need to be happen during an already stressful
downtime window and in this case can probably just be DROP and CREATE
statements. It has a downside that it puts more responsibility on the
user, not to mention the development window with excess complexity,
but it would be less risky if complex transforms are not done
on-the-fly.
--
John Naylor
Amazon Web Services
On Fri, Oct 30, 2026 at 2:00 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
On Mon, Oct 26, 2026 at 08:29:01AM -0400, Robert Haas wrote:
I don't really like that conclusion, because I've always felt that
pg_upgrade is a pretty half-baked version of upgrade-in-place. Both
the rather high possibility of failures requiring manual correction
and also the time it takes to run are real downsides, and I would like
to have something better. But I suspect this proposal is not the way.
I do wonder if some more limited version of it might make sense.I'm a bit hesitant to chime in because I don't want to derail John's
thread, but pg_upgrade downtime is something I've thought about and worked
on a lot over the years. I've witnessed or at least heard about many
attempts at $subject, and thus far I'm not convinced it's viable. Sure,
Well, I didn't get to where I am today by assuming if others failed
then it's not worth trying.
it's just software and almost anything is possible, but in this case I
suspect it comes with a huge amount of complexity, ongoing maintenance
burden, and really weird trade-offs.
And yet, many DBMS's are able to do it, so any theory about how
impossibly hard it is has to explain that fact. It should be table
stakes for a DBMS that aspires to be enterprise-grade.
It is non-trivial, to be sure. And I don't claim to have all the answers yet.
I've long wondered (and even prototyped at some point) if we can address
the downtime problem by doing the dump and restore while the old cluster is
running. The restore specifically contributes the majority of the
downtime, so allowing folks to use the old cluster up until the new
cluster's schema is ready would be a huge win. We'd need to block most
catalog changes (except for perhaps a few crucial ones like relfrozenxid
updates), so users wouldn't be able to run DDL commands, but that still
seems substantially better to me than being able to run _no_ commands.Maybe that's similarly complicated and unmaintainable, but ISTM that it
could be a good incremental step that helps minimize downtime while
retaining most of the battle-tested infrastructure of pg_upgrade.
That might very well be a low-risk practical improvement. The
benefit/effort ratio isn't enough for me to want to work in that
direction, but I certainly wouldn't discourage anyone else from
pursuing it.
--
John Naylor
Amazon Web Services
On Mon, Nov 2, 2026 at 7:51 AM John Naylor <johncnaylorls@gmail.com> wrote:
Okay, here's an idea, continuing with the not-null constraint example:
The old way up to version N is a v1 constraint. The N+1 release that
introduces the v2 constraint must still recognize the v1 constraint,
but all new constraints will be created as v2 constraints. A future
release, at least N+2, can drop support for v1 constraints. When the
user tries to upgrade off of N+1, the check step complains "The system
still has v1 constraints. These must be converted to v2 constraints",
and we would have some documented method to do that. This way, complex
conversions steps don't need to be happen during an already stressful
downtime window and in this case can probably just be DROP and CREATE
statements. It has a downside that it puts more responsibility on the
user, not to mention the development window with excess complexity,
but it would be less risky if complex transforms are not done
on-the-fly.
The problem with this is it moves the burden from your patch to other
people's future patches. Obviously, sometimes that's the right
trade-off: for example, we agreed to support pg_upgrade many years
ago, even though that has greatly constrained our ability to evolve
the on-disk format. But we have to be convinced as a group that it's
not going to create more problems than it solves, and Tom basically
said in his reply upthread that he thinks precisely the opposite:
we'll end up not being able to do important things that we want to do
because of this feature. I think Bruce's reply suggests a similar
concern, and I think that's also what Nathan and I are worried about.
That's why I suggested a "last 7 years" test. If you go back through
all of those catalog changes and make all that stuff work, then we
know it's possible and about how much work it would take. If you find
that there are cases you can't make work, where something like the
above would be needed, then you could work out what the old release
would have needed to do and what the new release would have needed to
do and code it up, and then we'll have an approximate idea about how
much of a problem this feature would be have created if we'd added it
years ago, and everyone can then compare that to what they think the
benefit of the feature will be. As we've both said, past performance
is no guarantee of future results, but it's often indicative.
I think it's pretty clear that nobody who has responded so far, other
than maybe you, is keen to give up the ability to make complex catalog
changes in future major releases. So for this effort to succeed, I
think you need to show that it will still be possible and the effort
won't be disproportionate. I think the pg_constraint sketch you give
above is a good example of what is going to make people nervous:
whatever benefits could be realized from the pg_constraint changes now
couldn't be realized until at least release N+2. Plus, the code for
release N+1 might have to be quite complicated to be able to
understand both catalog representations. So I doubt people are going
to be too enthusiastic about this design, but it's fair to point out
we may not yet have the full picture. If this is the only thing that
has happened in a long time that would have caused a serious issue,
maybe it would have been worth taking the hit. If history is littered
with such examples, it probably isn't. And then, maybe this isn't even
the best design possible for that particular case.
--
Robert Haas
Databricks
On Mon, Nov 02, 2026 at 07:57:02PM +0700, John Naylor wrote:
On Fri, Oct 30, 2026 at 2:00 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
it's just software and almost anything is possible, but in this case I
suspect it comes with a huge amount of complexity, ongoing maintenance
burden, and really weird trade-offs.And yet, many DBMS's are able to do it, so any theory about how
impossibly hard it is has to explain that fact.
As Tom alluded upthread, I think the main factor is that we (a relatively
small group of committers) have thus far been unwilling to take on the
maintenance cost and/or the restrictions on catalog changes that come with
it. I suspect this project becomes a whole lot more feasible if we can
find a way to minimize those.
It should be table stakes for a DBMS that aspires to be enterprise-grade.
Agreed. I think many, many users would be happy to have this feature.
--
nathan