PostgreSQL Cheat Sheet

Docker: a server in one command

docker run -d --name pg -p 5432:5432 \
  -e POSTGRES_PASSWORD=change-me -e POSTGRES_USER=app -e POSTGRES_DB=shop \
  -v pgdata:/var/lib/postgresql postgres:18

docker exec -it pg psql -U app -d shop        # psql inside the container
docker logs pg                                # first start runs initdb and the init scripts

Docker Compose with a health check

services:
  db:
    image: postgres:18
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: change-me
      POSTGRES_DB: shop
    ports: ["5432:5432"]
    volumes:
      - pgdata:/var/lib/postgresql        # 18+: the parent directory, not .../data
      - ./initdb:/docker-entrypoint-initdb.d:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d shop"]
      interval: 5s
      retries: 10
  api:
    depends_on:
      db: { condition: service_healthy }
volumes:
  pgdata:

Linux, macOS and Windows

# Debian / Ubuntu: the PGDG repository has every supported major version
sudo apt install -y postgresql-common && sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql-18
# RHEL / Rocky / Alma: dnf install from download.postgresql.org, then
sudo /usr/pgsql-18/bin/postgresql-18-setup initdb && sudo systemctl enable --now postgresql-18
# macOS
brew install postgresql@18 && brew services start postgresql@18
# Windows: the EDB installer from postgresql.org, or winget install PostgreSQL.PostgreSQL.18

Service control and where things live

sudo systemctl status postgresql          # Debian names the unit postgresql@18-main
sudo pg_lsclusters                        # Debian: clusters, ports, data directories
sudo -u postgres psql -c 'SHOW data_directory;'
sudo -u postgres psql -c 'SHOW config_file;'
pg_ctl -D /var/lib/postgresql/18/main status     # the low level tool behind the service
pg_ctl -D "$PGDATA" reload                   # re-read postgresql.conf and pg_hba.conf

psql connections, from simplest to explicit

sudo -u postgres psql                         # peer authentication as the postgres OS user
psql -h db.internal -p 5432 -U app -d shop
psql "postgresql://app@db.internal:5432/shop?sslmode=verify-full"
psql -h db.internal -U app -d shop -c 'SELECT version();'
psql -d shop -f migration.sql -v ON_ERROR_STOP=1 --single-transaction

Stop typing the password: .pgpass and service files

# ~/.pgpass, chmod 600, one line per server: host:port:database:user:password
db.internal:5432:shop:app:s3cret
*:5432:*:postgres:another-secret

# ~/.pg_service.conf, then: psql service=shop-prod
[shop-prod]
host=db.internal
port=5432
dbname=shop
user=app
sslmode=verify-full

export PGHOST=db.internal PGUSER=app PGDATABASE=shop     # libpq reads these too

Allow remote connections: listen_addresses and pg_hba.conf

# postgresql.conf
listen_addresses = '*'                  # default 'localhost'; restart after changing

# pg_hba.conf, first matching line wins: type  database  user  address  method
local   all       postgres                    peer
host    shop      app       10.0.0.0/16       scram-sha-256
hostssl all       all       0.0.0.0/0         scram-sha-256
host    all       all       0.0.0.0/0         reject

SELECT pg_reload_conf();                        -- pg_hba.conf changes only need a reload
SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;

Create a cluster by hand

initdb -D /data/pg18 --encoding=UTF8 --locale-provider=icu --icu-locale=en-US --auth-local=peer --auth-host=scram-sha-256
pg_ctl -D /data/pg18 -l /data/pg18/server.log start
initdb -D /data/pg18 --no-data-checksums     # 18 enables checksums by default; opt out only to match an old cluster for pg_upgrade

List and describe objects

\l                       -- databases
\c shop                  -- connect to another database
\conninfo                -- who and where you are connected
\dn                      -- schemas
\dt                      -- tables in the search_path
\dt billing.*            -- tables in one schema
\d orders                -- columns, indexes, constraints, triggers
\d+ orders               -- plus storage, size, description
\di+ orders*             -- indexes with sizes
\dv  \dm  \ds  \df       -- views, materialized views, sequences, functions
\du                      -- roles
\dx                      -- installed extensions
\dp orders               -- privileges on a table

Help and leaving

\?                  -- every meta-command
\h CREATE INDEX     -- SQL syntax for a statement
\h                  -- list of SQL commands
\q                  -- quit (Ctrl+D also works; exit and quit work since PostgreSQL 11)

Expanded rows, timing, null display

\x auto              -- one column per line when the row is too wide
\timing on           -- print how long each statement took
\pset null '(null)'  -- make NULL visible
\pset linestyle unicode
\pset border 2
SELECT * FROM pg_stat_activity WHERE pid = pg_backend_pid() \gx     -- \gx: expanded for one query

Output to CSV, a file or a pager

\pset format csv
SELECT id, email FROM customers LIMIT 3;
\pset format aligned

\o /tmp/report.txt       -- send output to a file
SELECT count(*) FROM orders;
\o                       -- back to the screen

psql -d shop --csv -c 'SELECT * FROM orders' > orders.csv     -- from the shell
psql -d shop -At -c 'SELECT count(*) FROM orders'             -- unaligned, tuples only: easy to script

Conditionals in psql scripts

SELECT count(*) > 0 AS has_orders FROM orders \gset
\if :has_orders
  \echo 'orders exist, skipping seed'
\else
  \i seed_orders.sql
\endif
psql -v ON_ERROR_STOP=1 -v env=staging -f deploy.sql     -- pass variables from the shell

Why did that fail: \errverbose and \gdesc

SELECT * FROM ordrs;
\errverbose                              -- the last error with SQLSTATE and source location
SELECT id, total * 1.21 AS gross, created_at::date FROM orders \gdesc

\bind: extended protocol and parameters from psql

SELECT * FROM orders WHERE customer_id = $1 AND status = $2 \bind 12 'paid' \g

Pivot a result with \crosstabview

SELECT country, date_trunc('month', created_at)::date AS month, sum(total)
FROM orders JOIN customers c ON c.id = orders.customer_id
GROUP BY 1, 2 ORDER BY 1, 2 \crosstabview

Edit, repeat, include

\e                    -- open the last query in $EDITOR, run it on save
\ef my_function       -- edit a function definition
\sf my_function       -- show a function definition
\i setup.sql          -- run a file
\ir ../common.sql     -- relative to the current script
\s                    -- history
\watch 2              -- re-run the last query every 2 seconds

Variables, \gset and \gexec

\set customer_id 42
SELECT * FROM orders WHERE customer_id = :customer_id;
\set email 'ana@example.com'
SELECT * FROM customers WHERE email = :'email';          -- :'x' quotes as a literal
SELECT count(*) AS n FROM orders \gset
\echo :n orders

SELECT format('VACUUM (ANALYZE) %I.%I;', schemaname, relname)
FROM pg_stat_user_tables WHERE n_dead_tup > 100000 \gexec

A sensible ~/.psqlrc

\set QUIET 1
\pset null '(null)'
\x auto
\timing on
\set HISTSIZE 10000
\set HISTFILE ~/.psql_history- :DBNAME
\set ON_ERROR_ROLLBACK interactive
\set PROMPT1 '%n@%M/%/%R%# '
\unset QUIET

\copy: load and export files from the client machine

\copy products (sku, name, price) FROM 'products.csv' WITH (FORMAT csv, HEADER true)
\copy (SELECT * FROM orders WHERE created_at >= '2026-09-01') TO 'september.csv' WITH (FORMAT csv, HEADER true)

Create, rename, drop

CREATE DATABASE shop OWNER app ENCODING 'UTF8' TEMPLATE template0;
CREATE DATABASE shop_test TEMPLATE shop;               -- fast copy; nobody may be connected to shop
ALTER DATABASE shop RENAME TO shop_old;
DROP DATABASE IF EXISTS shop_test;
DROP DATABASE shop_old WITH (FORCE);                  -- 13+: terminates its connections first

ICU collation for a new database

CREATE DATABASE shop_ro TEMPLATE template0 LOCALE_PROVIDER icu ICU_LOCALE 'ro-RO' ENCODING 'UTF8';
SELECT datname, datlocprovider, datcollate, datcollversion FROM pg_database;
ALTER DATABASE shop REFRESH COLLATION VERSION;         -- after reindexing, when the library changed

Case and accent insensitive collations

CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE TABLE members (email text COLLATE ci UNIQUE);
INSERT INTO members VALUES ('Ana@Example.com');
INSERT INTO members VALUES ('ana@example.com');          -- rejected: equal under ci
SELECT * FROM members WHERE email = 'ANA@EXAMPLE.COM';    -- found

Per database settings and connection limits

ALTER DATABASE shop SET timezone = 'UTC';
ALTER DATABASE shop SET statement_timeout = '30s';
ALTER DATABASE shop CONNECTION LIMIT 200;
ALTER DATABASE shop_archive ALLOW_CONNECTIONS false;     -- keep it, but nobody can connect
SELECT d.datname, s.setconfig FROM pg_db_role_setting s JOIN pg_database d ON d.oid = s.setdatabase;

Namespaces inside a database

CREATE SCHEMA billing AUTHORIZATION app;
CREATE TABLE billing.invoices (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, total numeric(12,2));
SHOW search_path;
SET search_path TO billing, public;                     -- this session
ALTER ROLE app SET search_path = billing, public;       -- every new session of app
ALTER SCHEMA billing RENAME TO finance;
DROP SCHEMA finance CASCADE;                            -- and everything in it

Sizes of databases, schemas and tables

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database ORDER BY pg_database_size(datname) DESC;

SELECT relname AS table,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS heap,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 5;

What is in this database

SELECT table_schema, table_name, table_type FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY 1, 2;

SELECT n.nspname AS schema, count(*) AS tables
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p') AND n.nspname NOT LIKE 'pg\_%' AND n.nspname <> 'information_schema'
GROUP BY 1 ORDER BY 2 DESC;

Pick the smallest type that is certainly big enough

Exact versus approximate arithmetic

SELECT 0.1::float8 + 0.2::float8 AS float_sum, 0.1::numeric + 0.2::numeric AS numeric_sum;
SELECT 7 / 2 AS int_div, 7 / 2.0 AS num_div, 7 % 2 AS remainder;
SELECT round(2.675::numeric, 2), round(2.675::float8::numeric, 2);

text versus varchar versus char

CREATE TABLE people (
  name  text NOT NULL CHECK (length(name) BETWEEN 1 AND 200),
  code  varchar(3),
  flag  char(2)
);
SELECT 'ab'::char(4) || '|' AS padded, length('ab'::char(4)) AS len;

timestamptz is almost always the right choice

SELECT now(), now()::date, current_time, localtimestamp;
SET timezone = 'Europe/Bucharest';
SELECT '2026-09-29 10:00:00+00'::timestamptz;                 -- shown in the session time zone
SELECT '2026-09-29 10:00:00'::timestamp AT TIME ZONE 'UTC';   -- timestamp to timestamptz
SELECT now() AT TIME ZONE 'America/New_York';                  -- timestamptz to local wall clock

Intervals and date arithmetic

SELECT now() + interval '7 days', now() - interval '1 month 2 hours';
SELECT '2026-09-29'::date - '2026-01-01'::date AS days_between;       -- integer
SELECT age('2026-09-29', '1990-03-14');                              -- years mons days
SELECT date_trunc('month', now()), date_trunc('week', now());
SELECT extract(epoch FROM interval '2 hours 30 minutes');            -- seconds
SELECT make_date(2026, 9, 29), make_interval(days => 10);

UUID keys: random or time ordered

SELECT gen_random_uuid();          -- version 4, built in since 13
SELECT uuidv7();                   -- 18+: time ordered, friendlier to B-tree indexes
SELECT uuid_extract_timestamp(uuidv7());
CREATE TABLE events (id uuid PRIMARY KEY DEFAULT uuidv7(), payload jsonb);

Enums and domains

CREATE TYPE order_status AS ENUM ('new', 'paid', 'shipped', 'refunded');
ALTER TYPE order_status ADD VALUE 'cancelled' AFTER 'paid';
SELECT enum_range(NULL::order_status);

CREATE DOMAIN email AS text CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$');
CREATE TABLE subscribers (address email NOT NULL);

Identity columns instead of serial

CREATE TABLE customers (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);
INSERT INTO customers (name) VALUES ('Ana') RETURNING id;
INSERT INTO customers (id, name) OVERRIDING SYSTEM VALUE VALUES (1000, 'Imported');
SELECT setval(pg_get_serial_sequence('customers', 'id'), (SELECT max(id) FROM customers));

Types people forget PostgreSQL has

SELECT '192.168.1.20'::inet << '192.168.1.0/24'::cidr AS in_subnet;
SELECT '\xdeadbeef'::bytea, md5('hello'), sha256('hello'::bytea);
SELECT 'fat & rat'::tsquery, '[2026-09-01,2026-10-01)'::daterange;
SELECT '(1,2)'::point <-> '(4,6)'::point AS distance;

A table with the constraints you will actually want

CREATE TABLE orders (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers (id) ON DELETE RESTRICT,
  status       text NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'paid', 'shipped', 'refunded')),
  total        numeric(12,2) NOT NULL CHECK (total >= 0),
  email        text NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  UNIQUE (customer_id, created_at)
);
CREATE INDEX ON orders (customer_id);
COMMENT ON TABLE orders IS 'One row per checkout';

Generated columns, virtual by default in 18

CREATE TABLE line_items (
  qty         integer NOT NULL,
  unit_price  numeric(10,2) NOT NULL,
  amount      numeric(12,2) GENERATED ALWAYS AS (qty * unit_price) VIRTUAL,
  search      tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(note, ''))) STORED,
  note        text
);

Exclusion constraints: no overlapping bookings

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
  room_id  integer NOT NULL,
  during   tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);
INSERT INTO bookings VALUES (1, '[2026-10-01 14:00, 2026-10-03 11:00)');
INSERT INTO bookings VALUES (1, '[2026-10-02 14:00, 2026-10-04 11:00)');

Temporal primary keys, 18+

CREATE TABLE prices (
  sku       text NOT NULL,
  valid     daterange NOT NULL,
  price     numeric(10,2) NOT NULL,
  PRIMARY KEY (sku, valid WITHOUT OVERLAPS)
);
CREATE TABLE price_refs (
  sku text, valid daterange,
  FOREIGN KEY (sku, PERIOD valid) REFERENCES prices (sku, PERIOD valid)
);

UNIQUE NULLS NOT DISTINCT

CREATE TABLE coupons (code text, customer_id bigint, UNIQUE NULLS NOT DISTINCT (code, customer_id));
INSERT INTO coupons VALUES ('WELCOME', NULL);
INSERT INTO coupons VALUES ('WELCOME', NULL);     -- rejected from 15 with NULLS NOT DISTINCT

Temporary, unlogged and copied tables

CREATE TEMP TABLE staging (LIKE products INCLUDING DEFAULTS);   -- gone at the end of the session
CREATE UNLOGGED TABLE import_buffer (line text);                -- fast, not crash safe, not replicated
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);        -- columns, defaults, constraints, indexes
CREATE TABLE top_customers AS SELECT customer_id, sum(total) FROM orders GROUP BY 1;

Sequences and identity values

CREATE SEQUENCE invoice_no START 1000 INCREMENT 1;
SELECT nextval('invoice_no'), currval('invoice_no');
SELECT setval('invoice_no', 5000);                       -- next value is 5001
ALTER TABLE customers ALTER COLUMN id RESTART WITH 1000;  -- identity columns
SELECT sequencename, last_value, increment_by FROM pg_sequences;

Common ALTER TABLE forms

ALTER TABLE orders ADD COLUMN note text;
ALTER TABLE orders ADD COLUMN source text NOT NULL DEFAULT 'web';     -- instant since 11
ALTER TABLE orders RENAME COLUMN note TO customer_note;
ALTER TABLE orders ALTER COLUMN customer_note TYPE varchar(500);
ALTER TABLE orders ALTER COLUMN source DROP DEFAULT;
ALTER TABLE orders ALTER COLUMN email SET NOT NULL;
ALTER TABLE orders DROP COLUMN IF EXISTS legacy_flag;
ALTER TABLE orders RENAME TO purchases;

Add constraints on a big table without a long lock

SET lock_timeout = '3s';                               -- give up instead of queueing everyone behind you
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;     -- scans, but lets reads and writes continue

ALTER TABLE orders ADD CONSTRAINT orders_email_nn CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_email_nn;
ALTER TABLE orders ALTER COLUMN email SET NOT NULL;   -- 12+: uses the validated check, no scan
ALTER TABLE orders DROP CONSTRAINT orders_email_nn;

Which changes rewrite the whole table

Filter, sort, limit

SELECT id, name, email
FROM customers
WHERE country = 'RO' AND created_at >= '2026-01-01'
ORDER BY created_at DESC NULLS LAST, id
LIMIT 20 OFFSET 40;
SELECT * FROM products ORDER BY price DESC FETCH FIRST 3 ROWS WITH TIES;   -- standard form, keeps ties

Every WHERE operator you will use

WHERE price BETWEEN 10 AND 50              -- inclusive at both ends
WHERE status IN ('paid', 'shipped')
WHERE id = ANY ('{3,7,9}'::bigint[])        -- one array parameter instead of a long IN list
WHERE name ILIKE 'an%'                      -- case insensitive LIKE
WHERE sku ~ '^OAK-[0-9]+$'                  -- POSIX regex; ~* ignores case, !~ negates
WHERE email IS NULL
WHERE shipped_at IS DISTINCT FROM delivered_at    -- NULL aware inequality
WHERE (country, city) = ('RO', 'Cluj')      -- row comparison

NULL rules that trip people up

SELECT NULL = NULL AS eq, NULL IS NULL AS is_null, NULL IS NOT DISTINCT FROM NULL AS same;
SELECT coalesce(phone, email, 'no contact') FROM customers;
SELECT nullif(discount, 0) FROM orders;          -- 0 becomes NULL, avoids division by zero
SELECT count(*) FROM orders WHERE coupon <> 'SUMMER';   -- rows with a NULL coupon are not counted

DISTINCT ON: the first row of each group

SELECT DISTINCT ON (customer_id) customer_id, id, total, created_at
FROM orders
ORDER BY customer_id, created_at DESC;         -- latest order per customer

CASE, and FILTER instead of CASE inside aggregates

SELECT id,
       CASE WHEN total >= 500 THEN 'large'
            WHEN total >= 100 THEN 'medium'
            ELSE 'small' END AS size
FROM orders;

SELECT count(*) FILTER (WHERE status = 'paid')     AS paid,
       count(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders;

Random samples without sorting the table

SELECT * FROM events TABLESAMPLE SYSTEM (1);              -- about 1% of pages, very fast
SELECT * FROM events TABLESAMPLE BERNOULLI (1) REPEATABLE (42);   -- about 1% of rows, reproducible
SELECT * FROM products ORDER BY random() LIMIT 5;         -- fine for small tables only

Inline data with VALUES

SELECT * FROM (VALUES (1, 'RO'), (2, 'DE'), (3, 'FR')) AS t(id, country);
SELECT o.* FROM orders o JOIN (VALUES (10042), (10049)) AS pick(id) USING (id);

LATERAL: the top 3 orders per customer

SELECT c.name, t.id, t.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT o.id, o.total FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.total DESC LIMIT 3
) t;

USING, self joins, several tables

SELECT * FROM order_items JOIN order_notes USING (order_id);   -- one output column for order_id
SELECT e.name AS employee, m.name AS manager
FROM staff e LEFT JOIN staff m ON m.id = e.manager_id;       -- self join
SELECT o.id, c.name, p.name AS product
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id;

Combine result sets

SELECT email FROM customers UNION SELECT email FROM newsletter;      -- distinct rows
SELECT email FROM customers UNION ALL SELECT email FROM newsletter;  -- keeps duplicates, faster
SELECT email FROM customers INTERSECT SELECT email FROM newsletter;  -- in both
SELECT email FROM customers EXCEPT SELECT email FROM newsletter;     -- in the first only

Semi join and join with an array column

SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 500);
SELECT p.name, t.tag FROM products p CROSS JOIN LATERAL unnest(p.tags) AS t(tag);
SELECT p.* FROM products p JOIN tags t ON t.name = ANY (p.tags);

GROUP BY with HAVING

SELECT status, count(*) AS orders, round(avg(total), 2) AS avg_total, sum(total) AS revenue
FROM orders
WHERE created_at >= date_trunc('month', now())
GROUP BY status
HAVING count(*) > 10
ORDER BY revenue DESC;

count variants and NULLs

SELECT count(*) AS rows, count(phone) AS with_phone, count(DISTINCT country) AS countries FROM customers;
SELECT coalesce(sum(total), 0) FROM orders WHERE customer_id = 999;   -- sum of no rows is NULL

Strings, arrays and JSON from groups

SELECT o.customer_id,
       string_agg(DISTINCT i.sku, ', ' ORDER BY i.sku) AS skus,
       array_agg(DISTINCT o.status) AS statuses,
       jsonb_agg(DISTINCT jsonb_build_object('id', o.id, 'total', o.total)) AS orders
FROM orders o JOIN order_items i ON i.order_id = o.id
GROUP BY o.customer_id;

Subtotals and a grand total in one query

SELECT country, status, sum(total) AS revenue, grouping(country, status) AS level
FROM orders JOIN customers c ON c.id = orders.customer_id
GROUP BY ROLLUP (country, status)
ORDER BY country NULLS LAST, status NULLS LAST;

Median, percentiles, mode

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY total) AS median,
       percentile_cont(ARRAY[0.9, 0.99]) WITHIN GROUP (ORDER BY total) AS p90_p99,
       percentile_disc(0.5) WITHIN GROUP (ORDER BY total) AS median_actual_value,
       mode() WITHIN GROUP (ORDER BY status) AS most_common_status
FROM orders;

Fill the gaps: days with no orders

SELECT d::date AS day, count(o.id) AS orders
FROM generate_series('2026-09-01'::date, '2026-09-03', interval '1 day') AS d
LEFT JOIN orders o ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY d ORDER BY d;

any_value, bool_and and friends

SELECT customer_id, any_value(email) AS email, bool_and(paid) AS all_paid, bool_or(refunded) AS any_refund,
       min(created_at), max(created_at)
FROM orders GROUP BY customer_id;

Scalar, list, derived table, correlated

SELECT name, (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS orders FROM customers c;
SELECT * FROM products WHERE price > (SELECT avg(price) FROM products);
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > 500);
SELECT avg(n) FROM (SELECT customer_id, count(*) AS n FROM orders GROUP BY 1) per_customer;

ANY and ALL

SELECT * FROM products WHERE price > ALL (SELECT price FROM products WHERE category = 'chairs');
SELECT * FROM orders WHERE status = ANY (ARRAY['paid', 'shipped']);
SELECT * FROM customers WHERE email ILIKE ANY (ARRAY['%@gmail.com', '%@yahoo.com']);

WITH: name the steps

WITH monthly AS (
  SELECT date_trunc('month', created_at) AS month, sum(total) AS revenue
  FROM orders GROUP BY 1
)
SELECT month, revenue, revenue - lag(revenue) OVER (ORDER BY month) AS change
FROM monthly ORDER BY month;

Recursive CTE: walk a tree

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth, ARRAY[id] AS path
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1, t.path || c.id
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT repeat('  ', depth - 1) || name AS category FROM tree ORDER BY path;

SEARCH and CYCLE clauses

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name FROM categories c JOIN tree t ON c.parent_id = t.id
) SEARCH DEPTH FIRST BY id SET ordercol
  CYCLE id SET is_cycle USING path
SELECT name FROM tree ORDER BY ordercol;

Data-modifying CTE: move rows in one statement

WITH moved AS (
  DELETE FROM orders
  WHERE created_at < now() - interval '2 years'
  RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;

Latest row per group, three ways

SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, created_at DESC;

SELECT * FROM (
  SELECT o.*, row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn FROM orders o
) t WHERE rn = 1;

SELECT c.id, last.* FROM customers c
CROSS JOIN LATERAL (SELECT * FROM orders o WHERE o.customer_id = c.id ORDER BY created_at DESC LIMIT 1) last;

row_number, rank, dense_rank

SELECT name, category, price,
       row_number() OVER w AS rn,
       rank()       OVER w AS rnk,
       dense_rank() OVER w AS dense
FROM products
WINDOW w AS (PARTITION BY category ORDER BY price DESC);

Top N per group

SELECT * FROM (
  SELECT p.*, row_number() OVER (PARTITION BY category ORDER BY sold DESC) AS rn
  FROM products p
) ranked
WHERE rn <= 3;

Running total, share of total, change from previous

SELECT created_at::date AS day, total,
       sum(total) OVER (ORDER BY created_at) AS running,
       round(100 * total / sum(total) OVER (), 1) AS pct,
       total - lag(total) OVER (ORDER BY created_at) AS diff
FROM orders WHERE customer_id = 12;

Moving averages with frames

SELECT day, revenue,
       avg(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_rows,
       avg(revenue) OVER (ORDER BY day RANGE BETWEEN interval '6 days' PRECEDING AND CURRENT ROW) AS avg_7_days
FROM daily_revenue;

first, last and nth values

SELECT customer_id, created_at,
       first_value(total) OVER w AS first_order,
       last_value(total)  OVER w AS last_order,
       nth_value(total, 2) OVER w AS second_order
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

IGNORE NULLS in window functions, PostgreSQL 19

SELECT day, price,
       lag(price) IGNORE NULLS OVER (ORDER BY day) AS last_known_price
FROM price_history;

Gaps and islands: consecutive active days

SELECT user_id, min(day) AS streak_start, max(day) AS streak_end, count(*) AS days
FROM (
  SELECT user_id, day, day - (row_number() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
  FROM (SELECT DISTINCT user_id, created_at::date AS day FROM events) d
) t
GROUP BY user_id, grp
ORDER BY days DESC LIMIT 5;

Sessions: a new session after 30 idle minutes

SELECT user_id, ts, sum(new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_no
FROM (
  SELECT user_id, ts,
         CASE WHEN ts - lag(ts) OVER (PARTITION BY user_id ORDER BY ts) <= interval '30 minutes' THEN 0 ELSE 1 END AS new_session
  FROM events
) e;

Distribution: percent_rank, cume_dist, ntile

SELECT id, total,
       round(percent_rank() OVER (ORDER BY total)::numeric, 3) AS pct_rank,
       ntile(4) OVER (ORDER BY total) AS quartile
FROM orders WHERE customer_id = 12;

The string functions you use every week

SELECT concat_ws(' ', first_name, last_name) AS full_name,      -- skips NULLs
       first_name || ' ' || last_name AS joined,                  -- NULL if either is NULL
       upper(email), initcap('ana maria'), length('ăîș'),
       left(sku, 3), right(sku, 2), lpad(id::text, 6, '0'),
       trim('  x  '), replace(phone, ' ', ''), position('@' IN email)
FROM customers LIMIT 1;

format(): safe string building

SELECT format('Hello %s, you have %s orders', name, order_count) FROM customer_stats;
SELECT format('SELECT count(*) FROM %I.%I WHERE status = %L', 'public', 'Orders', 'paid');

Split, join and pick apart

SELECT split_part('2026-09-29', '-', 2);                       -- 09
SELECT string_to_array('oak,ash,elm', ',');                    -- {oak,ash,elm}
SELECT array_to_string(ARRAY['a', 'b'], ' | ');
SELECT regexp_split_to_table('a1b22c', '\d+');                 -- a, b, c
SELECT * FROM unnest(string_to_array('x;y;z', ';')) WITH ORDINALITY AS t(item, n);

Regular expressions

SELECT regexp_replace('Oak   desk', '\s+', ' ', 'g');
SELECT (regexp_match('SKU OAK-1204', '([A-Z]+)-(\d+)'))[2];    -- first match, as an array
SELECT regexp_matches('a1 b2 c3', '([a-z])(\d)', 'g');         -- every match, one row each
SELECT 'OAK-1' ~ '^[A-Z]+-\d+$' AS valid;

regexp_count, regexp_substr, regexp_instr, regexp_like

SELECT regexp_count('banana', 'a'), regexp_substr('Order #10042', '\d+');
SELECT regexp_instr('Order #10042', '\d'), regexp_like('OAK-1', '^[A-Z]+-\d+$');

casefold() for case insensitive comparison

SELECT casefold('Straße') = casefold('STRASSE');
CREATE INDEX ON customers (casefold(email));
SELECT * FROM customers WHERE casefold(email) = casefold('Ana@Example.com');

Truncate, extract, format, parse

SELECT date_trunc('day', now()), date_trunc('month', now()), date_trunc('year', now());
SELECT extract(year FROM now()), extract(isodow FROM now()), extract(epoch FROM now());
SELECT to_char(now(), 'YYYY-MM-DD HH24:MI'), to_char(1234567.8, 'FM9G999G990D00');
SELECT to_date('29.09.2026', 'DD.MM.YYYY'), to_timestamp(1790000000);

now() versus clock_timestamp()

BEGIN;
SELECT now(), clock_timestamp();
SELECT pg_sleep(2);
SELECT now(), clock_timestamp();    -- now() is unchanged inside the transaction
COMMIT;

Bucket timestamps with date_bin

SELECT date_bin('15 minutes', created_at, '2026-01-01') AS bucket, count(*)
FROM events GROUP BY 1 ORDER BY 1;

Rounding, ranges, buckets, random

SELECT round(2.567, 2), trunc(2.567, 1), ceil(2.1), floor(-2.1), abs(-5);
SELECT greatest(3, 7, 5), least(3, 7, 5);
SELECT width_bucket(total, 0, 500, 5) AS bucket, count(*) FROM orders GROUP BY 1 ORDER BY 1;
SELECT random(), floor(random() * 6 + 1)::int AS die;

random(min, max)

SELECT random(1, 6) AS die, random(0.0, 1.0) AS fraction;
SELECT setseed(0.42); SELECT random(1, 100);        -- repeatable within the session

Conditional helpers

SELECT coalesce(nickname, name) FROM customers;
SELECT nullif(trim(phone), '') FROM customers;          -- empty string becomes NULL
SELECT greatest(updated_at, created_at) FROM orders;    -- ignores NULLs
SELECT num_nulls(phone, email, address), num_nonnulls(phone, email, address) FROM customers;

INSERT with RETURNING

INSERT INTO customers (name, email) VALUES ('Ana', 'ana@example.com') RETURNING id, created_at;
INSERT INTO customers (name, email) VALUES ('Radu', 'radu@example.com'), ('Ioana', 'ioana@example.com');
INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < '2025-01-01';
INSERT INTO settings DEFAULT VALUES;

Upsert with ON CONFLICT

INSERT INTO products (sku, name, price) VALUES ('OAK-1', 'Oak desk', 199)
ON CONFLICT (sku) DO UPDATE
SET name = EXCLUDED.name, price = EXCLUDED.price, updated_at = now()
WHERE products.price IS DISTINCT FROM EXCLUDED.price
RETURNING id, (xmax = 0) AS inserted;

INSERT INTO tags (name) VALUES ('oak') ON CONFLICT DO NOTHING;

ON CONFLICT DO SELECT, PostgreSQL 19

INSERT INTO tags (name) VALUES ('oak')
ON CONFLICT (name) DO SELECT
RETURNING id;                    -- the new row's id, or the existing row's

INSERT INTO tags (name) VALUES ('oak')
ON CONFLICT (name) DO SELECT FOR UPDATE
RETURNING *;                     -- and lock the existing row

UPDATE and DELETE, with RETURNING

UPDATE products SET price = price * 1.10 WHERE category = 'desks' RETURNING id, price;
DELETE FROM sessions WHERE expires_at < now() RETURNING id;
UPDATE orders SET status = 'shipped', shipped_at = now() WHERE id = 10042;

UPDATE FROM and DELETE USING: change rows based on another table

UPDATE customers c
SET lifetime_value = x.total, last_order_at = x.last_at
FROM (SELECT customer_id, sum(total) AS total, max(created_at) AS last_at FROM orders GROUP BY 1) x
WHERE x.customer_id = c.id;

DELETE FROM order_items i
USING orders o
WHERE o.id = i.order_id AND o.status = 'cancelled';

RETURNING OLD and NEW values

UPDATE products SET price = price * 1.10 WHERE id = 41
RETURNING old.price AS before, new.price AS after;

DELETE FROM carts WHERE id = 7 RETURNING old.*;

Delete millions of rows without one giant transaction

DELETE FROM events
WHERE ctid IN (SELECT ctid FROM events WHERE created_at < now() - interval '90 days' LIMIT 10000);
-- repeat until it reports DELETE 0; or drop a whole partition, which is instant

TRUNCATE: empty tables instantly

TRUNCATE sessions;
TRUNCATE orders, order_items RESTART IDENTITY;        -- also reset identity sequences
TRUNCATE customers CASCADE;                           -- and every table that references it

MERGE: sync a staging table into a target

MERGE INTO products p
USING staging_products s ON s.sku = p.sku
WHEN MATCHED AND s.discontinued THEN DELETE
WHEN MATCHED THEN UPDATE SET name = s.name, price = s.price
WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (s.sku, s.name, s.price);

MERGE with RETURNING and NOT MATCHED BY SOURCE

MERGE INTO stock t
USING feed f ON f.sku = t.sku
WHEN MATCHED THEN UPDATE SET qty = f.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (f.sku, f.qty)
WHEN NOT MATCHED BY SOURCE THEN DELETE
RETURNING merge_action(), t.sku, t.qty;

COPY from and to server files

COPY products (sku, name, price) FROM '/var/lib/postgresql/import/products.csv' WITH (FORMAT csv, HEADER true);
COPY (SELECT * FROM orders WHERE created_at >= '2026-09-01') TO '/tmp/september.csv' WITH (FORMAT csv, HEADER true);

Skip bad rows instead of failing the whole load

COPY products FROM STDIN WITH (FORMAT csv, HEADER true, ON_ERROR ignore, LOG_VERBOSITY verbose);

Stop after too many bad rows

COPY products FROM STDIN WITH (FORMAT csv, HEADER true, ON_ERROR ignore, REJECT_LIMIT 100);

Watch a long COPY

SELECT relid::regclass, command, type, bytes_processed, bytes_total, tuples_processed, tuples_excluded
FROM pg_stat_progress_copy;                     -- 14+

The operators: -> ->> #> #>>

SELECT meta -> 'address' AS address_json,          -- jsonb
       meta ->> 'tier' AS tier,                     -- text
       meta #> '{address,city}' AS city_json,       -- by path, jsonb
       meta #>> '{address,city}' AS city,           -- by path, text
       meta -> 'tags' -> 0 AS first_tag,
       (meta ->> 'score')::int + 1 AS score_plus_one
FROM customers WHERE id = 12;

Containment and existence

SELECT * FROM customers WHERE meta @> '{"tier": "gold"}';           -- contains
SELECT * FROM customers WHERE meta ? 'phone';                       -- top level key exists
SELECT * FROM customers WHERE meta ?| ARRAY['phone', 'mobile'];     -- any of these keys
SELECT * FROM customers WHERE meta -> 'tags' @> '["vip"]';          -- array contains an element
SELECT * FROM events WHERE payload @? '$.items[*] ? (@.qty > 10)';  -- jsonpath match

jsonpath queries

SELECT jsonb_path_query(payload, '$.items[*].sku') FROM events WHERE id = 7;
SELECT jsonb_path_query_array(payload, '$.items[*] ? (@.price > 100).sku') FROM events WHERE id = 7;
SELECT jsonb_path_exists(payload, '$.customer.email ? (@ like_regex "@example\\.com$")') FROM events;

Set, add and remove keys

UPDATE customers SET meta = jsonb_set(meta, '{address,city}', '"Iasi"') WHERE id = 12;
UPDATE customers SET meta = meta || '{"tier": "platinum", "vip": true}' WHERE id = 12;   -- merge, right side wins
UPDATE customers SET meta = meta - 'legacy_id' WHERE id = 12;                           -- remove a key
UPDATE customers SET meta = meta #- '{address,zip}' WHERE id = 12;                     -- remove by path
UPDATE customers SET meta['tier'] = '"gold"' WHERE id = 12;                            -- subscripting, 14+

Build JSON from rows

SELECT jsonb_build_object('id', id, 'name', name, 'orders', (
  SELECT coalesce(jsonb_agg(jsonb_build_object('id', o.id, 'total', o.total) ORDER BY o.id), '[]')
  FROM orders o WHERE o.customer_id = c.id)) AS doc
FROM customers c WHERE id = 12;
SELECT to_jsonb(c) - 'password_hash' FROM customers c WHERE id = 12;     -- a whole row, minus a column

Turn JSON into rows

SELECT key, value FROM jsonb_each('{"a": 1, "b": [2, 3]}');
SELECT elem ->> 'sku' AS sku, (elem ->> 'qty')::int AS qty FROM events, jsonb_array_elements(payload -> 'items') AS elem;
SELECT * FROM jsonb_to_recordset('[{"sku": "OAK-1", "qty": 2}, {"sku": "ASH-4", "qty": 1}]') AS t(sku text, qty int);

JSON_TABLE, JSON_VALUE, JSON_QUERY, JSON_EXISTS

SELECT jt.* FROM events e,
JSON_TABLE(e.payload, '$.items[*]' COLUMNS (
  sku   text          PATH '$.sku',
  qty   int           PATH '$.qty' DEFAULT 1 ON EMPTY,
  price numeric(10,2) PATH '$.price'
)) AS jt
WHERE e.id = 7;

SELECT JSON_VALUE(payload, '$.customer.email'), JSON_QUERY(payload, '$.items[0]'), JSON_EXISTS(payload, '$.coupon') FROM events;

Index JSONB

CREATE INDEX ON customers USING gin (meta jsonb_path_ops);    -- @>, @? and @@, smaller and faster
CREATE INDEX ON customers USING gin (meta);                   -- also ? ?| ?& key existence
CREATE INDEX ON customers ((meta ->> 'tier'));                 -- one key, B-tree, for = and ranges
EXPLAIN SELECT * FROM customers WHERE meta @> '{"tier": "gold"}';

Validate and construct with SQL/JSON

SELECT '{"a": 1}' IS JSON OBJECT, '[1, 2]' IS JSON ARRAY, 'oops' IS JSON;
SELECT JSON_OBJECT('id': 12, 'tier': 'gold'), JSON_ARRAY(1, 'two', NULL ABSENT ON NULL);
ALTER TABLE raw_events ADD CONSTRAINT payload_is_json CHECK (body IS JSON OBJECT);

Array columns and literals

CREATE TABLE products (id bigint PRIMARY KEY, name text, tags text[] NOT NULL DEFAULT '{}');
INSERT INTO products VALUES (1, 'Oak desk', ARRAY['wood', 'desk']), (2, 'Ash shelf', '{wood,shelf}');
SELECT tags[1], tags[2:3], cardinality(tags), array_length(tags, 1) FROM products;   -- 1 based
UPDATE products SET tags = array_append(tags, 'sale') WHERE id = 1;
UPDATE products SET tags = array_remove(tags, 'sale');
SELECT array_position(tags, 'desk') FROM products WHERE id = 1;

Search inside arrays

SELECT * FROM products WHERE 'desk' = ANY (tags);            -- element match
SELECT * FROM products WHERE tags @> ARRAY['wood', 'desk'];   -- has all of these
SELECT * FROM products WHERE tags && ARRAY['sale', 'new'];    -- has any of these
CREATE INDEX ON products USING gin (tags);                    -- makes @> and && fast

unnest and array_agg: rows to arrays and back

SELECT p.name, t.tag, t.pos FROM products p, unnest(p.tags) WITH ORDINALITY AS t(tag, pos);
SELECT customer_id, array_agg(id ORDER BY created_at) FROM orders GROUP BY 1;
SELECT * FROM unnest(ARRAY[1, 2, 3], ARRAY['a', 'b', 'c']) AS t(n, letter);     -- zip

array_sort and array_reverse

SELECT array_sort(ARRAY[3, 1, 2]), array_reverse(ARRAY[1, 2, 3]);
SELECT array_sort(tags, descending => true) FROM products;

Build, clean and compare arrays

SELECT ARRAY(SELECT DISTINCT unnest(tags) ORDER BY 1) FROM products;       -- dedupe and sort
SELECT array_agg(sku) FILTER (WHERE qty = 0) AS out_of_stock FROM stock;
SELECT ARRAY[1, 2] = ARRAY[2, 1] AS same_order, ARRAY[1, 2] <@ ARRAY[2, 1, 3] AS subset;

Range types and operators

SELECT '[2026-10-01,2026-10-05)'::daterange @> '2026-10-03'::date AS contains,
       '[1,10)'::int4range && '[5,20)'::int4range AS overlaps,
       lower('[2026-10-01,2026-10-05)'::daterange), upper('[2026-10-01,2026-10-05)'::daterange),
       isempty('[5,5)'::int4range);
SELECT * FROM bookings WHERE during && tstzrange('2026-10-01', '2026-10-02');
CREATE INDEX ON bookings USING gist (during);

Multiranges and range_agg

SELECT range_agg(during) FROM bookings WHERE room_id = 1;            -- merged busy periods
SELECT datemultirange('[2026-10-01,2026-10-10)') - datemultirange('[2026-10-03,2026-10-05)');

BEGIN, COMMIT, ROLLBACK, SAVEPOINT

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

BEGIN;
INSERT INTO orders (customer_id, total) VALUES (12, 50);
SAVEPOINT before_items;
INSERT INTO order_items (order_id, sku) VALUES (currval('orders_id_seq'), 'NOPE');   -- fails
ROLLBACK TO SAVEPOINT before_items;
COMMIT;

Isolation levels

SHOW default_transaction_isolation;                  -- read committed
BEGIN ISOLATION LEVEL REPEATABLE READ;               -- one snapshot for the whole transaction
BEGIN ISOLATION LEVEL SERIALIZABLE;                  -- as if transactions ran one at a time
BEGIN READ ONLY;
SET default_transaction_isolation = 'serializable';  -- per session

Read, lock, decide, write

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;     -- others wait to lock or update this row
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE;    -- weaker: does not block inserts of child rows
SELECT * FROM seats WHERE id = 42 FOR UPDATE NOWAIT;      -- fail at once instead of waiting

A job queue with SKIP LOCKED

WITH next AS (
  SELECT id FROM jobs
  WHERE status = 'queued' AND run_at <= now()
  ORDER BY run_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs j SET status = 'running', started_at = now(), worker = 'w3'
FROM next WHERE j.id = next.id
RETURNING j.id, j.payload;

Advisory locks: application level mutexes

SELECT pg_try_advisory_lock(hashtext('nightly-report'));   -- true if you got it, session lifetime
SELECT pg_advisory_unlock(hashtext('nightly-report'));
SELECT pg_advisory_xact_lock(42);                          -- released at COMMIT or ROLLBACK

Who is blocking whom

SELECT a.pid, a.usename, a.state, now() - a.xact_start AS in_tx,
       pg_blocking_pids(a.pid) AS blocked_by, left(a.query, 60) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0;

Timeouts that save you

SET lock_timeout = '5s';                              -- give up waiting for a lock
SET statement_timeout = '30s';                        -- cancel long queries
SET idle_in_transaction_session_timeout = '60s';      -- kill sessions that BEGIN and wander off
ALTER ROLE app SET statement_timeout = '15s';         -- default for every session of a role

transaction_timeout

SET transaction_timeout = '5min';
ALTER ROLE batch_jobs SET transaction_timeout = '30min';

Which lock each statement takes, and what it blocks

Deadlocks

-- session 1: UPDATE accounts SET ... WHERE id = 1; then WHERE id = 2
-- session 2: UPDATE accounts SET ... WHERE id = 2; then WHERE id = 1
SHOW deadlock_timeout;             -- 1s: how long to wait before checking for a deadlock
SET log_lock_waits = on;           -- log waits longer than deadlock_timeout

Match documents against a query

SELECT to_tsvector('english', 'The quick brown foxes jumped');
SELECT to_tsvector('english', 'foxes jumping') @@ to_tsquery('english', 'fox & jump');
SELECT websearch_to_tsquery('english', '"oak desk" -pine or walnut');   -- search box syntax

A searchable column with a GIN index

ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
                       setweight(to_tsvector('english', coalesce(body, '')), 'B')) STORED;
CREATE INDEX ON articles USING gin (search);

SELECT id, title, ts_rank(search, q) AS rank
FROM articles, websearch_to_tsquery('english', 'postgres vacuum') q
WHERE search @@ q
ORDER BY rank DESC LIMIT 10;

Highlight the matches

SELECT ts_headline('english', body, websearch_to_tsquery('english', 'vacuum'),
                   'StartSel=<mark>, StopSel=</mark>, MaxWords=30, MinWords=10')
FROM articles WHERE id = 118;

Prefix and phrase search

SELECT title FROM articles WHERE search @@ to_tsquery('english', 'postg:*');          -- prefix: search as you type
SELECT title FROM articles WHERE search @@ phraseto_tsquery('english', 'point in time recovery');
SELECT to_tsquery('english', 'logical <-> replication'), to_tsquery('english', 'vacuum <2> table');

Trigrams: typo tolerant search and fast ILIKE

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ON customers USING gin (name gin_trgm_ops);
SELECT name, similarity(name, 'Jonh Smiht') AS score
FROM customers WHERE name % 'Jonh Smiht' ORDER BY score DESC LIMIT 5;
SELECT * FROM customers WHERE name ILIKE '%smith%';     -- the same index serves this

Ignore accents

CREATE EXTENSION IF NOT EXISTS unaccent;
SELECT unaccent('Brașov, Timișoara, São Paulo');
CREATE TEXT SEARCH CONFIGURATION ro_unaccent (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION ro_unaccent ALTER MAPPING FOR hword, hword_part, word WITH unaccent, simple;

B-tree basics and multicolumn order

CREATE INDEX CONCURRENTLY orders_customer_created_idx ON orders (customer_id, created_at DESC);
-- serves: WHERE customer_id = ?                     (leftmost column)
-- serves: WHERE customer_id = ? ORDER BY created_at DESC LIMIT 10
-- serves: WHERE customer_id = ? AND created_at >= ?
-- 18+ can also skip scan it for: WHERE created_at >= ? (few distinct customer_id values)
CREATE UNIQUE INDEX CONCURRENTLY customers_email_key ON customers (lower(email));

CONCURRENTLY: build and drop indexes on a live table

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY IF EXISTS orders_old_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx;       -- 12+: rebuild a bloated index online
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;   -- leftovers from failed builds

Partial, expression and covering indexes

CREATE INDEX ON jobs (run_at) WHERE status = 'queued';                   -- only the rows you query
CREATE INDEX ON customers (lower(email));                               -- matches WHERE lower(email) = ?
CREATE INDEX ON orders ((created_at::date));                             -- error for timestamptz: not immutable
CREATE INDEX ON orders (customer_id) INCLUDE (total, status);           -- 11+: index only scans
CREATE UNIQUE INDEX ON subscriptions (user_id) WHERE cancelled_at IS NULL;   -- one active subscription each

Which index type for which query

BRIN for append only time series

CREATE INDEX events_created_brin ON events USING brin (created_at) WITH (pages_per_range = 64);
SELECT pg_size_pretty(pg_relation_size('events_created_brin')) AS brin,
       pg_size_pretty(pg_relation_size('events_created_at_idx')) AS btree;

Unused and oversized indexes

SELECT s.relname AS table, s.indexrelname AS index, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

Duplicate indexes

SELECT indrelid::regclass AS table, array_agg(indexrelid::regclass) AS duplicates
FROM pg_index
GROUP BY indrelid, indkey::text, indclass::text, coalesce(indexprs::text, ''), coalesce(indpred::text, '')
HAVING count(*) > 1;

Index bloat, roughly

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT avg_leaf_density, leaf_fragmentation FROM pgstatindex('orders_customer_created_idx');

EXPLAIN and EXPLAIN ANALYZE

EXPLAIN SELECT * FROM orders WHERE customer_id = 12;                         -- estimated plan only
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 12;      -- runs it, real numbers
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) SELECT ...;
BEGIN; EXPLAIN ANALYZE DELETE FROM orders WHERE id = 1; ROLLBACK;            -- ANALYZE executes writes

The node types, in plain words

Paste a plan into the toolbox

EXPLAIN (ANALYZE, BUFFERS) SELECT c.id, sum(o.total)
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01' GROUP BY c.id ORDER BY 2 DESC;
-- copy the whole output into the SQL toolbox at the top and press "read EXPLAIN"

pg_stat_statements: the most expensive queries

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'   (restart)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT round(total_exec_time::numeric / 1000, 1) AS total_s, calls,
       round(mean_exec_time::numeric, 2) AS mean_ms, rows,
       round(100 * shared_blks_hit::numeric / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       left(query, 60) AS query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
SELECT pg_stat_statements_reset();

Log slow queries and their plans

ALTER SYSTEM SET log_min_duration_statement = '500ms';
ALTER SYSTEM SET auto_explain.log_min_duration = '1s';
ALTER SYSTEM SET session_preload_libraries = 'auto_explain';
ALTER SYSTEM SET auto_explain.log_analyze = on;
SELECT pg_reload_conf();

Prepared statements and the plan cache

PREPARE by_customer(bigint) AS SELECT * FROM orders WHERE customer_id = $1;
EXECUTE by_customer(12);
EXPLAIN EXECUTE by_customer(12);
SET plan_cache_mode = force_custom_plan;     -- 12+: always plan with the actual value
DEALLOCATE by_customer;

Progress of long running maintenance

SELECT relid::regclass, phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;              -- CREATE INDEX and REINDEX
SELECT relid::regclass, phase, round(100.0 * sample_blks_scanned / nullif(sample_blks_total, 0), 1) AS pct
FROM pg_stat_progress_analyze;

Statistics: ANALYZE, targets and extended statistics

ANALYZE orders;
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;     -- more detail for a skewed column
CREATE STATISTICS orders_city_zip (dependencies, ndistinct) ON city, zip FROM addresses;
ANALYZE addresses;
SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'orders' AND attname = 'status';

Test a plan by switching things off, in one session only

SET enable_seqscan = off;         -- would it use the index if forced to?
SET enable_nestloop = off;
SET work_mem = '256MB';           -- would the sort fit in memory?
SET random_page_cost = 1.1;       -- SSD storage
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET ALL;

Dead rows and what VACUUM does

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;
VACUUM (VERBOSE, ANALYZE) orders;

VACUUM FULL versus online alternatives

VACUUM FULL orders;                         -- rewrites the table compactly; ACCESS EXCLUSIVE lock throughout
CLUSTER orders USING orders_customer_created_idx;   -- rewrite in index order; also locks
-- online, with an extension: pg_repack --table=orders --no-order -d shop

Per table settings for big, busy tables

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 10000);
ALTER TABLE events SET (autovacuum_analyze_scale_factor = 0.02);
ALTER TABLE events SET (autovacuum_vacuum_insert_scale_factor = 0.05);   -- 13+: insert only tables too
-- postgresql.conf, for the whole server
autovacuum_max_workers = 5
autovacuum_vacuum_cost_limit = 2000        -- default -1 uses vacuum_cost_limit (200): too slow for SSDs
autovacuum_naptime = 30s

What is autovacuum doing right now

SELECT p.pid, p.relid::regclass AS table, p.phase,
       round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 1) AS pct,
       now() - a.xact_start AS running
FROM pg_stat_progress_vacuum p JOIN pg_stat_activity a USING (pid);

What stops VACUUM from cleaning up

SELECT pid, usename, state, now() - xact_start AS age, left(query, 50) AS query
FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 5;
SELECT slot_name, active, age(xmin) AS xmin_age FROM pg_replication_slots;
SELECT gid, prepared, owner FROM pg_prepared_xacts;

How far from wraparound is each database

SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2147483648, 1) AS pct_to_wraparound
FROM pg_database ORDER BY 2 DESC;
SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' ORDER BY 2 DESC LIMIT 5;

HOT updates and fillfactor

SELECT relname, n_tup_upd, n_tup_hot_upd, round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables ORDER BY n_tup_upd DESC LIMIT 5;
ALTER TABLE counters SET (fillfactor = 80);      -- leave room on each page for new row versions

SHOW, SET, ALTER SYSTEM, reload

SHOW work_mem;
SET work_mem = '64MB';                                  -- this session
SET LOCAL work_mem = '256MB';                           -- this transaction only
ALTER SYSTEM SET work_mem = '32MB';                     -- writes postgresql.auto.conf
SELECT pg_reload_conf();
ALTER DATABASE shop SET statement_timeout = '30s';
ALTER ROLE reporting SET work_mem = '256MB';

Where a setting came from, and what needs a restart

SELECT name, setting, unit, source, sourcefile, pending_restart
FROM pg_settings WHERE source NOT IN ('default', 'override') ORDER BY name;
SELECT name, setting, context FROM pg_settings WHERE name IN ('shared_buffers', 'work_mem', 'max_connections');
SELECT * FROM pg_file_settings WHERE error IS NOT NULL;      -- typos in the config files

Lock ALTER SYSTEM down

allow_alter_system = off                # postgresql.conf, 17+: configuration stays under config management
SHOW allow_alter_system;

What changes on RDS, Cloud SQL and Azure

Habits that transfer to any managed service

SELECT current_setting('server_version'), current_setting('max_connections');
SELECT name, setting, source FROM pg_settings WHERE source <> 'default' ORDER BY name;
SELECT extname, extversion FROM pg_extension ORDER BY 1;
\copy big_table TO 'big_table.csv' WITH (FORMAT csv, HEADER true)     -- instead of server side COPY

Memory and I/O on a dedicated server

shared_buffers = 4GB                # about 25% of RAM (restart)
effective_cache_size = 12GB         # about 75% of RAM: a planner hint, allocates nothing
work_mem = 32MB                     # per sort or hash node, per query, per connection
maintenance_work_mem = 1GB          # VACUUM, CREATE INDEX
random_page_cost = 1.1              # SSD; the default 4 assumes spinning disks
effective_io_concurrency = 200      # SSD
max_wal_size = 8GB                  # fewer, larger checkpoints
huge_pages = try                    # large shared_buffers

Connections and pooling

SELECT count(*), state FROM pg_stat_activity GROUP BY state;
SHOW max_connections;
-- pgbouncer.ini
[databases]
shop = host=127.0.0.1 port=5432 dbname=shop
[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 40

Logging worth turning on

log_min_duration_statement = 500ms
log_checkpoints = on                 # default on since 15
log_connections = on
log_lock_waits = on
log_temp_files = 0                   # every sort or hash that spilled to disk
log_autovacuum_min_duration = 1s
log_line_prefix = '%m [%p] %q%u@%d '

Asynchronous I/O in 18

SHOW io_method;                          -- worker (default), io_uring or sync
ALTER SYSTEM SET io_method = 'io_uring'; -- Linux builds with liburing; restart
SHOW io_workers;                         -- worker mode: background I/O processes, default 3
SELECT * FROM pg_aios;                   -- in flight asynchronous I/O

Range partitions by month

CREATE TABLE events (
  id          bigint GENERATED ALWAYS AS IDENTITY,
  created_at  timestamptz NOT NULL,
  payload     jsonb,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_09 PARTITION OF events FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE events_2026_10 PARTITION OF events FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_default PARTITION OF events DEFAULT;
CREATE INDEX ON events (created_at);        -- created on every partition, present and future
\d+ events

List and hash partitioning

CREATE TABLE customers_by_region (id bigint, region text NOT NULL, name text) PARTITION BY LIST (region);
CREATE TABLE customers_eu PARTITION OF customers_by_region FOR VALUES IN ('RO', 'DE', 'FR');
CREATE TABLE customers_us PARTITION OF customers_by_region FOR VALUES IN ('US', 'CA');

CREATE TABLE sessions (id uuid, data jsonb) PARTITION BY HASH (id);
CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
INSERT INTO customers_by_region VALUES (1, 'JP', 'Kenji');

Retention: drop old data instantly

ALTER TABLE events DETACH PARTITION events_2025_09 CONCURRENTLY;     -- 14+: no lock on queries of events
DROP TABLE events_2025_09;
ALTER TABLE events ATTACH PARTITION events_2026_11 FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

Check that pruning happens

EXPLAIN SELECT count(*) FROM events WHERE created_at >= '2026-09-15' AND created_at < '2026-09-20';
SELECT tableoid::regclass AS partition, count(*) FROM events GROUP BY 1;

Merge or split partitions today

BEGIN;
CREATE TABLE events_2026_q1 (LIKE events INCLUDING ALL);
INSERT INTO events_2026_q1 SELECT * FROM events WHERE created_at >= '2026-01-01' AND created_at < '2026-04-01';
ALTER TABLE events DETACH PARTITION events_2026_01;
ALTER TABLE events DETACH PARTITION events_2026_02;
ALTER TABLE events DETACH PARTITION events_2026_03;
ALTER TABLE events ATTACH PARTITION events_2026_q1 FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
COMMIT;
DROP TABLE events_2026_01, events_2026_02, events_2026_03;

pg_partman: create and drop time partitions automatically

CREATE EXTENSION IF NOT EXISTS pg_partman;
SELECT partman.create_parent(p_parent_table => 'public.events', p_control => 'created_at', p_interval => '1 month', p_premake => 3);
UPDATE partman.part_config SET retention = '12 months', retention_keep_table = false WHERE parent_table = 'public.events';
SELECT partman.run_maintenance();            -- schedule it, for example with pg_cron every hour

A SQL function in one line

CREATE OR REPLACE FUNCTION price_with_vat(p numeric, country text DEFAULT 'RO')
RETURNS numeric LANGUAGE sql IMMUTABLE PARALLEL SAFE
RETURN round(p * CASE country WHEN 'RO' THEN 1.21 WHEN 'DE' THEN 1.19 ELSE 1.20 END, 2);

SELECT name, price_with_vat(price) FROM products;
SELECT price_with_vat(100, country => 'DE');

PL/pgSQL: variables, IF, loops, RETURN QUERY

CREATE OR REPLACE FUNCTION customer_summary(p_customer bigint)
RETURNS TABLE (status text, orders bigint, revenue numeric)
LANGUAGE plpgsql STABLE AS $$
DECLARE
  v_exists boolean;
BEGIN
  SELECT EXISTS (SELECT 1 FROM customers WHERE id = p_customer) INTO v_exists;
  IF NOT v_exists THEN
    RAISE EXCEPTION 'customer % not found', p_customer USING ERRCODE = 'no_data_found';
  END IF;
  RETURN QUERY
    SELECT o.status, count(*), sum(o.total)
    FROM orders o WHERE o.customer_id = p_customer
    GROUP BY o.status;
END;
$$;
SELECT * FROM customer_summary(12);

Catch errors and report details

CREATE OR REPLACE FUNCTION safe_insert_tag(p_name text) RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE v_id bigint; v_msg text; v_state text;
BEGIN
  INSERT INTO tags (name) VALUES (p_name) RETURNING id INTO v_id;
  RETURN v_id;
EXCEPTION
  WHEN unique_violation THEN
    SELECT id INTO v_id FROM tags WHERE name = p_name;
    RETURN v_id;
  WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS v_msg = MESSAGE_TEXT, v_state = RETURNED_SQLSTATE;
    RAISE WARNING 'tag insert failed: % (%)', v_msg, v_state;
    RETURN NULL;
END;
$$;

Procedures can COMMIT between batches

CREATE OR REPLACE PROCEDURE purge_events(p_days int)
LANGUAGE plpgsql AS $$
DECLARE n int;
BEGIN
  LOOP
    DELETE FROM events WHERE ctid IN (
      SELECT ctid FROM events WHERE created_at < now() - make_interval(days => p_days) LIMIT 5000);
    GET DIAGNOSTICS n = ROW_COUNT;
    COMMIT;
    EXIT WHEN n = 0;
  END LOOP;
END;
$$;
CALL purge_events(90);

DO: run a block of code once

DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'staging' LOOP
    EXECUTE format('ANALYZE %I.%I', r.schemaname, r.tablename);
    RAISE NOTICE 'analyzed %.%', r.schemaname, r.tablename;
  END LOOP;
END;
$$;

Dynamic SQL without injection

EXECUTE format('SELECT count(*) FROM %I.%I WHERE status = $1', p_schema, p_table)
  INTO v_count USING p_status;
EXECUTE format('ALTER TABLE %I ADD COLUMN %I %s', p_table, p_column, 'text');

SECURITY DEFINER done safely

CREATE OR REPLACE FUNCTION reset_password(p_user bigint, p_hash text) RETURNS void
LANGUAGE sql SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
BEGIN ATOMIC
  UPDATE public.users SET password_hash = p_hash WHERE id = p_user;
END;
REVOKE ALL ON FUNCTION reset_password(bigint, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION reset_password(bigint, text) TO app;

Views, updatable views and CHECK OPTION

CREATE OR REPLACE VIEW active_customers AS
  SELECT id, name, email, country FROM customers WHERE deleted_at IS NULL;
CREATE VIEW ro_customers WITH (security_invoker = true) AS         -- 15+: caller's privileges and RLS
  SELECT * FROM customers WHERE country = 'RO'
  WITH CHECK OPTION;
UPDATE active_customers SET email = 'new@example.com' WHERE id = 12;   -- simple views are updatable

Materialized views for expensive reports

CREATE MATERIALIZED VIEW daily_revenue AS
  SELECT created_at::date AS day, count(*) AS orders, sum(total) AS revenue
  FROM orders GROUP BY 1
WITH DATA;
CREATE UNIQUE INDEX ON daily_revenue (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

Keep updated_at current

CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;
CREATE TRIGGER orders_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION set_updated_at();

An audit trail in jsonb

CREATE TABLE audit_log (id bigint GENERATED ALWAYS AS IDENTITY, tbl text, op text, row_id bigint,
                        old jsonb, new jsonb, by_user text DEFAULT current_user, at timestamptz DEFAULT now());
CREATE OR REPLACE FUNCTION audit() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log (tbl, op, row_id, old, new)
  VALUES (TG_TABLE_NAME, TG_OP, coalesce(NEW.id, OLD.id),
          CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
          CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
  RETURN NULL;
END;
$$;
CREATE TRIGGER orders_audit AFTER INSERT OR UPDATE OR DELETE ON orders FOR EACH ROW EXECUTE FUNCTION audit();

Statement triggers with transition tables

CREATE TRIGGER orders_bulk_stats
AFTER INSERT ON orders
REFERENCING NEW TABLE AS inserted
FOR EACH STATEMENT EXECUTE FUNCTION refresh_counts();
-- inside refresh_counts(): SELECT customer_id, count(*) FROM inserted GROUP BY 1

LISTEN and NOTIFY

LISTEN order_events;
CREATE OR REPLACE FUNCTION notify_order() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  PERFORM pg_notify('order_events', json_build_object('id', NEW.id, 'status', NEW.status)::text);
  RETURN NULL;
END;
$$;
CREATE TRIGGER orders_notify AFTER INSERT OR UPDATE OF status ON orders FOR EACH ROW EXECUTE FUNCTION notify_order();

Event triggers: react to DDL

CREATE OR REPLACE FUNCTION log_ddl() RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
    RAISE NOTICE 'DDL: % on %', r.command_tag, r.object_identity;
  END LOOP;
END;
$$;
CREATE EVENT TRIGGER track_ddl ON ddl_command_end EXECUTE FUNCTION log_ddl();

Roles, users and group roles

CREATE ROLE app LOGIN PASSWORD 'change-me' CONNECTION LIMIT 50;     -- CREATE USER = CREATE ROLE ... LOGIN
CREATE ROLE readers NOLOGIN;                                        -- a group
GRANT readers TO analyst;
ALTER ROLE app WITH PASSWORD 'new-secret' VALID UNTIL '2027-12-31';
ALTER ROLE app SET statement_timeout = '15s';
DROP ROLE IF EXISTS old_app;
\du

GRANT on tables, schemas and databases

GRANT CONNECT ON DATABASE shop TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;
GRANT SELECT (id, name, email) ON customers TO support;       -- column privileges
REVOKE DELETE ON orders FROM app;
\dp orders

Default privileges for tables created later

ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app;
\ddp

Predefined roles instead of superuser

One database, many tenants

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;            -- apply to the table owner too
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::bigint)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

SET app.tenant_id = '42';                                 -- the application sets this per request
SELECT count(*) FROM invoices;                            -- only tenant 42's rows

Check what a role may do

SELECT has_table_privilege('app', 'orders', 'DELETE'), has_schema_privilege('app', 'public', 'CREATE');
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name = 'orders';
SET ROLE app;  SELECT * FROM orders LIMIT 1;  RESET ROLE;       -- test as that role

Client certificates instead of passwords

# pg_hba.conf
hostssl  all  all  10.0.0.0/16  cert                                   # the certificate CN must equal the role
hostssl  all  all  0.0.0.0/0    scram-sha-256 clientcert=verify-full   # password and certificate
# postgresql.conf: ssl_ca_file = 'root.crt' (the CA that signs client certificates)
psql "host=db1 dbname=shop user=app sslmode=verify-full sslcert=app.crt sslkey=app.key sslrootcert=root.crt"

pgAudit: who changed what

-- postgresql.conf: shared_preload_libraries = 'pgaudit'   (restart)
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'ddl, role, write';
ALTER ROLE analyst SET pgaudit.log = 'read';
SELECT pg_reload_conf();

SCRAM passwords, and finding md5 leftovers

SHOW password_encryption;                                   -- scram-sha-256, the default since 14
SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';
SET password_encryption = 'scram-sha-256';
\password app                                                -- re-enter to store a SCRAM hash

Require TLS

-- postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_min_protocol_version = 'TLSv1.2'
-- pg_hba.conf: hostssl lines only, and a final reject for plain host
SELECT ssl, version, cipher FROM pg_stat_ssl JOIN pg_stat_activity USING (pid) WHERE pid = pg_backend_pid();

OAuth sign-in, 18+

# pg_hba.conf
host  all  all  0.0.0.0/0  oauth  issuer="https://login.example.com" scope="openid postgres"
# postgresql.conf: a validator module from your identity provider or a third party
oauth_validator_libraries = 'my_validator'

pg_dump and pg_restore

pg_dump -Fc -d shop -f shop.dump                       # custom format: compressed, selective restore
pg_dump -Fd -j 8 -d shop -f shop.d                     # directory format: parallel dump
pg_dumpall --globals-only -f globals.sql               # roles and tablespaces
createdb shop_restored
pg_restore -d shop_restored -j 8 --no-owner shop.dump
pg_restore -d shop_restored -t orders --data-only shop.dump   # one table

Restore only some objects

pg_restore -l shop.dump > toc.list          # table of contents
# edit toc.list: comment out lines with ;
pg_restore -L toc.list -d shop_restored shop.dump
pg_restore --schema-only -f schema.sql shop.dump     # just the DDL, as SQL
pg_restore -f - shop.dump | grep -i 'CREATE INDEX'   # read the archive without a database

Copy one database to another server

pg_dump -Fc -d shop -h old-db | pg_restore -h new-db -d shop --no-owner
pg_dump -d shop -h old-db | psql -h new-db -d shop -v ON_ERROR_STOP=1

WAL archiving plus a base backup

# postgresql.conf
wal_level = replica
archive_mode = on                                          # restart
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

pg_basebackup -D /backups/base-2026-09-29 -X stream -P -h db1 -U replicator
pg_verifybackup /backups/base-2026-09-29

Point in time recovery

# restore the base backup into an empty data directory, then:
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-09-29 14:02:00+00'     # just before the bad DELETE
recovery_target_action = 'pause'
touch $PGDATA/recovery.signal
pg_ctl -D $PGDATA start
SELECT pg_wal_replay_resume();                      -- after checking the data looks right

Incremental backups, 17+

ALTER SYSTEM SET summarize_wal = on;  SELECT pg_reload_conf();
pg_basebackup -D /backups/full -h db1 -U replicator
pg_basebackup -D /backups/incr1 --incremental=/backups/full/backup_manifest -h db1 -U replicator
pg_combinebackup /backups/full /backups/incr1 -o /restore/data

Build a replica

-- on the primary
CREATE ROLE replicator LOGIN REPLICATION PASSWORD 'change-me';
-- pg_hba.conf: host replication replicator 10.0.0.0/16 scram-sha-256

# on the replica host, into an empty data directory
pg_basebackup -h db1 -U replicator -D /var/lib/postgresql/18/main -X stream -R -C -S replica1 -P
pg_ctl -D /var/lib/postgresql/18/main start

Lag, from both sides

-- on the primary
SELECT application_name, client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS behind, replay_lag
FROM pg_stat_replication;
-- on the replica
SELECT pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp() AS delay;

Slots that silently fill the disk

SELECT slot_name, slot_type, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
SELECT pg_drop_replication_slot('old_replica');
ALTER SYSTEM SET max_slot_wal_keep_size = '50GB';        -- 13+: cap what one slot can hold

Patroni: automatic failover in practice

patronictl -c /etc/patroni/patroni.yml list
patronictl -c /etc/patroni/patroni.yml switchover --leader db1 --candidate db2
patronictl -c /etc/patroni/patroni.yml edit-config      # cluster wide postgresql settings

CloudNativePG on Kubernetes

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: shop-db
spec:
  instances: 3
  imageName: ghcr.io/cloudnative-pg/postgresql:18
  storage:
    size: 100Gi
  postgresql:
    parameters:
      shared_buffers: 2GB
kubectl cnpg status shop-db

Read your own writes on a replica, PostgreSQL 19

-- on the primary, after the write
SELECT pg_current_wal_insert_lsn();          -- for example 0/3F2A1B8
-- on the replica, before the read
WAIT FOR LSN '0/3F2A1B8' WITH (TIMEOUT '2s');

Promote a replica

SELECT pg_promote();                   -- on the replica; 12+
pg_ctl -D /var/lib/postgresql/18/main promote
SHOW synchronous_standby_names;        -- on the primary, for synchronous replicas
ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (replica1, replica2)';

Publish and subscribe tables

-- source: wal_level = logical (restart)
CREATE PUBLICATION shop_pub FOR TABLE orders, customers;
CREATE PUBLICATION paid_orders FOR TABLE orders WHERE (status = 'paid');          -- 15+: row filter
CREATE PUBLICATION eu_pub FOR TABLES IN SCHEMA eu;                                 -- 15+
-- target: the tables must already exist
CREATE SUBSCRIPTION shop_sub
  CONNECTION 'host=db1 dbname=shop user=replicator password=change-me'
  PUBLICATION shop_pub;
SELECT * FROM pg_stat_subscription;

Turn a physical replica into a logical one

pg_createsubscriber -D /var/lib/postgresql/18/replica -P 'host=db1 dbname=shop' -d shop --publication=shop_pub --subscription=shop_sub
ALTER SUBSCRIPTION shop_sub DISABLE;
ALTER SUBSCRIPTION shop_sub SET (failover = true);      -- 17+: only while the subscription is disabled
ALTER SUBSCRIPTION shop_sub ENABLE;

Active queries, longest first

SELECT pid, usename, datname, state, wait_event_type, wait_event,
       now() - query_start AS runtime, left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY query_start;

Cancel or kill a session

SELECT pg_cancel_backend(48213);          -- cancel the current query, keep the connection
SELECT pg_terminate_backend(48213);       -- close the connection
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - state_change > interval '10 minutes';

Cache hit ratio, rollbacks, temp files, deadlocks

SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
       xact_commit, xact_rollback, deadlocks, temp_files, pg_size_pretty(temp_bytes) AS temp
FROM pg_stat_database WHERE datname = current_database();

Tables read by sequential scans

SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0 AND n_live_tup > 100000
ORDER BY seq_tup_read DESC LIMIT 5;

I/O by backend type and checkpoints

SELECT backend_type, object, context, reads, writes, extends, hits
FROM pg_stat_io WHERE reads > 0 OR writes > 0 ORDER BY reads DESC LIMIT 5;

Checkpoints: timed or forced

SELECT num_timed, num_requested, write_time, buffers_written FROM pg_stat_checkpointer;

Is the server up

pg_isready -h db1 -p 5432
SELECT pg_postmaster_start_time(), now() - pg_postmaster_start_time() AS uptime, version();

pg_upgrade: minutes instead of a dump and restore

pg_upgrade --check -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
  -d /var/lib/postgresql/17/main -D /var/lib/postgresql/18/main
pg_upgrade -b ... -B ... -d ... -D ... --link --jobs 8     # hard links: fast, old cluster unusable after start
vacuumdb --all --analyze-in-stages                         # before 18: statistics must be rebuilt

Upgrading to 18: --swap and kept statistics

pg_upgrade -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin -d ... -D ... --swap --jobs 8
vacuumdb --all --analyze-in-stages --missing-stats-only    # 18 carries planner statistics over; fill only the gaps

Install, list, update

SELECT name, default_version, installed_version FROM pg_available_extensions ORDER BY name;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
ALTER EXTENSION pg_stat_statements UPDATE;
\dx
DROP EXTENSION IF EXISTS hstore;

pgvector: similarity search on embeddings

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE docs (id bigint PRIMARY KEY, body text, embedding vector(1536));
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);
SELECT id, body, embedding <=> $1 AS distance      -- $1: the query embedding
FROM docs ORDER BY embedding <=> $1 LIMIT 5;

PostGIS: distance and nearby searches

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE stores (id bigint PRIMARY KEY, name text, location geography(Point, 4326));
CREATE INDEX ON stores USING gist (location);
SELECT name, round(ST_Distance(location, ST_MakePoint(23.5947, 46.7712)::geography)) AS meters
FROM stores
WHERE ST_DWithin(location, ST_MakePoint(23.5947, 46.7712)::geography, 5000)
ORDER BY location <-> ST_MakePoint(23.5947, 46.7712)::geography LIMIT 5;

pgcrypto: password hashes and random bytes

CREATE EXTENSION IF NOT EXISTS pgcrypto;
UPDATE users SET password_hash = crypt('user-password', gen_salt('bf', 12)) WHERE id = 1;
SELECT id FROM users WHERE email = 'ana@example.com' AND password_hash = crypt('user-password', password_hash);
SELECT encode(gen_random_bytes(32), 'hex') AS api_token;

postgres_fdw: query another PostgreSQL server

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER legacy FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'old-db', dbname 'shop');
CREATE USER MAPPING FOR app SERVER legacy OPTIONS (user 'reader', password 'change-me');
CREATE SCHEMA legacy;
IMPORT FOREIGN SCHEMA public LIMIT TO (orders, customers) FROM SERVER legacy INTO legacy;
SELECT count(*) FROM legacy.orders WHERE created_at >= '2026-01-01';

pg_cron: scheduled jobs inside the database

CREATE EXTENSION IF NOT EXISTS pg_cron;     -- needs shared_preload_libraries = 'pg_cron'
SELECT cron.schedule('refresh-revenue', '*/15 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue');
SELECT cron.schedule('purge-sessions', '0 3 * * *', $$DELETE FROM sessions WHERE expires_at < now()$$);
SELECT jobid, jobname, schedule, active FROM cron.job;
SELECT cron.unschedule('purge-sessions');

Small ones worth knowing

Pool size: smaller than you think

-- a starting point for the total across all application servers
connections ~= (CPU cores x 2) + number of disks
SELECT count(*) FILTER (WHERE state = 'active') AS active, count(*) AS total FROM pg_stat_activity;

What breaks behind PgBouncer in transaction mode

See the SQL an ORM sends

ALTER ROLE app SET log_min_duration_statement = 0;     -- temporarily: log everything app runs
# Django:     print(queryset.query), or django-debug-toolbar
# SQLAlchemy: create_engine(url, echo=True)
# Prisma:     new PrismaClient({ log: ['query'] })
# Laravel:    DB::enableQueryLog(); ... DB::getQueryLog();
ALTER ROLE app RESET log_min_duration_statement;

Bulk inserts from code: COPY beats INSERT

# psycopg 3
with cur.copy("COPY events (user_id, kind, created_at) FROM STDIN") as copy:
    for row in rows:
        copy.write_row(row)
// node: pg-copy-streams;  Go: pool.CopyFrom(ctx, pgx.Identifier{"events"}, cols, pgx.CopyFromRows(rows))

Connection refused and pg_hba.conf rejects

sudo systemctl status postgresql                 # is it running?
SHOW listen_addresses;  SHOW port;                # is it listening beyond localhost?
-- add a pg_hba.conf line for 10.0.9.4, then SELECT pg_reload_conf();

Mixed case names and relation does not exist

CREATE TABLE "Customers" ("Name" text);
SELECT Name FROM Customers;              -- fails: both fold to lower case
SELECT "Name" FROM "Customers";          -- works
ALTER TABLE "Customers" RENAME TO customers;

current transaction is aborted

BEGIN;
SELECT 1/0;
SELECT 1;       -- any statement now fails
ROLLBACK;

Out of memory and the OOM killer

sudo dmesg -T | grep -i -E 'killed process|out of memory'
grep -i 'terminated by signal 9' /var/log/postgresql/postgresql-18-main.log
SHOW work_mem;  SHOW max_connections;  SHOW shared_buffers;

PostgreSQL 18 highlights

SHOW io_method;                                   -- asynchronous I/O
SELECT uuidv7();                                  -- time ordered UUIDs
ALTER TABLE t ADD COLUMN c int GENERATED ALWAYS AS (a + b);   -- virtual by default
UPDATE t SET x = 1 RETURNING old.x, new.x;       -- OLD and NEW in RETURNING
PRIMARY KEY (id, valid WITHOUT OVERLAPS)          -- temporal keys
-- also: B-tree skip scan, statistics kept by pg_upgrade, EXPLAIN ANALYZE shows BUFFERS by default,
-- OAuth authentication, data checksums on by default, md5 passwords deprecated, casefold()

PostgreSQL 19, as of beta 4

INSERT ... ON CONFLICT (k) DO SELECT RETURNING id;          -- get or create in one statement
REPACK orders;                                              -- replaces VACUUM FULL and CLUSTER
WAIT FOR LSN '0/3F2A1B8' WITH (TIMEOUT '2s');               -- read your own writes on a replica
SELECT lag(price) IGNORE NULLS OVER (ORDER BY day) FROM prices;
-- also: sequences replicated by logical replication, pg_plan_advice, parallel autovacuum,
-- jit off by default, a warning after every md5 login, RADIUS authentication removed

Announced for 19, then reverted

-- committed during the 19 cycle and removed before release, likely to return in 20:
GROUP BY ALL                                   -- reverted in July: wrong results with some ORDER BY cases
ALTER TABLE ... MERGE PARTITIONS / SPLIT PARTITION   -- reverted in August: correctness problems
SQL/PGQ property graphs (GRAPH_TABLE)          -- reverted on September 7
UPDATE / DELETE ... FOR PORTION OF             -- reverted on September 15: concurrency problem
online enabling of data checksums              -- reverted on September 16
pg_get_role_ddl() and related DDL functions    -- removed in beta 4

The same task, translated

Behaviour that surprises people coming from MySQL

SELECT 'abc' = 'ABC';                          -- false: comparisons are case sensitive
SELECT '5' + 3;                                -- 8 only because '5' is an untyped literal
SELECT '5'::text + 3;                          -- error: no implicit text to number cast
SELECT count(*) FROM t WHERE flag = '';        -- '' is not NULL and not 0
BEGIN; CREATE TABLE x (id int); ROLLBACK;      -- DDL rolls back too

One task, three databases