PostgreSQL 12

PostgreSQL 12 added stored generated columns, SQL/JSON path expressions and related JSON functions, and the ability to rebuild indexes with REINDEX CONCURRENTLY. It substantially expanded declarative partitioning, including improved partition pruning and support for foreign keys involving partitioned tables. Query planning changed in ways that can improve performance: eligible common table expressions are no longer always materialized and may be inlined, while JIT compilation and parallel-query capabilities were improved. Recovery configuration also changed: settings formerly kept in recovery.conf are handled through the main configuration and recovery signal files, which matters when carrying standby or recovery setups into later releases.

This cycle has reached end of life, so defects and security issues found in PostgreSQL 12 no longer receive upstream fixes. The practical exposure includes operating-system, TLS, authentication, driver, backup-tool, and extension compatibility becoming harder to maintain as surrounding software moves forward. Remaining on 12 can also defer performance and operational improvements available in newer PostgreSQL releases, while increasing the size and risk of the eventual major-version upgrade. Workloads that depend on CTE materialization behavior, partitioning semantics, generated columns, JSON path queries, custom extensions, or recovery configuration need particular testing because later releases can expose assumptions that were harmless on 12.

Plan a supported-target upgrade rather than treating PostgreSQL 12 as a patching exercise. Inventory databases, extensions, client drivers, backup and monitoring agents, replication topology, authentication settings, collations, and operating-system dependencies; verify that every required extension has a compatible build for the target release. Build a representative test environment, restore or copy production-like data, and run application, reporting, maintenance, failover, and backup-and-restore tests against the target. Review queries that rely on CTEs as optimization fences, validate generated-column and JSON-path behavior, and translate any recovery.conf-based automation to the newer recovery configuration model. Choose between an in-place major upgrade using pg_upgrade and a logical migration based on acceptable downtime, data size, rollback needs, and replication requirements; physical streaming replication cannot directly span major PostgreSQL versions. Rehearse the chosen path, measure validation and switchover time, retain tested backups and a rollback plan, then schedule the production cutover only after the rehearsal meets the service objective.