A staging environment is a pre-production system used to test proposed database changes before they reach production. For PostgreSQL upgrades, a successful upgrade is not proof that an application will behave or perform the same way: optimizer and behavior changes can produce different execution plans and query costs. Make staging resemble production as closely as feasible, especially in data and workload characteristics. section 10.2 section 11.2
The tempting mistake is to upgrade after a small functional check, then assume production is safe. Instead, test critical queries and known edge cases against realistic data, and run the workload under load. A stress test can reveal overall performance effects that isolated query checks miss. section 10.2 section 10.6
Use staging as a controlled release gate:
- Read release notes for every PostgreSQL version between the current and target versions; account for optimizations and breaking changes before diagnosing them under production pressure. section 10.6
- Run application code, critical queries, and established edge cases with production-like data rather than a convenient small sample. section 10.2
- Exercise the proposed change under load, then inspect whether query plans or aggregate performance have changed materially. section 10.2
- Test a solution in the production-like environment before rolling it out deliberately, leaving time to correct problems rather than escalating them. section 11.2
Testing an upgraded query plan
A complex invoice-reminder query can be run with EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) in staging after an upgrade. Its actual plan exposes choices such as parallel scans, nested loops, hash joins, sorting, and aggregation. Compare observed behavior under realistic conditions instead of treating successful execution as sufficient evidence of safety. section 10.2
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
WITH unp AS (
SELECT id, customer c, order_group AS og
FROM erp.invoices
WHERE paid = false
),
ni AS (
SELECT og.id
FROM erp.order_groups og
JOIN erp.orders o ON o.order_group = og.id
WHERE o.item IS NULL
)
SELECT DISTINCT email
FROM erp.customer_contact_details ccd
JOIN unp ON unp.c = ccd.id
JOIN ni ON ni.id = unp.og
JOIN erp.sent_emails se ON se.invoice = unp.id
AND se.email_type = 'Invoice reminder';
Use this representative multi-join query as a staging workload for collecting an actual execution plan under the target PostgreSQL release. section 10.2
For upgrade dry runs, pg_upgrade can support the testing process. Staging also supports proactive changes beyond upgrades: review code and access patterns, test a proposed solution in a production-like setting, then deploy at a controlled pace. section 10.2 section 11.2
