> ## Documentation Index
> Fetch the complete documentation index at: https://miliracle.online/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL in Depth: Types, Constraints and Transactions

> How PostgreSQL protects your data in three layers: picking the right data type, letting constraints refuse bad rows, and using transactions, savepoints and isolation levels so concurrent work stays correct.

> This is the Markdown version of an interactive page. Every animated figure on the web page is replaced here by a table or code block with the same facts. Sources: the PostgreSQL manual, chapters [Data Types](https://www.postgresql.org/docs/current/datatype.html), [Constraints](https://www.postgresql.org/docs/current/ddl-constraints.html), [Transactions (tutorial)](https://www.postgresql.org/docs/current/tutorial-transactions.html) and [Transaction Isolation](https://www.postgresql.org/docs/current/transaction-iso.html).

A shop sells coffee for \$4.50. One day the price shows as \$4.4999999. Next week a product appears with a price of -5. Then two staff members edit the same order at the same moment, and one change quietly disappears.

None of these are bugs in the app's logic. Each one is the database being asked to keep data it should have refused, or being used in a way that lets two sessions step on each other. PostgreSQL has a tool for each problem. **Types** decide what a value is. **Constraints** decide which rows may exist. **Transactions** decide who sees a change, and when.

This post walks through all three with one small shop and one small bank. It assumes you know basic SQL. For the everyday syntax, see the [PostgreSQL Cheatsheet](https://miliracle.online/writing/database/postgresql-cheatsheet.md). For the theory behind transactions, see [What Are ACID Transactions?](https://miliracle.online/writing/database/acid-transactions.md).

> [!TIP]
>
> **In short**
>
> * Pick types for what they **store**, not what they look like: `numeric` for money, `timestamptz` for moments in time, `text` for strings, `jsonb` for JSON you will query.
> * Constraints refuse bad rows before they are saved. Watch two traps: a `CHECK` lets `NULL` through, and `UNIQUE` allows many `NULL`s. A foreign key does **not** index the child column for you.
> * A transaction is all or nothing. A **savepoint** lets you undo part of one.
> * PostgreSQL's default level is **Read Committed**: each statement sees a fresh snapshot. **Repeatable Read** keeps one snapshot per transaction. **Serializable** also cancels transactions whose combined result could not happen one at a time.
> * At Repeatable Read and Serializable, the app must **retry** a transaction that fails with SQLSTATE `40001`.

## Pick the right type

A type is a promise about a value. It decides what you can store, how it is compared and what comes back out. Find the question that matches what you are storing, and open it to see why. Then read the four traps after the lists.

**If you only remember five:** `bigint` for ids, `numeric` for money, `text` for strings, `timestamptz` for moments, `jsonb` for JSON.

### Numbers

* **Storing row ids? Use `bigint GENERATED ALWAYS AS IDENTITY`.**
  * Example: An events table that logs every click.
  * Why: `integer` stops at about 2.1 billion, and changing the column type later rewrites the whole table. `GENERATED ALWAYS AS IDENTITY` numbers the rows for you.
  * Avoid: `integer` ids on tables that grow without limit. `serial` still works, but identity is the SQL-standard form.
* **Storing counts and quantities? Use `integer`.**
  * Example: Stock levels, seats, items in a cart.
  * Why: Big enough for almost every count and half the size of `bigint`.
  * Avoid: `smallint` (up to 32,767): it rarely saves enough to be worth the risk. Use `bigint` for totals that grow forever.
* **Storing money and prices? Use `numeric(12,2)`.**
  * Example: A \$4.50 coffee plus a \$0.10 tip must total \$4.60, every time.
  * Why: `numeric` stores exact decimal digits. `(12,2)` means 12 digits in total, 2 after the point. Storing cents in a `bigint` is also exact.
  * Avoid: `double precision`: 0.1 + 0.2 gives 0.30000000000000004. The `money` type: its output depends on the server's `lc_monetary` setting.
* **Storing an exact rate, like 7.25%? Use `numeric(5,2)`.**
  * Example: A 7.25% tax rate.
  * Why: The rate must stay 7.25, not 7.2499999. You can also store the fraction 0.0725 in `numeric(6,4)`.
  * Avoid: Mixing percent and fraction styles in one schema. Pick one.
* **Storing measurements or sensor data? Use `double precision`.**
  * Example: A sensor reading of 21.3°C.
  * Why: The reading is already approximate, so a tiny rounding error changes nothing, and float maths is fast.
  * Avoid: `real` unless space matters: it keeps only about 6 digits.

### Text

* **Storing names, titles or any string? Use `text`.**
  * Example: Product names, titles, descriptions.
  * Why: `text` and `varchar(n)` are stored the same way and run at the same speed. With no limit, you never migrate because one name was 256 characters long.
  * Avoid: Guessing a `varchar(255)` limit out of habit from other databases.
* **Storing a string with a hard limit? Use `text + CHECK (length(x) <= 100)`.**
  * Example: A username of 3 to 30 characters.
  * Why: `CHECK (length(username) BETWEEN 3 AND 30)` refuses the rest. A named constraint makes the error say which rule broke.
  * Avoid: Relying on `varchar(30)` alone: it can only check the maximum.
* **Storing fixed codes, like VN or US? Use `text + CHECK (x ~ '^[A-Z]{2}$')`.**
  * Example: Country codes like `VN` and `US`.
  * Why: A pattern `CHECK` refuses `vn` and `V1`, not just values of the wrong length.
  * Avoid: `char(2)`: `char(n)` pads short values with spaces, and those spaces count in some comparisons and not others.
* **Storing emails, matched in any case? Use `text + unique index on lower(email)`.**
  * Example: [Ann@shop.com](mailto:Ann@shop.com) and [ann@shop.com](mailto:ann@shop.com) are the same inbox.
  * Why: `CREATE UNIQUE INDEX ON users (lower(email))` refuses the second one. Search with `WHERE lower(email) = lower($1)` so the index is used. The `citext` extension is another option.
  * Avoid: A plain `UNIQUE` on `email`: it sees two different strings.

### Dates and times

* **Recording when something happened? Use `timestamptz`.**
  * Example: An order placed at 09:00 in Ho Chi Minh City is 22:00 the day before in New York.
  * Why: `timestamptz` stores the moment in UTC and shows it in each reader's time zone. Use it for `created_at`, `paid_at` and every "when did this happen" column.
  * Avoid: Plain `timestamp` for events: nobody can tell which 09:00 it meant.
* **Storing a calendar day, like a birthday? Use `date`.**
  * Example: A birthday of 1990-05-14.
  * Why: It is the same day everywhere, and `date` never moves.
  * Avoid: `timestamptz` at midnight: a reader further west sees the 13th.
* **Storing a time of day, like opening hours? Use `time`.**
  * Example: A shop that opens at 08:00 every day.
  * Why: `time` stores just the clock reading, whatever the date.
  * Avoid: `timetz`: an offset means little without a date, because of daylight saving. The manual says it exists mainly for SQL-standard compliance.
* **Storing a length of time? Use `interval`.**
  * Example: A subscription that lasts one month.
  * Why: `date '2026-01-31' + interval '1 month'` gives 2026-02-28. Also fits durations like a 30-minute session timeout.
  * Avoid: A count of seconds: it can't know that months differ in length.
* **Storing a future local time, like 19:00 in Tokyo? Use `timestamp + zone name in a text column`.**
  * Example: A concert at 19:00 Tokyo time next year.
  * Why: Store the clock reading as `timestamp` and `Asia/Tokyo` as `text`. Work out the moment when you need it: `starts_at AT TIME ZONE zone`.
  * Avoid: `timestamptz` saved today: if the zone's rules change, it points to the wrong hour.
* **Storing a booking period? Use `tstzrange or daterange`.**
  * Example: Room 4 booked from 14:00 to 16:00.
  * Why: One column holds both ends, and `&&` asks "do these overlap?". An exclusion constraint refuses a second, overlapping booking (see Foreign keys in depth).
  * Avoid: Two separate `start` and `end` columns: the database can't check overlaps for you.

### Choices and flags

* **Storing a yes or no answer? Use `boolean`.**
  * Example: `is_paid`, `is_active`.
  * Why: Clear yes or no. Add `NOT NULL DEFAULT false` so every row has an answer.
  * Avoid: Leaving it nullable when `NULL` means nothing: you get three states by accident.
* **Storing a status from a short fixed list? Use `text + CHECK (status IN (...))`.**
  * Example: An order that is `pending`, `paid` or `shipped`.
  * Why: `CHECK (status IN ('pending', 'paid', 'shipped'))` refuses a typo like `payed`. Adding `refunded` later is a short migration.
  * Avoid: Free `text` with no check: typos become new statuses.
* **Storing a list of values that never shrinks? Use `CREATE TYPE ... AS ENUM`.**
  * Example: `CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')`.
  * Why: `ORDER BY mood` gives sad, ok, happy. `ALTER TYPE mood ADD VALUE 'great'` is quick.
  * Avoid: Enums for lists that may shrink: there is no command to remove a value.
* **Storing a few tags on one row? Use `text[]`.**
  * Example: A product tagged `{organic,decaf}`.
  * Why: Tags stay on the row, and a GIN index makes `WHERE tags @> '{decaf}'` fast.
  * Avoid: Arrays when tags need their own data or foreign keys: use a separate tags table.

### Ids, JSON and the rest

* **Need a public id that can't be guessed? Use `uuid`.**
  * Example: Order 1042 in a URL invites a visitor to try 1043.
  * Why: A `uuid` can't be guessed. `uuidv7()` (PostgreSQL 18+) adds new ids at the end of the index, which keeps it compact. Many tables keep a `bigint` key inside and show a `uuid` outside.
  * Avoid: Random `gen_random_uuid()` v4 ids as the primary key of a huge table: they land at random spots in the index.
* **Storing JSON you will filter or index? Use `jsonb`.**
  * Example: Product options like `{"size": "M", "milk": "oat"}` that differ per product.
  * Why: Reads are faster, and a GIN index makes `WHERE options @> '{"milk": "oat"}'` fast.
  * Avoid: `jsonb` when every row has the same keys: real columns get types and constraints.
* **Storing JSON you must keep byte for byte? Use `json`.**
  * Example: A webhook payload kept for audit.
  * Why: `json` keeps spaces, key order and duplicate keys exactly as sent.
  * Avoid: `json` for data you query: it is parsed again every time and can't use a GIN index.
* **Storing IP addresses? Use `inet`.**
  * Example: The IP address of each login.
  * Why: `inet` refuses `'999.1.1.1'`, and `WHERE ip << '10.0.0.0/8'` means "inside this network". Use `cidr` for the networks themselves.
  * Avoid: Storing IPs as `text`: no validation, no network operators.
* **Storing small binary data? Use `bytea`.**
  * Example: A small thumbnail or a signature.
  * Why: Small binary values sit right next to the row that owns them.
  * Avoid: Photos and PDFs in the database: put them in object storage such as S3 and keep the URL in a `text` column.

### Four traps behind these picks

Two types can accept the same input and still give different answers later. Figure 1 shows four cases that surprise people.

**Add 0.1 + 0.2**

```sql theme={null}
SELECT 0.1::numeric + 0.2::numeric;                   -- 0.3
SELECT 0.1::double precision + 0.2::double precision; -- 0.30000000000000004
```

| type | 0.1 + 0.2 | equals 0.3? |
| - | - | - |
| `numeric` | 0.3 | true |
| `double precision` | 0.30000000000000004 | false |

**Save 09:00 in Ho Chi Minh City (UTC+7), read it in New York (UTC-4 in October)**

```sql theme={null}
SET TimeZone = 'Asia/Ho_Chi_Minh';
INSERT INTO meetings VALUES ('2026-10-20 09:00');
SET TimeZone = 'America/New_York';
```

| type | kept on disk | read in Ho Chi Minh City | read in New York |
| - | - | - | - |
| `timestamptz` | 02:00 UTC | 2026-10-20 09:00:00+07 | 2026-10-19 22:00:00-04 |
| `timestamp` | 09:00, no zone | 2026-10-20 09:00:00 | 2026-10-20 09:00:00 (11 hours off) |

**Insert 'tea' and 'espresso' into columns limited to 5 characters**

| type | 'tea' is stored as | 'espresso' (8 characters) |
| - | - | - |
| `char(5)` | `'tea  '` (padded with spaces) | ERROR: value too long for type character(5) |
| `varchar(5)` | `'tea'` | ERROR: value too long for type character varying(5) |
| `text` | `'tea'` | `'espresso'` |

An explicit cast such as `'espresso'::varchar(5)` silently cuts the value to `'espre'` instead of raising an error.

**Store JSON with a repeated key**: input `{"size": "L", "name": "Tea", "size": "M"}`

| type | what comes back | GIN index? |
| - | - | - |
| `json` | `{"size": "L", "name": "Tea", "size": "M"}` (exact input text) | no |
| `jsonb` | `{"name": "Tea", "size": "M"}` (last duplicate wins, key order not kept) | yes |

Each case teaches a rule:

* **Money needs `numeric`.** `real` and `double precision` are binary floating point. They are fast and fine for measurements, but they can't store 0.1 exactly. `numeric(10,2)` stores exact decimal digits: up to 10 digits, 2 after the point.
* **Moments need `timestamptz`.** The name sounds like it stores a time zone. It does not. It converts your input to UTC and converts it back to each reader's `TimeZone`. Plain `timestamp` stores a clock reading with no zone, so nobody can tell which 09:00 it meant.
* **Strings need `text`.** In PostgreSQL, `text`, `varchar(n)` and `char(n)` are equally fast. `char(n)` pads with spaces, which causes odd comparisons. If a limit matters, `varchar(n)` or a `CHECK (length(name) <= 100)` adds it.
* **JSON you query needs `jsonb`.** `json` keeps the exact text and re-parses it on every query. `jsonb` parses once, supports indexes, and has operators like `@>` ("contains"). Use `json` only when you must keep the exact text.

## Constraints: the database says no

The app should validate input. But the app is not the only thing that writes to the database. Scripts, admin tools, a second service and a tired engineer at 2 a.m. all do too. A constraint is a rule the database checks on **every** write, no matter who sends it. If a row breaks the rule, PostgreSQL raises an error and saves nothing.

Here is a `products` table with five kinds of rules. Figure 2 tries six inserts. Four are obvious. Two are traps.

```sql theme={null}
CREATE TABLE products (
  product_no integer PRIMARY KEY,
  name       text NOT NULL,
  sku        text UNIQUE,
  price      numeric(10,2) CHECK (price > 0),
  sale_price numeric(10,2),
  CHECK (sale_price < price)
);
-- Existing rows: (1, 'Coffee', 'CF-1', 4.50, 4.00) and (2, 'Tea', NULL, 3.00, NULL)
```

| Insert (product\_no 3, Mocha) | Result | Why |
| - | - | - |
| name NULL | Refused: `null value in column "name" of relation "products" violates not-null constraint` | `NOT NULL` needs a value |
| price -5.00 | Refused: `new row for relation "products" violates check constraint "products_price_check"` | -5 > 0 is false |
| price 5.00, sale\_price 6.00 | Refused: `... violates check constraint "products_check"` | Table-level `CHECK` across two columns: 6.00 \< 5.00 is false |
| price NULL | **Accepted** | `NULL > 0` is NULL (unknown), not false. A `CHECK` only refuses false. Add `NOT NULL` to forbid it |
| sku 'CF-1' (Coffee's) | Refused: `duplicate key value violates unique constraint "products_sku_key"` | `UNIQUE` allows each value once |
| sku NULL (Tea's is NULL too) | **Accepted** | By default `UNIQUE` treats every NULL as different. `UNIQUE NULLS NOT DISTINCT` (PostgreSQL 15+) allows only one |

What each rule does:

* **`NOT NULL`**: the column must have a value. The manual's advice is that most columns should be `NOT NULL`.
* **`CHECK (expression)`**: the expression must not be false for the row. Written after a column, it is a column constraint. Written as its own line, it can compare columns, like `sale_price < price`. It can't look at other rows or other tables.
* **`UNIQUE`**: no two rows share the value. PostgreSQL builds an index to enforce it. For uniqueness on only some rows (say, one active email per user), use a unique partial index instead.
* **`PRIMARY KEY`**: `UNIQUE` plus `NOT NULL`. One per table. [Keys and Relationships](https://miliracle.online/writing/database/keys-and-relationships.md) covers it in depth.

Name your constraints when the default name won't tell you much: `CONSTRAINT positive_price CHECK (price > 0)`. The name shows up in the error, so your app can turn it into a clear message.

## Foreign keys in depth

> [!NOTE]
>
> **At a glance**
>
> * A foreign key means: every order item must point at an order that exists.
> * `ON DELETE` decides what happens to the items when their order is deleted: refuse, delete them too, or clear the link.
> * PostgreSQL does **not** index the child column for you. Add the index, or every delete reads the whole child table.

### What happens to the children

An order has items. Each item says which order it belongs to, in `order_items.order_id`. Now someone deletes order 7. What should happen to its two items? You choose with `ON DELETE`. Figure 3 tries every choice.

Tables: `orders` has orders 0 (unassigned), 7 (Ann) and 8 (Bo). `order_items` has (7, Coffee, 2), (7, Tea, 1) and (8, Coffee, 1). The key is `order_id integer DEFAULT 0 REFERENCES orders ON DELETE <action>`. Statement: `DELETE FROM orders WHERE order_id = 7;`

| Action | Result | Order 7's two items afterwards |
| - | - | - |
| `NO ACTION` (default) | Refused | Unchanged |
| `RESTRICT` | Refused | Unchanged |
| `CASCADE` | Deleted | Deleted too |
| `SET NULL` | Deleted | Kept, `order_id` is NULL |
| `SET DEFAULT` | Deleted | Kept, `order_id` is 0 (the default, which must exist in `orders`) |

Error for the refused cases: `update or delete on table "orders" violates foreign key constraint "order_items_order_id_fkey" on table "order_items"`

How to pick:

* **`CASCADE`** when the child means nothing without its parent: an item without its order.
* **`RESTRICT` or `NO ACTION`** when the child is history you must keep: the manual's example refuses to delete a product that someone ordered.
* **`SET NULL`** when the child can live on its own, like a task whose assignee left.

`NO ACTION` and `RESTRICT` both refuse, but at different times. `RESTRICT` checks right away. `NO ACTION` checks at the end of the statement. If you declare the key `DEFERRABLE INITIALLY DEFERRED`, it waits until `COMMIT`, so a transaction may break the link for a moment and repair it before it ends.

The manual's full example uses both ends of that choice:

```sql theme={null}
CREATE TABLE order_items (
  product_no integer REFERENCES products ON DELETE RESTRICT,
  order_id   integer REFERENCES orders   ON DELETE CASCADE,
  quantity   integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (product_no, order_id)
);
```

### Index the child column

When you delete order 7, PostgreSQL must first find order 7's items, so it searches `order_items.order_id`. The parent side is already fast, because `orders.order_id` is a primary key and has an index. The child side has no index unless you add one, so PostgreSQL reads the whole table. Figure 4 shows the difference.

`order_items` has 12 rows; rows 3 and 8 have `order_id = 7`.

| Delete order 7 | How PostgreSQL finds the items | Rows read |
| - | - | - |
| Without an index on `order_items (order_id)` | Reads every row and checks its `order_id` | 12 of 12 (10 million of 10 million on a real table) |
| With the index | Looks up 7 in the sorted index, then fetches rows 3 and 8 | 2 of 12 |

**When do you need it?** Use this table for every foreign key you add:

| Situation | Index `order_items (order_id)`? |
| - | - |
| You delete or update orders | **Yes** |
| You list an order's items, or join on it | **Yes** |
| An index already starts with `order_id` | No, already covered |
| An index has `order_id` second | **Yes**, that one can't help |
| Parent never deleted, never searched by | Usually no |

What those rows mean in practice:

* **Deleting parents.** Every deleted order runs one search of `order_items`. Clean up 1,000 old orders in a table of 10 million items, and that is 1,000 full reads of 10 million rows.
* **Looking up children.** "Show the items in order 7" is `WHERE order_id = 7`, and `orders JOIN order_items` matches on the same column. This is the most common reason, and the same index serves it.
* **Already covered.** `PRIMARY KEY (order_id, product_no)` starts with `order_id`, so it already finds rows by `order_id`. A second index would only slow writes.
* **Second column doesn't count.** The manual's key is `(product_no, order_id)`. It is sorted by `product_no` first, so it can't find `order_id = 7` without reading everything.
* **Usually no.** A `country_code` that points at a 250-row `countries` table: you never delete countries and never search items by country. The index would cost disk space and slow every insert for nothing.

**What to do:**

1. Add the index in the same migration as the foreign key:

   ```sql theme={null}
   CREATE INDEX ON order_items (order_id);
   ```

2. On a large table that is already live, build it without blocking writes:

   ```sql theme={null}
   CREATE INDEX CONCURRENTLY ON order_items (order_id);
   ```

   `CONCURRENTLY` is slower to build and can't run inside a transaction block.

### Two more traps

* **NULL skips the check.** An item with `order_id` NULL is not checked at all, and it is accepted. Add `NOT NULL` if every item must have an order.
* **Some rules need an exclusion constraint.** "No two bookings for the same room may overlap" is not something `UNIQUE` can say. `EXCLUDE USING gist (room_no WITH =, during WITH &&)` can. Using `=` on an integer in a GiST index needs the `btree_gist` extension.

## Transactions and savepoints

Moving \$100 from Alice to Bob is two updates. If the database crashes between them, \$100 vanishes. A **transaction** groups the statements so they all happen or none do. You start it with `BEGIN` and end it with `COMMIT`, or with `ROLLBACK` to throw everything away.

Without `BEGIN`, PostgreSQL wraps each statement in its own tiny transaction and commits it at once. Some client libraries send `BEGIN` for you, so check what yours does.

Sometimes you want to undo only part of a transaction. A **savepoint** marks a spot you can return to. Figure 5 runs the example from the PostgreSQL tutorial: the money goes to Bob by mistake, and the transaction steps back and sends it to Wally.

```sql theme={null}
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100 WHERE name = 'Wally';
COMMIT;
```

Illustrative balances. Session A runs the statements; session B is another connection reading the same table.

| Step | Statement | Session A sees (Alice, Bob, Wally) | Session B sees |
| - | - | - | - |
| 1 | `BEGIN` | 500, 300, 200 | 500, 300, 200 |
| 2 | debit Alice | 400, 300, 200 | 500, 300, 200 |
| 3 | `SAVEPOINT my_savepoint` | 400, 300, 200 | 500, 300, 200 |
| 4 | credit Bob | 400, 400, 200 | 500, 300, 200 |
| 5 | `ROLLBACK TO my_savepoint` | 400, 300, 200 (step 4 undone) | 500, 300, 200 |
| 6 | credit Wally | 400, 300, 300 | 500, 300, 200 |
| 7 | `COMMIT` | 400, 300, 300 | 400, 300, 300 (all changes appear together) |

Rules worth remembering:

* **Others see nothing until `COMMIT`**, and then they see everything at once.
* **`ROLLBACK TO` keeps the savepoint.** You can return to it again. `RELEASE SAVEPOINT` drops it when you are done.
* **An error freezes the transaction.** After any error inside a transaction, every later statement fails with `current transaction is aborted, commands ignored until end of transaction block`. The only ways out are `ROLLBACK`, or `ROLLBACK TO` a savepoint set before the error.
* **Sequences don't roll back.** If an `INSERT` takes id 42 and the transaction is rolled back, 42 is gone for good. Gaps in ids are normal.

## Snapshots: Read Committed vs Repeatable Read

So far only one session was writing. Real apps have many, and they don't wait for each other to read. Instead, each one reads from a **snapshot**.

Think of a snapshot as a photo of the table. It holds every change that was committed when the photo was taken, and nothing after. Your `SELECT` reads the photo, not the live table. If someone commits a change a second later, your photo doesn't show it.

The **isolation level** decides when the photo is taken:

| Level | Takes a new snapshot | Sees another session's commit |
| - | - | - |
| Read Committed (default) | Before **every statement** | On your next statement |
| Repeatable Read | Once, at the **first statement** | Never, until a new transaction |

`BEGIN` itself takes no snapshot. At Repeatable Read, the photo is taken by the first real statement, like the first `SELECT`.

Figure 6 runs both levels. Alice has \$500. Session A reads her balance twice. In between, session B sets it to \$400 and commits. Watch the two cards above the timeline. One shows the real table, the other shows A's photo.

Alice starts at 500. Session B runs one statement and commits it immediately.

| # | Session | Statement | Read Committed | Repeatable Read |
| - | - | - | - | - |
| 1 | A | `BEGIN ISOLATION LEVEL …` | no snapshot yet | no snapshot yet |
| 2 | A | `SELECT balance … WHERE name = 'Alice'` | new snapshot, reads 500 | snapshot taken, reads 500 (kept until COMMIT) |
| 3 | B | `UPDATE accounts SET balance = 400 WHERE name = 'Alice'` | committed; table now 400, A's snapshot still 500 | committed; table now 400, A's snapshot still 500 |
| 4 | A | same `SELECT` again | new snapshot, reads 400 | same snapshot as step 2, reads 500 |
| 5 | A | `UPDATE … SET balance = balance + 50 WHERE name = 'Alice'` | new snapshot, UPDATE 1: 400 + 50 = 450 | same snapshot, but the row changed after it: `ERROR: could not serialize access due to concurrent update` (SQLSTATE 40001) |
| 6 | A | `COMMIT` / `ROLLBACK` and retry | Alice = 450 | retry takes a new snapshot, reads 400 and writes 450 |

At **Read Committed**, A read two different balances inside one transaction. That is a *non-repeatable read*. Its `UPDATE` still did the right thing: when an `UPDATE` finds a row that another transaction changed and committed, it waits for that transaction if needed, re-checks its `WHERE` clause on the newest version of the row, and works on that version. B's change was not lost.

That re-check has a sharp edge. One command can see the newest version of the rows it updates while seeing old versions of other rows. The manual's example: a `website` table has two rows with `hits` 9 and 10. One session runs `UPDATE website SET hits = hits + 1` and, before it commits, another runs `DELETE FROM website WHERE hits = 10`. The `DELETE` waits for the first session, re-checks each row and deletes nothing. The row that was 10 is now 11, and the row that was 9 was not 10 when the `DELETE` took its snapshot. Read Committed is fine for simple updates by key, like the transfer above. Complex read-then-write logic needs a stricter level.

At **Repeatable Read**, A's photo never changed, so both reads said 500. But at step 5, A tries to update a row that B changed after A's photo was taken. A would be adding \$50 to a balance it never saw. So PostgreSQL refuses, and A must start again with a new photo.

PostgreSQL accepts the level `READ UNCOMMITTED` too, but treats it as Read Committed. It never shows uncommitted data. Its Repeatable Read also blocks phantom reads, which the SQL standard would allow. [ACID Transactions](https://miliracle.online/writing/database/acid-transactions.md) explains dirty, non-repeatable and phantom reads with animations.

## Serializable and retries

Repeatable Read still lets one problem through. Two transactions can each read something, then each write something the other one read. Each looks fine on its own. Together, they produce a result that no one-at-a-time order could produce. This is called a **serialization anomaly**.

Figure 7 is the example from the manual. The table `mytab` has rows in two classes. Transaction A sums class 1 and saves that sum as a new class 2 row. Transaction B does the opposite.

`mytab` starts with: (class 1, value 10), (1, 20), (2, 100), (2, 200).

| # | Session | Statement | Repeatable Read | Serializable |
| - | - | - | - | - |
| 1 | A | `BEGIN ISOLATION LEVEL …` | | |
| 2 | B | `BEGIN ISOLATION LEVEL …` | | |
| 3 | A | `SELECT SUM(value) FROM mytab WHERE class = 1` | 30 | 30 |
| 4 | B | `SELECT SUM(value) FROM mytab WHERE class = 2` | 300 | 300 |
| 5 | A | `INSERT INTO mytab VALUES (2, 30)` | ok | ok |
| 6 | B | `INSERT INTO mytab VALUES (1, 300)` | ok | ok |
| 7 | A | `COMMIT` | committed | committed |
| 8 | B | `COMMIT` | committed | `ERROR: could not serialize access due to read/write dependencies among transactions` (40001) |
| 9 | B | retry | — | sum of class 2 is now 330; inserts (1, 330) |

Repeatable Read result: (2, 30) and (1, 300). No serial order gives this: A then B would make B's sum 330; B then A would make A's sum 330.
Serializable result: (2, 30) and (1, 330), the same as running A then B.

**Serializable** works like Repeatable Read, plus it tracks what each transaction read. It does this with *predicate locks*, which never block anyone. They only record reads. When it sees a pattern that could produce an impossible result, it cancels one of the transactions. The technique is called Serializable Snapshot Isolation (SSI). In real runs the error can arrive at an earlier statement instead of at `COMMIT`. The fix is the same either way.

That fix is a **retry**. At Repeatable Read and Serializable, a failure with SQLSTATE `40001` means "nothing is wrong with your code, try again". Rerun the whole transaction from `BEGIN`, including the reads, so the retry sees fresh data. The manual suggests retrying deadlocks (`40P01`) the same way. With node-postgres, a helper can look like this:

```js theme={null}
async function inTransaction(pool, work, tries = 5) {
  for (let attempt = 1; ; attempt++) {
    const client = await pool.connect();
    try {
      await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
      const result = await work(client);
      await client.query("COMMIT");
      return result;
    } catch (err) {
      await client.query("ROLLBACK");
      const retryable = err.code === "40001" || err.code === "40P01";
      if (!retryable || attempt === tries) throw err;
    } finally {
      client.release();
    }
  }
}
```

Tips from the manual for keeping Serializable fast:

* Keep transactions short, and never leave a session "idle in transaction".
* Mark transactions `READ ONLY` when they only read.
* Use a connection pool so fewer transactions overlap.
* Drop the `SELECT … FOR UPDATE` locks that Serializable makes unnecessary.

### Which level should you use?

* **Read Committed** (default): most web requests that update rows by key.
* **Repeatable Read**: reports and exports that need one consistent view across many queries. Read-only transactions never need a retry here.
* **Serializable**: any read-then-write rule ("only book if a seat is free", "the sums must match") where you'd rather retry than reason about every race.

## Check yourself

Questions this document answers:

* Which type should hold money? `numeric(p, s)`. It is exact; `double precision` is binary floating point, so 0.1 + 0.2 = 0.30000000000000004.
* What is the difference between `timestamp` and `timestamptz`? `timestamptz` converts input to UTC and displays it in each session's `TimeZone`, so it stores a moment. `timestamp` stores a clock reading with no zone. Neither stores the zone name.
* Is `varchar(n)` faster than `text`? No. They perform the same; `varchar(n)` only adds a length check. `char(n)` pads with spaces.
* `json` or `jsonb`? `jsonb` for almost everything: parsed once, indexable with GIN, supports `@>`. It drops duplicate keys (last wins) and does not keep key order or spacing. `json` keeps the exact input text.
* Does a `CHECK` constraint reject NULL? No. A `CHECK` passes when the expression is true or NULL. Add `NOT NULL`.
* Can a `UNIQUE` column hold several NULLs? Yes, by default. `UNIQUE NULLS NOT DISTINCT` (PostgreSQL 15+) allows only one.
* What is the difference between `NO ACTION` and `RESTRICT`? `RESTRICT` checks immediately. `NO ACTION` (the default) can be deferred to the end of the transaction with `DEFERRABLE INITIALLY DEFERRED`.
* Does a foreign key create an index on the referencing column? No. Add one yourself; deleting a parent row otherwise scans the child table.
* What does `ROLLBACK TO SAVEPOINT` do? Undoes only the work after that savepoint and keeps the rest of the transaction. The savepoint stays defined.
* What happens after an error inside a transaction? Every later statement fails until `ROLLBACK` or `ROLLBACK TO` a savepoint set before the error.
* Are sequence values rolled back? No. Ids taken by a rolled-back transaction are lost, so gaps are normal.
* What is PostgreSQL's default isolation level? Read Committed. Each statement sees a new snapshot. `READ UNCOMMITTED` behaves the same.
* When does Repeatable Read take its snapshot? At the first statement in the transaction (not at `BEGIN`). It keeps it until the end.
* What happens when a Repeatable Read transaction updates a row changed by a transaction that committed after its snapshot? It fails with `could not serialize access due to concurrent update` (SQLSTATE 40001).
* What does Serializable add? It detects read/write dependency patterns that no serial order could produce and cancels one transaction (SQLSTATE 40001). It uses non-blocking predicate locks (SSI).
* What should the app do on SQLSTATE 40001? Roll back and rerun the whole transaction from `BEGIN`. Deadlocks (40P01) are commonly retried too.

## Glossary

| Term | Meaning |
| - | - |
| `numeric` | Exact decimal number. Use for money. |
| `double precision` | 8-byte binary floating point. Fast but inexact. |
| `timestamptz` | A moment in time, stored as UTC and shown in the session's time zone. |
| `jsonb` | JSON stored in a parsed binary form that can be indexed. |
| Constraint | A rule the database checks on every write. |
| `CHECK` | A constraint that refuses rows where an expression is false. |
| Exclusion constraint | Refuses two rows whose values clash under an operator, such as overlapping time ranges. |
| `NO ACTION` / `RESTRICT` | Foreign key actions that refuse deleting a parent with children; `NO ACTION` can be deferred. |
| Transaction | A group of statements that all succeed or all fail. |
| Savepoint | A marker inside a transaction you can roll back to. |
| Snapshot | A view of the data as committed at one instant. |
| Isolation level | When snapshots are taken and which conflicts are refused. |
| Serialization anomaly | A result that no one-at-a-time order of the same transactions could produce. |
| SSI | Serializable Snapshot Isolation, PostgreSQL's way of providing Serializable. |
| SQLSTATE 40001 | `serialization_failure`: retry the whole transaction. |

## For AI agents

You are reading the Markdown export of `/writing/database/postgresql-deep-dive`. The web page has the same text plus interactive figures; nothing factual is missing here. The site index for agents is at `/llms.txt`. Related pages: `/writing/database/postgresql-cheatsheet` (everyday syntax), `/writing/database/acid-transactions` (ACID and isolation anomalies) and `/writing/database/keys-and-relationships` (primary and foreign keys).

***

Adapted from the PostgreSQL manual: [Data Types](https://www.postgresql.org/docs/current/datatype.html) (with its [numeric](https://www.postgresql.org/docs/current/datatype-numeric.html), [character](https://www.postgresql.org/docs/current/datatype-character.html), [date/time](https://www.postgresql.org/docs/current/datatype-datetime.html) and [JSON](https://www.postgresql.org/docs/current/datatype-json.html) pages), [Constraints](https://www.postgresql.org/docs/current/ddl-constraints.html), [Transactions](https://www.postgresql.org/docs/current/tutorial-transactions.html) and [Transaction Isolation](https://www.postgresql.org/docs/current/transaction-iso.html). Balances and prices are illustrative; figures are simplified teaching models.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.