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
- Schema: a named collection of database objects (like namespace or packages in a programming language)
- Database: is the topmost named collection of objects
- Multitenancy: a system offering per-tenant data isolation while sharing resources like CPU and memory
- Table-level: Data for all tenants is stored in shared tables
- Schema-level: Each tenant has a dedicated schema
- Database-level: Each tenant has its own database
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:
- Not-null: ensures a column cannot be set to a null value.
- Unique: guarantees that a column or group of columns doesn’t hold duplicate values.
- Primary key: the column or group of columns that is the unique identifier for rows in the table.
- Foreign key: the column or group of columns must match values in a row from another table.
- Check: the value of a column must satisfy a custom condition.
- Exclusion: for any two rows being compared, at least one must be
falseornull
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)
idhas aPRIMARY KEYconstraint (unique and non-null)nameanddescriptionare non-nullcategoryhas a check that must becoffee,mug, ort-shirtstock_quantityhas a check that must be >=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.