What is the difference between pessimistic and optimistic locking in PostgreSQL?

Pessimistic locking in PostgreSQL assumes that conflicts when accessing data are likely, so locks are immediately applied when reading or modifying data to prevent concurrent changes. For example, using SELECT ... FOR UPDATE locks the rows for other transactions.

Optimistic locking, on the other hand, assumes that conflicts are rare and does not lock data when reading. Instead, when saving changes, it checks whether the data has been modified since it was read (for example, by version or checksum). If the data has changed, the transaction is rolled back or retried.

In PostgreSQL, optimistic locking is implemented at the application level, for example, using a version field (timestamp or counter). Pessimistic locking is a built-in mechanism of the DBMS.

Example of pessimistic locking:

BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- changes
COMMIT;

Optimistic locking requires additional logic in the application.