postgres_fdw: Emit Message When batch_size is Reduced

Core Problem

In PostgreSQL's postgres_fdw (the foreign data wrapper for connecting to remote PostgreSQL servers), users can configure a batch_size option to control how many rows are inserted in a single batch during foreign table modifications (INSERT operations). This batching optimization, introduced in PostgreSQL 14, uses libpq's pipeline mode to send multiple tuples in a single network round-trip.

However, there is a practical constraint: libpq imposes a limit on the number of parameters that can be sent in a single protocol message (PQ_QUERY_PARAM_LIMIT, which is 65535). When the configured batch_size multiplied by the number of columns in the target table exceeds this limit, the function postgresGetForeignModifyBatchSize() silently reduces the effective batch size to fit within libpq's parameter limit.

The calculation is roughly:

/* In postgresGetForeignModifyBatchSize() */
max_batch_size = PQ_QUERY_PARAM_LIMIT / num_params_per_row;
if (batch_size > max_batch_size)
    batch_size = max_batch_size;  /* Silent reduction! */

For example, if a table has 100 columns and the user sets batch_size = 1000, the actual batch size would be silently capped at 65535 / 100 = 655. The user has no visibility into this adjustment.

Why This Matters Architecturally

1. Observability and Transparency: Silent performance-affecting adjustments violate the principle of least surprise. A DBA tuning batch_size for optimal bulk-insert throughput may not realize their setting is being overridden, leading to confusion when performance doesn't match expectations.

2. Debugging Difficulty: Without any feedback, diagnosing why a foreign table INSERT isn't achieving expected throughput requires reading source code to discover the libpq parameter limit constraint.

3. Consistency with PostgreSQL Conventions: PostgreSQL commonly emits DEBUG-level messages or NOTICEs when internal adjustments override user-specified configurations (e.g., work_mem adjustments, shared_buffers rounding).

Proposed Solution

The patch proposes adding a DEBUG-level message (likely DEBUG1 or DEBUG2) in postgresGetForeignModifyBatchSize() when the batch size is reduced below the user-configured value. This is a minimal, low-risk change:

• DEBUG level ensures the message doesn't appear by default in production — only when a user explicitly sets client_min_messages or log_min_messages to DEBUG level
• It provides the information needed for performance tuning without adding noise
• The message would likely include the configured batch_size, the effective batch_size, and possibly the reason (libpq parameter limit)

Technical Context

The relevant code path is in contrib/postgres_fdw/postgres_fdw.c, specifically in postgresGetForeignModifyBatchSize(). This function is called by the executor during foreign table modification operations to determine how many rows to batch together. The libpq limit exists because the extended query protocol uses a 16-bit unsigned integer to encode the number of parameters, capping it at 65535.

Design Considerations

• Message Level: Using ereport(DEBUG1, ...) is appropriate since this is diagnostic information, not an error or warning. Some might argue for a NOTICE or WARNING on the first occurrence, but DEBUG is more conservative and aligned with how PostgreSQL handles similar internal adjustments.
• Performance Impact: Negligible — the message is only formatted and emitted when the appropriate message level is configured.
• Alternative Approaches: One could argue for emitting this at table creation/ALTER time when batch_size is set, but the actual reduction depends on the number of columns in the specific INSERT statement, which is only known at execution time.

Assessment

This is a small, straightforward quality-of-life improvement. It's the type of patch that is generally uncontroversial — adding observability without changing behavior. The main discussion points would likely be:

1. Exact message wording
2. Whether DEBUG1 or DEBUG2 is the appropriate level
3. Whether to include additional context (table name, column count, limit value)