In this lesson, you will learn how PostgreSQL manages the lifecycle of cached execution plans and why they are discarded when the underlying schema evolves.
The Lifecycle of a Cached Plan
When you execute a prepared statement (using PREPARE) or a parameterized query in a long-lived session, PostgreSQL doesn't want to re-parse, re-analyze, and re-optimize the query every time. Instead, it stores the resulting execution plan in a private memory cache associated with your session.
This cache allows the database to skip the expensive "Planner/Optimizer Pipeline" stages for subsequent executions of the same query. However, this optimization introduces a danger: what happens if the table structure changes while a plan is cached?
Triggering Invalidation
PostgreSQL uses a "dependency tracking" mechanism to ensure that cached plans don't become stale. Every plan maintains a list of dependencies on the database objects it touches—such as tables, indexes, or custom types.
If you perform a DDL operation that alters these objects, the system triggers a cache invalidation. For example:
-- Session creates a cached plan based on the current schema
PREPARE my_plan AS SELECT * FROM inventory WHERE id = $1;
-- If you alter the table in another session:
ALTER TABLE inventory ADD COLUMN last_updated TIMESTAMP;
When the ALTER TABLE command executes, the database marks the inventory table as having undergone a structural change. The system then invalidates all cached plans that depend on that specific OID (Object Identifier). The next time you attempt to run EXECUTE my_plan, the engine detects the invalidation flag, discards the old plan, and triggers a full re-planning process to account for the new column and updated statistics.
The Cost of Invalidation
While this ensures data integrity and correctness, frequent schema changes in a high-concurrency environment can lead to "plan churn." If your application constantly modifies schemas, prepared statements will be frequently invalidated, forcing the optimizer to reconstruct plans repeatedly. This negates the performance benefits of using prepared statements and puts additional CPU load on the database.
Key Takeaway: Cached plans are tied to the specific structure of the objects they reference; any DDL modification to those objects forces the system to discard the cached plan and regenerate it during the next execution.