Prepare statements plan invalidation in postgresql Postgresql caches plans for prepared statements and their subsequent executes. see:
https://www.postgresql.org/docs/current/sql-prepare.html
https://stackoverflow.com/questions/7142335/how-does-postgresql-cache-statements-and-data

The question I have is what will destroy/re-write/clear postgres has cached? Naturally, any DDL statement (such as drop, alter etc.) would do. But are there other things?
Plans are invalidated if any object that they use suffers &ldquo;cache invalidation&rdquo;. Compare this source comment:

 * Currently, we track exactly the dependencies of plans on relations and
 * user-defined functions.  On relcache invalidation events or pg_proc
 * syscache invalidation events, we invalidate just those plans that depend
 * on the particular object being modified.  (Note: this scheme assumes
 * that any table modification that requires replanning will generate a
 * relcache inval event.)  We also watch for inval events on certain other
 * system catalogs, such as pg_namespace; but for them, our response is
 * just to invalidate all plans.  We expect updates on those catalogs to
 * be infrequent enough that more-detailed tracking is not worth the effort.


In the case of tables:

git grep CacheInvalidateRelcache


on the PostgreSQL source, you can find which changes involving tables cause plans to be invalidated:


contraints are added, removed, modified or renamed
attaching or detaching a partition
an index is added or dropped
REINDEX
a table is added to or removed from a publication
CLUSTER and VACUUM (FULL)
a row level security policy is added, modified or dropped
an inheritance child is added
ALTER TABLE
a trigger is added, dropped, renamed, disabled or enabled
a query rewrite rule is added or removed
TRUNCATE


Other actions that lead to any such action will of course also invalidate plans.