Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Database Engineer practice bank has an additional 769 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
A company runs a microservices application on Cloud Run that connects to a Cloud SQL for PostgreSQL (Enterprise Plus edition) instance via Managed Connection Pooling on TCP port 6432. The application experiences frequent connection surges and exhibits these characteristics:
BEGIN ... COMMIT)asyncpg Python library, which relies on protocol-level named prepared statementsLISTEN, WITH HOLD cursors, temporary tables, or session-level advisory locksWhich pooling mode and configuration strategy should the database engineer implement?
pool_mode to session and keep max_prepared_statements at its default value of 0.Session pooling mode (session) assigns a dedicated backend PostgreSQL server connection to a client for the entire lifetime of the client's connection. The backend connection preserves all session-level state—such as SET variables, temporary tables, advisory locks, and prepared statements—and is only returned to the connection pool after the client explicitly disconnects.
session mode causes backend connection exhaustion (max_pool_size saturation), forcing new client requests into query_wait_timeout queues.LISTEN, WITH HOLD cursors, or advisory locks, so paying the resource penalty of dedicated session state brings no functional benefit.session mode behaves similarly to direct connections for concurrency scaling.max_prepared_statements scope: The max_prepared_statements setting applies specifically to transaction pooling mode, because backend connections in session mode already retain their own native prepared statements.This configuration would be appropriate if the application relied heavily on session-scoped PostgreSQL features—such as LISTEN/NOTIFY, SET/RESET session variables, WITH HOLD cursors, LOAD, or session-level advisory locks—that cannot operate safely when backend connections are shared across clients between transactions.
pool_mode to transaction, leave max_prepared_statements at 0, and use SQL-level PREPARE and DEALLOCATE statements.This configuration enables transaction pooling mode while leaving max_prepared_statements at its default value of 0 and attempting to manage prepared statements manually through SQL-level PREPARE and DEALLOCATE commands inside application queries.
max_prepared_statements to 0 explicitly turns off protocol-level named prepared statement support in Managed Connection Pooling. Because the asyncpg library relies on protocol-level prepared statements by default when connecting through the pooler on port 6432, queries will fail or require disabling statement caching.PREPARE/DEALLOCATE is unsupported: In transaction pooling mode, SQL-level PREPARE and DEALLOCATE statements are explicitly unsupported because the client can be routed to a different backend server connection on its next transaction, where a previously PREPAREd SQL statement does not exist.max_prepared_statements behavior: Although transaction is the default pool_mode, max_prepared_statements defaults to 0 to avoid unnecessary pooler memory allocation for workloads that do not use named prepared statements.transaction mode: Alongside PREPARE/DEALLOCATE, transaction mode does not support SET/RESET, LISTEN, WITH HOLD CURSOR, PRESERVE/DELETE ROW temporary tables, LOAD, or session-level advisory locks.max_prepared_statements is greater than 0, the pooler intercepts protocol-level parse/bind/execute messages and transparently ensures the prepared statement exists on whichever backend connection executes the transaction.Leaving max_prepared_statements at 0 in transaction mode would be appropriate if the application used simple parameterized or un-prepared queries (without SQL PREPARE/DEALLOCATE or asyncpg prepared statement caching) and wanted to minimize pooler memory overhead.
pool_mode to transaction and set max_prepared_statements to a value greater than 0 per connection pooler.Transaction pooling mode (transaction) is a connection multiplexing strategy in which an intermediary connection pooler assigns a backend PostgreSQL server connection to a client only for the duration of an active transaction. Once the transaction issues a COMMIT or ROLLBACK, the server connection is immediately returned to the pool for another client to reuse. Setting max_prepared_statements above 0 enables the pooler to track and map protocol-level named prepared statements across pooled backend connections.
BEGIN until COMMIT or ROLLBACK, preserving transactional atomicity and isolation across multiple SQL statements.asyncpg compatibility: By default, max_prepared_statements is 0, which disables prepared statement support. Raising max_prepared_statements to exceed the number of commonly used prepared statements allows asyncpg on TCP port 6432 to execute protocol-level prepared statements reliably.LISTEN, WITH HOLD cursors, temporary tables, and session-level advisory locks, it is unaffected by transaction mode restrictions.max_prepared_statements per pooler ensures each pooler caches statement mappings independently.max_prepared_statements slightly above the workload's active prepared statements balances query performance against pooler memory usage.Compared to session mode, transaction mode maximizes backend connection reuse for bursty serverless workloads. Compared to leaving max_prepared_statements at 0, explicitly enabling it prevents asyncpg runtime errors while retaining the scalability of transaction-level multiplexing.
pool_mode to statement so server connections return to the pool immediately after each individual SQL statement.Statement pooling mode is the most granular connection pooling strategy found in some external database proxies (such as standalone PgBouncer). In statement mode, a backend server connection is assigned to a client for a single SQL statement and is immediately returned to the pool as soon as that statement finishes executing, without waiting for a transaction boundary.
BEGIN and COMMIT. In statement pooling, consecutive statements in the same logical transaction could be routed to different backend server connections, violating transaction boundaries and causing SQL transaction errors or data inconsistency.pool_mode: transaction (default) and session. There is no statement pooling mode option available in the managed service.AUTOCOMMIT mode as an independent, single-statement operation.BEGIN/COMMIT blocks nor session-level constructs (like temporary tables or prepared statements) can span across separate requests.statement mode because typical OLTP workloads rely on multi-statement ACID transactions, making transaction mode the practical upper bound for multiplexing.Using statement-level pooling would only be viable if you deployed a custom external pooler (such as self-managed PgBouncer) in front of an application that exclusively executed single-statement autocommit queries with zero multi-statement transactions and no protocol-level prepared statements.
When comparing the three pooling modes, session preserves connection state across the whole connection, transaction preserves transactional state between BEGIN and COMMIT, and statement preserves no state beyond a single query.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated