pdo_pgsql: DISCARD ALL on persistent connection cleanup drops PgBouncer's prepared statements (+52% CPU per request) #24461

Description

Since 19deb91 (#20572; PHP-8.6 since 8.6.0alpha1, and master), pdo_pgsql runs DISCARD ALL in its persistent_shutdown callback, which PDO calls when a PDO object on a persistent connection is freed. Under PHP-FPM that happens no later than request shutdown, so every request that uses a persistent PDO connection resets the session. #20572 lists leftover prepared statements among the session state it clears, together with settings, temporary objects and advisory locks, and the review noted that DISCARD ALL includes DEALLOCATE ALL.

Behind PgBouncer in transaction pooling, this has a large cost. With max_prepared_statements > 0 (200 by default since PgBouncer 1.24), PgBouncer tracks each client's protocol-level prepared statements and keeps them prepared on its server connections across transactions; DISCARD ALL and DEALLOCATE ALL clear the statements it tracks for the client, and DISCARD PLANS drops the plans of the ones that stay. So prepared statements are mostly not reused across requests: PostgreSQL parses them again and plans them again.

I measured the pgsql_driver.c part of the commit applied to PHP 8.5.11 against 8.5.11: PHP-FPM (32 children, persistent PDO, native prepares), a JSON API with four statements per request, PostgreSQL 17.11, PgBouncer 1.25.1 (transaction pooling, max_prepared_statements = 200), 2000 req/s, 5 alternating passes, AMD Ryzen 9 5950X. CPU per request of the stack (nginx, PHP-FPM, PgBouncer) and of PostgreSQL, medians; the change is the median of the per-pass ratios to 8.5.11:

PgBouncer's own counters confirm repeated preparation on the server (a separate diagnostic run over 2000 requests): PgBouncer sent 55 Parse messages to PostgreSQL with 8.5.11 and 6,649 with DISCARD ALL, 84% of the same 7,934 Parse messages from the clients. Discarding the cached plans is the other large part: a reset that keeps the statements but runs DISCARD PLANS still adds 24.6% (below).

A narrower reset avoids most of this cost, but it keeps prepared statements and their plans. It runs the parts of DISCARD ALL other than DEALLOCATE ALL and DISCARD PLANS in one PQexec:

CLOSE ALL; SET SESSION AUTHORIZATION DEFAULT; RESET ALL; UNLISTEN *; SELECT pg_advisory_unlock_all(); DISCARD TEMP; DISCARD SEQUENCES

This adds 5.9% CPU per request behind PgBouncer and 4.1% directly on the same setup; with DISCARD PLANS added (statements kept, plans dropped), 24.6% behind PgBouncer and 3.9% directly. In a smoke test on one persistent connection, the narrower reset resets a setting (application_name), drops temporary tables, releases advisory locks and removes LISTEN, as DISCARD ALL does. Unlike DISCARD ALL, it keeps statements the application prepared with SQL PREPARE and the cached plans of prepared statements; I have not evaluated what that means across requests when roles, search_path or the schema change. Sent in one PQexec call, the commands run as one implicit transaction, so a failing command stops the rest; the result is ignored, as in the commit, so error handling would also need to be addressed. Statements prepared by PDOStatement are closed when the statement object is destroyed in any case, and ext/pgsql sends RESET ALL when it reuses a persistent connection, which also keeps prepared statements.

I suggest reconsidering the unconditional DISCARD ALL: behind PgBouncer, keeping prepared statements and their plans across requests matters for CPU, and which session state must be reset between requests needs discussion.

Patches, data, PgBouncer counters, scripts and the outputs of the session check: https://github.com/ArtUkrainskiy/php-src-bench/tree/cbf59e68b1c6e548f4d2a607d882248c87d057a5/reports/pdo-pgsql-discard-all

Not tested: PHP-8.6 or master themselves (only the backport), session pooling in PgBouncer, worker runtimes with persistent connections, other SQL workloads (the cost of DISCARD PLANS depends on the plans).

cc @KentarouTakeda @devnexen @SakiTakamachi

PHP Version

PHP 8.6.0RC3 (PHP-8.6, since 8.6.0alpha1) and master (8.7.0-dev); measured as a backport of 19deb91002a to PHP 8.5.11.

Operating System

Ubuntu 26.04