compute_query_id defaults to auto because the PostgreSQL developers were cornered. PostgreSQL 14 moved query-identifier computation into the core server so that everything wanting a query id, pg_stat_statements, pg_stat_activity, EXPLAIN, and the logs, could share one canonical value instead of each inventing its own. That left the question of what the default should be, and both obvious answers were bad. Compute the id for everyone and you tax every backend with a hash it may never use; compute it for nobody and you silently break every monitoring stack and tuning guide that assumed query ids were present. auto is the way out: compute the id only when something that needs it asks.
The values are on, off, auto, and regress. auto computes a query id if and only if a loaded module requests one, and in practice that module is pg_stat_statements. on always computes it. off never does. regress behaves like auto but suppresses the id in EXPLAIN output so it doesn’t churn regression-test diffs: it has, shall we say, limited application. The context is superuser, and despite what half the setup guides imply, it does not require a restart. The restart those guides are remembering belongs to pg_stat_statements, which has to be loaded through shared_preload_libraries; compute_query_id itself you can change with a reload or a SET. Two different requirements that happen to appear in the same setup.
The reason to want any of this is correlation across views. The query_id shown against a running statement in pg_stat_activity is the same one aggregated in pg_stat_statements, the same one EXPLAIN (VERBOSE) prints as Query Identifier, and the same one %Q writes into your log lines. You can lift a query id off a backend that’s been running for ten minutes, look up its historical statistics, and grep it out of the logs, all keyed on one bigint (a signed one, so don’t be alarmed when half your query ids come out negative). Before PostgreSQL 14, pg_stat_statements computed an id that appeared nowhere else, so this is a real improvement and not just plumbing.
The trap in auto is that it does nothing by itself. Put %Q in your log_line_prefix, or build a dashboard on pg_stat_activity.query_id, without running a module that requests computation, and every id comes back empty or NULL. It looks broken. It isn’t; you simply never asked for the id to be computed, and auto took you at your word. The fix is to load pg_stat_statements or to set compute_query_id = on.
That second option is the one to reach for if you care about query ids in your logs or activity view independently of pg_stat_statements. Setting it to on states the intent outright and guarantees the ids are there regardless of which modules happen to be loaded, rather than leaving their existence as a side effect of someone else’s configuration. If you do run pg_stat_statements, auto is already correct and you can leave it; the explicit on just removes any doubt about why the ids exist.
The only argument for off is an extension that computes query ids its own way. Since only one identifier is ever calculated, the in-core one has to stand down so the extension’s can take over. Short of that, leave it at auto and promote it to on the moment you depend on the ids being present rather than merely available. When auto produces no ids, that isn’t a bug to chase; it’s auto doing exactly what it says.