PostgreSQL flashcards

The open source relational database that does it all.

Beginner friendly, starts from zero

CardsCheatsheetTips and tricks

PostgreSQLStart from zero · 1 of 12

What is PostgreSQL, in one plain sentence?

Think of the answer, then tap

PostgreSQL (often called Postgres) is a free program that stores an app's data in and answers questions about it, safely, for many people at once.

A tireless record keeper that never loses a page.

Space turn over · K got it · J again · ← back

All topics

PostgreSQL at a glance

PostgreSQL is a free database that keeps an app's data in tables and answers questions about it.

Like a strict, fast librarian: every fact on a labelled shelf, and any record found the moment you ask.

Words you will meet

Table: A grid of data, like one sheet in a spreadsheet
Row: One record in a table, such as one customer
Column: One kind of fact in a table, such as the city
Query: One question or command you send to the database
Primary key: The column that gives each row its own unique id
Foreign key: A column that points to a row in another table
NULL: No value or unknown, not the same as zero
Index: A shortcut that helps the database find rows fast

The steps

1. Start from zero (12 cards): What a database is, why it exists, and the words you need before Core basics.
2. Core basics (17 cards): psql, tables, types, keys and the everyday queries you run on day one.
3. Next steps (16 cards): Transactions, indexes, query plans, JSONB, upserts, roles and backups.
4. Go deeper (18 cards): MVCC, vacuum, locking, isolation, partitioning, replication and tuning at scale.

All 12 cards in Start from zero

What is PostgreSQL, in one plain sentence?: PostgreSQL (often called Postgres) is a free program that stores an app's data in tables and answers questions about it, safely, for many people at once.
Before databases, how did a shop keep track of thousands of orders?: In ledgers, spreadsheets or loose files. Two people editing at once overwrote each other, files got damaged, and finding one order among millions took ages.
Where do you already meet a database like PostgreSQL in daily life?: Your bank balance, a train ticket booking, your order history in a shopping app and your saved address are all rows in a database behind the app.
What is a good everyday picture of a database?: A very strict, very fast librarian. Every fact goes on a labelled shelf, he refuses anything that breaks the rules, and he finds any record the moment you ask.
What are a table, a row and a column?: A table is like one sheet in a spreadsheet, such as customers. Each row is one record (one customer) and each column is one kind of fact (name, city). Relational means tables can link to each other.
Your first tiny win: what does this SQL ask?: SQL is the language you talk to the database in, and one question is a query. SELECT picks which columns, FROM names which table, and WHERE keeps only which rows match.
What do server, client and connection mean for a database?: PostgreSQL runs as a server, a program that waits for questions. You reach it with a client such as psql, opening a connection with a user name, a password and a database name.
What is a data type, and what does NULL mean?: Each column has a data type: text, a whole number, a decimal, a date and time, or true and false. NULL means no value or unknown, which is not the same as zero or empty text.
What are a primary key and a foreign key?: A primary key is a column that gives each row its own unique id, like a roll number in a class. A foreign key is a column that points to a row in another table, like an order pointing to its customer.
What are a constraint and an index?: A constraint is a rule the database enforces, such as 'price cannot be negative'. An index is like the index at the back of a book: it lets the database find rows fast without reading every one.
What do you need before you start?: Nothing to install to begin: read the cards and their example results. To try queries yourself, install PostgreSQL 18 on your computer, which also gives you the psql tool.
After this topic, what will you be able to do?: Connect to a database, create tables that refuse bad data, add, change and remove rows, join tables together and build simple reports with counts and totals.

PostgreSQL cheatsheet

psql

\l \c db \dn Databases, connect, schemas
\dt \d+ orders Tables, full table detail
\di \df \du Indexes, functions, roles
\x auto Expanded view for wide rows
\timing on Show query time
\e Edit last query in $EDITOR
\copy t TO 'out.csv' CSV HEADER Export from the client side
\watch 2 Rerun the last query every 2 s

Postgres SQL

INSERT ... RETURNING id Get generated values back
ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v Upsert
SELECT DISTINCT ON (k) ... ORDER BY k, ts DESC Latest row per key
FOR UPDATE SKIP LOCKED Job queue pick
generate_series('2026-10-01'::date, '2026-10-31', '1 day') Fill date gaps
a IS DISTINCT FROM b NULL-safe compare
x::int / now() - interval '7 days' Cast, date maths

JSONB and arrays

data->'a' / data->>'a' Get as jsonb / as text
data #>> '{a,b}' Nested path as text
data @> '{"k":"v"}' Contains (GIN-indexable)
data ? 'k' Key exists
jsonb_set(data, '{a}', '1') Change one key
data - 'k' Remove a key
tags @> ARRAY['rust'] Array contains

Performance

EXPLAIN (ANALYZE, BUFFERS) Real plan with I/O
CREATE INDEX CONCURRENTLY Build without blocking writes
CREATE INDEX ... WHERE cond Partial index
USING gin (col jsonb_path_ops) Fast jsonb containment
USING brin (created_at) Tiny index for append-only time
pg_stat_statements Top queries by total time
ANALYZE orders Refresh planner statistics

Admin and safety

SET lock_timeout = '3s' Fail fast in migrations
ALTER ROLE r SET statement_timeout = '5s' Cap query time
pg_dump -Fc / pg_restore -j 8 Backup and parallel restore
SELECT pg_cancel_backend(pid) Cancel a query
SELECT pg_terminate_backend(pid) Kill a session
pg_stat_activity WHERE state <> 'idle' What is running now
SELECT pg_reload_conf() Reload config, no restart