Transactions are one of the main features of Postgres that make Postgres a great database. When code is ran in a transaction, Postgres provides a set of guarantees about the behavior of the code. Altogether, this set of guarantees is commonly referred to as ACID, with each letter in ACID standing for a different guarantee.

First of all there two main ways to run SQL code in a transaction. The first way is use BEGIN and COMMIT. Any code between the statements BEGIN and COMMIT is ran in a single transaction. For example, if we have a table representing bank accounts and want to run several statements in a single transaction, we can do the following:

BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 10;
UPDATE accounts SET balance = balance - 100 WHERE id = 11;
COMMIT;

This runs the two update statements in a single transaction. The other main way of running code in a transaction is to just run a single statement. Any single statement sent to Postgres implicitly has a BEGIN and COMMIT attached to it. Running the query:

SELECT COUNT(*) FROM accounts;

Is equivalent to running:

BEGIN;
SELECT * FROM accounts;
COMMIT;

As for the ACID guarantees, let’s take a look at each one.

Atomicity

The first ACID guarantee is called “atomicity”. For a set of statements ran in a transaction, atomicity guarantees that either every statement in the transaction succeeds, or the entire transaction fails and none of the statements in the transaction have any effect. Using the first snippet above as an example, after running it, either both updates will have completed or neither will have. This means either 100 dollars will be transferred from account #11 to account #10, or the money transfer will fail completely. It is impossible for the balance of one account to be changed and for the balance of the other to remain unchanged. For many applications, atomicity is absolutely critical. It is completely unacceptable for a bank to complete only one side of a money transfer.

Atomicity is guaranteed even if the power goes out while Postgres is in the middle of executing a transaction! When Postgres comes back up after a crash, it will act as if all of the transactions running at the time of the crash had never happened at all! You can also manually abort a currently running transaction by running the command ROLLBACK before running COMMIT. Running ROLLBACK will undo every command ran after BEGIN.

Consistency

The second ACID guarantee is consistency. Consistency matters when database constraints are used. If a constraint holds true before the transaction starts, the constraint will also hold true when the transaction completes, if it completes. As an example, there may be a unique constraint on a column which guarantees all values of that column are distinct. Whenever a transaction completes, Postgres guarantees that the unique constraint still holds true. This means if two transactions try to insert the same value into the column at the same time, Postgres has to abort one of them, since if they both complete, the unique constraint will be violated. Consistency can be summarized as the database will be in a consistent state both before a transaction begins and after it completes.

Isolation

The third guarantee is isolation. If transactions are fully isolated from each other, two transactions will have absolutely no effect on each other at all. Under the default settings, Postgres provides only partial transaction isolation. That means that transactions can still interact with each other in some cases, but they will be isolated from each other in most cases.

You can change the level of isolation Postgres uses to make transactions completely isolated from each other, although there are quite a few downsides to doing so. If it isn’t possible for Postgres to keep two transactions completely isolated, it will abort one of them, and that transaction will have to be retried later. Raising the isolation level makes it much more likely that any given transaction will fail. I’m not going to go into all of the details in this post, but if you’re curious, you can read about all of the different transaction isolation levels and all of the tradeoffs that come up in the Postgres manual. Generally speaking, the default isolation level is good enough for most use cases.

Durability

The last ACID guarantee is durability. Once Postgres says it has committed a transaction, it will not lose the data for that transaction. Even if there is a power outage immediately after the transaction committed, Postgres will not lose any data that was just committed. All durability means is Postgres won’t lose your data.

---

Overall Postgres provides a set of properties that make it easy to reason about Postgres queries. Without all of the ACID guarantees, it will be much harder to use Postgres, and there would be many use cases where Postgres could not be used at all. How Postgres implements each part of ACID is fairly interesting in and of itself, and has far reaching consequences throughout all of Postgres. We will see how Postgres implements the different guarantees of ACID as we look at more parts of Postgres.