Overview of data write methods
AnalyticDB for PostgreSQL supports four methods for writing data: INSERT statement, COPY statement, parallel write with OSS external tables, and Client SDK. Each method suits different throughput requirements and use cases.
Choose a method
| Method | Executor | Throughput | Best for |
|---|---|---|---|
| INSERT statement | Coordinator node | Up to 3 MB/s | Low-volume writes, real-time row inserts |
| COPY statement | Coordinator node | Up to 30 MB/s | Bulk loading from a local .txt file |
| OSS external tables | All compute nodes (parallel) | Up to 30 MB/s per node, scales linearly | Large-scale parallel data loading from OSS |
| Client SDK | API-based | Multiple times faster than COPY and INSERT | Custom data pipelines requiring high throughput |
INSERT statement
Batch multiple rows into a single INSERT statement to maximize write speed. The maximum throughput is 3 MB/s.
INSERT INTO performers (name, specialty) VALUES ('Sinatra', 'Singer'), …;Disable the ORCA optimizer for high-speed INSERT workloads. The ORCA optimizer is designed for complex queries and adds unnecessary parsing and CPU overhead for simple inserts.
To disable it for the current session, run
set optimizer = off;.To disable it instance-wide, submit a ticket.
COPY statement
Use a COPY statement to import a formatted .txt file from your local machine into AnalyticDB for PostgreSQL. The file must use a consistent delimiter such as a comma (,) or semicolon (;). You can also run COPY statements through JDBC using the CopyIn method. Maximum throughput is 30 MB/s.
For step-by-step instructions, see Use the \copy command to import data from your computer to AnalyticDB for PostgreSQL.
Parallel write with OSS external tables
OSS external tables let all compute nodes read and write data in parallel. Each compute node handles up to 30 MB/s, and overall throughput scales linearly with the number of compute nodes — making this method the best fit for large-scale data loading.
For step-by-step instructions, see Use an external table to import data from OSS at a high speed.
Client SDK
The Client SDK uses APIs to write data into AnalyticDB for PostgreSQL. It handles parallel processing, connection pooling, and caching internally, so you can focus on your application logic rather than managing write infrastructure. A custom program built with the Client SDK delivers multiple times higher throughput than COPY or INSERT statements.
For the full API reference, see Use AnalyticDB for PostgreSQL Client SDK to write data.