This topic provides an overview of the four data write methods supported by AnalyticDB for PostgreSQL: INSERT statement, COPY statement, parallel write with OSS external tables, and Client SDK.
INSERT and COPY statements are executed by the coordinator node to write data. An INSERT statement writes up to 3 MB of data per second whereas a COPY statement writes up to 30 MB of data per second. In addition, all compute nodes can read and write data in parallel by using OSS external tables. Each compute node can load up to 30 MB of data from OSS. The overall data loading speed of your AnalyticDB for PostgreSQL instance increases linearly in proportion with the number of compute nodes.
Method 1: INSERT statement
We recommend that you combine multiple values into one INSERT statement to increase the write speed. The maximum speed can reach 3 MB per second.
INSERT INTO performers (name, specialty) VALUES ('Sinatra', 'Singer'), …;If you want to write data at a high speed, we recommend that you disable the ORCA SQL optimizer. The ORCA SQL optimizer is used to optimize complex queries. For simple queries, we recommend that you disable it to avoid additional parsing and CPU computing optimization workloads. To disable the ORCA SQL optimizer for a session, you can run the set optimizer = off; command. However, to disable the ORCA SQL optimizer for an instance, you must submit a ticket.
Method 2: COPY statement
You can execute a COPY statement to import a .txt file from your computer to an AnalyticDB for PostgreSQL instance. Only .txt files can be imported from your computer to an AnalyticDB for PostgreSQL instance. In addition, they must be formatted. For example, they must use commas (,), semicolons (;), or other special characters as delimiters. In addition, you can execute a COPY statement by using JDBC in which the CopyIn method is encapsulated. A COPY statement can write or import up to 30 MB of data per second.
For more information, see Use the \copy command to import data from your computer to AnalyticDB for PostgreSQL.
Method 3: Parallel write with OSS external tables
You can import or export data in parallel from compute nodes by using OSS external tables. The overall import or export speed increases linearly in proportion with the number of compute nodes. Each compute node can write up to 30 MB of data per second.
For more information, see Use an external table to import data from OSS at a high speed.
Method 4: Client SDK
The Client SDK uses APIs to write data into AnalyticDB for PostgreSQL. It supports the custom development and integration of data write programs.AnalyticDB for PostgreSQL A custom data write program developed by using the Client SDK simplifies the data write process with its internal mechanisms such as parallel processing and improves performance multiple times compared with COPY and INSERT statements. So, you do not need to worry about connection pool or cache issues.AnalyticDB for PostgreSQL
For more information about the APIs used by the Client SDK, see Use AnalyticDB for PostgreSQL Client SDK to write data.