PostgreSQL in Depth Module 14 – Applications, scaling and what comes next
From app code: COPY, pipelining, RETURNING
Load rows from Python and Node.js with COPY, skip bad input with ON_ERROR, cut round trips with pipeline mode and read changed rows back with RETURNING.
What you will learn
- Load and export rows with COPY from Python and Node.js, and explain why it needs fewer round trips than row-by-row INSERTs
- Handle malformed input with the ON_ERROR, REJECT_LIMIT and LOG_VERBOSITY options of COPY
- Use pipeline mode to send many small statements in a few round trips, and predict what an error inside a pipeline skips
- Use RETURNING with old and new values, and MERGE ... RETURNING merge_action(), instead of a second query
- Prevent SQL injection by sending values as parameters and quoting chosen names as identifiers
Before you start
What you’ll learn · Before you start
What you will learn
- Load and export rows with COPY from Python and Node.js, and explain why it needs fewer round trips than row-by-row INSERTs
- Handle malformed input with the ON_ERROR, REJECT_LIMIT and LOG_VERBOSITY options of COPY
- Use pipeline mode to send many small statements in a few round trips, and predict what an error inside a pipeline skips
- Use RETURNING with old and new values, and MERGE ... RETURNING merge_action(), instead of a second query
- Prevent SQL injection by sending values as parameters and quoting chosen names as identifiers
Before you start
On this page
Sending 5,000 orders to PostgreSQL one INSERT at a time cost 5,001 round trips in the run below, and COPY cost 3. Each round trip: One message from a client to the server and the answer back. A client in the ordinary mode waits for one before it sends its next statement, so the network delay is paid once per statement. is a wait for the network, so a loader that feels quick against a server on the same computer spends at least five seconds waiting once the database is a millisecond away, before the server has done any work.
Three features of PostgreSQL’s protocol let application code do more in each trip: COPY streams many rows as one statement, pipeline mode sends statements without waiting for each answer, and RETURNING sends back what a statement changed in the same answer. You will use all three from Python and Node.js, handle bad input rows, and keep every value out of the SQL text.
Row by row pays a round trip per row, and COPY pays three
The run below loads the same 5,000 orders into four empty tables of the same shape: row by row in autocommit mode, with psycopg’s executemany() inside one transaction, as five INSERT statements of 1,000 rows each, and with COPY: PostgreSQL's command for moving many rows in one statement between a table and a file or the client's connection (FROM STDIN, TO STDOUT), in text, CSV or binary format. FROM STDIN, which streams the rows over the connection. A relay between the program and the server counts the messages of PostgreSQL’s wire protocol, and the session then reads how many statements the server ran, from pg_stat_statements, and how many bytes of write-ahead log: The ordered record of every change to a PostgreSQL cluster, flushed to disk before the changed data pages may be written. Crash recovery, archiving and replication all replay it. More in “WAL, checkpoints, checksums and durability” each load wrote:
bulk_session.py
"""The same 5,000 orders loaded four ways from Python (psycopg 3.3.6) into PostgreSQL 18.6, with what each way cost.
bulk_load.py does the loading; this session runs it once per way through a relay that counts the messages of the
wire protocol (pg_session.WireCounter), then reads what the server saw: the statements in pg_stat_statements and the
bytes of WAL each load wrote. Request cycles are the server's ReadyForQuery messages: in the ordinary mode a client
waits for each one before it sends the next statement, so each cycle is one network round trip.
No time is printed: on a shared computer times change from run to run, and every number below is the same on every
run. wal_level = minimal keeps the background writer's standby snapshots, which appear every 15 seconds at other
levels, out of the WAL measured during a load; autovacuum is off so that nothing else writes WAL.
"""
from pg_session import Server
SETTINGS = {"autovacuum": "off", "shared_preload_libraries": "'pg_stat_statements'", "wal_level": "minimal",
"max_wal_senders": "0", "checkpoint_timeout": "'1h'", "max_wal_size": "'4GB'"}
WAYS = [("rows", "row by row, autocommit"), ("many", "executemany, one transaction"),
("multi", "INSERT of 1,000 rows each"), ("copy", "COPY FROM STDIN")]
with Server(settings=SETTINGS) as pg:
pg.up()
pg.psql("bulk_setup.sql", flags=("-q",))
results = []
for way, label in WAYS:
before = pg.query("SELECT pg_current_wal_insert_lsn()")
with pg.wire() as wire:
pg.app("python3", "bulk_load.py", way, port=wire.port, env=wire.env)
wal = pg.query(f"SELECT pg_wal_lsn_diff(pg_current_wal_insert_lsn(), '{before}')")
stored = pg.query(f"SELECT count(*) FROM orders_{way}")
calls = pg.query("SELECT coalesce(sum(calls), 0) FROM pg_stat_statements "
f"WHERE query LIKE 'INSERT INTO orders_{way}%' OR query LIKE 'COPY orders_{way}%'")
results.append((label, stored, calls, wire.cycles(), wire.sent("S"), int(float(wal))))
print(f"{'way of loading':30} {'rows':>5} {'statements':>10} {'cycles':>7} {'syncs':>6} {'WAL bytes':>10}")
for label, stored, calls, cycles, syncs, wal in results:
print(f"{label:30} {stored:>5} {calls:>10} {cycles:>7} {syncs:>6} {wal:>10,}")Output
$ psql -X -q -f bulk_setup.sql $ python3 bulk_load.py rows rows: 5000 orders sent $ python3 bulk_load.py many many: 5000 orders sent $ python3 bulk_load.py multi multi: 5000 orders sent $ python3 bulk_load.py copy copy: 5000 orders sent way of loading rows statements cycles syncs WAL bytes row by row, autocommit 5000 5000 5001 5001 973,232 executemany, one transaction 5000 5000 3 1 772,576 INSERT of 1,000 rows each 5000 5 7 5 772,576 COPY FROM STDIN 5000 1 3 0 535,488
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 bulk_session.py
bulk_load.py
"""Loads the same 5,000 orders into PostgreSQL in one of four ways (python3 bulk_load.py rows|many|multi|copy)."""
import sys
import psycopg
ORDERS = [(n, f"customer-{n % 500}", (n * 37) % 9000 + 100) for n in range(1, 5001)]
def row_by_row(conn):
# autocommit: every INSERT is its own statement and its own transaction
for order in ORDERS:
conn.execute("INSERT INTO orders_rows VALUES (%s, %s, %s)", order)
def execute_many(conn):
with conn.transaction():
conn.cursor().executemany("INSERT INTO orders_many VALUES (%s, %s, %s)", ORDERS)
def multi_row(conn, per_statement=1000):
with conn.transaction():
for start in range(0, len(ORDERS), per_statement):
batch = ORDERS[start:start + per_statement]
placeholders = ", ".join(["(%s, %s, %s)"] * len(batch))
values = [value for order in batch for value in order]
conn.execute(f"INSERT INTO orders_multi VALUES {placeholders}", values)
def copy_rows(conn):
with conn.transaction():
with conn.cursor().copy("COPY orders_copy (id, customer, total_cents) FROM STDIN") as copy:
for order in ORDERS:
copy.write_row(order)
LOADERS = {"rows": row_by_row, "many": execute_many, "multi": multi_row, "copy": copy_rows}
with psycopg.connect(autocommit=True) as conn:
LOADERS[sys.argv[1]](conn)
print(f"{sys.argv[1]}: {len(ORDERS)} orders sent")bulk_setup.sql
-- Four empty tables of the same shape, one for each way of loading.
CREATE TABLE orders_rows (id bigint PRIMARY KEY, customer text NOT NULL, total_cents integer NOT NULL);
CREATE TABLE orders_many (LIKE orders_rows INCLUDING ALL);
CREATE TABLE orders_multi (LIKE orders_rows INCLUDING ALL);
CREATE TABLE orders_copy (LIKE orders_rows INCLUDING ALL);
CREATE EXTENSION pg_stat_statements;A request cycle ends with the server’s ReadyForQuery message, and a client in the ordinary mode waits for it before it sends the next statement, so each cycle is one round trip. Row by row needed 5,001: one for each INSERT, each in its own implicit transaction: A transaction the server opens by itself for statements sent without BEGIN: one statement on its own, or everything between two sync points of a pipeline. An error in it rolls back all of it. More in “psql for daily work: meta-commands and scripts”, plus one more when psycopg turned the INSERT into a prepared statement: A statement the server has parsed and stored under a name for one session, so that it can be run many times with different parameter values ($1, $2 …) without being parsed again. More in “psql for daily work: meta-commands and scripts” after its fifth run (its prepare_threshold is 5 by default; the lesson on the plan cache shows what preparing changes). Those 5,000 commits cost WAL as well: 973,232 bytes against 772,576 for the same rows in one transaction, about 40 bytes of commit record for each row.
executemany() still ran 5,000 INSERTs, but psycopg 3.1 and later send them in pipeline mode, so they needed only 3 cycles: BEGIN, the batch and COMMIT. The five INSERTs of 1,000 rows needed 7. COPY ran a single statement in 3 cycles and wrote the least WAL, 535,488 bytes, because the server inserts the rows of a COPY in batches, of up to 1,000 rows or about 64 kB by the constants of its source, instead of one log record per row.
The price of a round trip is set by the network. On one computer it is so short that 5,001 cycles hardly show; with the database 1 ms away they are at least 5 seconds of waiting, and from another region at 50 ms more than four minutes, while the three cycles of COPY stay a few milliseconds. The session prints counts instead of times because times change from run to run on a shared computer, and these counts do not.
Which way fits depends on what the program needs back and on how far it trusts its input. Answer the three questions about a load of your own, and the table rules out the ways that cannot work:
Answer the questions above: the table then marks what fits your case and says why the rest does not.
| Way of loading | Round trips | Generated ids back | A row that breaks a rule | WAL for 5,000 rows |
|---|---|---|---|---|
| Row by row Sends: one INSERT per row | Poor 5,001 request cycles in the run | Good RETURNING on each INSERT | Mixed only that row fails, in its own transaction | Poor 973,232 bytes: one commit record per row |
| executemany Sends: one INSERT per row, pipelined | Good 3 cycles: psycopg pipelines the batch | Good executemany(returning=True) collects them | Poor one bad row rolls back the whole transaction | Mixed 772,576 bytes |
| Multi-row INSERT Sends: 1,000 rows per INSERT | Good 7 cycles: BEGIN, 5 INSERTs, COMMIT | Good RETURNING lists every row it inserted | Poor one bad row fails its statement of 1,000 | Mixed 772,576 bytes |
| COPY FROM STDIN Sends: one COPY, a stream of rows | Good 3 cycles: BEGIN, COPY, COMMIT | Poor COPY answers with a row count only | Mixed ON_ERROR skips values that do not convert, nothing else | Good 535,488 bytes: rows written in batches |
| COPY into staging Sends: COPY, then INSERT ... SELECT | Good a few cycles, like COPY | Good the INSERT ... SELECT can return them | Good rows are checked in SQL before they move | Mixed each row is written twice |
How the answers decide
- “Does the program need the ids the database generates for the new rows?” Yes, it uses them next: rules out COPY FROM STDIN. COPY returns a row count and nothing else. An INSERT with RETURNING, or COPY into a staging table and then an INSERT ... SELECT, hands the ids back.
- “Can the input hold rows that break a rule, such as a CHECK or a foreign key?” Yes, it comes from outside: rules out executemany and Multi-row INSERT and adds a caveat to COPY FROM STDIN. One row that breaks a constraint fails its statement or its whole transaction, and ON_ERROR ignore skips only values that do not convert to their type.
- “Is the database more than a millisecond away, in another zone or region?” Yes, every round trip costs: rules out Row by row. 5,001 round trips at 1 ms each are 5 seconds of waiting before the server does any work; every other way needs a handful.
The same COPY from Node.js, in both directions
node-postgres runs COPY through the pg-copy-streams package, which gives COPY FROM STDIN as a stream to write into and COPY TO STDOUT as a stream to read from. The program below pipes a generator of 5,000 tab-separated lines into the table, then exports a three-line report with COPY (SELECT …) TO STDOUT WITH (FORMAT csv, HEADER):
copy_stream_session.py
"""COPY from Node.js: copy_stream.mjs (node-postgres 8.23.1, pg-copy-streams 7.0.0) streams 5,000 orders into
PostgreSQL 18.6 and exports a three-line CSV report, through a relay that counts the request cycles
(pg_session.WireCounter: one per ReadyForQuery the server sends)."""
from pg_session import Server
with Server(settings={"autovacuum": "off"}) as pg:
pg.up()
pg.query("CREATE TABLE orders (id bigint PRIMARY KEY, customer text NOT NULL, total_cents integer NOT NULL)")
with pg.wire() as wire:
pg.app("node", "copy_stream.mjs", port=wire.port, env=wire.env)
print(f"request cycles: {wire.cycles()} (COPY in, the count, COPY out); "
f"copy data messages sent: {wire.sent('d')}")Output
$ node copy_stream.mjs loaded: 5000 orders customer,orders,cents customer-121,10,68270 customer-364,10,68180 customer-120,10,67900 request cycles: 3 (COPY in, the count, COPY out); copy data messages sent: 5000
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 copy_stream_session.py
copy_stream.mjs
// Streams 5,000 orders into PostgreSQL with COPY FROM STDIN, then exports a report with COPY ... TO STDOUT.
// node copy_stream.mjs (node-postgres 8.23.1 and pg-copy-streams 7.0.0; the connection comes from PGHOST, PGPORT …)
import { Readable, Writable } from 'node:stream';
import { pipeline } from 'node:stream/promises';
import pg from 'pg';
import { from as copyFrom, to as copyTo } from 'pg-copy-streams';
function* orderLines() {
for (let n = 1; n <= 5000; n++) {
yield `${n}\tcustomer-${n % 500}\t${(n * 37) % 9000 + 100}\n`;
}
}
const client = new pg.Client();
await client.connect();
await pipeline(
Readable.from(orderLines()),
client.query(copyFrom('COPY orders (id, customer, total_cents) FROM STDIN')),
);
const { rows } = await client.query('SELECT count(*)::int AS n FROM orders');
console.log(`loaded: ${rows[0].n} orders`);
const report = `COPY (SELECT customer, count(*) AS orders, sum(total_cents) AS cents
FROM orders GROUP BY customer ORDER BY sum(total_cents) DESC LIMIT 3)
TO STDOUT WITH (FORMAT csv, HEADER)`;
let csv = '';
await pipeline(
client.query(copyTo(report)),
new Writable({ write(chunk, _encoding, done) { csv += chunk; done(); } }),
);
process.stdout.write(csv);
await client.end();The load took one request cycle, the count a second and the export a third. A COPY TO of a query is the quick way to export a report or a slice of a table: the server writes the CSV itself, header included, and the program never builds a row object. PostgreSQL 19’s notes add a json format for COPY TO, beside text, CSV and binary.
Taking executemany() for a single statement
Wrong: “executemany() turns 5,000 rows into one statement”. Right: the server ran 5,000 INSERTs in the run; psycopg saved round trips by pipelining them, not statements. A multi-row INSERT makes one statement of 1,000 rows, and COPY one statement of them all, with the least WAL.
Checkpoint
Check: Round trips, statements and COPY
COPY can skip rows that do not convert, up to a limit
Input from outside nearly always holds a few bad values. The file contacts.csv below has ten rows, and three of them hold a value that cannot become its column’s type: a PIN code typed with two letters O, a day that does not exist, and an id of 7a. The session loads it five times with psql’s \copy, which reads the file on the client and sends it as COPY FROM STDIN, the way an application does:
on_error_session.py
"""COPY with bad input on PostgreSQL 18.6: contacts.csv has ten rows, three of them with a value that does not convert
to its column's type (a letter O in a PIN code, a day that does not exist, an id of 7a); contacts_more.csv has a PIN code that
converts but breaks the table's CHECK constraint. psql's \\copy reads the files on the client and sends them with
COPY ... FROM STDIN, as an application does; the session expects three of the five loads to fail."""
from pg_session import Server
with Server(settings={"autovacuum": "off"}) as pg:
pg.up()
pg.psql("on_error.sql", errors=3)Output
$ psql -X -a -f on_error.sql
CREATE TABLE contacts (
id integer PRIMARY KEY,
name text NOT NULL,
pin_code integer NOT NULL CHECK (pin_code BETWEEN 110000 AND 999999),
joined date NOT NULL
);
CREATE TABLE
-- 1. The default, ON_ERROR stop: the first value that does not convert ends the whole load.
\copy contacts FROM 'contacts.csv' WITH (FORMAT csv, HEADER true)
psql:on_error.sql:8: ERROR: invalid input syntax for type integer: "68OO11"
CONTEXT: COPY contacts, line 4, column pin_code: "68OO11"
SELECT count(*) AS loaded FROM contacts;
loaded
--------
0
(1 row)
-- 2. ON_ERROR ignore skips each row with a value that does not convert to its column's type.
\copy contacts FROM 'contacts.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore)
psql:on_error.sql:11: NOTICE: 3 rows were skipped due to data type incompatibility
COPY 7
SELECT count(*) AS loaded FROM contacts;
loaded
--------
7
(1 row)
TRUNCATE contacts;
TRUNCATE TABLE
-- 3. REJECT_LIMIT 2 (PostgreSQL 18): more than two skipped rows fail the load after all.
\copy contacts FROM 'contacts.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore, REJECT_LIMIT 2)
psql:on_error.sql:15: ERROR: skipped more than REJECT_LIMIT (2) rows due to data type incompatibility
CONTEXT: COPY contacts, line 8, column id: "7a"
-- 4. REJECT_LIMIT 5 with LOG_VERBOSITY verbose: the load succeeds and names each skipped line.
\copy contacts FROM 'contacts.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore, REJECT_LIMIT 5, LOG_VERBOSITY verbose)
psql:on_error.sql:17: NOTICE: skipping row due to data type incompatibility at line 4 for column "pin_code": "68OO11"
psql:on_error.sql:17: NOTICE: skipping row due to data type incompatibility at line 6 for column "joined": "2024-02-30"
psql:on_error.sql:17: NOTICE: skipping row due to data type incompatibility at line 8 for column "id": "7a"
psql:on_error.sql:17: NOTICE: 3 rows were skipped due to data type incompatibility
COPY 7
SELECT count(*) AS loaded FROM contacts;
loaded
--------
7
(1 row)
-- 5. A value that converts but breaks a constraint is not a conversion error: ON_ERROR does not skip it.
\copy contacts FROM 'contacts_more.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore)
psql:on_error.sql:20: ERROR: new row for relation "contacts" violates check constraint "contacts_pin_code_check"
DETAIL: Failing row contains (12, Omar Shaikh, 4000, 2024-01-22).
CONTEXT: COPY contacts, line 3: "12,Omar Shaikh,4000,2024-01-22"
SELECT count(*) AS loaded FROM contacts;
loaded
--------
7
(1 row)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 on_error_session.py
on_error.sql
CREATE TABLE contacts (
id integer PRIMARY KEY,
name text NOT NULL,
pin_code integer NOT NULL CHECK (pin_code BETWEEN 110000 AND 999999),
joined date NOT NULL
);
-- 1. The default, ON_ERROR stop: the first value that does not convert ends the whole load.
\copy contacts FROM 'contacts.csv' WITH (FORMAT csv, HEADER true)
SELECT count(*) AS loaded FROM contacts;
-- 2. ON_ERROR ignore skips each row with a value that does not convert to its column's type.
\copy contacts FROM 'contacts.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore)
SELECT count(*) AS loaded FROM contacts;
TRUNCATE contacts;
-- 3. REJECT_LIMIT 2 (PostgreSQL 18): more than two skipped rows fail the load after all.
\copy contacts FROM 'contacts.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore, REJECT_LIMIT 2)
-- 4. REJECT_LIMIT 5 with LOG_VERBOSITY verbose: the load succeeds and names each skipped line.
\copy contacts FROM 'contacts.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore, REJECT_LIMIT 5, LOG_VERBOSITY verbose)
SELECT count(*) AS loaded FROM contacts;
-- 5. A value that converts but breaks a constraint is not a conversion error: ON_ERROR does not skip it.
\copy contacts FROM 'contacts_more.csv' WITH (FORMAT csv, HEADER true, ON_ERROR ignore)
SELECT count(*) AS loaded FROM contacts;contacts.csv
id,name,pin_code,joined
1,Asha Rao,560034,2024-01-15
2,Vikram Iyer,600020,2024-01-16
3,Meera Nair,68OO11,2024-01-16
4,Rahul Sen,700019,2024-01-17
5,Farah Khan,400050,2024-02-30
6,Arjun Das,110017,2024-01-18
7a,Kavya Menon,500081,2024-01-19
8,Nikhil Jain,302001,2024-01-19
9,Sana Qureshi,226010,2024-01-20
10,Dev Patel,380015,2024-01-21contacts_more.csv
id,name,pin_code,joined
11,Ira Bose,700091,2024-01-22
12,Omar Shaikh,4000,2024-01-22
13,Lata Pillai,695014,2024-01-23With the default, ON_ERROR stop, the first bad value ends the load and nothing is stored: the count after it is 0. ON_ERROR ignore skips every row with a value that does not convert, so the second load stored 7 rows and sent one notice that 3 rows were skipped. With REJECT_LIMIT 2 the third skip made the COPY fail after all, which turns a quiet loss into an error once the input is worse than you accept. LOG_VERBOSITY verbose names each skipped line with its number and its column, and silent leaves out even the summary notice. Follow the lines of the file through the four steps COPY takes with each, load by load:
The drawing shows the whole scene; its 7 steps are listed below.
Every step as text
- A good line goes through. Line 2, Asha Rao with PIN code 560034, converts to an integer, a text and a date, passes the CHECK on the PIN code and is stored.
- The default stops at line 4. Line 4 has 68OO11, with two letters O, where an integer belongs. With the default, ON_ERROR stop, that one value ends the whole COPY, and the run's count shows 0 rows stored.
- ON_ERROR ignore skips the row. With ON_ERROR ignore the line is dropped and counted, and COPY goes on with line 5. Only a value that cannot become its column's type is skipped this way.
- Three lines skipped, seven kept. Line 6 holds a day that does not exist and line 8 the id 7a, so both are skipped too. At the end the server sends one notice that 3 rows were skipped, and COPY reports 7.
- REJECT_LIMIT 2 is passed. With REJECT_LIMIT 2 the third skip, at line 8, is one too many, so the COPY fails after all and stores nothing. The limit turns a silent loss into an error once input is worse than you accept.
- LOG_VERBOSITY names each line. With REJECT_LIMIT 5 and LOG_VERBOSITY verbose the load succeeds, and the server sends one notice per skipped line, with its line number and the column whose value did not convert.
- A rule is not a conversion. In contacts_more.csv, line 3 has the PIN code 4000. It converts to an integer, so ON_ERROR has nothing to skip, and the CHECK then fails the whole COPY. Rules need a staging step of their own.
The last load is the one to remember. A PIN code of 4000 is a perfectly good integer, so there was nothing for ON_ERROR to skip, and the CHECK constraint then failed the whole COPY. ON_ERROR covers the conversion of input values only, as the COPY page says, not constraints, foreign keys or duplicate keys. Input that can break such rules goes into a staging table with loose types first, and moves to the real table only after SQL or the program has checked it; the exercise at the end of this lesson writes that check for a sign-up file.
Which release added what
ON_ERROR ignore and LOG_VERBOSITY came with PostgreSQL 17, REJECT_LIMIT and LOG_VERBOSITY silent with 18. The release notes of PostgreSQL 19 add ON_ERROR set_null, which stores NULL in the bad column instead of dropping the row; the PostgreSQL 19 lesson of this module runs it.
Checkpoint
Check: Skipping bad rows with ON_ERROR
A pipeline sends the next statement before the last answer
An order of 50 lines takes 100 statements in the program below: an INSERT for each line and an UPDATE of its stock, all in one transaction. Sent the ordinary way, each statement waits for its answer. In pipeline mode: A way for a client to send several statements without waiting for each answer, ending the batch with one Sync, so many statements share a few round trips. the program sends them all and reads the answers afterwards; psycopg starts it with conn.pipeline(). The relay counts the cycles again, and the third order asks for more of SKU 30 than is left:
pipeline_session.py
"""Pipeline mode from Python (psycopg 3.3.6) on PostgreSQL 18.6: the same order of 50 lines, 100 statements in one
transaction, sent without and with a pipeline through a relay that counts request cycles (pg_session.WireCounter:
one per ReadyForQuery, the answer a client in the ordinary mode waits for before it sends the next statement). The
third order asks for 2 of SKU 30, which the first two orders have taken down to 0, so its 60th statement breaks a
CHECK constraint inside the pipeline. Statements run are the server's CommandComplete messages for the program's
statements (BEGIN, COMMIT and ROLLBACK, sent as simple queries, are not counted).
For the two pipelined orders, another session holds a lock on order_lines until the program has sent its whole
pipeline and its Sync, and then lets go: every statement has then left before the server runs the first, as happens
anyway when the server is far away, and psycopg reports an error at the end of the pipeline on every run instead of
wherever an early answer happened to arrive."""
import subprocess
from pg_session import Server
def hold_order_lines(pg):
"""Another session that locks order_lines and keeps the lock until release() commits it."""
holder = subprocess.Popen([str(pg.bin / "psql"), "-X", "-q", "-v", "ON_ERROR_STOP=1"], stdin=subprocess.PIPE,
stdout=subprocess.DEVNULL, stderr=subprocess.STDOUT, text=True, env=pg.env, cwd=pg.base)
holder.stdin.write("BEGIN;\nLOCK TABLE order_lines IN EXCLUSIVE MODE;\n")
holder.stdin.flush()
pg.wait_for("SELECT count(*) > 0 FROM pg_locks WHERE relation = 'order_lines'::regclass "
"AND mode = 'ExclusiveLock' AND granted", "t")
return holder
def release(holder):
holder.stdin.write("COMMIT;\n")
holder.stdin.close()
if holder.wait(timeout=60) != 0:
raise SystemExit("the session that held the lock failed")
with Server(settings={"autovacuum": "off"}) as pg:
pg.up()
pg.psql("pipeline_setup.sql", flags=("-q",))
for mode, order_id, qty in (("plain", 1, 1), ("pipeline", 2, 1), ("pipeline", 3, 2)):
with pg.wire() as wire:
if mode == "plain":
pg.app("python3", "place_order.py", mode, order_id, qty, port=wire.port, env=wire.env)
else:
holder = hold_order_lines(pg)
def let_go(wire=wire, holder=holder):
wire.wait_sent("S", 1)
release(holder)
pg.app("python3", "place_order.py", mode, order_id, qty, port=wire.port, env=wire.env,
during=let_go)
ran = wire.received("C") - wire.sent("Q")
print(f" statements sent: {wire.sent('E')}, run by the server: {ran}, request cycles: {wire.cycles()}")
print()
print(pg.query("SELECT 'lines stored per order: ' || string_agg(order_id || ' -> ' || n, ', ' ORDER BY order_id) "
"FROM (SELECT order_id, count(*) AS n FROM order_lines GROUP BY order_id) AS per_order"))
print(pg.query("SELECT 'stock of SKU 1 and 30: ' || string_agg(on_hand::text, ' and ' ORDER BY sku) "
"FROM stock WHERE sku IN (1, 30)"))Output
$ psql -X -q -f pipeline_setup.sql $ python3 place_order.py plain 1 1 order 1: 50 lines placed (plain) statements sent: 100, run by the server: 100, request cycles: 102 $ python3 place_order.py pipeline 2 1 order 2: 50 lines placed (pipeline) statements sent: 100, run by the server: 100, request cycles: 3 $ python3 place_order.py pipeline 3 2 order 3: rolled back (pipeline): new row for relation "stock" violates check constraint "stock_on_hand_check" statements sent: 100, run by the server: 59, request cycles: 3 lines stored per order: 1 -> 50, 2 -> 50 stock of SKU 1 and 30: 8 and 0
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 pipeline_session.py
place_order.py
"""Places one order of 50 lines: for each line an INSERT and a stock UPDATE, all in one transaction.
python3 place_order.py plain|pipeline <order id> <quantity per line>"""
import sys
import psycopg
mode, order_id, qty = sys.argv[1], int(sys.argv[2]), int(sys.argv[3])
def statements(conn):
for sku in range(1, 51):
conn.execute("INSERT INTO order_lines VALUES (%s, %s, %s)", (order_id, sku, qty))
conn.execute("UPDATE stock SET on_hand = on_hand - %s WHERE sku = %s", (qty, sku))
# prepare_threshold=None: psycopg would otherwise prepare each statement on the server after its fifth run,
# which adds a request cycle of its own; here every cycle is one the program asked for
with psycopg.connect(prepare_threshold=None) as conn:
try:
with conn.transaction():
if mode == "pipeline":
with conn.pipeline():
statements(conn)
else:
statements(conn)
print(f"order {order_id}: 50 lines placed ({mode})")
except psycopg.errors.CheckViolation as error:
print(f"order {order_id}: rolled back ({mode}): {error.diag.message_primary}")pipeline_setup.sql
CREATE TABLE stock (sku integer PRIMARY KEY, on_hand integer NOT NULL CHECK (on_hand >= 0));
INSERT INTO stock SELECT n, 10 FROM generate_series(1, 50) AS n;
UPDATE stock SET on_hand = 2 WHERE sku = 30;
CREATE TABLE order_lines (order_id integer, sku integer REFERENCES stock, qty integer, PRIMARY KEY (order_id, sku));The ordinary order needed 102 cycles, its 100 statements plus BEGIN and COMMIT; in a pipeline the same 100 statements needed 3: BEGIN, one Sync and COMMIT. The statements travel as the Parse, Bind and Execute messages of the extended query protocol: The way most drivers send statements to PostgreSQL: a Parse message with the SQL text, a Bind message with the parameter values, then Execute and Sync. More in “Prepared statements and generic plans”, and the Sync at the end asks the server to finish the batch and answer. With a database 50 ms away, that is the difference between about 5 seconds and 150 ms for one order. Step through both orders, and the one that failed:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- Without a pipeline. The program sends the first INSERT and then waits. Nothing else can go until the answer is back, so the network delay is paid once for this statement.
- One answer, one round trip. The server runs the INSERT and answers. Only now does the program send the UPDATE of the stock, and wait again.
- 102 waits for one order. The run counted 102 request cycles for this order, its 100 statements plus BEGIN and COMMIT. With a database 50 ms away that is about 5 seconds of waiting.
- In a pipeline, no waiting. In pipeline mode the program sends the 100 statements one after another without reading any answer, as the extended protocol's Parse, Bind and Execute messages.
- One Sync ends the batch. Then it sends a single Sync. The server runs the statements in order and answers each one, and the Sync's answer, ReadyForQuery, tells the program the batch is finished.
- An error in the pipeline. For the third order the 60th statement breaks a CHECK constraint. The server answers it with an error and skips the 40 statements after it until the Sync, and the program rolls back.
The third order shows what an error does to a pipeline. The server ran 59 statements, the 60th broke the CHECK on the stock, and the 40 after it were skipped until the Sync; the libpq manual calls such a pipeline aborted. psycopg raised the error, and because the statements ran inside the transaction it was rolled back, so order 3 left no lines and SKU 30 kept its stock of 0. A pipeline does not change what a transaction means. It only removes the waiting between its statements.
Key idea
A pipeline removes the waits between statements, not the statements: the same 100 ran in 102 round trips or in 3, and one error still undoes the whole transaction.
Pipelining pays when many small statements do not need each other’s answers, and most when the server is far away, as the libpq manual puts it. It cannot help a statement whose input is the previous result: the program must wait for that answer first. psql 18 can pipeline too, with \startpipeline and \endpipeline around the statements, which makes it easy to try by hand.
Keep reads out of the pipeline block
Calling fetchone() inside conn.pipeline() makes psycopg flush and wait for that result. Collect the cursors in the block and read them after it, or let one statement produce what the next needs, for example with a CTE whose INSERT has a RETURNING clause.
Checkpoint
Check: Pipeline mode and its errors
RETURNING answers with the rows a statement changed
A program often needs something back from a write: the id the server generated, the balance after a debit, which rows a MERGE inserted. Asking with a second query costs a round trip, and it can read a row that another transaction changed in between. A RETURNING clause: The part of an INSERT, UPDATE, DELETE or MERGE that makes the statement answer with values from the rows it changed, such as generated ids or, since PostgreSQL 18, old and new values. makes the write’s own answer carry the rows it changed. The script below uses it with INSERT, UPDATE, an upsert and DELETE, and the Node.js program after it with a guarded debit and a MERGE: An SQL statement that compares source rows with a table and, for each row, inserts, updates or deletes by the first WHEN clause that fits.:
returning_session.py
"""RETURNING on PostgreSQL 18.6: a psql script (returning.sql) shows the generated ids of an INSERT, the old and new
values of an UPDATE and of an upsert (PostgreSQL 18), and the rows a DELETE removed; then a Node.js program
(debit.mjs, node-postgres 8.23.1) makes a guarded debit and a MERGE whose RETURNING names each row's action."""
from pg_session import Server
with Server(settings={"autovacuum": "off"}) as pg:
pg.up()
pg.psql("returning.sql")
pg.app("node", "debit.mjs")Output
$ psql -X -a -f returning.sql
CREATE TABLE tickets (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL);
CREATE TABLE
CREATE TABLE accounts (id integer PRIMARY KEY, owner text NOT NULL, balance integer NOT NULL CHECK (balance >= 0));
CREATE TABLE
INSERT INTO accounts VALUES (1, 'asha', 100), (2, 'vikram', 40);
INSERT 0 2
-- The ids the server generated, in the INSERT's own answer:
INSERT INTO tickets (title) VALUES ('Printer jam'), ('VPN down') RETURNING id, title;
id | title
----+-------------
1 | Printer jam
2 | VPN down
(2 rows)
INSERT 0 2
-- PostgreSQL 18: the row before and after the change, from the same statement:
UPDATE accounts SET balance = balance - 30 WHERE id = 1
RETURNING id, old.balance AS before, new.balance AS after;
id | before | after
----+--------+-------
1 | 100 | 70
(1 row)
UPDATE 1
-- An upsert: old is NULL for the row it inserted and holds the earlier row for the one it updated.
INSERT INTO accounts VALUES (2, 'vikram', 15), (3, 'farah', 60)
ON CONFLICT (id) DO UPDATE SET balance = accounts.balance + excluded.balance
RETURNING id, old.balance AS before, new.balance AS after;
id | before | after
----+--------+-------
2 | 40 | 55
3 | | 60
(2 rows)
INSERT 0 2
-- DELETE returns the rows it removed:
DELETE FROM accounts WHERE balance < 60 RETURNING id, owner, balance;
id | owner | balance
----+--------+---------
2 | vikram | 55
(1 row)
DELETE 1
$ node debit.mjs
debit 30: 100 -> 70
debit 90: refused, the balance is too low
UPDATE pen: 5 -> 8
INSERT pad: - -> 4
DELETE ink: 2 -> -
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 returning_session.py
returning.sql
CREATE TABLE tickets (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL);
CREATE TABLE accounts (id integer PRIMARY KEY, owner text NOT NULL, balance integer NOT NULL CHECK (balance >= 0));
INSERT INTO accounts VALUES (1, 'asha', 100), (2, 'vikram', 40);
-- The ids the server generated, in the INSERT's own answer:
INSERT INTO tickets (title) VALUES ('Printer jam'), ('VPN down') RETURNING id, title;
-- PostgreSQL 18: the row before and after the change, from the same statement:
UPDATE accounts SET balance = balance - 30 WHERE id = 1
RETURNING id, old.balance AS before, new.balance AS after;
-- An upsert: old is NULL for the row it inserted and holds the earlier row for the one it updated.
INSERT INTO accounts VALUES (2, 'vikram', 15), (3, 'farah', 60)
ON CONFLICT (id) DO UPDATE SET balance = accounts.balance + excluded.balance
RETURNING id, old.balance AS before, new.balance AS after;
-- DELETE returns the rows it removed:
DELETE FROM accounts WHERE balance < 60 RETURNING id, owner, balance;debit.mjs
// A debit and a stock merge from Node.js (node-postgres 8.23.1): every value is a parameter ($1, $2 ...), and each
// statement's own RETURNING answers what a second query would otherwise have to ask.
import pg from 'pg';
const client = new pg.Client();
await client.connect();
await client.query(`CREATE TABLE wallet (id integer PRIMARY KEY, balance integer NOT NULL CHECK (balance >= 0));
INSERT INTO wallet VALUES (7, 100);
CREATE TABLE stock (sku text PRIMARY KEY, on_hand integer NOT NULL);
INSERT INTO stock VALUES ('pen', 5), ('ink', 2);`);
async function debit(walletId, amount) {
const { rows } = await client.query(
`UPDATE wallet SET balance = balance - $2 WHERE id = $1 AND balance >= $2
RETURNING old.balance AS before, new.balance AS after`,
[walletId, amount],
);
return rows.length === 1 ? `debit ${amount}: ${rows[0].before} -> ${rows[0].after}`
: `debit ${amount}: refused, the balance is too low`;
}
console.log(await debit(7, 30));
console.log(await debit(7, 90));
const changes = [['pen', 3], ['pad', 4], ['ink', -2]];
const { rows } = await client.query(
`MERGE INTO stock AS s
USING (SELECT * FROM unnest($1::text[], $2::int[]) AS c(sku, delta)) AS c ON s.sku = c.sku
WHEN MATCHED AND s.on_hand + c.delta = 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET on_hand = s.on_hand + c.delta
WHEN NOT MATCHED THEN INSERT VALUES (c.sku, c.delta)
RETURNING merge_action() AS action, c.sku, old.on_hand AS before, new.on_hand AS after`,
[changes.map((c) => c[0]), changes.map((c) => c[1])],
);
for (const r of rows) console.log(`${r.action.padEnd(6)} ${r.sku}: ${r.before ?? '-'} -> ${r.after ?? '-'}`);
await client.end();PostgreSQL 18 added old and new to RETURNING, so the UPDATE returned the balance before and after the debit, 100 and 70, from one statement. In the upsert, old was empty for farah’s row, which the INSERT added, and 40 for vikram’s, which ON CONFLICT turned into an update to 55. MERGE has had RETURNING since PostgreSQL 17, with merge_action() saying what happened to each row: the pen went from 5 to 8, the pad was inserted with 4, and the ink was deleted when its change brought it to 0.
The debit in debit.mjs puts the business rule into the statement: with WHERE id = $1 AND balance >= $2, the second debit of 90 matched no row, and an empty RETURNING told the program to refuse it, without a read first and without a race. An ON CONFLICT DO NOTHING returns no row for a conflict either; PostgreSQL 19 adds ON CONFLICT DO SELECT, which returns the row that was already there.
Reading the row after writing it
Wrong: an UPDATE of the balance, then a SELECT of the new balance. That is two round trips, and another session’s debit can commit between them, so the program shows a balance its own update never produced. Right: UPDATE … RETURNING new.balance, one statement whose answer is the row it wrote.
Checkpoint
Check: RETURNING with old, new and MERGE
Values go as parameters, never as SQL text
Every program above sent its values as parameters: %s in psycopg, $1 in node-postgres. The reason is SQL injection: A flaw where a value pasted into SQL text changes the statement itself, because a quote or a keyword inside the value is read as SQL. Parameters prevent it.. When a value is pasted into the SQL text, a quote inside it ends the string early, and the rest is read as SQL. The lookup below counts the orders for a name typed into a search box, once pasted into the text and once as a parameter, checks each version, and prints what the server received:
lookup_session.py
"""Parameters against SQL injection on PostgreSQL 18.6 (psycopg 3.3.6): lookup.py counts orders for a name typed
into a search box, once pasted into the SQL text and once as a parameter, and checks each version; then the session
prints what the server received for each query, from its own log (log_statement = 'all', log_line_prefix = '')."""
from pg_session import Server
SETTINGS = {"autovacuum": "off", "log_statement": "'all'", "log_line_prefix": "''"}
with Server(settings=SETTINGS) as pg:
pg.up()
pg.silent("CREATE TABLE orders AS SELECT n AS id, 'customer-' || n % 500 AS customer FROM generate_series(1, 5000) AS n")
pg.app("python3", "lookup.py")
log = (pg.base / "data.log").read_text().splitlines()
print("-- the server log: what arrived for the first lookup of the typed text in each version")
pasted = next(i for i, line in enumerate(log) if "customer = 'x'" in line)
bound = next(i for i, line in enumerate(log) if "customer = $1" in line)
print(log[pasted])
print(log[bound])
print(log[bound + 1])Output
$ python3 lookup.py pasted "x' OR '1'='1" matched 5000 orders; check FAILS bound "x' OR '1'='1" matched 0 orders; check holds SELECT count(*) FROM "Order Lines" SELECT count(*) FROM "Order Lines" -- the server log: what arrived for the first lookup of the typed text in each version LOG: statement: SELECT count(*) FROM orders WHERE customer = 'x' OR '1'='1' LOG: execute <unnamed>: SELECT count(*) FROM orders WHERE customer = $1 DETAIL: Parameters: $1 = 'x'' OR ''1''=''1'
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 lookup_session.py
lookup.py
"""Counting a customer's orders by a name typed into a search box: pasted into the SQL text, and as a parameter."""
import psycopg
from psycopg import sql
TYPED = "x' OR '1'='1" # what someone typed into the search box
def pasted(conn, name):
# The weak version: the value becomes part of the SQL text, so its quote ends the string early.
return conn.execute(f"SELECT count(*) FROM orders WHERE customer = '{name}'").fetchone()[0]
def bound(conn, name):
# The fix: the SQL text has a placeholder, and the value travels on its own, as a parameter.
return conn.execute("SELECT count(*) FROM orders WHERE customer = %s", (name,)).fetchone()[0]
def check(lookup, conn):
"""Holds only when the typed text matches no order and a real name still finds its own 10."""
return lookup(conn, TYPED) == 0 and lookup(conn, "customer-7") == 10
with psycopg.connect(autocommit=True) as conn:
for lookup in (pasted, bound):
found = lookup(conn, TYPED)
verdict = "holds" if check(lookup, conn) else "FAILS"
print(f"{lookup.__name__:7} {TYPED!r} matched {found} orders; check {verdict}")
# A table or column name cannot be a parameter: quote it as an identifier instead.
chosen = "Order Lines"
print(sql.SQL("SELECT count(*) FROM {}").format(sql.Identifier(chosen)).as_string(conn))
print(conn.execute("SELECT format('SELECT count(*) FROM %%I', %s::text)", (chosen,)).fetchone()[0])Pasted, the text x' OR '1'='1 turned the WHERE clause into a condition that is true for every row, and the lookup matched all 5,000 orders, so its check, which expects no match for that text and 10 for a real customer, fails. As a parameter the same text matched nothing, and the check holds. The server log shows why: the pasted query arrived as one string of SQL, while the other arrived as customer = $1 with the value beside it, and the server never parses a parameter as SQL. psycopg 3 sends parameters to the server separately from the query, and node-postgres does the same with $1 and $2.
A parameter can only stand for a value, never for a table or column name. When a program must choose a name at run time, it quotes the name as an identifier, with psycopg’s sql.Identifier or the server’s format('%I', …); both turned Order Lines into a quoted name in the run. OWASP’s cheat sheet on SQL injection puts parameterized queries first among its defences, and for names it advises checking them against a list of allowed ones.
Three rules for every query
Send every value as a parameter. Quote a chosen table or column name as an identifier, after checking it against the names you allow. Never build SQL from input with string formatting, f-strings or concatenation, even for a value you think is safe.
Checkpoint
Check: Parameters and identifiers
Practise: a messy sign-up file, then the runs above
The exercise writes the check that ON_ERROR cannot do: it splits a sign-up file into rows ready for COPY and rejects that name their line and the first bad column, as LOG_VERBOSITY verbose does for values that fail to convert. The questions after it go back to the runs of this lesson, starting with the idea you have shown least:
Exercise · Medium · Python
Split a sign-up file into rows for COPY and rejects
COPY ... ON_ERROR ignore skips only values that cannot become their column's type. A PIN code of 4000 or a phone number with nine digits converts without complaint, so rules like these are checked before the rows go to COPY. Write split_rows(lines) in signups.py. It takes the lines of a sign-up file as strings, the first of them the header id,name,pin_code,phone, and returns a pair (rows, rejects):
rowslists, in file order, a tuple(id, name, pin_code, phone)for every line that passes all four rules:idas anint,namewithout the spaces around it,pin_codeas a string of six digits,phoneas its ten digits.rejectslists, in file order, a pair(line_number, column)for every other line: the line number counts the header as line 1, as COPY's messages do, andcolumnis the first of"id","name","pin_code","phone"whose value breaks its rule.
The rules:
idis digits only and greater than 0.nameis not empty once the spaces around it are removed.pin_codeis exactly six digits and does not start with 0.phone, after removing spaces and hyphens, may start with+91, which is dropped; a leading0is dropped only when 11 digits are left. What remains must be 10 digits whose first is 6, 7, 8 or 9.
Use the csv module to split each line, so a name such as "Rao, Asha" in quotes stays one field. The sample tests check the cleaned rows, the rejects of a small file and a file with only its header.
Starter code · signups.py
import csv
def split_rows(lines):
"""Returns (rows, rejects) for the lines of a sign-up file whose first line is its header."""
rows, rejects = [], []
# Your code here: read lines[1:] with csv.reader, check id, name, pin_code and phone in that order,
# and append either a cleaned tuple to rows or (line_number, column) to rejects.
return rows, rejectsThe sample tests · test_signups.py
from signups import split_rows
LINES = [
"id,name,pin_code,phone",
"1,Asha Rao,560034,+91 98450 12345",
"2,Vikram Iyer,600020,044-2345678",
"3,Meera Nair,68OO11,9876543210",
"x4,Rahul Sen,700019,9123456789",
"5, ,400050,9000000001",
'6,"Das, Arjun",110017,09812345678',
"7,Kavya Menon,682011,98450 123456",
]
def test_good_rows_are_cleaned():
rows, _ = split_rows(LINES)
assert rows == [(1, "Asha Rao", "560034", "9845012345"), (6, "Das, Arjun", "110017", "9812345678")]
def test_rejects_name_the_line_and_the_first_bad_column():
_, rejects = split_rows(LINES)
assert rejects == [(3, "phone"), (4, "pin_code"), (5, "id"), (6, "name"), (8, "phone")]
def test_a_file_with_only_its_header():
assert split_rows(["id,name,pin_code,phone"]) == ([], [])A hint
Check the four columns in order and stop at the first bad one: that is the column a reject names. For the phone, clean the text first (remove spaces and hyphens), then drop the prefix, then test what is left.
Results of the sample tests
| Test | Result | Details |
|---|
What your code printed
The sample tests run on this device, in your browser (Pyodide): nothing is sent to mysmartcopilot.com. The first run downloads Python (about 13.5 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.
Practice that adapts to you
Practise this lesson
15 questions about 5 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.
Interview questions
Warm-up for freshers and 2–5 years: why does a COPY of many rows beat INSERT statements sent one at a time?
INSERTs sent one at a time cost one round trip each, and in autocommit mode one commit each, so a client 1 ms from the server waits at least 5 seconds for 5,000 rows before any other cost. The server also parses, plans and runs every statement. COPY is one statement: the client streams the rows as data messages, the server parses each line against the column types and inserts the rows in batches, and one commit ends it. In the run of this lesson, 5,000 orders took 5,001 request cycles and 973,232 bytes of WAL row by row, and 3 cycles and 535,488 bytes with COPY. The trade-off is that COPY returns only a count, so generated ids need RETURNING on an INSERT instead.
Warm-up for 2–5 years: why do parameters stop SQL injection when escaping quotes by hand does not?
With a parameter, the SQL text and the value travel separately: the server parses WHERE customer = $1 once, and the value fills the slot as data, so no character in it can change the statement. Escaping by hand keeps the value inside the SQL text and trusts every code path to escape every value correctly, for every type, encoding and setting; one missed case is enough. The run of this lesson showed both: the pasted name x' OR '1'='1 matched all 5,000 orders, the same text as a parameter matched none, and the server log showed the value beside the statement rather than in it. Names of tables or columns cannot be parameters, so they are quoted as identifiers and checked against an allowed list.
Two more questions go further, into a retry after a lost answer and a load of tens of gigabytes; the module’s question page gives each a first move and its follow-ups:
Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.
-
Mid to senior
A sync job loads 50,000 rows with COPY in one transaction every 10 minutes. Once a day the network drops after the job has sent COMMIT and before the answer arrives, and the job retries the whole batch. Some days the table ends up with 100,000 rows. How do you make the retry safe?
-
Senior to staff
A monthly COPY of a 40 GB CSV file into a table with six indexes and two foreign keys takes 9 hours on a server that otherwise sits idle that night. The business wants it under 2 hours. What do you change, in which order, and what does each change give up?
Flashcards · 6 cards
Recall it, then check
Say the answer to yourself before you look: recalling it is what makes it stick.
-
How does COPY compare with row-by-row INSERT for a bulk load?
Every INSERT is a round trip and, in autocommit mode, a commit: 5,000 orders took 5,001 request cycles and 973,232 bytes of WAL, while COPY took 3 cycles and 535,488 bytes.
-
What does COPY FROM STDIN / TO STDOUT do, and what can't it return?
They stream rows into and out of PostgreSQL from drivers such as psycopg and, with pg-copy-streams, from Node.js. COPY answers only with a row count and never with generated ids.
-
How does COPY FROM handle bad input rows?
ON_ERROR ignore skips only values that do not convert to their type, REJECT_LIMIT turns too many skips into an error, and LOG_VERBOSITY verbose names each skipped line. A broken rule such as a CHECK constraint still fails the whole COPY.
-
What does pipeline mode do, and what happens on an error?
It sends statements without waiting for each answer, so 100 statements took 3 round trips instead of 102. An error skips everything up to the next Sync.
-
What does RETURNING give you?
It answers with the changed rows in the same statement — old and new values since PostgreSQL 18, and merge_action() for MERGE since 17 — which saves a follow-up query and closes a race.
-
How do parameters and quoted identifiers prevent SQL injection?
Values travel as parameters and chosen names as quoted identifiers, so typed text such as x' OR '1'='1 is carried as data and never read as SQL.
Key takeaways
- Row by row, every INSERT is a round trip and, in autocommit mode, a commit: 5,000 orders took 5,001 request cycles and 973,232 bytes of WAL, while COPY took 3 cycles and 535,488 bytes.
- COPY FROM STDIN and COPY TO STDOUT stream rows into and out of PostgreSQL from psycopg and, with pg-copy-streams, from Node.js, but COPY answers with a row count and never with generated ids.
- ON_ERROR ignore skips only values that do not convert to their type, REJECT_LIMIT turns too many skips into an error, LOG_VERBOSITY verbose names each skipped line, and a broken rule such as a CHECK still fails the whole COPY.
- Pipeline mode sends statements without waiting for each answer, so the order of 100 statements took 3 round trips instead of 102, and an error skips everything up to the next Sync.
- RETURNING, with old and new values since PostgreSQL 18 and merge_action() for MERGE since 17, answers with the changed rows in the same statement, which saves a query and closes a race.
- Values travel as parameters and chosen names as quoted identifiers, so typed text such as
x' OR '1'='1is never read as SQL.
Terms in this lesson
- COPY
- PostgreSQL's command for moving many rows in one statement between a table and a file or the client's connection (FROM STDIN, TO STDOUT), in text, CSV or binary format.
- MERGE
- An SQL statement that compares source rows with a table and, for each row, inserts, updates or deletes by the first WHEN clause that fits.
- pipeline mode
- A way for a client to send several statements without waiting for each answer, ending the batch with one Sync, so many statements share a few round trips.
- RETURNING clause
- The part of an INSERT, UPDATE, DELETE or MERGE that makes the statement answer with values from the rows it changed, such as generated ids or, since PostgreSQL 18, old and new values.
- round trip
- One message from a client to the server and the answer back. A client in the ordinary mode waits for one before it sends its next statement, so the network delay is paid once per statement.
- SQL injection
- A flaw where a value pasted into SQL text changes the statement itself, because a quote or a keyword inside the value is read as SQL. Parameters prevent it.
References
- PostgreSQL 18 documentation: COPY (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 14.4 Populating a Database (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 32.5 Pipeline Mode (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 6.4 Returning Data from Modified Rows (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: MERGE (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: INSERT (ON CONFLICT) (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: psql (\copy and the pipeline commands) (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: F.32 pg_stat_statements (The PostgreSQL Global Development Group)
- PostgreSQL 17 release notes (COPY ON_ERROR and LOG_VERBOSITY, MERGE RETURNING) (The PostgreSQL Global Development Group)
- PostgreSQL 18 release notes (RETURNING old and new, REJECT_LIMIT) (The PostgreSQL Global Development Group)
- PostgreSQL 19 release notes (in preparation) (The PostgreSQL Global Development Group)
- PostgreSQL source, REL_18_STABLE: copyfrom.c (MAX_BUFFERED_TUPLES) (The PostgreSQL Global Development Group)
- Psycopg 3 documentation: Using COPY TO and COPY FROM (The Psycopg Team)
- Psycopg 3 documentation: Pipeline mode support (The Psycopg Team)
- Psycopg 3 documentation: Passing parameters to SQL queries (The Psycopg Team)
- Psycopg 3 documentation: Prepared statements and the connection API (prepare_threshold) (The Psycopg Team)
- Psycopg 3 documentation: psycopg.sql, SQL string composition (The Psycopg Team)
- node-postgres documentation: Queries (parameterized queries) (Brian Carlson and the node-postgres contributors)
- pg-copy-streams 7.0.0 README (COPY FROM and COPY TO for node-postgres) (The node-pg-copy-streams contributors)
- OWASP Cheat Sheet Series: SQL Injection Prevention (OWASP Foundation)
Related tools
Report a problem with this lesson
Kept only in this browser. Your Learn progress