Just Use Postgres!

Notes for Just Use Postgres! by Denis Magda. I’m following along with the book on my NixOS dev machine so these notes will include learnings for both Postgres and Nix.

Table of Contents

Chapter 1: Meeting Postgres

This chapter sets the scene for what Postgres is and why it is so popular, how to run it in docker, and some basic hands-on experience. Since I’m doing this in Nix, I’m going to try and use a flake.

Adding postgresql_17 to my flake.nix file adds postgres and psql to my dev shell.

pg@dev:~/pg/ > postgres --version
postgres (PostgreSQL) 17.10

pg@dev:~/pg/ > psql --version
psql (PostgreSQL) 17.10

I also wanted the man pages, but adding postgresql_17.man didn’t work. man-db searches for man pages relative to where the binary is in the PATH, and Nix has crazy isolation, so I think it couldn’t find it. Grouping the two together via buildEnv makes share/man in the same directory as bin/postgres.

postgresql = pkgs.buildEnv {
  name = "postgresql-with-docs";
  paths = with pkgs; [ postgresql_17 postgresql_17.man ];
}

The book tells you to start the server by running the docker container, but since I’m running this in Nix, I’m going to run it directly. So reading through the man pages reveals a related initdb command, which initializes a database:

pg@dev:~/pg/ > initdb --username postgres --pwfile=<(echo password) --pgdata data
...

Success. You can now start the database server using:

    pg_ctl -D data -l logfile start

Following the instructions..

pg@dev:~/pg/ > pg_ctl -D data -l logs start
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.

pg@dev:~/pg/ > cat logs
...
2026-07-25 21:30:33.370 PDT [17462] FATAL:  could not create lock file "/run/postgresql/.s.PGSQL.5432.lock": No such file or directory

Hm.. it’s trying to use /run/postgresql for the Unix-domain socket to listen for connections. To fix this, we we can make a local one and pass the -k option to specify where to look (see man postgres).

pg@dev:~/pg/ > mkdir run
pg@dev:~/pg/ > export PGHOST="$PWD/run"
pg@dev:~/pg/ > export PGDATA="$PWD/data"
pg@dev:~/pg/ > pg_ctl -l logs -o "-k $PGHOST" start
waiting for server to start.... done
server started

Now to Nix-ify it:

shellHook = ''
  export PGDATA="$PWD/data"
  export PGHOST="$PWD/run"
  mkdir -p "$PGHOST"
'';

And psql works like normal (since it uses PGHOST).

SELECT count(*) FROM trades;
 count
-------
  1000
(1 row)

It’s best practice to list column names in SELECT queries instead of *, except count(*) is specially optimized in Postgres.

Chapter 2: Standard RDBMS Capabilities

This chapter covers some of the basic relational database management system capabilities. Things like creating database objects, querying data, using JOINs, transactions, executing application logic, and enforcing data integrity. This is primarily demonstrated through a multi-tenant eCommerce application example.

Database Structure

eCommerce Example

psql -U postgres
CREATE DATABASE coffee_chain;
CREATE DATABASE brewery;

SELECT datname FROM pg_database; -- view existing databases

-- connect to the coffee_chain database
-- alternative: psql -U postgres -d coffee_chain
\c coffee_chain

-- view existing schemas
\dn

CREATE SCHEMA products;
CREATE SCHEMA customers;
CREATE SCHEMA sales;

SHOW search_path; -- view which schema is currently set
SET search_path TO products; -- change the default schema

-- Create the catalog table under the products schema.
-- See: https://www.postgresql.org/docs/current/datatype.html
CREATE TABLE products.catalog (
    id SERIAL PRIMARY KEY,          -- auto incrementing four-byte integer
    name VARCHAR (100) NOT NULL,    -- strings up to 100 characters
    description TEXT NOT NULL,      -- strings of unlimited length (up to 1GB)
    category TEXT CHECK (category IN ('coffe', 'mug', 't-shirt')),
    price NUMERIC(10, 2),           -- fixed point number containing 10 digits,
                                    -- 2 of which are after the decimal point
    stock_quantity INT CHECK (stock_quantity >= 0)
);

CREATE TABLE products.reviews (
    id BIGSERIAL PRIMARY KEY,   -- autoincrementing eight-byte integer
    product_id INT,
    customer_id INT,
    review TEXT,
    rank SMALLINT               -- signed two-byte integer
);

-- view existing tables
\d
\dts products.*

-- I made a typo in the original constraint, so I looked at the existing
-- constraints, removed it, then re-added it with the correct spelling.
\d+ products.catalog
BEGIN;
ALTER TABLE products.catalog DROP CONSTRAINT "catalog_category_check";
ALTER TABLE products.catalog ADD CONSTRAINT "catalog_category_check"
    CHECK (category IN ('coffee', 'mug', 't-shirt'));
COMMIT;


-- Adding data.
INSERT INTO products.catalog
  (name, description, category, price, stock_quantity) VALUES
  ('Sunrise Blend',
   'A smooth and balanced blend with notes of caramel and citrus.',
   'coffee', 14.99, 50),
  ('Midnight Roast',
   'A dark roast with rich flavors of chocolate and toasted nuts.',
   'coffee', 16.99, 40),
  ('Morning Glory',
   'A light roast with bright acidity and floral notes.',
   'coffee', 13.99, 30),
  ('Sunrise Brew Co. Mug',
   'A ceramic mug with the Sunrise Brew Co. logo.',
   'mug', 9.99, 100),
  ('Sunrise Brew Co. T-Shirt',
   'A soft cotton t-shirt with the Sunrise Brew Co. logo.',
   't-shirt', 19.99, 25);

-- View data.
SELECT id, name, price, stock_quantity FROM products.catalog;
-- Interestingly, the id starts at 2. I think this happened because of my
-- failed INSERT query earlier.

-- Update data.
UPDATE products.catalog SET price = 16.54 WHERE id = 2;

-- Delete data.
DELETE FROM products.catalog WHERE id = 3;

Constraints

Data types and constraints for columns are ways to enforce rules for the shape the data can be.

Postgres supports the following constraints:

eCommerce Example

\d products.catalog
                                           Table "products.catalog"
     Column     |          Type          | Collation | Nullable |                   Default
----------------+------------------------+-----------+----------+----------------------------------------------
 id             | integer                |           | not null | nextval('products.catalog_id_seq'::regclass)
 name           | character varying(100) |           | not null |
 description    | text                   |           | not null |
 category       | text                   |           |          |
 price          | numeric(10,2)          |           |          |
 stock_quantity | integer                |           |          |
Indexes:
    "catalog_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "catalog_category_check" CHECK (category = ANY (ARRAY['coffee'::text, 'mug'::text, 't-shirt'::text]))
    "catalog_stock_quantity_check" CHECK (stock_quantity >= 0)
-- add a constraint
ALTER TABLE products.catalog ADD CONSTRAINT "catalog_price_check"
    CHECK (price >= 0);

-- blocked
UPDATE products.catalog SET price = -16 WHERE name = 'Morning Glory';

-- add a constraints to the reviews table
ALTER TABLE products.reviews
    ALTER COLUMN review SET NOT NULL,
    ADD CONSTRAINT review_rank_check CHECK (rank BETWEEN 1 AND 5);

-- add a foreign key constraint between tables
ALTER TABLE products.reviews
    ADD CONSTRAINT products_review_product_id_fk
    FOREIGN KEY (product_id) REFERENCES products.catalog(id);

-- create an accounts table
CREATE TABLE customers.accounts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    passwd_hash TEXT NOT NULL
);

-- add a foreign key constraint on products.reviews to the account id
ALTER TABLE products.reviews
    ADD CONSTRAINT products_review_customer_id_fk
    FOREIGN KEY (customer_id) REFERENCES customers.accounts(id);

Now that the constraints are added, let’s simulate some data and events.

-- add some accounts that have signed up
INSERT INTO customers.accounts (name, email, passwd_hash)
VALUES
('Alice Johnson', 'alice.johnson@example.com',
    '5f4dcc3b5aa765d61d8327deb882cf99'),
('Bob Smith', 'bob.smith@example.com', 'd8578edf8458ce06fbc5bb76a58c5ca4'),
('Charlie Brown', 'charlie.brown@example.com',
     '5f4dcc3b5aa765d61d8327deb882cf99');

SELECT id, name FROM customers.accounts;
SELECT id, name FROM products.catalog WHERE name = 'Sunrise Brew Co. Mug';

-- simulate an application off-by-1000 error
INSERT INTO products.reviews (product_id, customer_id, review, rank)
VALUES (1005, 1, 'This mug is perfect — sturdy, stylish, and keeps my coffee warm for a good while.', 5);

Foreign key constraints would also prevent us from deleting accounts if the user has left reviews. The pattern to maintain this information but mark an account as deleted is soft deletion. This works by adding a deleted column to the table and flipping the flag when a user requests to delete their account.

Alternatively, an ON DELETE CASCADE clause on the foreign key constraint would delete any rows referencing the key when the foreign key is deleted.

Transactions

Postgres provides transactions to help developers prevent data inconsistencies and enforce data integrity. Implicit transactions are queries that are self-referential, whereas explicit transactions are ones that use BEGIN..COMMIT statements. These are useful when you need to execute multiple SQL statements atomically, treating them as a single operation that must complete successfully. If anything goes wrong during a transaction, the database will roll the changes back to the values before the transaction began.

eCommerce Example

-- implicit transaction adding to the stock
UPDATE products.catalog
    SET stock_quantity = stock_quantity + 100
    WHERE id = 2;

-- implicit transaction adding to the stock of multiple rows
UPDATE products.catalog
    SET stock_quantity = stock_quantity + 50
    WHERE id = 2 or id = 4;

-- setup a sales order table that must be updated with order_items table
-- atomically
CREATE TABLE sales.orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id INT REFERENCES customers.accounts(id),
    order_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2)
);

CREATE TABLE sales.order_items (
    order_id UUID REFERENCES sales.orders(id),
    product_id INT REFERENCES products.catalog(id),
    quantity INT CHECK (quantity > 0),
    price DECIMAL (10, 2),
    PRIMARY KEY (order_id, product_id)
);

-- explicit transaction creating an order with order items

-- when I first ran this, I had product_id=1, which failed the foreign key
-- constraint and rolled back the transaction!
BEGIN;

INSERT INTO sales.orders (id, customer_id, total_amount)
    VALUES ('19a0cffc-8757-453c-a4d2-b554fdc08954', 1, 26.53);

INSERT INTO sales.order_items (order_id, product_id, quantity, price)
    VALUES ('19a0cffc-8757-453c-a4d2-b554fdc08954', 2, 1, 16.54),
           ('19a0cffc-8757-453c-a4d2-b554fdc08954', 5, 1, 9.99);

UPDATE products.catalog
    SET stock_quantity = stock_quantity - 1
    WHERE id IN (2, 5);

COMMIT;

Changes that get rolled back stay in the database until garbage collection is performed (called a vacuum). The multiversion concurrency control (MVCC) engine ensures these changes are no longer visible to subsequent application requests and transactions.

Postgres implements Multiversion concurrence control (MVCC) and uses read committed isolation level to execute statements concurrently. The goal of this isolation level is to avoid dirty reads. No transaction should see uncommitted changes.

The following example demonstrates this:

-- Open two psql connections:
--  psql -U postgres -d coffee_chain
--
-- 1. Run the block of statements in the first connection
-- 2. Run the block of statements in the second connection
--     Notice how the SELECT statement returns the same value since the UPDATE
--     from the first connection has not been committed yet.
--     Also, the UPDATE is blocked because the first connection has locked
--     writing that row.
-- 3. Commit in the first connection
--     Notice how the UPDATE in the second connection gets unblocked.
-- 4. Commit in the second connection
--     Notice how the UPDATE uses the committed value and correctly decrements
--     the quantity by 1.

-- Block ----------------------------------------
BEGIN;
SELECT stock_quantity FROM products.catalog
WHERE id = 2;

UPDATE products.catalog
SET stock_quantity = stock_quantity - 1
WHERE id = 2;
-- Block ----------------------------------------

COMMIT;

More phenomena and isolation levels can be found in the Postgres documentation.