Data Modification

RETURNING makes an INSERT, UPDATE or DELETE return columns from the rows it affected, in the same statement and round trip. For an INSERT that typically means generated identity values, defaults or computed columns; for an UPDATE, the new values after the change; for a DELETE, the rows that were removed, which is useful for auditing or for moving data. It avoids a second SELECT, which would cost another round trip and could see a different state under concurrency. It also pairs well with guarded updates: zero rows returned tells you the state transition was not allowed. PostgreSQL 17 supports it on MERGE too. From JDBC, read it with executeQuery.

An upsert inserts a row, or updates the existing row if one with the same key already exists, as one atomic operation. In PostgreSQL it is INSERT ... ON CONFLICT (key columns) DO UPDATE, where EXCLUDED refers to the values you tried to insert; DO NOTHING turns it into an idempotent insert. The conflict target must match a unique index or constraint, because that index is what makes the operation safe under concurrency: two sessions inserting the same key cannot both succeed. MySQL offers INSERT ... ON DUPLICATE KEY UPDATE. Upserts are the natural tool for idempotent event consumers, synchronising reference data and retry-safe writes from services.

Because the check and the change happen in one statement under one row lock, there is no window for another transaction to slip in between. With read-modify-write in Java, two requests can both read stock = 1, both decide there is enough, and both write 0, so you oversell and lose an update. The single UPDATE locks the row; a concurrent update waits, then re-evaluates the WHERE condition against the newly committed value and updates zero rows. The application checks the affected row count to detect that failure. It also saves a round trip. A CHECK (stock >= 0) constraint is a useful safety net if someone later changes the code.

Use JDBC batching, not one execute per row with autocommit. Prepare one INSERT, bind each row and call addBatch, then executeBatch every 500 to 5,000 rows inside a transaction, committing per chunk to bound memory and transaction size. With PgJDBC, set reWriteBatchedInserts=true so the driver rewrites the batch into multi-row VALUES statements, which typically gives another two to three times. In Spring, JdbcTemplate.batchUpdate does this; with Hibernate, set jdbc.batch_size and order_inserts, and avoid IDENTITY ids because they disable insert batching. For very large loads, millions of rows, PostgreSQL COPY through CopyManager is fastest because it streams rows without per-statement overhead.

PostgreSQL never overwrites a row in place. An UPDATE writes a complete new tuple version, stamped with the updating transaction's id as xmin, and marks the old version with xmax, so concurrent readers with older snapshots still see the old row under MVCC. Every index normally gets a new entry pointing to the new tuple, and the change is written to the WAL. The old version becomes a dead tuple that VACUUM later reclaims. If no indexed column changes and the page has free space, a HOT update keeps the new version on the same page and skips index updates, which is why you avoid indexing frequently updated columns and avoid no-op updates.

Never as one DELETE. A single statement over hundreds of millions of rows runs as one huge transaction: it holds locks for hours, generates massive WAL that makes replicas lag, blocks vacuum and leaves the table full of dead tuples, and if it fails it all rolls back. If this is regular time-based retention, the right design is range partitioning by month or day, so removal becomes DETACH or DROP PARTITION, which takes seconds. For a one-off cleanup on an unpartitioned table, delete in batches of around 10,000 rows by key, commit each batch, pause between batches, and watch replication lag and autovacuum while the job runs.

For concurrent single-row upserts from application code in PostgreSQL I choose INSERT ... ON CONFLICT. It uses the unique index to arbitrate, so two sessions inserting the same key cannot both insert; one inserts and the other updates, with no unique violation. MERGE is the SQL standard and more expressive: it can insert, update and delete in one statement based on a join with a source, which suits batch synchronisation from a staging table. But in PostgreSQL MERGE does not guarantee race-free behaviour under concurrent inserts and can raise unique violations, so callers need retries. Rule of thumb: ON CONFLICT for application upserts, MERGE for controlled batch jobs.

I treat production data changes as deployments, not ad-hoc typing. Each change is a scripted, reviewed file linked to a ticket, ideally run by a pipeline rather than a person. The script first runs a SELECT with the same predicate to establish the expected count, then executes inside an explicit transaction with RETURNING or a row-count check, and commits only if the count matches. Humans have read-only access by default, with time-boxed elevated roles. Point-in-time recovery is configured and regularly tested so mistakes are recoverable. Large changes are batched, and routine retention uses partition drops. Together these turn a missing WHERE clause into a rolled-back transaction instead of an outage.