AWS aurora-dsql documentation change
Summary
Updated documentation to clarify use of PostgreSQL \copy command instead of COPY, and added best practices for INSERT transactions including security advice on parameterized queries to prevent SQL injection.
Security assessment
The change adds security best practices by recommending parameterized queries to mitigate SQL injection, which is a common security vulnerability, but there is no concrete evidence of addressing a specific security issue; it is general preventive guidance.
Diff
diff --git a/aurora-dsql/latest/userguide/loading-data.md b/aurora-dsql/latest/userguide/loading-data.md index 474f3e1f7..cac9f1d25 100644 --- a//aurora-dsql/latest/userguide/loading-data.md +++ b//aurora-dsql/latest/userguide/loading-data.md @@ -5 +5 @@ -Choosing a loading approachAurora DSQL LoaderMigration pathwaysUsing PostgreSQL COPYAdditional resources +Choosing a loading approachAurora DSQL LoaderMigration pathwaysUsing PostgreSQL \copyAdditional resources @@ -18 +18 @@ Approach | Best for | Considerations -**PostgreSQL`COPY`** \- Standard PostgreSQL feature | Simple loads when you are already connected via a compatible client | Requires your client to handle IAM authentication; you manage parallelization yourself +**PostgreSQL`\copy`** \- Client-side `psql` meta-command | Simple loads when you are already connected via `psql` | Reads files on the client and streams data over the connection; you manage parallelization yourself @@ -205,0 +206,2 @@ For a complete list of options and additional configuration parameters, run: + * **Pre-create tables when possible** – Define the target table with explicit column data types and primary keys before loading data. Pre-created schemas give you control over type precision and indexing, which typically results in better query performance compared to auto-inferred schemas. + @@ -301 +303 @@ Ensure your IAM identity has `s3:GetObject` permission on the source objects. -## Using PostgreSQL COPY +## Using PostgreSQL \copy @@ -303 +305 @@ Ensure your IAM identity has `s3:GetObject` permission on the source objects. -If you are already connected to Aurora DSQL through a client that handles IAM authentication, you can use the standard PostgreSQL `COPY` command to load data. This approach works well for simple, single-threaded loads. +If you are already connected to Aurora DSQL through a `psql` session that handles IAM authentication, you can use the client-side `\copy` meta-command to load data from your local file system. Unlike the server-side `COPY` statement, `\copy` reads the file on the client machine and streams the data over the existing connection, so no server-side file access is required. This approach works well for simple, single-threaded loads. @@ -305 +307 @@ If you are already connected to Aurora DSQL through a client that handles IAM au -###### Example Loading a CSV file with COPY +###### Example Loading a CSV file with \copy @@ -308 +310 @@ If you are already connected to Aurora DSQL through a client that handles IAM au - COPY my_table FROM '/path/to/data.csv' WITH (FORMAT csv, HEADER true); + \copy my_table FROM '/path/to/data.csv' WITH (FORMAT csv, HEADER true); @@ -310 +312 @@ If you are already connected to Aurora DSQL through a client that handles IAM au -When using `COPY` directly, you are responsible for: +When using `\copy` directly, you are responsible for: @@ -320,0 +323,17 @@ When using `COPY` directly, you are responsible for: +### Best practices for INSERT transactions + +When using `INSERT` statements to load data into Aurora DSQL, follow these practices to improve throughput and reliability: + + * **Batch rows into multi-row INSERTs** – Group multiple rows into a single `INSERT` statement to reduce round trips. For example, `INSERT INTO my_table VALUES (1, 'a'), (2, 'b'), (3, 'c')` is more efficient than three separate statements. + + * **Use parameterized queries** – Use prepared statements with parameter binding instead of string concatenation. This avoids SQL injection risks and allows the database to reuse query plans. + + * **Keep transactions small** – Aurora DSQL uses optimistic concurrency control, so large transactions that touch many rows are more likely to encounter conflicts. Aim for transactions of a few hundred rows rather than thousands. + + * **Implement retry logic** – Transient errors such as optimistic concurrency control (OCC) conflicts are expected in a distributed system. Implement exponential backoff with retry for failed transactions. + + * **Parallelize across connections** – Open multiple connections and distribute inserts across them. Each connection can process a different subset of data concurrently. + + + +