PostgreSQL in Depth: Types, Constraints and Transactions

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. For the theory behind transactions, see What Are ACID Transactions?.

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 NULLs. 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

Text

Dates and times

Choices and flags

Ids, JSON and the rest

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. 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. 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 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

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. 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:

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. 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:

Add the index in the same migration as the foreign key:
CREATE INDEX ON order_items (order_id);
On a large table that is already live, build it without blocking writes:
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. 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. 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 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. 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:

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

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

Adapted from the PostgreSQL manual: Data Types (with its numeric, character, date/time and JSON pages), Constraints, Transactions and Transaction Isolation. Balances and prices are illustrative; figures are simplified teaching models.

All posts

Frontend

Database

PostgreSQL in Depth: Types, Constraints and Transactions

Pick the right type

Numbers

Text

Dates and times

Choices and flags

Ids, JSON and the rest

Four traps behind these picks

Constraints: the database says no

Foreign keys in depth

What happens to the children

Index the child column

Two more traps

Transactions and savepoints

Snapshots: Read Committed vs Repeatable Read

Serializable and retries

Which level should you use?

Check yourself

Glossary

For AI agents

​Pick the right type

​Numbers

​Text

​Dates and times

​Choices and flags

​Ids, JSON and the rest

​Four traps behind these picks

​Constraints: the database says no

​Foreign keys in depth

​What happens to the children

​Index the child column

​Two more traps

​Transactions and savepoints

​Snapshots: Read Committed vs Repeatable Read

​Serializable and retries

​Which level should you use?

​Check yourself

​Glossary

​For AI agents

Pick the right type

Numbers

Text

Dates and times

Choices and flags

Ids, JSON and the rest

Four traps behind these picks

Constraints: the database says no

Foreign keys in depth

What happens to the children

Index the child column

Two more traps

Transactions and savepoints

Snapshots: Read Committed vs Repeatable Read

Serializable and retries

Which level should you use?

Check yourself

Glossary

For AI agents