The annual Postgres upgrade you keep deferring

PostgreSQL ships a major version every autumn and has done for well over a decade. Each one is supported for five years. The arithmetic is simple and most organisations still get it wrong in the same direction.

the trap#

Five years of support sounds generous, so the upgrade never makes the roadmap. Then one of two things happens:

You reach end of life and have to do four or five major versions at once, under time pressure, with an accumulated set of behaviour changes nobody has read about.

You quietly lose years of free improvement. Postgres releases carry substantial planner, vacuum and I/O work every single year. A database three versions behind is slower than it needs to be, on hardware you are already paying for.

The teams that find upgrades painful are always the ones who defer them. The teams that upgrade every year find it boring, because one version of behaviour change is a changelog and four versions is a project.

what to actually do#

Read one document: the release notes' incompatibilities section. Not the feature list — the "Migration to Version N" section at the top. It is usually under a page and it is the only part that can hurt you.

Test on a copy of production, at production size. Restore a real dump, run your application's test suite against it, and run your slowest queries. Planner changes are the main source of surprise, and they only show up with real statistics and real data volume.

Check your extensions first. This is the most common blocker. Every extension must have a build for the target version, and if you depend on something niche, verify before you plan anything else.

Use logical replication for anything where downtime matters. Replicate old to new, let it catch up, cut over. pg_upgrade --link is fast and requires a maintenance window; logical replication is more setup and needs seconds.

Run ANALYZE immediately after. Modern pg_upgrade carries statistics across, which removed the classic post-upgrade outage where a statistics-free database planned everything terribly. Verify it happened rather than assuming.

the operational habit worth adopting#

Put it in the calendar: evaluate in October, upgrade in the first quarter.

That gives the release a few point versions to settle, keeps you at most one version behind, and turns the whole thing into a scheduled chore rather than an emergency. The same pattern works for any dependency with an annual cadence, and the calendar entry is what makes it happen — nobody ever gets around to it otherwise.

the version everyone is actually on#

Run this and find out where you really are:

SELECT version();
SELECT name, setting FROM pg_settings WHERE name IN
  ('server_version_num', 'shared_buffers', 'effective_cache_size',
   'work_mem', 'max_connections', 'effective_io_concurrency');

While you are there: max_connections is almost certainly too high and effective_io_concurrency is almost certainly too low for SSD storage. Both defaults date from an era of different hardware, and both are one-line changes with measurable effects.

The upgrade is the boring part. The settings nobody has revisited since the database was provisioned are usually the bigger win.