1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# macOS (Homebrew)
brew install postgresql@17
brew services start postgresql@17

# Ubuntu / Debian
sudo apt install postgresql-17 postgresql-client-17
sudo systemctl enable --now postgresql

# Docker (recommended for dev)
docker run -d --name pg \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_USER=app \
  -e POSTGRES_DB=appdb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:17-alpine

# Connect
psql -h localhost -U app -d appdb            # password prompt
psql "postgresql://app:secret@localhost/appdb"
psql "host=localhost user=app dbname=appdb sslmode=require"

# Connection URI with options
psql "postgresql://app@host:5432/db?sslmode=verify-full&connect_timeout=10"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
\?                 list meta-commands
\h SELECT          syntax help for SQL
\l                 list databases
\c dbname          connect to database
\dt                list tables (current schema)
\dt+               with size & description
\dt *.*            tables in all schemas
\d table           describe table (columns, indexes, constraints)
\d+ table          extended description (storage, stats target)
\dn                list schemas
\di                list indexes
\dv                list views
\dm                list materialized views
\df                list functions
\df+ name          function definition
\dx                installed extensions
\du                list roles
\dp table          show table privileges
\timing on         print query timing
\x                 toggle expanded display (vertical)
\e                 open last query in $EDITOR
\ef func           edit function
\i file.sql        execute file
\o file.txt        redirect output
\copy table FROM 'file.csv' CSV HEADER
\watch 2           re-run query every 2s
\q                 quit

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- Numeric
SMALLINT  INTEGER  BIGINT
NUMERIC(precision, scale)        -- exact, for money
REAL      DOUBLE PRECISION       -- floating point
SERIAL    BIGSERIAL              -- legacy auto-increment
INTEGER GENERATED ALWAYS AS IDENTITY  -- modern (SQL standard)

-- Character
CHAR(n)   VARCHAR(n)   TEXT      -- TEXT preferred; no perf diff
CITEXT                            -- case-insensitive (extension)

-- Date / Time
DATE              -- 'YYYY-MM-DD'
TIME              -- 'HH:MM:SS'
TIMESTAMP         -- without TZ; avoid
TIMESTAMPTZ       -- with TZ; ALWAYS use this
INTERVAL '1 day 2 hours'

-- Boolean
BOOLEAN           -- TRUE / FALSE / NULL

-- Binary / Network
BYTEA             -- raw bytes
INET   CIDR       -- IP addresses
MACADDR

-- Structured
UUID                                              -- needs uuid-ossp or pgcrypto
JSON                                              -- text, validated
JSONB                                             -- binary, indexable, preferred
ARRAY: INTEGER[], TEXT[]
HSTORE                                            -- key-value (extension)
TSVECTOR / TSQUERY                                -- full-text search

-- Ranges
INT4RANGE  INT8RANGE  NUMRANGE  TSTZRANGE  DATERANGE
'[1,10)'::int4range

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE TABLE users (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       CITEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  role        TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user','admin','staff')),
  data        JSONB NOT NULL DEFAULT '{}'::jsonb,
  tags        TEXT[] DEFAULT '{}',
  active      BOOLEAN NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  status      TEXT NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Alter
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMPTZ;
ALTER TABLE users DROP COLUMN tags;
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users RENAME TO accounts;
ALTER TABLE users ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(id);

-- Indexes
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX CONCURRENTLY idx_users_created ON users (created_at DESC);  -- non-blocking
CREATE UNIQUE INDEX uq_orders_external ON orders (external_id) WHERE external_id IS NOT NULL;
CREATE INDEX idx_users_data_gin ON users USING GIN (data);                 -- JSONB
CREATE INDEX idx_users_data_path ON users USING GIN (data jsonb_path_ops); -- @> faster
CREATE INDEX idx_posts_tsv ON posts USING GIN (to_tsvector('english', body));
CREATE INDEX idx_orders_total_brin ON orders USING BRIN (created_at);      -- huge tables, ordered

DROP INDEX CONCURRENTLY idx_old;
REINDEX INDEX CONCURRENTLY idx_users_email;     -- PG12+
REINDEX TABLE CONCURRENTLY users;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Single & multi-row INSERT
INSERT INTO users (email, name) VALUES ('a@x.com', 'Ann');
INSERT INTO users (email, name) VALUES
  ('a@x.com','Ann'), ('b@x.com','Bob'), ('c@x.com','Cara')
RETURNING id, email;

-- UPSERT (ON CONFLICT)
INSERT INTO users (email, name) VALUES ('a@x.com','Ann')
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name,
      updated_at = NOW()
RETURNING *;

INSERT INTO logs (key, count) VALUES ('hit', 1)
ON CONFLICT (key) DO UPDATE SET count = logs.count + 1;

INSERT INTO users (email) VALUES ('a@x.com')
ON CONFLICT DO NOTHING;

-- UPDATE with JOIN (FROM clause)
UPDATE orders o
SET status = 'flagged'
FROM users u
WHERE o.user_id = u.id AND u.role = 'admin';

-- UPDATE FROM VALUES (bulk)
UPDATE products p SET price = v.price
FROM (VALUES (1, 9.99), (2, 19.99), (3, 29.99)) AS v(id, price)
WHERE p.id = v.id;

-- DELETE
DELETE FROM users WHERE last_login_at < NOW() - INTERVAL '2 years' RETURNING id;
DELETE FROM users USING orders WHERE users.id = orders.user_id AND orders.status = 'fraud';

-- TRUNCATE (very fast, resets identity, can't rollback DDL on some setups)
TRUNCATE TABLE logs RESTART IDENTITY CASCADE;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- DISTINCT ON (most recent per user)
SELECT DISTINCT ON (user_id) user_id, created_at, status
FROM orders
ORDER BY user_id, created_at DESC;

-- CASE
SELECT name,
  CASE WHEN total_cents > 10000 THEN 'whale'
       WHEN total_cents > 1000  THEN 'regular'
       ELSE 'small' END AS tier
FROM orders;

-- COALESCE / NULLIF / GREATEST / LEAST
SELECT COALESCE(nickname, name, 'anon')          AS display_name,
       NULLIF(input, '')                          AS clean,
       GREATEST(a, b, c)                          AS max_val,
       LEAST(price, sale_price)                   AS shown_price;

-- LIMIT / OFFSET (avoid OFFSET for deep pagination)
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 100;

-- Keyset (seek) pagination — recommended for large data
SELECT * FROM posts
WHERE (created_at, id) < ($last_created, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- INNER, LEFT, RIGHT, FULL OUTER
SELECT u.name, o.total_cents
FROM users u INNER JOIN orders o ON o.user_id = u.id;

SELECT u.name, COUNT(o.id) AS orders
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

-- LATERAL — like a correlated subquery you can reference
SELECT u.id, recent.created_at, recent.total_cents
FROM users u
LEFT JOIN LATERAL (
  SELECT * FROM orders o WHERE o.user_id = u.id
  ORDER BY created_at DESC LIMIT 1
) recent ON TRUE;

-- USING / NATURAL (rarely needed)
SELECT * FROM a JOIN b USING (id);

-- Anti-join (users with no orders)
SELECT u.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Semi-join (EXISTS)
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SELECT status, COUNT(*) AS n, SUM(total_cents) AS rev,
       AVG(total_cents)::INT AS avg_cents,
       MIN(created_at), MAX(created_at),
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_cents) AS median
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY status
HAVING COUNT(*) > 5
ORDER BY rev DESC;

-- GROUPING SETS / ROLLUP / CUBE
SELECT region, product, SUM(qty)
FROM sales
GROUP BY GROUPING SETS ((region), (product), (region, product), ());

-- FILTER clause (conditional aggregate)
SELECT
  COUNT(*) FILTER (WHERE status = 'paid') AS paid,
  COUNT(*) FILTER (WHERE status = 'refunded') AS refunded,
  SUM(total_cents) FILTER (WHERE created_at >= NOW() - INTERVAL '7 days') AS rev_7d
FROM orders;

-- string_agg / array_agg / jsonb_agg
SELECT user_id, string_agg(tag, ',' ORDER BY tag) AS tags
FROM user_tags GROUP BY user_id;

SELECT jsonb_agg(jsonb_build_object('id', id, 'name', name)) FROM users;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT id, user_id, total_cents,
  ROW_NUMBER()  OVER (PARTITION BY user_id ORDER BY created_at)  AS rn,
  RANK()        OVER (PARTITION BY user_id ORDER BY total_cents DESC) AS rk,
  DENSE_RANK()  OVER (ORDER BY total_cents DESC)                 AS drk,
  LAG(total_cents)  OVER (PARTITION BY user_id ORDER BY created_at) AS prev,
  LEAD(total_cents) OVER (PARTITION BY user_id ORDER BY created_at) AS next,
  SUM(total_cents) OVER (PARTITION BY user_id ORDER BY created_at
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(total_cents) OVER (PARTITION BY user_id ORDER BY created_at
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7
FROM orders;

-- Named window
SELECT id, SUM(amt) OVER w, AVG(amt) OVER w
FROM tx
WINDOW w AS (PARTITION BY user_id ORDER BY created_at);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Plain CTE
WITH recent AS (
  SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '7 days'
)
SELECT user_id, COUNT(*) FROM recent GROUP BY user_id;

-- Data-modifying CTE
WITH del AS (
  DELETE FROM staging WHERE processed RETURNING *
)
INSERT INTO archive SELECT * FROM del;

-- Recursive — org chart
WITH RECURSIVE tree AS (
  SELECT id, manager_id, name, 1 AS depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, t.depth + 1
  FROM employees e JOIN tree t ON e.manager_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;

-- Recursive — generate series of dates
WITH RECURSIVE d(day) AS (
  SELECT DATE '2025-01-01'
  UNION ALL SELECT day + 1 FROM d WHERE day < DATE '2025-01-31'
) SELECT * FROM d;

-- Easier: generate_series
SELECT generate_series('2025-01-01'::date, '2025-01-31', '1 day');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Operators
data -> 'key'           -- returns jsonb
data ->> 'key'          -- returns text
data #> '{a,b,0}'       -- jsonb at path
data #>> '{a,b}'        -- text at path
data @> '{"role":"admin"}'::jsonb  -- contains (indexable with GIN)
data <@ '{...}'                    -- contained by
data ? 'key'                       -- has key
data ?| ARRAY['a','b']             -- has any
data ?& ARRAY['a','b']             -- has all

-- Build / modify
SELECT jsonb_build_object('id', 1, 'name', 'Ann', 'tags', jsonb_build_array('a','b'));
SELECT data || '{"verified": true}'::jsonb;            -- merge
SELECT data - 'temp';                                   -- remove key
SELECT data #- '{a,b}';                                 -- remove path
SELECT jsonb_set(data, '{role}', '"admin"', TRUE);      -- set path

-- Extract & filter
SELECT id, data->>'role' AS role FROM users WHERE data @> '{"active": true}';

-- jsonb_path (SQL/JSON path)
SELECT jsonb_path_query(data, '$.items[*] ? (@.price > 100).name') FROM cart;
SELECT * FROM cart WHERE data @? '$.items[*] ? (@.qty > 5)';

1
2
3
4
5
6
7
8
9
10
SELECT ARRAY[1,2,3] || ARRAY[4,5];
SELECT '{1,2,3}'::int[] @> '{2,3}';   -- contains
SELECT array_length(arr, 1), cardinality(arr);
SELECT unnest(ARRAY['a','b','c']);    -- to rows
SELECT array_agg(name ORDER BY id) FROM users;

-- Indexable filters
WHERE tags && ARRAY['sql','db']         -- overlap (uses GIN)
WHERE tags @> ARRAY['sql']              -- contains all

1
2
3
4
5
6
7
8
9
SELECT NOW(), CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;
SELECT NOW() AT TIME ZONE 'UTC' AT TIME ZONE 'America/Toronto';
SELECT DATE_TRUNC('month', NOW());                -- floor to month
SELECT EXTRACT(YEAR FROM created_at);
SELECT AGE(NOW(), birth_date);
SELECT NOW() + INTERVAL '7 days';
SELECT created_at::date = CURRENT_DATE;
SELECT to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS TZ');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
ALTER TABLE posts ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,'')),  'B')
  ) STORED;

CREATE INDEX idx_posts_tsv ON posts USING GIN (tsv);

SELECT id, title, ts_rank(tsv, q) AS rank
FROM posts, websearch_to_tsquery('english', 'postgres "window function"') q
WHERE tsv @@ q
ORDER BY rank DESC
LIMIT 20;

-- Highlight
SELECT ts_headline('english', body, websearch_to_tsquery('english', 'index'),
  'StartSel=<b>, StopSel=</b>') FROM posts;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
BEGIN;
-- ... statements ...
COMMIT;     -- or ROLLBACK;

-- Isolation levels
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;       -- default
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Savepoints
BEGIN;
  SAVEPOINT sp1;
  -- ...
  ROLLBACK TO SAVEPOINT sp1;
COMMIT;

-- Row locks (in transaction)
SELECT * FROM accounts WHERE id = $1 FOR UPDATE;
SELECT * FROM accounts WHERE id = $1 FOR UPDATE SKIP LOCKED;   -- queue/worker pattern
SELECT * FROM accounts WHERE id = $1 FOR UPDATE NOWAIT;
SELECT * FROM accounts WHERE id = $1 FOR SHARE;

-- Advisory locks (app-level)
SELECT pg_advisory_lock(12345);
SELECT pg_try_advisory_lock(12345);
SELECT pg_advisory_unlock(12345);

-- Show locks
SELECT * FROM pg_locks JOIN pg_stat_activity USING (pid) WHERE NOT granted;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE VIEW v_active_users AS
SELECT id, email, name FROM users WHERE active;

-- Updatable view (simple) — INSERT/UPDATE/DELETE work directly

-- Materialized view
CREATE MATERIALIZED VIEW mv_user_stats AS
SELECT user_id, COUNT(*) AS n_orders, SUM(total_cents) AS rev
FROM orders GROUP BY user_id
WITH DATA;

CREATE UNIQUE INDEX ON mv_user_stats (user_id);   -- enables CONCURRENTLY refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_stats;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Function (SQL)
CREATE OR REPLACE FUNCTION user_rev(p_user BIGINT) RETURNS BIGINT
LANGUAGE sql STABLE AS $$
  SELECT COALESCE(SUM(total_cents), 0) FROM orders WHERE user_id = p_user;
$$;

-- Function (PL/pgSQL)
CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END
$$;

CREATE TRIGGER trg_users_touch
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

-- Procedure (can COMMIT inside)
CREATE OR REPLACE PROCEDURE run_batch(p_size INT)
LANGUAGE plpgsql AS $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT id FROM jobs WHERE status='pending' LIMIT p_size LOOP
    UPDATE jobs SET status='done' WHERE id = r.id;
    COMMIT;
  END LOOP;
END
$$;

CALL run_batch(100);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE events (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  occurred_at TIMESTAMPTZ NOT NULL,
  payload JSONB NOT NULL,
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE events_2025_02 PARTITION OF events
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Default partition
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Attach existing
ALTER TABLE events ATTACH PARTITION events_legacy FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Detach (non-blocking in PG14+)
ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY;

-- pg_partman extension automates monthly/daily creation

1
2
3
4
5
6
7
8
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON, SETTINGS, VERBOSE)
  SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '1 day';

-- Look for: Seq Scan vs Index Scan, rows= vs actual rows=, Buffers (shared hit/read),
-- Sort Method, Hash vs Merge vs Nested Loop, "never executed" subplans.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
ANALYZE users;                                    -- refresh planner stats
VACUUM (VERBOSE, ANALYZE) users;
VACUUM FULL users;                                -- rewrites table, takes ACCESS EXCLUSIVE lock
CLUSTER users USING idx_users_email;              -- physically reorder by index

-- pg_repack (extension) — like VACUUM FULL with no exclusive lock

-- Per-column stats target (more accuracy for skewed columns)
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;

-- Bloat & dead tuples
SELECT relname, n_dead_tup, n_live_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- Active connections / running queries
SELECT pid, state, wait_event_type, wait_event, query_start, LEFT(query, 200) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY query_start;

-- Long-running queries
SELECT pid, now() - query_start AS duration, state, LEFT(query, 200)
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > INTERVAL '30 seconds';

-- Cancel / kill
SELECT pg_cancel_backend(pid);     -- soft
SELECT pg_terminate_backend(pid);  -- hard

-- Locks waiting
SELECT bl.pid AS waiting_pid, bl.mode AS waiting_mode,
       kl.pid AS blocking_pid, kl.mode AS blocking_mode,
       a.query AS blocking_query
FROM pg_locks bl
JOIN pg_locks kl ON bl.locktype = kl.locktype
              AND NOT bl.granted AND kl.granted
              AND bl.pid <> kl.pid
JOIN pg_stat_activity a ON a.pid = kl.pid;

-- Table & DB sizes
SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS total
FROM pg_class WHERE relkind='r' ORDER BY pg_total_relation_size(oid) DESC LIMIT 20;

-- Index usage
SELECT relname, idx_scan, seq_scan, n_live_tup
FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 20;

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

-- pg_stat_statements (top queries by total time)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT calls, total_exec_time, mean_exec_time, rows, LEFT(query, 120)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE ROLE app LOGIN PASSWORD 'secret';
CREATE ROLE readonly NOLOGIN;
GRANT readonly TO app;

GRANT CONNECT ON DATABASE appdb TO app;
GRANT USAGE   ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;
GRANT USAGE  ON ALL SEQUENCES IN SCHEMA public TO app;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;

-- Row-Level Security (RLS)
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY orders_owner ON orders
  FOR ALL USING (user_id = current_setting('app.user_id')::bigint);

-- Force RLS even for table owner
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

SET app.user_id = '42';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# Logical (per DB)
pg_dump -h host -U user -Fc appdb > appdb.dump          # custom format
pg_dump -Fd -j 8 -f outdir appdb                         # directory, parallel
pg_dump --schema-only appdb > schema.sql
pg_dump --data-only --table=users appdb > users.sql

# Restore
pg_restore -d appdb -j 8 appdb.dump
psql -d appdb -f schema.sql

# Whole cluster
pg_dumpall -h host -U postgres > cluster.sql

# Physical (point-in-time, hot)
pg_basebackup -D /backup -F tar -z -P -h host -U replicator -X stream

1
2
3
4
5
6
7
8
- Streaming replication: primary ships WAL to standby(s), low lag
- Synchronous replication: synchronous_standby_names; commits wait
- Logical replication (PUBLICATION/SUBSCRIPTION): per-table, cross-version
- Hot standby: read-only queries on standby
- Replication slots: prevent WAL deletion before standby consumes it
- Tools: pgBackRest, Barman, repmgr, Patroni (HA orchestration with etcd/Consul)
- Cloud: AWS RDS Multi-AZ, Aurora, Google Cloud SQL HA, Azure Flexible Server

1
2
3
4
5
6
7
-- Logical replication
CREATE PUBLICATION pub_users FOR TABLE users, orders;
-- On subscriber:
CREATE SUBSCRIPTION sub_users
  CONNECTION 'host=primary user=repl password=... dbname=appdb'
  PUBLICATION pub_users;

1
2
3
4
5
6
7
8
9
10
11
12
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;   -- query metrics
CREATE EXTENSION IF NOT EXISTS pgcrypto;             -- gen_random_uuid(), crypto
CREATE EXTENSION IF NOT EXISTS uuid-ossp;            -- uuid generation
CREATE EXTENSION IF NOT EXISTS citext;               -- case-insensitive text
CREATE EXTENSION IF NOT EXISTS pg_trgm;              -- trigram fuzzy search / GIN on LIKE
CREATE EXTENSION IF NOT EXISTS btree_gin;            -- combine btree+gin in one index
CREATE EXTENSION IF NOT EXISTS postgis;              -- geospatial
CREATE EXTENSION IF NOT EXISTS timescaledb;          -- time-series (chunks)
CREATE EXTENSION IF NOT EXISTS vector;               -- pgvector for embeddings
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS pgaudit;              -- detailed audit logs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE docs (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  content TEXT,
  embedding vector(1536)
);

CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);   -- PG 16+ recommended
-- or IVFFlat for older / large data:
-- CREATE INDEX ON docs USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

-- KNN search
SELECT id, content, embedding <=> $1::vector AS distance
FROM docs ORDER BY embedding <=> $1::vector LIMIT 10;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
shared_buffers           = 25% of RAM (e.g., 8GB on 32GB box)
effective_cache_size     = 50–75% of RAM
work_mem                 = 16MB–64MB (per sort/hash, per connection!)
maintenance_work_mem     = 1GB
max_connections          = 100–300; use pgbouncer for more
wal_buffers              = 16MB
checkpoint_timeout       = 15min
max_wal_size             = 8GB
random_page_cost         = 1.1 (SSD)
effective_io_concurrency = 200 (SSD)
default_statistics_target= 100 (raise for skewed cols)
autovacuum_*             = leave on; tune naptime/cost limits for big tables
log_min_duration_statement = 1000ms     # log slow queries
log_lock_waits = on

1
2
3
4
5
6
pgbouncer (most common)
  - transaction pooling (most compatible with frameworks)
  - session pooling (when you need prepared statements / SET LOCAL)
pgcat (Rust, supports sharding, load balancing)
Supabase Supavisor (transaction pooling at scale)

1
2
3
4
5
6
7
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_pg FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'other', port '5432', dbname 'other');
CREATE USER MAPPING FOR app SERVER remote_pg OPTIONS (user 'app', password 's');
IMPORT FOREIGN SCHEMA public LIMIT TO (events) FROM SERVER remote_pg INTO public;
SELECT * FROM events LIMIT 5;

1
2
3
4
5
6
7
8
COPY users (email, name) FROM '/tmp/users.csv' WITH (FORMAT csv, HEADER true);
COPY (SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '1 day')
TO '/tmp/orders.csv' WITH (FORMAT csv, HEADER true);

-- Client-side (no server file access needed)
\copy users FROM 'users.csv' CSV HEADER
\copy (SELECT * FROM users) TO 'users.csv' CSV HEADER

1
2
3
4
5
6
7
8
9
- SQL/JSON: JSON_TABLE, JSON_VALUE, JSON_QUERY, JSON_EXISTS (16/17)
- Incremental sort & better partitionwise joins
- Logical replication: bidirectional, row filters, column lists, sequences (17)
- pg_basebackup: incremental backups (17)
- VACUUM: much faster on large tables, dynamic memory limit
- BUFFERS in EXPLAIN by default (17)
- ALTER SYSTEM RESET — easier conf management
- SCRAM-SHA-256 default authentication

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Generate slug
SELECT regexp_replace(lower(unaccent(title)), '[^a-z0-9]+', '-', 'g') AS slug FROM posts;

-- Soft delete pattern
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
CREATE VIEW v_active_users AS SELECT * FROM users WHERE deleted_at IS NULL;

-- Audit log via trigger
CREATE TABLE audit (id BIGSERIAL PRIMARY KEY, tbl TEXT, op TEXT,
                    row_id BIGINT, data JSONB, at TIMESTAMPTZ DEFAULT NOW());
CREATE OR REPLACE FUNCTION fn_audit() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit(tbl, op, row_id, data)
  VALUES (TG_TABLE_NAME, TG_OP, COALESCE(NEW.id, OLD.id), to_jsonb(COALESCE(NEW, OLD)));
  RETURN COALESCE(NEW, OLD);
END $$;

-- Idempotent / safe migration
CREATE TABLE IF NOT EXISTS schema_migrations (version TEXT PRIMARY KEY, run_at TIMESTAMPTZ DEFAULT NOW());

-- Queue with SKIP LOCKED
DELETE FROM jobs WHERE id = (
  SELECT id FROM jobs WHERE status='ready'
  ORDER BY priority DESC, id LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

1
2
3
4
5
6
7
8
9
- "could not connect" → pg_hba.conf, listen_addresses, firewall, sslmode
- "FATAL: too many connections" → pgbouncer, raise max_connections
- "out of shared memory" → increase max_locks_per_transaction
- "deadlock detected" → consistent lock ordering, shorter transactions
- Hot updates not happening? → leave fillfactor=85–90 on update-heavy tables, no index on updated cols
- Sequential scans on huge table → ANALYZE, check index, raise statistics target
- Slow JOIN → check work_mem (Hash spill to disk), ANALYZE both sides
- WAL bloat → check unused replication slots: pg_replication_slots

1
2
3
4
5
6
7
8
9
10
11
12
# Show config without comments
psql -c "SELECT name, setting, unit, source FROM pg_settings WHERE source <> 'default';"

# Dump query as CSV
psql -At -F',' -c "SELECT id,name FROM users" > users.csv

# Restore single table
pg_restore -d appdb -t users appdb.dump

# Run with role
PGUSER=app PGPASSWORD=secret psql -h host -d appdb