[Feature][connector-file] Add PostgreSQL COPY Command Support to JDBC Source for Enhanced Data Extraction Performance

[Ruiii-w]

Search before asking

• I had searched in the feature and found no similar feature requirement.

Description

The current JDBC Source connector cannot use PostgreSQL's native COPY command for data extraction, forcing users to rely on slower standard SELECT queries when reading large datasets. This results in unnecessarily long extraction times and inefficient resource usage during data synchronization from PostgreSQL.

Usage Scenario

Enable high-speed data extraction from PostgreSQL databases in SeaTunnel ETL/ELT jobs.

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[DanielCarter-stack]

Thanks for your interest in contributing! This feature request is valid. Currently, COPY is only supported on the Sink side (JdbcSinkOptions.use_copy_statement, CopyManagerProxy.copyInMethod), while the Source side exclusively uses standard SELECT queries (ChunkSplitter.java, JdbcInputFormat.java).

The PostgreSQL dialect already optimizes with cursor mode (PostgresDialect.creatPreparedStatement), but COPY ... TO STDOUT would provide better performance for large exports.

Key implementation points to consider:

• Extend CopyManagerProxy to support copyOut (returns InputStream)
• Add a new Source option (e.g., use_copy_statement for Source)
• Handle COPY limitations: no support for custom query (only table_path), complex WHERE clauses, or interactions with partition_column

Questions to clarify:

1. Should COPY fall back to SELECT when custom query is configured?
2. Should COPY be compatible with parallel reads via partition_column, or mutually exclusive?

[Ruiii-w]

Thanks for your interest in contributing! This feature request is valid. Currently, COPY is only supported on the Sink side (JdbcSinkOptions.use_copy_statement, CopyManagerProxy.copyInMethod), while the Source side exclusively uses standard SELECT queries (ChunkSplitter.java, JdbcInputFormat.java).

The PostgreSQL dialect already optimizes with cursor mode (PostgresDialect.creatPreparedStatement), but COPY ... TO STDOUT would provide better performance for large exports.

Key implementation points to consider:

• Extend CopyManagerProxy to support copyOut (returns InputStream)
• Add a new Source option (e.g., use_copy_statement for Source)
• Handle COPY limitations: no support for custom query (only table_path), complex WHERE clauses, or interactions with partition_column

Questions to clarify:

1. Should COPY fall back to SELECT when custom query is configured?
2. Should COPY be compatible with parallel reads via partition_column, or mutually exclusive?

To ensure compatibility with the existing JDBC configuration and make COPY compatible with the current JDBC sharding mechanism, we can try incorporating query, partition_column, and where_condition into the SQL construction of COPY for more flexible applications.

Additionally, the choice between using COPY and SELECT can be directly controlled by the use_copy_statement configuration for the Source.

Scenario 1: Both partition_column and where_condition are configured

When partition_column and where_condition are configured, the generated SQL will be as follows:

COPY (
    SELECT * FROM (
        SELECT * FROM "schema"."table_name" 
        WHERE "partition_column" >= <start_value> 
          AND "partition_column" < <end_value>
    ) tmp <where_condition>
) TO STDOUT WITH [BINARY|CSV];

Scenario 2: Only query is enabled

When only the custom query is enabled, the generated SQL will be simplified to:

COPY (
    SELECT * FROM (
        <query>
    ) tmp
) TO STDOUT WITH [BINARY|CSV];

Performance Verification

Through practical experiments and measurements, i have confirmed that this form of COPY-based SQL performs better than standard SELECT queries.