PostgreSQL cheat sheet

A scannable PostgreSQL reference: 16 short snippets across 10 topics, each linking back to the lesson it came from.

At a glance

Quick snippets

psql and the basics of SQL

Getting comfortable in psql

psql -h 10.0.0.20 -p 5432 -U app -d appdb
psql "postgresql://app:[email protected]:5432/appdb?sslmode=require"
psql -c "SELECT now();" appdb          # one statement, good in scripts
psql -f schema.sql appdb               # run a file
PGHOST=10.0.0.20 PGUSER=app psql appdb # env vars instead of flags

Full lesson: psql and the basics of SQL →

JSONB and arrays

jsonb versus json

SELECT payload -> 'sku'          AS sku_json,     -- jsonb
       payload ->> 'sku'         AS sku_text,     -- text
       payload #> '{ship,zip}'   AS zip_json,     -- path, jsonb
       payload #>> '{ship,zip}'  AS zip_text      -- path, text
FROM   events;

-- containment, key existence, and merge
SELECT id FROM events WHERE payload @> '{"status":"failed"}';
SELECT id FROM events WHERE payload ? 'retry_count';
UPDATE events SET payload = payload || '{"reviewed":true}'::jsonb WHERE id = 7;
UPDATE events SET payload = payload - 'debug_trace' WHERE id = 7;

Indexing JSONB

-- general purpose: supports @>, ?, ?|, ?&
CREATE INDEX idx_events_payload ON events USING gin (payload);

-- containment only: smaller and faster
CREATE INDEX idx_events_payload_path ON events USING gin (payload jsonb_path_ops);

-- frequent equality or sorting on one extracted field
CREATE INDEX idx_events_sku ON events ((payload ->> 'sku'));
CREATE INDEX idx_events_created ON events (((payload ->> 'created_at')::timestamptz));

Full lesson: JSONB and arrays →

Transactions and MVCC basics

Transactions and isolation levels

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  SAVEPOINT after_debit;
  UPDATE ledger SET note = 'transfer' WHERE ref = 'T-9';
  ROLLBACK TO SAVEPOINT after_debit;   -- undo only the last change
COMMIT;

Transactions and isolation levels

-- tighten isolation for one transaction only
BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT sum(balance) FROM accounts;
COMMIT;

Full lesson: Transactions and MVCC basics →

Joins, subqueries, CTEs and LATERAL

CTEs and recursion

-- data-modifying CTEs: several writes in one statement, all-or-nothing
with moved as (
  delete from staging.book where imported_at is not null
  returning *
)
insert into book (title, author_id)
select title, author_id from moved
returning id, title;

LATERAL and RETURNING

insert into book (isbn, title, author_id)
values ('9780441013593', 'The Dispossessed', 1)
on conflict (isbn) do update
  set title = excluded.title,
      updated_at = now()
returning id, title, (xmax = 0) as inserted;

Full lesson: Joins, subqueries, CTEs and LATERAL →

Window functions, full-text search and regex

Full-text search

create extension if not exists pg_trgm;
create index book_title_trgm on book using gin (title gin_trgm_ops);

select title, similarity(title, 'dispossesed') as sim
from book
where title % 'dispossesed'          -- similarity above the threshold
order by sim desc
limit 10;

Pattern matching

select '2026-09-18' ~ '^\d{4}-\d{2}-\d{2}$';                 -- boolean
select regexp_replace('a  b   c', '\s+', ' ', 'g');           -- normalise whitespace
select regexp_matches('order-42-item-7', 'order-(\d+)-item-(\d+)');  -- array of groups
select regexp_split_to_table('a,b,,c', ',');
select substring('ISBN 978-0-441' from '([0-9-]+)');

select id from invoice where number like 'INV-2026-%';         -- anchored prefix, index friendly
select id from invoice where number ilike '%2026%';            -- case-insensitive, no index use

Full lesson: Window functions, full-text search and regex →

Roles, permissions, schemas and row-level security

Row-level security

-- use a SECURITY DEFINER function to grant a narrow bypass
create or replace function admin_document_count()
returns bigint
language sql
security definer
set search_path = app, pg_catalog
as $$
  select count(*) from document;
$$;

revoke all on function admin_document_count() from public;
grant execute on function admin_document_count() to app_read;

Full lesson: Roles, permissions, schemas and row-level security →

Query planning and performance tuning

Pooling and prepared statements

alter role app_write set statement_timeout = '30s';
alter role app_write set idle_in_transaction_session_timeout = '60s';
alter role web_report set work_mem = '64MB';
alter role web_report set statement_timeout = '5min';

Full lesson: Query planning and performance tuning →

Backup, PITR and replication

Point-in-time recovery

# postgresql.conf on the primary
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_timeout = 60s
max_wal_senders = 10
wal_keep_size = 1GB

Point-in-time recovery

# recovery settings for a restore
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-09-18 09:30:00+00'
recovery_target_action = 'promote'
recovery_target_inclusive = on

Full lesson: Backup, PITR and replication →

Partitioning, extensions and advanced deployment

Extensions worth knowing

-- pgvector: nearest neighbours without another service
create extension if not exists vector;
alter table document add column embedding vector(768);
create index document_embedding_idx on document
  using hnsw (embedding vector_cosine_ops);

select id, left(content, 80) as preview
from document
order by embedding <=> '[...]'::vector
limit 5;

Upgrading and operating

pg_upgrade --old-datadir /var/lib/postgresql/16/main \
           --new-datadir /var/lib/postgresql/17/main \
           --old-bindir /usr/lib/postgresql/16/bin \
           --new-bindir /usr/lib/postgresql/17/bin --check

# logical alternative: a clean replica on the new version, then a switch
# 1. create a logical replication slot on the old primary
# 2. subscribe from the new cluster
# 3. wait until lag is zero, then promote and repoint the application

Full lesson: Partitioning, extensions and advanced deployment →

Next steps: the PostgreSQL ecosystem and migrations

Managed versus self-hosted

-- monitoring queries worth a dashboard
select count(*) filter (where state = 'active')             as active,
       count(*) filter (where state = 'idle in transaction') as idle_in_txn,
       count(*)                                             as total
from pg_stat_activity where backend_type = 'client backend';

select schemaname, relname,
       round(100 * n_dead_tup / greatest(n_live_tup + n_dead_tup, 1), 1) as dead_pct,
       last_autovacuum
from pg_stat_user_tables where n_dead_tup > 10000
order by dead_pct desc limit 10;

Full lesson: Next steps: the PostgreSQL ecosystem and migrations →

FAQ

Related cheat sheets

SQL MySQL MongoDB Redis SQLite

Last refreshed 2026-09-27.