We currently support explicitly prepared statements (via Prepare) and automatic prepared statements (Max Auto Prepare). These mechanisms have the following drawbacks:

• Both these mechanisms require going through an internal caching mechanism inside Npgsql, where the prepared statement is constantly looked up by using the SQL as a key.
• There's an (admittedly exotic) edge case, where the same SQL with different parameter types cannot be prepared, since we only key by the SQL. Introducing the parameter type into the cache key would make things slightly slower for everyone, just to support something almost nobody needs.
• Explicit preparation isn't a good fit with data source (connectionless) commands, since it would require us to keep the command open once Prepare completes (to ensure that execution uses the same connection, and therefore executes prepared). That goes against the idea of connection-less commands, and also requires the command to be disposed, otherwise there's a connection leak (and we know DbCommand isn't always disposed). See Implement DbDataSource dotnet/runtime#70006 (comment) for a full discussion.

Now that we have NpgsqlDataSource, we can have an API to prepare SQL for all connections handed out by the data source. The user would provide a SQL and parameter types (actual API shape TBD), and the API would return an opaque prepared statement handle (in reality wrapping a PostgreSQL prepared statement name as a string). Then, when using an execution API on NpgsqlCommand, that handle could be set on the command, causing us to bypass all internal lookups. We may even be able to do something to perform type handler lookup based on the handle - especially if we make the type handlers shared across the connections of a data source.

/cc @NinoFloris