Postgres Tutorials

PostgreSQL from a first CREATE TABLE to something you can put in front of users — psql and roles, data types and constraints, the SQL you write every day, JSONB, indexes, EXPLAIN, transactions and locking, migrations and the production checklist. Every query is run against a real 400,000-row booking database before it ships, and every plan is a plan Postgres actually chose.

• Postgres – The Production Checklist
  What has to be true before the database is in front of users. Connection pooling and why max_connections is not the answer, the four settings worth changing from the defaults, autovacuum and how to see whether it is keeping up, pg_dump versus physical backup and point-in-time recovery, the monitoring queries to have ready before you need them, and what managed Postgres does and does not do for you.
• Postgres – Schema Migrations Without Downtime
  The DDL that takes a lock long enough to be an outage, and the version of the same change that does not. Adding a column with a default, adding NOT NULL in two steps with a validated CHECK, CREATE INDEX CONCURRENTLY and the invalid index it can leave behind, renaming a column across two deploys, and lock_timeout — the one setting that turns a migration from an outage into a retry.
• Postgres – Transactions, Isolation and Locking
  BEGIN, COMMIT, ROLLBACK, and what Postgres promises in between. Read Committed versus Repeatable Read versus Serializable, shown with two real psql sessions rather than described. SELECT FOR UPDATE, the lost update it prevents, deadlocks and the ordering rule that avoids them, advisory locks, and the idle-in-transaction connection that blocks your next deploy.
• Postgres – EXPLAIN and Query Performance
  How to read a plan instead of guessing. EXPLAIN versus EXPLAIN ANALYZE and the danger of running the second one on an UPDATE, what BUFFERS tells you that timing does not, the scan and join nodes you will actually meet, and the single most useful signal in the output — the gap between estimated and actual rows. Plus pg_stat_statements for finding the query worth fixing.
• Postgres – Indexes
  Every index in this post is created on a 400,000-row table and the plan before and after is shown. B-tree and why column order in a composite index decides whether it is used, partial indexes for the status you actually query, expression indexes for lower(email), covering indexes and index-only scans, GIN for jsonb, and the write cost that makes an unused index worse than no index.
• Postgres – JSON and JSONB
  jsonb is a real column type with real operators, not a text field you parse in the application. -> versus ->>, the containment operator @> and the GIN index that makes it fast, jsonb_path_query, updating one key with jsonb_set, and expanding an array into rows. Ends with the harder question: which of your columns should NOT have been jsonb.
• Postgres – INSERT, UPDATE, DELETE and Upsert
  Writing data, including the parts SQL tutorials skip. RETURNING so you do not need a second round trip for the generated id, ON CONFLICT DO UPDATE for a real upsert and the unique index it requires, UPDATE ... FROM and DELETE ... USING for joining in the rows you are changing, COPY for bulk loads, and why a soft delete is a column and not a DELETE.
• Postgres – Window Functions
  An aggregate collapses rows; a window function keeps them and adds a column. OVER and PARTITION BY, row_number versus rank versus dense_rank and which one you meant, running totals with a frame clause, lag and lead for comparing a row to the one before it, and top-N-per-group — the query that is ugly with a subquery and three lines with a window.
• Postgres – Subqueries and CTEs
  Scalar subqueries, IN, EXISTS and the difference that matters when the inner query returns NULL. WITH for naming the steps of a query you can still read next month, the MATERIALIZED keyword that Postgres 12 made optional and when to force it, recursive CTEs, and writing to two tables in one statement with a data-modifying CTE.
• Postgres – Aggregation and GROUP BY
  count, sum, avg, min, max, and the rule that decides what may appear in the SELECT list. WHERE versus HAVING and why the order matters for speed, count(*) versus count(column) on nullable data, FILTER for counting several things in one pass, string_agg and array_agg, and GROUPING SETS for a subtotal row without a second query.
• Postgres – Joins
  INNER, LEFT, RIGHT, FULL and CROSS, each with the row count it produces on real tables so the difference is visible rather than described. The LEFT JOIN whose WHERE clause silently turns it back into an INNER JOIN, anti-joins with NOT EXISTS, self-joins, and LATERAL — the one that lets you write top-N-per-group without a window function.
• Postgres – SELECT, WHERE and ORDER BY
  The query you write a hundred times a day, done carefully. Filtering with AND/OR and the parenthesis that changes the answer, LIKE versus ILIKE, IN and BETWEEN, ordering with NULLS LAST, DISTINCT versus DISTINCT ON, and keyset pagination — because OFFSET 10000 reads ten thousand rows to throw them away. Plus the three ways NULL will surprise you.
• Postgres – Tables, Keys and Constraints
  CREATE TABLE, then the constraints that make wrong data impossible instead of unlikely. Identity columns rather than serial, primary and foreign keys and what ON DELETE actually chooses, UNIQUE, NOT NULL, CHECK — and the exclusion constraint that stops two guests booking the same property on the same night, enforced by the database rather than by hoping the application checked.
• Postgres – Data Types
  The choices you cannot cheaply undo later. `text` versus `varchar(n)` and why the length limit buys nothing, `numeric` for money and never a float, `timestamptz` versus `timestamp` and what actually gets stored, `uuid` alongside an integer key rather than instead of it, arrays, and why StayHub stores its enums as varchar. With a mapping table to Python and Java types.
• Postgres – Databases, Schemas, Roles and Privileges
  A database holds schemas, a schema holds tables, and a role is both a user and a group. Creating an application account that is not a superuser: what `public` grants you by default and why the first thing to do is revoke it, GRANT versus ALTER DEFAULT PRIVILEGES and why the second one is the one people forget, search_path, and the owner/app/read-only split worth having on day one.
• Postgres – psql and the Tools You Actually Use
  psql is the tool that ships with the database and the one every answer online assumes. The meta-commands worth memorising — \d, \d+, \di, \l, \dn, \x, \timing — the connection URI in full, running a file with ON_ERROR_STOP so a migration does not half-apply, and a .psqlrc that makes the shell bearable. Plus where a GUI still wins.
• Postgres – Install and Connect
  Postgres 16 in a container in one command, then the same thing done properly: a docker-compose file with a named volume, a healthcheck that waits for the database rather than the process, and an init script. Why the published port is the one that enforces a password and the container socket is not, what happens to your data on `down -v`, and how to connect from psql, a URI and an app.
• Postgres – Introduction
  What Postgres actually is under the marketing: one process per connection, a write-ahead log, and MVCC — which is why a reader never blocks a writer and why VACUUM has to exist. Which version to run, what the release cycle promises, and an honest table of where Postgres beats MySQL, where it does not, and the three questions worth asking before you pick either.