PostgreSQL 速查表

# connect to a database
psql -h localhost -p 5432 -U postgres -d mydb
# -h host, -p port, -U user, -d database

# connect via connection string
psql "postgresql://user:pass@localhost:5432/mydb"

# common psql meta-commands
\l              # list databases
\c mydb         # connect to mydb
\dt             # list tables
\d users        # describe table
\dn             # list schemas
\df             # list functions
\du             # list roles/users
\q              # quit
\?              # help on meta-commands
\timing         # toggle query timing

# create a database
CREATE DATABASE mydb
  WITH OWNER alice
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8'
  TEMPLATE template0
  CONNECTION LIMIT 100;

# create from a template (clone)
CREATE DATABASE mydb_copy TEMPLATE mydb;

# drop a database (must disconnect first)
DROP DATABASE IF EXISTS mydb;

# drop with force (PostgreSQL 13+)
DROP DATABASE IF EXISTS mydb WITH (FORCE);

# rename a database
ALTER DATABASE mydb RENAME TO newdb;

# create a schema
CREATE SCHEMA IF NOT EXISTS app_schema AUTHORIZATION alice;

# set the search path (schema lookup order)
SET search_path TO app_schema, public;
SHOW search_path;

# create a table in a specific schema
CREATE TABLE app_schema.users (id serial PRIMARY KEY);

# move a table between schemas
ALTER TABLE public.users SET SCHEMA app_schema;

# list objects in a schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'app_schema';

# drop a schema (and its objects)
DROP SCHEMA IF EXISTS app_schema CASCADE;

# view a setting
SHOW shared_buffers;
SHOW max_connections;

# set a parameter for the session
SET work_mem = '64MB';
SET statement_timeout = '30s';

# set a parameter for a transaction
BEGIN;
SET LOCAL work_mem = '256MB';
-- heavy sort here
COMMIT;  -- reverts to previous value

# set at database or role level (persists)
ALTER DATABASE mydb SET log_min_duration_statement = 100;
ALTER ROLE alice SET search_path TO app_schema, public;

# reload config without restart
SELECT pg_reload_conf();

# view config file location
SHOW config_file;

# run a SQL file
psql -d mydb -f script.sql
psql -d mydb < script.sql

# inside psql
\i /path/to/script.sql

# use psql variables
\set table_name 'users'
SELECT * FROM :table_name;

# prompt for input
\echo -n 'Enter user ID: ' \set user_id `read x && echo $x`
SELECT * FROM users WHERE id = :user_id;

# output to file
\o results.txt
SELECT * FROM users;
\o

# execute shell command
\! date

CREATE TABLE users (
  id          serial PRIMARY KEY,
  username    varchar(50) UNIQUE NOT NULL,
  email       varchar(255) UNIQUE NOT NULL,
  password    text NOT NULL,
  age         integer CHECK (age >= 0 AND age <= 150),
  role        varchar(20) NOT NULL DEFAULT 'user',
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

# create with a foreign key
CREATE TABLE posts (
  id          serial PRIMARY KEY,
  user_id     integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title       text NOT NULL,
  body        text,
  published   boolean DEFAULT false
);

# create a temporary table (session-scoped)
CREATE TEMP TABLE temp_stats AS
  SELECT user_id, count(*) FROM posts GROUP BY user_id;

# preferred over serial (SQL standard)
CREATE TABLE products (
  id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# allow manual override (like serial behavior)
CREATE TABLE products (
  id    integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# restart a sequence
ALTER TABLE products ALTER COLUMN id RESTART WITH 1000;

# view identity info
SELECT * FROM pg_sequences WHERE sequencename = 'products_id_seq';

# add a column
ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users ADD COLUMN score numeric DEFAULT 0;

# drop a column
ALTER TABLE users DROP COLUMN IF EXISTS bio;
ALTER TABLE users DROP COLUMN bio CASCADE;  -- drop dependent objects

# rename a column
ALTER TABLE users RENAME COLUMN username TO handle;

# change column type
ALTER TABLE users ALTER COLUMN age TYPE smallint USING age::smallint;

# set/drop default
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'member';
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;

# add constraint
ALTER TABLE users ADD CONSTRAINT email_check
  CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$');

# named constraint for easy management
ALTER TABLE users
  ADD CONSTRAINT positive_age CHECK (age >= 0);

# composite primary key
CREATE TABLE enrollments (
  student_id integer REFERENCES students(id),
  course_id  integer REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

# unique constraint on multiple columns
ALTER TABLE users ADD CONSTRAINT unique_email_domain
  UNIQUE (email, domain);

# foreign key with actions
ALTER TABLE posts
  ADD FOREIGN KEY (user_id) REFERENCES users(id)
  ON UPDATE CASCADE ON DELETE SET NULL;

# drop a constraint
ALTER TABLE users DROP CONSTRAINT positive_age;

# defer a constraint until commit
SET CONSTRAINTS unique_email_domain DEFERRED;

# PostgreSQL supports table inheritance
CREATE TABLE people (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE employees (
  salary numeric,
  dept   text
) INHERITS (people);

CREATE TABLE customers (
  credit_limit numeric
) INHERITS (people);

# query parent sees all children
SELECT * FROM people;          -- includes employees and customers
SELECT * FROM ONLY people;     -- only people, not children

# insert into a child
INSERT INTO employees (name, salary, dept)
  VALUES ('Alice', 50000, 'Engineering');

# integer types (use the smallest that fits)
smallint    -- 2 bytes, -32768 to 32767
integer     -- 4 bytes, -2B to 2B
bigint      -- 8 bytes, very large

# auto-incrementing (use identity, not serial)
id integer GENERATED ALWAYS AS IDENTITY

# decimal/numeric (exact)
numeric(precision, scale)   -- e.g. numeric(10,2) for currency
decimal                     -- alias for numeric

# floating point (inexact, faster)
real        -- 4 bytes, 6 decimal digits
double precision  -- 8 bytes, 15 decimal digits

# special
serial / bigserial   -- legacy auto-increment (prefer identity)
money                -- currency (prefer numeric)

# examples
CREATE TABLE products (
  price numeric(10,2),        -- $99999999.99
  weight real,                -- 1.5 (kg)
  quantity integer DEFAULT 0
);

# three text types (all variable-length)
varchar(n)   -- varchar with max length
char(n)      -- fixed-length, padded with spaces
text         -- unlimited length (preferred)

# in practice, all three perform the same
# varchar(n) adds a length check; char(n) pads (rarely useful)

CREATE TABLE articles (
  title    varchar(200) NOT NULL,
  slug     varchar(100) UNIQUE,
  body     text,
  summary  text DEFAULT ''
);

# string functions
SELECT length(body), char_length(title), octet_length(title)
FROM articles;

# change length limit (fast, no rewrite)
ALTER TABLE articles ALTER COLUMN title TYPE varchar(500);

# pattern matching
SELECT * FROM articles WHERE title LIKE '%postgres%';
SELECT * FROM articles WHERE title ILIKE '%postgres%'; -- case-insensitive
SELECT * FROM articles WHERE slug ~ '^[a-z0-9-]+$';   -- regex

# date/time types
date              -- date only (4 bytes)
time              -- time only (8 bytes)
timetz            -- time with time zone (12 bytes)
timestamp         -- timestamp without tz (8 bytes)
timestamptz       -- timestamp WITH time zone (8 bytes, preferred)
interval          -- time span (16 bytes)

# always use timestamptz for timestamps
CREATE TABLE events (
  id         serial PRIMARY KEY,
  created_at timestamptz DEFAULT now(),
  scheduled  timestamptz,
  duration   interval
);

# common operations
SELECT now();                               -- current timestamp
SELECT current_date;                         -- today
SELECT created_at + interval '1 hour';       -- add time
SELECT age('2025-01-01', '2024-01-01');       -- interval between
SELECT date_trunc('month', created_at);       -- truncate to month
SELECT to_char(created_at, 'YYYY-MM-DD HH:MI:SS');
SELECT to_timestamp('2025/01/15', 'YYYY/MM/DD');
SELECT EXTRACT(YEAR FROM created_at);

# timezone handling
SET timezone = 'UTC';
SET timezone = 'Asia/Shanghai';

# json stores text; jsonb stores binary (faster, preferred)
CREATE TABLE products (
  id    serial PRIMARY KEY,
  data  jsonb
);

# insert JSON
INSERT INTO products (data) VALUES
  ('{"name": "Widget", "price": 19.99, "tags": ["sale", "new"]}');

# query JSON fields
SELECT data->'name'        FROM products;  -- returns jsonb
SELECT data->>'name'       FROM products;  -- returns text
SELECT data->'tags'->0     FROM products;  -- first tag (jsonb)

# filter by JSON
SELECT * FROM products WHERE data->>'name' = 'Widget';
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';  -- containment

# modify JSON
UPDATE products SET data = jsonb_set(data, '{price}', '29.99');
UPDATE products SET data = data || '{"sale": true}';        -- merge
UPDATE products SET data = data - 'sale';                    -- remove key

# index JSON (GIN index)
CREATE INDEX idx_products_data ON products USING GIN (data);
CREATE INDEX idx_products_name ON products ((data->>'name'));

# array columns
CREATE TABLE teams (
  id      serial PRIMARY KEY,
  name    text,
  members text[]           -- array of text
);

INSERT INTO teams (name, members) VALUES
  ('Engineering', ARRAY['Alice', 'Bob', 'Carol']),
  ('Sales', '{"Dave", "Eve"}');

# query arrays
SELECT * FROM teams WHERE 'Alice' = ANY(members);
SELECT * FROM teams WHERE members @> ARRAY['Alice'];  -- contains
SELECT members[1] FROM teams;            -- 1-indexed!
SELECT array_length(members, 1) FROM teams;
SELECT unnest(members) FROM teams WHERE id = 1;  -- expand to rows

# custom enum type
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE persons (id serial PRIMARY KEY, current_mood mood);
INSERT INTO persons (current_mood) VALUES ('happy');

# composite type
CREATE TYPE address AS (street text, city text, zip text);
CREATE TABLE contacts (id serial, addr address);

# basic insert
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);

# multi-row insert
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

# insert with RETURNING (get auto-generated values)
INSERT INTO users (username, email)
VALUES ('eve', '[email protected]')
RETURNING id, username, created_at;

# insert from a query (INSERT ... SELECT)
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE active = false;

# upsert (insert or update on conflict)
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email
RETURNING id;

# do nothing on conflict
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username) DO NOTHING;

# basic update
UPDATE users SET email = '[email protected]' WHERE id = 1;

# update multiple columns
UPDATE users
SET email = '[email protected]', age = 31, updated_at = now()
WHERE id = 1;

# update with RETURNING
UPDATE users SET role = 'admin' WHERE id = 1
RETURNING id, username, role;

# update from another table
UPDATE posts p
SET title = p.title || ' (archived)'
FROM users u
WHERE p.user_id = u.id AND u.role = 'admin';

# conditional update with CASE
UPDATE products
SET price = CASE
  WHEN price < 10 THEN price * 1.2
  WHEN price < 50 THEN price * 1.1
  ELSE price
END;

# update all rows (be careful!)
UPDATE users SET status = 'active';

# basic delete
DELETE FROM users WHERE id = 1;

# delete with RETURNING
DELETE FROM users WHERE active = false
RETURNING id, username;

# delete based on a join
DELETE FROM posts
WHERE user_id IN (
  SELECT id FROM users WHERE role = 'deleted'
);

# delete using USING (join syntax)
DELETE FROM posts p
USING users u
WHERE p.user_id = u.id AND u.status = 'deleted';

# truncate (fast, resets sequences, DDL-like)
TRUNCATE TABLE posts;
TRUNCATE TABLE posts, comments;          -- multiple tables
TRUNCATE TABLE posts RESTART IDENTITY;   -- reset sequences
TRUNCATE TABLE posts CASCADE;            -- also truncates FK refs

# delete all rows (slower than TRUNCATE, but transactional)
DELETE FROM posts;

# basic select
SELECT id, username, email FROM users;

# filtering
SELECT * FROM users WHERE age >= 18 AND role = 'user';
SELECT * FROM users WHERE role IN ('admin', 'moderator');
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE username LIKE 'al%';

# sorting and limiting
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users ORDER BY age DESC, username ASC
OFFSET 20 LIMIT 10;  -- pagination (or use FETCH)

# distinct
SELECT DISTINCT role FROM users;
SELECT DISTINCT ON (user_id) * FROM posts
ORDER BY user_id, created_at DESC;  -- latest post per user

# column aliases and expressions
SELECT
  username AS name,
  EXTRACT(YEAR FROM created_at) AS join_year,
  age * 365 AS age_in_days
FROM users;

# MERGE: insert, update, or delete in one statement (PG15+)
MERGE INTO products p
USING new_prices n
ON p.sku = n.sku
WHEN MATCHED AND p.price != n.price THEN
  UPDATE SET price = n.price, updated_at = now()
WHEN MATCHED AND n.discontinued = true THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT (sku, name, price) VALUES (n.sku, n.name, n.price);

# simpler upsert (still works, often preferred)
INSERT INTO products (sku, name, price) VALUES ('A1', 'Widget', 9.99)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price;

# MERGE with a source query
MERGE INTO inventory i
USING (SELECT product_id, sum(qty) AS total FROM orders GROUP BY product_id) o
ON i.product_id = o.product_id
WHEN MATCHED THEN UPDATE SET quantity = i.quantity - o.total;

# comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price <> 0;        -- not equal
SELECT * FROM products WHERE name IS DISTINCT FROM 'Test';

# logical operators
SELECT * FROM products
WHERE (price > 50 AND category = 'electronics')
   OR (price < 10 AND category = 'books');

# IN, BETWEEN, LIKE/ILIKE
SELECT * FROM products WHERE category IN ('books', 'toys');
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM products WHERE name LIKE '_o%';     -- second char 'o'
SELECT * FROM products WHERE name ILIKE '%widget%';

# IS NULL / IS NOT NULL
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT DISTINCT FROM NULL;

# ANY/ALL with arrays
SELECT * FROM products WHERE id = ANY(ARRAY[1,2,3]);
SELECT * FROM products WHERE price > ALL(ARRAY[10, 20, 30]);

# regex matching
SELECT * FROM products WHERE name ~ '^[A-C]';       -- starts with A-C
SELECT * FROM products WHERE name ~* 'widget';       -- case-insensitive
SELECT * FROM products WHERE name !~ 'test';         -- does not match

# basic sorting
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;

# sort by expression
SELECT * FROM products ORDER BY (price * 1.1) DESC;
SELECT * FROM events ORDER BY created_at::date;

# NULLS handling
SELECT * FROM users ORDER BY last_login DESC NULLS LAST;
SELECT * FROM users ORDER BY last_login DESC NULLS FIRST;

# LIMIT/OFFSET pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;   -- page 1
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;  -- page 2

# keyset pagination (faster, use for large datasets)
SELECT * FROM users WHERE id > 100 ORDER BY id LIMIT 10;

# FETCH (SQL standard)
SELECT * FROM users ORDER BY id FETCH FIRST 10 ROWS ONLY;

# random rows
SELECT * FROM products ORDER BY random() LIMIT 5;  -- slow on large tables
SELECT * FROM products TABLESAMPLE BERNOULLI(1);    -- 1% sample

# basic grouping
SELECT category, count(*) FROM products GROUP BY category;
SELECT user_id, sum(amount) FROM orders GROUP BY user_id;

# multiple aggregates
SELECT
  category,
  count(*)           AS total,
  avg(price)         AS avg_price,
  min(price)         AS min_price,
  max(price)         AS max_price,
  sum(price)         AS total_value
FROM products
GROUP BY category;

# HAVING (filter on aggregates, like WHERE for groups)
SELECT user_id, count(*) AS order_count
FROM orders
GROUP BY user_id
HAVING count(*) > 5;

# GROUP BY with multiple columns
SELECT category, status, count(*)
FROM products
GROUP BY category, status
ORDER BY category, status;

# GROUP BY ROLLUP/CUBE (subtotals)
SELECT category, status, count(*)
FROM products
GROUP BY ROLLUP (category, status);  -- adds subtotals and grand total

# GROUP BY GROUPING SETS
SELECT category, status, count(*)
FROM products
GROUP BY GROUPING SETS ((category, status), (category), ());

# distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT category, status FROM products;

# DISTINCT ON (first row per group)
SELECT DISTINCT ON (category) *
FROM products
ORDER BY category, price DESC;  -- most expensive per category

# UNION (combine, remove duplicates)
SELECT 'user' AS type, username FROM users
UNION
SELECT 'admin' AS type, username FROM admins;

# UNION ALL (faster, keeps duplicates)
SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users;

# INTERSECT (common rows)
SELECT product_id FROM orders_2024
INTERSECT
SELECT product_id FROM orders_2025;

# EXCEPT (rows in first but not second)
SELECT product_id FROM all_products
EXCEPT
SELECT product_id FROM discontinued_products;

# set operations with ORDER BY (applies to the whole result)
SELECT username FROM users
UNION
SELECT username FROM admins
ORDER BY username;

# simple CASE
SELECT
  name,
  CASE category
    WHEN 'book' THEN 'Media'
    WHEN 'laptop' THEN 'Electronics'
    ELSE 'Other'
  END AS category_group
FROM products;

# searched CASE (more flexible)
SELECT
  name,
  CASE
    WHEN price < 10 THEN 'Cheap'
    WHEN price < 50 THEN 'Moderate'
    WHEN price < 100 THEN 'Expensive'
    ELSE 'Luxury'
  END AS price_tier
FROM products;

# CASE in aggregate (pivot-like)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active_count,
  count(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM users;

# CASE for conditional sorting
SELECT * FROM products
ORDER BY
  CASE WHEN featured THEN 0 ELSE 1 END,
  name;

# INNER JOIN (only matching rows)
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

# LEFT JOIN (all left rows, matching right or NULL)
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

# RIGHT JOIN (all right rows, matching left or NULL)
SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

# FULL OUTER JOIN (all rows from both, NULLs where no match)
SELECT u.username, p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;

# find rows with no match (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;  -- users with no posts

# join three tables
SELECT u.username, p.title, c.text AS comment
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id;

# join with different conditions
SELECT
  u.username,
  p.title,
  l.name AS liked_by
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN likes l ON p.id = l.post_id AND l.user_id != u.id;

# self-join (e.g., employees and their managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

# join with aggregation
SELECT u.username, count(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;

# CROSS JOIN (Cartesian product — every combination)
SELECT u.username, c.name
FROM users u
CROSS JOIN categories c;
-- same as: SELECT ... FROM users, categories;

# useful for generating combinations
SELECT generate_series(1, 5) AS n, c.name
FROM categories c
CROSS JOIN generate_series(1, 5);

# LATERAL (subquery can reference outer query)
SELECT u.username, recent.title
FROM users u
CROSS JOIN LATERAL (
  SELECT title FROM posts
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) recent;

# LATERAL with LEFT JOIN (include users with no posts)
SELECT u.username, recent.title
FROM users u
LEFT JOIN LATERAL (
  SELECT title FROM posts WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 3
) recent ON true;

# see how PostgreSQL joins tables
EXPLAIN SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join strategies you'll see:
#   Nested Loop:   for each left row, scan right (good for small/indexed)
#   Hash Join:     hash one table, probe with other (good for large unsorted)
#   Merge Join:    both sorted, merge (good for sorted/indexed large tables)

# force a join strategy (for testing, not production)
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = off;

# check actual execution stats
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join removal: PostgreSQL can remove unnecessary joins
# (e.g., LEFT JOIN to a table only used for FK validation)
EXPLAIN SELECT u.* FROM users u LEFT JOIN posts p ON u.id = p.user_id;

# always index foreign key columns
CREATE INDEX idx_posts_user_id ON posts(user_id);

# index join columns on both sides
CREATE INDEX idx_orders_product_id ON orders(product_id);
CREATE INDEX idx_products_id ON products(id);  -- PK already indexed

# composite index for multi-column joins
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

# analyze tables for accurate statistics
ANALYZE users;
ANALYZE posts;

# avoid joining on expressions (prevents index use)
-- BAD:  JOIN ON lower(a.email) = lower(b.email)
-- GOOD: JOIN ON a.email = b.email (normalize case on insert)

# limit columns selected (reduces I/O)
SELECT u.username, p.title  -- not SELECT *
FROM users u JOIN posts p ON u.id = p.user_id;

# partition large tables for join pruning
SELECT * FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2025-01-01';  -- prunes partitions

# standard aggregates
SELECT
  count(*)              AS total_rows,
  count(distinct category) AS unique_categories,
  sum(price)            AS total_value,
  avg(price)            AS avg_price,
  min(price)            AS min_price,
  max(price)            AS max_price,
  stddev(price)         AS std_dev,
  variance(price)       AS variance
FROM products;

# string aggregation
SELECT
  user_id,
  string_agg(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY user_id;

# array aggregation
SELECT
  user_id,
  array_agg(title ORDER BY created_at DESC) AS recent_titles
FROM posts
GROUP BY user_id;

# boolean aggregation
SELECT
  bool_or(published)    AS any_published,
  bool_and(published)   AS all_published
FROM posts;

# statistical aggregates
SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY price) AS median,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY price) AS p95,
  mode() WITHIN GROUP (ORDER BY price) AS most_common
FROM products;

# ROW_NUMBER: unique sequential number
SELECT
  title,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products;

# RANK and DENSE_RANK (handle ties)
SELECT
  title,
  category,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS cat_rank,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

# LAG and LEAD (compare to previous/next row)
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change,
  LEAD(revenue, 1) OVER (ORDER BY date) AS next_day
FROM daily_sales;

# running total
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

# frame types
#   ROWS:   exact row count
#   RANGE:  logical range (e.g., same value)
#   GROUPS: groups of peer rows

SELECT
  date,
  revenue,
  # running sum from start to current
  SUM(revenue) OVER (ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,

  # 7-day moving average
  AVG(revenue) OVER (ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,

  # sum of all rows (no frame = entire partition)
  SUM(revenue) OVER () AS grand_total
FROM daily_sales;

# NTILE: divide rows into N equal buckets
SELECT
  name,
  price,
  NTILE(4) OVER (ORDER BY price DESC) AS price_quartile
FROM products;

# FIRST_VALUE / LAST_VALUE
SELECT
  name,
  category,
  price,
  FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY price DESC) AS most_expensive,
  LAST_VALUE(name) OVER (
    PARTITION BY category ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS least_expensive
FROM products;

# FILTER (PostgreSQL extension, cleaner than CASE)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active,
  count(*) FILTER (WHERE status = 'inactive') AS inactive,
  count(*) FILTER (WHERE status = 'banned') AS banned,
  sum(amount) FILTER (WHERE status = 'active') AS active_revenue
FROM orders
GROUP BY user_id;

# equivalent with CASE (more verbose)
SELECT
  count(*) AS total,
  count(CASE WHEN status = 'active' THEN 1 END) AS active,
  sum(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_revenue
FROM orders
GROUP BY user_id;

# FILTER with multiple aggregates in one pass (efficient)
SELECT
  category,
  count(*) FILTER (WHERE price > 100) AS expensive_count,
  avg(price) FILTER (WHERE price > 100) AS expensive_avg,
  count(*) FILTER (WHERE price <= 100) AS cheap_count,
  avg(price) FILTER (WHERE price <= 100) AS cheap_avg
FROM products
GROUP BY category;

# basic CTE (WITH clause)
WITH active_users AS (
  SELECT id, username FROM users WHERE active = true
)
SELECT au.username, count(p.id) AS post_count
FROM active_users au
LEFT JOIN posts p ON au.id = p.user_id
GROUP BY au.username;

# multiple CTEs (can reference each other)
WITH user_stats AS (
  SELECT user_id, count(*) AS post_count FROM posts GROUP BY user_id
),
top_users AS (
  SELECT user_id FROM user_stats WHERE post_count > 10
)
SELECT u.username, us.post_count
FROM users u
JOIN top_users tu ON u.id = tu.user_id
JOIN user_stats us ON u.id = us.user_id;

# recursive CTE (tree traversal)
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth FROM org_tree ORDER BY depth;

# CTE for DML (data modification)
WITH deleted AS (
  DELETE FROM users WHERE active = false RETURNING id
)
INSERT INTO archived_users (user_id)
SELECT id FROM deleted;

# create a B-tree index (default)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

# unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

# composite index (order matters!)
CREATE INDEX idx_posts_user_created
  ON posts(user_id, created_at DESC);

# partial index (only rows matching the condition)
CREATE INDEX idx_active_users ON users(last_login)
  WHERE active = true;

# expression index (index on a function)
CREATE INDEX idx_users_lower_email ON users(lower(email));
# query must use the same expression:
#   SELECT * FROM users WHERE lower(email) = '[email protected]'

# check index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';

# drop an index
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;  -- no lock, slower

# GIN (Generalized Inverted Index) — for multi-value columns
# great for JSONB, arrays, full-text search

CREATE INDEX idx_products_data ON products USING GIN (data);        -- JSONB
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);              -- array
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector); -- full-text

# query patterns that use GIN
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';
SELECT * FROM posts WHERE tags && ARRAY['python'];
SELECT * FROM articles WHERE search_vector @@ to_tsquery('postgres & index');

# GiST (Generalized Search Tree) — for geometric/range data
CREATE INDEX idx_locations ON places USING GIST (location);  -- point/polygon
CREATE INDEX idx_events ON events USING GIST (during);       -- tsrange

# geometric queries
SELECT * FROM places
WHERE location <@ box '(0,0),(10,10)';

# range queries
SELECT * FROM events
WHERE during && tsrange('2025-01-01', '2025-02-01');

# BRIN (Block Range Index) — tiny, for naturally ordered data
# best for huge tables where data is physically ordered (e.g., time-series)

CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);
# index is kilobytes, not gigabytes!

# BRIN works by storing min/max for each block range
# effective when physical order matches logical order (e.g., append-only logs)

# compare sizes:
#   B-tree on 1B rows: ~20GB
#   BRIN on 1B rows:   ~2MB (10000x smaller)

# SP-GiST (space-partitioned GiST) — for non-balanced data
CREATE INDEX idx_prefix ON routes USING SPGiST (prefix);  -- e.g., IP routing

# Hash index (equality only, crash-safe since PG10)
CREATE INDEX idx_users_session ON users USING HASH (session_token);
# only supports = operator, not ranges or sorting

# check what index types are available
SELECT amname FROM pg_am WHERE amtype = 'i';

# find unused indexes (candidates for removal)
SELECT
  schemaname, relname, indexrelname,
  idx_scan AS scans,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

# find duplicate indexes
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
       (array_agg(idx::text))[1] AS indexes
FROM (
  SELECT indexrelid::regclass AS idx, indrelid::regclass AS rel
  FROM pg_index
  GROUP BY indrelid, indkey
  HAVING count(*) > 1
) sub;

# REINDEX (rebuild a bloated index)
REINDEX INDEX idx_users_email;
REINDEX TABLE CONCURRENTLY users;  -- PG12+, no lock

# check index bloat
SELECT
  relname, pg_size_pretty(pg_relation_size(relid)) AS size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_relation_size(relid) DESC;

# 1. index primary keys (auto-created)
# 2. index foreign keys (NOT auto-created!)
CREATE INDEX idx_posts_user_id ON posts(user_id);

# 3. index columns in WHERE, JOIN, ORDER BY, GROUP BY
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_created ON orders(created_at);

# 4. use composite indexes for multi-column queries
# leftmost prefix rule: (a, b, c) helps WHERE a=?, WHERE a=? AND b=?
# but NOT WHERE b=? alone
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

# 5. partial indexes for common filters
CREATE INDEX idx_active_orders ON orders(created_at)
  WHERE status = 'active';

# 6. covering indexes (INCLUDE for index-only scans)
CREATE INDEX idx_products_cat_price ON products(category, price)
  INCLUDE (name);  -- name available without heap fetch

# 7. don't over-index — every index slows writes
# drop indexes used < 10 times/month (check pg_stat_user_indexes)

# verify the index is used
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

# explicit transaction
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK to undo

# savepoints (partial rollback)
BEGIN;
  INSERT INTO orders (user_id, amount) VALUES (1, 50);
  SAVEPOINT order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
  -- oops, wrong product
  ROLLBACK TO order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 200);
COMMIT;

# transaction control
BEGIN;
  -- statements
SAVEPOINT my_savepoint;
  -- more statements
ROLLBACK TO my_savepoint;  -- undo back to savepoint
RELEASE my_savepoint;      -- remove savepoint (commit its changes)
COMMIT;
-- or ROLLBACK to undo everything

# auto-commit (default — each statement is its own transaction)
SET autocommit = on;  -- default

# set isolation level for a transaction
BEGIN ISOLATION LEVEL READ COMMITTED;   -- default
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

# or set it for the session
SET default_transaction_isolation = 'serializable';

# READ COMMITTED (default):
#   - each query sees committed data at query start
#   - no dirty reads, but non-repeatable reads and phantoms possible

# REPEATABLE READ:
#   - each query sees snapshot from transaction start
#   - no non-repeatable reads, but serialization anomalies possible
#   - PostgreSQL's RR is stronger than SQL standard (no phantoms!)

# SERIALIZABLE:
#   - strongest isolation, behaves as if transactions ran one at a time
#   - may abort with serialization failure (retry needed)
#   - uses SSI (Serializable Snapshot Isolation)

# check current level
SHOW transaction_isolation;

# handle serialization failures (retry)
BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- work
COMMIT;  -- may fail with SQLSTATE 40001 -> retry the transaction

# row-level locks (acquired by UPDATE, DELETE, SELECT FOR UPDATE)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;        -- lock row
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE; -- weaker lock
SELECT * FROM accounts WHERE id = 1 FOR SHARE;         -- allow reads, block writes

# advisory locks (application-level locks)
SELECT pg_advisory_lock(12345);          -- session-level
SELECT pg_advisory_unlock(12345);
SELECT pg_try_advisory_lock(12345);      -- non-blocking
SELECT pg_advisory_xact_lock(12345);     -- transaction-level (auto-released)

# view active locks
SELECT pid, mode, granted, query
FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

# terminate a blocked session
SELECT pg_cancel_backend(pid);    -- cancel query
SELECT pg_terminate_backend(pid); -- kill session

# deadlocks (PostgreSQL detects and resolves by killing one transaction)
-- Transaction A: UPDATE users WHERE id=1; UPDATE users WHERE id=2;
-- Transaction B: UPDATE users WHERE id=2; UPDATE users WHERE id=1;
-- -> ERROR: deadlock detected

# PostgreSQL uses MVCC (Multi-Version Concurrency Control)
# each transaction sees a consistent snapshot of data

# visible transactions
SELECT txid_current();           -- current transaction ID
SELECT txid_snapshot_xmin(txid_current_snapshot());

# see long-running transactions (can prevent vacuum)
SELECT
  pid,
  age(clock_timestamp(), xact_start) AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY xact_start;

# long transactions block vacuum (old row versions can't be removed)
# check for this:
SELECT
  pid,
  age(txid_current(), backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;

# vacuum and the transaction ID wraparound
# (autovacuum handles this, but monitor it!)
SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000;

# pessimistic locking (lock before work)
BEGIN;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
  -- now safe to modify
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

# optimistic locking (check version, retry on conflict)
-- uses a version column
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  -- 0 rows = conflict, retry

# SKIP LOCKED (process queue items without blocking)
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;
-- grabs 10 jobs, skips locked ones (other workers don't wait)

# NOWAIT (fail immediately if locked)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock if row is locked

# named lock for coordination
SELECT pg_advisory_lock(hashtext('migrate_users'));
-- only one session can hold this lock
SELECT pg_advisory_unlock(hashtext('migrate_users'));

# simple view
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE active = true;

# use it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';

# view with computed columns
CREATE VIEW user_summary AS
SELECT
  u.id,
  u.username,
  count(p.id) AS post_count,
  max(p.created_at) AS last_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;

# updatable view (simple views can be inserted/updated through)
CREATE VIEW user_emails AS
SELECT id, email FROM users;
INSERT INTO user_emails (id, email) VALUES (1, '[email protected]');  -- works!

# WITH CHECK OPTION (prevent inserts that wouldn't be visible)
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true
WITH CHECK OPTION;
-- INSERT with active=false fails

# modify or drop a view
ALTER VIEW active_users RENAME TO current_users;
DROP VIEW IF EXISTS active_users CASCADE;

# create a materialized view (stores the result)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
  date_trunc('day', created_at) AS day,
  product_id,
  count(*) AS order_count,
  sum(amount) AS total_revenue
FROM orders
GROUP BY 1, 2
WITH DATA;  -- or WITH NO DATA to populate later

# refresh (re-runs the query)
REFRESH MATERIALIZED VIEW sales_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;  -- needs unique index

# create a unique index for concurrent refresh
CREATE UNIQUE INDEX idx_sales_summary_day_product
ON sales_summary(day, product_id);

# schedule refresh (use pg_cron extension or external scheduler)
-- pg_cron: SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary');

# drop
DROP MATERIALIZED VIEW IF EXISTS sales_summary;

# use it (like a regular table)
SELECT * FROM sales_summary WHERE day = '2025-01-01';

# column-level security (hide sensitive columns)
CREATE VIEW public_users AS
SELECT id, username, created_at FROM users;
-- password, email columns are hidden

# row-level security via view
CREATE VIEW user_posts AS
SELECT * FROM posts WHERE user_id = current_setting('app.current_user_id')::int;

-- set the user context per session
SET app.current_user_id = '42';
SELECT * FROM user_posts;  -- only sees user 42's posts

# view with joins for simplified access
CREATE VIEW order_details AS
SELECT
  o.id AS order_id,
  o.created_at,
  u.username,
  p.name AS product_name,
  oi.quantity,
  oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

# grant access to view but not underlying tables
GRANT SELECT ON public_users TO app_readonly;
REVOKE SELECT ON users FROM app_readonly;

# list views
SELECT viewname, definition FROM pg_views WHERE schemaname = 'public';

# get view definition
\d+ view_name  -- in psql

# create or replace (limited — can't change columns)
CREATE OR REPLACE VIEW user_summary AS
SELECT id, username, email FROM users WHERE active = true;
-- can add columns at the end, but can't remove/reorder

# to change columns significantly, drop and recreate
DROP VIEW IF EXISTS user_summary;
CREATE VIEW user_summary AS
SELECT id, username FROM users WHERE active = true;

# view dependencies (what depends on this view)
SELECT dependee.relname AS view, depender.relname AS depends_on
FROM pg_depend d
JOIN pg_class dependee ON d.objid = dependee.oid
JOIN pg_class depender ON d.refobjid = depender.oid
WHERE dependee.relname = 'user_summary';

# refresh all materialized views
DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = 'public' LOOP
    EXECUTE 'REFRESH MATERIALIZED VIEW ' || r.matviewname;
  END LOOP;
END $$;

# recursive view for hierarchical data
CREATE RECURSIVE VIEW employee_tree(id, name, manager_id, level, path) AS
SELECT
  id, name, manager_id, 0, name::text
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
  e.id, e.name, e.manager_id, et.level + 1,
  et.path || ' > ' || e.name
FROM employees e
JOIN employee_tree et ON e.manager_id = et.id;

# query the tree
SELECT * FROM employee_tree ORDER BY path;

# find all subordinates (2 levels deep)
SELECT * FROM employee_tree
WHERE path LIKE 'CEO > Engineering > %' AND level <= 3;

# view for graph traversal (e.g., friend connections)
CREATE RECURSIVE VIEW friend_chain(person1, person2, distance, path) AS
SELECT person1, person2, 1, person1 || '->' || person2
FROM friendships
UNION
SELECT
  fc.person1, f.person2, fc.distance + 1,
  fc.path || '->' || f.person2
FROM friend_chain fc
JOIN friendships f ON fc.person2 = f.person1
WHERE fc.distance < 6;  -- limit depth to prevent infinite loops

# length and position
SELECT length('hello'), char_length('hello'), octet_length('hello');
SELECT position('lo' in 'hello');          -- 4
SELECT strpos('hello', 'lo');               -- 4

# substring and slicing
SELECT substring('hello' from 2 for 3);     -- 'ell'
SELECT substring('hello' from 2);           -- 'ello'
SELECT substr('hello', 2, 3);               -- 'ell'
SELECT left('hello', 3);                    -- 'hel'
SELECT right('hello', 3);                   -- 'llo'

# case and trim
SELECT upper('hello'), lower('HELLO'), initcap('hello world');
SELECT trim('  hello  '), ltrim('xxhello', 'x'), rtrim('helloxx', 'x');
SELECT btrim('xxhelloxx', 'x');             -- trim both sides

# split and join
SELECT split_part('a,b,c', ',', 2);         -- 'b' (1-indexed)
SELECT string_to_array('a,b,c', ',');       -- {a,b,c}
SELECT array_to_string(ARRAY['a','b','c'], ', ');  -- 'a, b, c'

# replace and pad
SELECT replace('hello', 'l', 'L');          -- 'heLLo'
SELECT lpad('5', 3, '0');                   -- '005'
SELECT rpad('5', 3, '0');                   -- '500'
SELECT repeat('ab', 3);                     -- 'ababab'
SELECT reverse('hello');                    -- 'olleh'

# current date/time
SELECT now(), current_timestamp, transaction_timestamp();
SELECT current_date, current_time;

# extract components
SELECT EXTRACT(YEAR FROM now()), EXTRACT(MONTH FROM now());
SELECT date_part('dow', now());     -- day of week (0=Sunday)
SELECT date_part('epoch', now());   -- Unix timestamp

# date arithmetic
SELECT now() + interval '1 day';
SELECT now() - interval '2 hours';
SELECT age('2025-01-01');                    -- interval since date
SELECT age('2025-01-01', '2024-01-01');      -- interval between dates
SELECT date '2025-01-15' - date '2025-01-01'; -- 14 (integer days)

# truncation and rounding
SELECT date_trunc('month', now());           -- first day of month, 00:00
SELECT date_trunc('hour', now());            -- current hour, 00 minutes

# formatting
SELECT to_char(now(), 'YYYY-MM-DD HH:MI:SS');
SELECT to_char(now(), 'Day, DD Mon YYYY');
SELECT to_char(12345.678, 'FM999,999.00');
SELECT to_date('2025/01/15', 'YYYY/MM/DD');
SELECT to_timestamp(1700000000);             -- Unix epoch

# generate series (date ranges)
SELECT generate_series(
  '2025-01-01'::date,
  '2025-01-31'::date,
  '1 day'::interval
);

# rounding
SELECT round(3.14159, 2);    -- 3.14
SELECT ceil(3.1), ceiling(3.1);  -- 4
SELECT floor(3.9);           -- 3
SELECT trunc(3.14159, 2);    -- 3.14 (truncate, no rounding)

# power and roots
SELECT power(2, 10);         -- 1024
SELECT sqrt(16);             -- 4
SELECT cbrt(27);             -- 3
SELECT exp(1);               -- 2.718... (e^x)
SELECT ln(10), log(100);     -- natural log, base-10 log

# trigonometry (radians!)
SELECT sin(radians(90));     -- 1 (convert degrees to radians)
SELECT degrees(1.5708);      -- ~90
SELECT pi();                 -- 3.14159...

# random
SELECT random();             -- 0 to 1
SELECT floor(random() * 100)::int;  -- 0 to 99
SELECT setseed(0.5);         -- seed for reproducibility

# sequences and ranges
SELECT generate_series(1, 10);
SELECT generate_series(1, 10, 2);   -- 1, 3, 5, 7, 9
SELECT generate_series(0, 1, 0.1);  -- 0.0, 0.1, ..., 1.0

# absolute, sign, gcd
SELECT abs(-5), sign(-5);    -- 5, -1
SELECT gcd(12, 8);           -- 4 (PG 13+)
SELECT lcm(12, 8);           -- 24 (PG 13+)

# COALESCE (return first non-NULL)
SELECT COALESCE(nickname, username, email, 'anonymous') FROM users;

# NULLIF (return NULL if two values are equal)
SELECT NULLIF(status, '') FROM users;  -- '' becomes NULL
-- useful to avoid division by zero:
SELECT total / NULLIF(count, 0) FROM stats;  -- NULL instead of error

# GREATEST / LEAST
SELECT GREATEST(1, 2, 3);        -- 3
SELECT LEAST(NULL, 1, 2);        -- NULL (NULLs propagate)
SELECT GREATEST(a, b, c) FROM numbers;

# CASE expressions
SELECT
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age >= 65 THEN 'senior'
    ELSE 'adult'
  END AS age_group
FROM users;

# ISNULL / NOTNULL (PostgreSQL shortcuts)
SELECT * FROM users WHERE email ISNULL;    -- IS NULL
SELECT * FROM users WHERE email NOTNULL;   -- IS NOT NULL

# row comparison
SELECT ROW(1, 2) < ROW(1, 3);  -- true (compares column by column)

# array functions
SELECT array_length(ARRAY[1,2,3], 1);    -- 3
SELECT array_append(ARRAY[1,2], 3);       -- {1,2,3}
SELECT array_prepend(1, ARRAY[2,3]);      -- {1,2,3}
SELECT array_concat(ARRAY[1,2], ARRAY[3,4]); -- {1,2,3,4}
SELECT array_remove(ARRAY[1,2,3], 2);     -- {1,3}
SELECT array_replace(ARRAY[1,2,1], 1, 9); -- {9,2,9}
SELECT unnest(ARRAY[1,2,3]);              -- 3 rows
SELECT array_agg(name) FROM users;        -- aggregate to array

# JSONB functions
SELECT data->'name'           -- jsonb (key access)
SELECT data->>'name'          -- text (key access)
SELECT data#>'{address,city}' -- jsonb (path access)
SELECT data#>>'{address,city}'-- text (path access)
SELECT jsonb_build_object('name', username, 'age', age) FROM users;
SELECT jsonb_object_keys(data) FROM products;  -- keys as rows
SELECT jsonb_array_elements(data->'tags') FROM products;  -- expand array

# JSONB predicates
SELECT data @> '{"active": true}'     -- contains
SELECT data ? 'name'                   -- key exists
SELECT data ?| ARRAY['name','email']   -- any key exists
SELECT data ?& ARRAY['name','email']   -- all keys exist

# modify JSONB
SELECT jsonb_set(data, '{price}', '29.99')
SELECT data || '{"new": true}'         -- merge
SELECT data - 'old_key'                -- remove key
SELECT data #- '{nested,old_key}'      -- remove nested key

# create a trigger function (must return trigger)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# attach the trigger
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

# now any UPDATE on users sets updated_at automatically
UPDATE users SET email = '[email protected]' WHERE id = 1;
-- updated_at is set automatically

# audit log trigger
CREATE OR REPLACE FUNCTION audit_log()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table (table_name, operation, user_name, changed_at, row_data)
  VALUES (
    TG_TABLE_NAME, TG_OP, current_user, now(),
    to_jsonb(OLD)  -- or NEW for insert
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
  AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_log();

# ROW trigger (fires once per affected row)
CREATE TRIGGER log_each_change
  AFTER UPDATE ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION log_price_change();

# STATEMENT trigger (fires once per statement, regardless of rows)
CREATE TRIGGER refresh_summary
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION refresh_sales_summary();

# BEFORE vs AFTER
# BEFORE: can modify NEW, skip the operation (return NULL), or change values
# AFTER: cannot modify the row, used for side effects (logs, notifications)

# conditional trigger (WHEN clause — evaluated per row)
CREATE TRIGGER check_high_value
  BEFORE INSERT ON orders
  FOR EACH ROW
  WHEN (NEW.amount > 10000)
  EXECUTE FUNCTION require_approval();

# transition tables (NEW/OLD as sets, for statement triggers)
CREATE TRIGGER audit_orders
  AFTER INSERT ON orders
  REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT
  EXECUTE FUNCTION log_bulk_insert();

# event triggers fire on DDL events (CREATE, ALTER, DROP)
CREATE OR REPLACE FUNCTION no_drop_table()
RETURNS event_trigger AS $$
BEGIN
  IF tg_event = 'ddl_command_end' AND tg_tag = 'DROP TABLE' THEN
    RAISE EXCEPTION 'Dropping tables is not allowed in production';
  END IF;
END;
$$ LANGUAGE plpgsql;

# register the event trigger
CREATE EVENT TRIGGER protect_tables
  ON ddl_command_end
  WHEN tag IN ('DROP TABLE')
  EXECUTE FUNCTION no_drop_table();

# log all DDL changes
CREATE OR REPLACE FUNCTION log_ddl()
RETURNS event_trigger AS $$
DECLARE
  obj record;
BEGIN
  INSERT INTO ddl_log (event, tag, user_name, object_identity)
  VALUES (tg_event, tg_tag, current_user, NULL);
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER ddl_logger
  ON ddl_command_end
  EXECUTE FUNCTION log_ddl();

# drop an event trigger
DROP EVENT TRIGGER IF EXISTS protect_tables;

# list triggers
SELECT
  event_object_table AS table_name,
  trigger_name,
  action_timing,       -- BEFORE/AFTER/INSTEAD OF
  event_manipulation,  -- INSERT/UPDATE/DELETE
  action_statement     -- function call
FROM information_schema.triggers;

# or use psql
\d+ users  -- shows triggers on the table

# enable/disable triggers
ALTER TABLE users DISABLE TRIGGER set_updated_at;
ALTER TABLE users ENABLE TRIGGER set_updated_at;
ALTER TABLE users DISABLE TRIGGER ALL;    -- all triggers
ALTER TABLE users ENABLE TRIGGER ALL;

# session_replication_role (disable for replication)
SET session_replication_role = 'replica';
-- triggers don't fire (useful for bulk data loads)
SET session_replication_role = 'origin';  -- re-enable

# drop a trigger
DROP TRIGGER IF EXISTS set_updated_at ON users;

# rename a trigger
ALTER TRIGGER set_updated_at ON users RENAME TO update_timestamp;

# 1. Auto-update updated_at
CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

# 2. Audit log (track all changes)
CREATE FUNCTION audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, op, old_data, new_data, changed_by, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP,
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END,
    current_user, now());
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

# 3. Enforce business rules
CREATE FUNCTION check_budget() RETURNS trigger AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM departments d
    WHERE d.id = NEW.dept_id AND d.budget < NEW.amount
  ) THEN
    RAISE EXCEPTION 'Amount exceeds department budget';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# 4. Sync denormalized data
CREATE FUNCTION update_post_count() RETURNS trigger AS $$
BEGIN
  UPDATE users SET post_count = (
    SELECT count(*) FROM posts WHERE user_id = NEW.user_id
  ) WHERE id = NEW.user_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# basic function
CREATE OR REPLACE FUNCTION add(a integer, b integer)
RETURNS integer AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

# function with variables and logic
CREATE OR REPLACE FUNCTION get_user_post_count(user_id integer)
RETURNS integer AS $$
DECLARE
  post_count integer;
BEGIN
  SELECT count(*) INTO post_count
  FROM posts WHERE posts.user_id = get_user_post_count.user_id;

  IF post_count IS NULL THEN
    RETURN 0;
  END IF;

  RETURN post_count;
END;
$$ LANGUAGE plpgsql;

# function returning a table (set-returning)
CREATE OR REPLACE FUNCTION get_recent_posts(limit_count integer DEFAULT 10)
RETURNS TABLE(id integer, title text, created_at timestamptz) AS $$
BEGIN
  RETURN QUERY
  SELECT posts.id, posts.title, posts.created_at
  FROM posts
  ORDER BY posts.created_at DESC
  LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

# call functions
SELECT get_user_post_count(42);
SELECT * FROM get_recent_posts(5);

# IF/ELSIF/ELSE
CREATE OR REPLACE FUNCTION categorize_price(price numeric)
RETURNS text AS $$
BEGIN
  IF price IS NULL THEN RETURN 'unknown';
  ELSIF price < 10 THEN RETURN 'cheap';
  ELSIF price < 50 THEN RETURN 'moderate';
  ELSE RETURN 'expensive';
  END IF;
END;
$$ LANGUAGE plpgsql;

# CASE expression (inside BEGIN...END)
CREATE OR REPLACE FUNCTION get_discount(tier text)
RETURNS numeric AS $$
BEGIN
  RETURN CASE tier
    WHEN 'gold'   THEN 0.20
    WHEN 'silver' THEN 0.10
    WHEN 'bronze' THEN 0.05
    ELSE 0.00
  END;
END;
$$ LANGUAGE plpgsql;

# LOOP (with EXIT)
CREATE OR REPLACE FUNCTION find_first_null(table_name text)
RETURNS integer AS $$
DECLARE i integer := 1; val text;
BEGIN
  LOOP
    EXECUTE format('SELECT col FROM %I WHERE id = %s', table_name, i) INTO val;
    EXIT WHEN val IS NULL;
    i := i + 1;
    EXIT WHEN i > 1000;  -- safety limit
  END LOOP;
  RETURN i;
END;
$$ LANGUAGE plpgsql;

# WHILE and FOR loops
CREATE OR REPLACE FUNCTION sum_n(n integer)
RETURNS integer AS $$
DECLARE total integer := 0;
BEGIN
  FOR i IN 1..n LOOP
    total := total + i;
  END LOOP;
  RETURN total;
END;
$$ LANGUAGE plpgsql;

# PROCEDURE (unlike FUNCTION, can manage transactions)
CREATE OR REPLACE PROCEDURE transfer_money(
  from_account integer,
  to_account integer,
  amount numeric
) AS $$
BEGIN
  -- inside a procedure, we can use COMMIT/ROLLBACK
  UPDATE accounts SET balance = balance - amount WHERE id = from_account;
  UPDATE accounts SET balance = balance + amount WHERE id = to_account;

  -- check for negative balance
  IF EXISTS (SELECT 1 FROM accounts WHERE id = from_account AND balance < 0) THEN
    ROLLBACK;
    RAISE EXCEPTION 'Insufficient funds';
  END IF;

  COMMIT;
END;
$$ LANGUAGE plpgsql;

# call a procedure (CALL, not SELECT)
CALL transfer_money(1, 2, 100.00);

# procedure with output parameters
CREATE OR REPLACE PROCEDURE get_stats(
  OUT user_count integer,
  OUT post_count integer
) AS $$
BEGIN
  SELECT count(*) INTO user_count FROM users;
  SELECT count(*) INTO post_count FROM posts;
END;
$$ LANGUAGE plpgsql;

CALL get_stats(?, ?);  -- output params

# RAISE for messages and exceptions
CREATE OR REPLACE FUNCTION validate_age(age integer)
RETURNS void AS $$
BEGIN
  IF age < 0 THEN
    RAISE EXCEPTION 'Age cannot be negative: %', age
      USING HINT = 'Please provide a valid age (0-150)';
  ELSIF age > 150 THEN
    RAISE EXCEPTION 'Age seems unrealistic: %', age;
  ELSE
    RAISE NOTICE 'Age validated: %', age;
  END IF;
END;
$$ LANGUAGE plpgsql;

# TRY/CATCH with EXCEPTION block
CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric AS $$
DECLARE result numeric;
BEGIN
  result := a / b;
  RETURN result;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Division by zero, returning NULL';
    RETURN NULL;
  WHEN OTHERS THEN
    RAISE NOTICE 'Unexpected error: %', SQLERRM;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

# RAISE levels: DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION
# EXCEPTION aborts the transaction; others just log

# get error details
SELECT sqlstate, message, detail, hint, context
FROM pg_stat_activity;  -- or in exception handler: SQLSTATE, SQLERRM

# EXECUTE for dynamic queries
CREATE OR REPLACE FUNCTION count_rows(table_name text)
RETURNS integer AS $$
DECLARE result integer;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I', table_name) INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

# format() for safe SQL construction
#   %I = identifier (table/column name, quoted)
#   %L = literal (value, quoted)
#   %s = simple string substitution (UNSAFE — avoid for user input)
SELECT format('SELECT * FROM %I WHERE id = %L', 'users', 42);
-- 'SELECT * FROM users WHERE id = 42'

# dynamic SQL with USING for parameters
CREATE OR REPLACE FUNCTION get_user_by(field text, value text)
RETURNS setof users AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT * FROM users WHERE %I = $1', field
  ) USING value;
END;
$$ LANGUAGE plpgsql;

# bulk operations with dynamic SQL
CREATE OR REPLACE FUNCTION archive_old_data(days_old integer)
RETURNS integer AS $$
DECLARE deleted_count integer;
BEGIN
  EXECUTE format(
    'WITH deleted AS (
       DELETE FROM logs WHERE created_at < now() - interval '%s days'
       RETURNING *
     ) SELECT count(*) FROM deleted'
  ) INTO deleted_count USING days_old;

  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

# create a role (users are roles that can log in)
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';
CREATE ROLE analyst WITH LOGIN PASSWORD 'secret' VALID UNTIL '2025-12-31';

# create a role that cannot log in (for grouping)
CREATE ROLE readonly;

# alter a role
ALTER ROLE app_user WITH PASSWORD 'new_secret';
ALTER ROLE app_user WITH VALID UNTIL 'infinity';
ALTER ROLE app_user CONNECTION LIMIT 10;
ALTER ROLE app_user SET search_path TO app_schema, public;

# grant role to another (inherit permissions)
GRANT readonly TO app_user;
GRANT readonly TO analyst;

# view roles
\du  -- in psql
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

# drop a role (must revoke privileges first)
REVOKE readonly FROM app_user;
DROP ROLE app_user;

# grant table privileges
GRANT SELECT, INSERT, UPDATE ON products TO app_user;
GRANT ALL ON products TO admin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

# grant column-level privileges
GRANT SELECT (id, name, email) ON users TO app_user;
-- app_user can only see those columns

# grant schema privileges
GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT CREATE ON SCHEMA app_schema TO developer;

# grant sequence privileges (for serial/identity)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

# grant database privileges
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT CREATE ON DATABASE mydb TO developer;
GRANT TEMPORARY ON DATABASE mydb TO app_user;

# grant function privileges
GRANT EXECUTE ON FUNCTION get_user_post_count(integer) TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

# revoke privileges
REVOKE INSERT, UPDATE ON products FROM app_user;

# alter default privileges (for future objects)
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
  IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

# enable RLS on a table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

# create a policy (users see only their own posts)
CREATE POLICY user_posts ON posts
  FOR SELECT
  USING (user_id = current_setting('app.user_id')::int);

# policy for all operations
CREATE POLICY user_posts_all ON posts
  USING (user_id = current_setting('app.user_id')::int)
  WITH CHECK (user_id = current_setting('app.user_id')::int);

# USING:    filter existing rows (SELECT, UPDATE, DELETE)
# WITH CHECK: validate new rows (INSERT, UPDATE)

# set the user context per session
SET app.user_id = '42';
SELECT * FROM posts;  -- only sees user 42's posts

# bypass RLS (for admins)
ALTER TABLE posts FORCE ROW LEVEL SECURITY;  -- even owner respects RLS
GRANT BYPASSRLS TO admin_role;  -- role-level bypass

# policy for admins (see all)
CREATE POLICY admin_all ON posts
  FOR ALL
  TO admin_role
  USING (true)
  WITH CHECK (true);

# disable RLS
ALTER TABLE posts DISABLE ROW LEVEL SECURITY;

# pg_hba.conf controls who can connect and how
# host  database  user  address  method
#   host all all 127.0.0.1/32 scram-sha-256
#   host all all 0.0.0.0/0   scram-sha-256
#   hostssl all all 0.0.0.0/0 scram-sha-256  # require SSL

# reload pg_hba.conf
SELECT pg_reload_conf();

# authentication methods:
#   trust:       no password (NEVER use in production)
#   md5:         legacy password hash (deprecated)
#   scram-sha-256:  modern password auth (default, recommended)
#   cert:        client certificate
#   peer:        OS username matches DB username (local only)

# set password encryption
SET password_encryption = 'scram-sha-256';
ALTER ROLE app_user PASSWORD 'secret';  -- re-hash with scram

# SSL connections
SHOW ssl;                  -- is SSL enabled?
SHOW ssl_cert_file;
SHOW ssl_key_file;

# connect with SSL
psql "postgresql://user@host/db?sslmode=require"
# sslmode: disable, allow, prefer, require, verify-ca, verify-full

# view active connections
SELECT pid, usename, client_addr, ssl, ssl_cipher
FROM pg_stat_activity;

# encrypt columns with pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
  id serial PRIMARY KEY,
  email text,
  ssn bytea  -- encrypted
);

INSERT INTO users (email, ssn) VALUES
  ('[email protected]', pgp_sym_encrypt('123-45-6789', 'secret_key'));

SELECT pgp_sym_decrypt(ssn, 'secret_key') AS ssn FROM users;

# one-way hashing (passwords)
SELECT crypt('password', gen_salt('bf'));      -- bcrypt
SELECT crypt('password', gen_salt('md5'));     -- MD5 (weak)
-- store the hash, verify with:
SELECT crypt('password', stored_hash) = stored_hash;

# audit with pgAudit extension
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl';
SELECT pg_reload_conf();
-- logs all writes and DDL to the PostgreSQL log

# session auditing
SELECT
  pid, usename, application_name, client_addr,
  query_start, state, query
FROM pg_stat_activity
WHERE state = 'active';

# backup a single database
pg_dump -h localhost -U postgres -d mydb -F c -f mydb.dump
# -F c: custom format (compressed, supports parallel restore)
# -F p: plain SQL (human-readable, default)
# -F d: directory format (parallel, multiple files)

# backup with options
pg_dump -d mydb -F c -f mydb.dump \
  --no-owner           -- don't include ownership commands
  --no-privileges      -- don't include GRANT/REVOKE
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data, no schema
  --schema-only        -- only schema, no data
  --clean              -- include DROP statements
  --if-exists          -- use IF EXISTS in DROPs

# backup all databases
pg_dumpall -h localhost -U postgres -f all_databases.sql
# includes roles, tablespaces, and all databases

# compress a plain SQL dump
pg_dump -d mydb | gzip > mydb.sql.gz

# backup a remote database
pg_dump "postgresql://user:pass@remote-host:5432/mydb" -F c -f mydb.dump

# restore from custom format
pg_restore -h localhost -U postgres -d mydb -F c mydb.dump

# restore with options
pg_restore -d mydb mydb.dump \
  --clean              -- drop objects before recreating
  --if-exists          -- use IF EXISTS in DROPs
  --no-owner           -- don't restore ownership
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data
  --jobs=4             -- parallel restore (4 threads)
  --verbose

# restore to a new database
createdb newdb
pg_restore -d newdb mydb.dump

# restore from plain SQL
psql -d mydb -f mydb.sql
gunzip -c mydb.sql.gz | psql -d mydb

# list contents of a dump
pg_restore -l mydb.dump

# restore specific items
pg_restore -d mydb mydb.dump --table=users --table=posts

# generate SQL to inspect (without restoring)
pg_restore mydb.dump > mydb.sql
pg_restore --list mydb.dump  -- list items with indices

# physical backup with pg_basebackup (online)
pg_basebackup -h localhost -U repluser -D /backup/base \
  -Ft -z -P          -- tar format, compressed, progress
  --wal-method=stream  -- stream WAL files separately

# set up WAL archiving (postgresql.conf)
#   archive_mode = on
#   archive_command = 'cp %p /archive/%f'
SELECT pg_reload_conf();

# Point-in-Time Recovery (PITR):
# 1. Restore base backup
# 2. Create recovery.signal file
# 3. Configure recovery (postgresql.conf or recovery.conf):
#      restore_command = 'cp /archive/%f %p'
#      recovery_target_time = '2025-01-15 14:30:00'
#      recovery_target_action = 'promote'
# 4. Start PostgreSQL — it replays WAL to the target time

# create a recovery point
SELECT pg_create_restore_point('before_migration');

# check WAL archiving status
SELECT * FROM pg_stat_archiver;

# on primary (postgresql.conf):
#   wal_level = replica
#   max_wal_senders = 10
#   hot_standby = on  (for replica)

# create replication role
CREATE ROLE repluser WITH REPLICATION LOGIN PASSWORD 'secret';

# in pg_hba.conf, allow replication connections:
#   host replication repluser 0.0.0.0/0 scram-sha-256

# on replica, create standby using pg_basebackup:
pg_basebackup -h primary-host -U repluser -D /var/lib/postgresql/data \
  -Fp -Xs -P -R
# -R creates standby.signal and configures primary_conninfo

# monitor replication
# on primary:
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_current_wal_lsn();

# on replica:
SELECT * FROM pg_stat_wal_receiver;
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

# promote a replica to primary (failover)
pg_ctl promote -D /var/lib/postgresql/data

# cascading replication
#   replica can also be a primary to other replicas
#   set cascade_level or use primary_conninfo pointing to another replica

# logical replication (PG10+) — replicate specific tables
# on publisher:
CREATE PUBLICATION my_pub FOR TABLE users, posts;

# add more tables to the publication
ALTER PUBLICATION my_pub ADD TABLE comments;

# on subscriber:
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=publisher-host user=repluser dbname=mydb'
  PUBLICATION my_pub;

# initial data copy happens automatically
# subsequent changes are replicated in real-time

# monitor logical replication
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_slots;

# replicate only specific operations
CREATE PUBLICATION insert_only FOR TABLE logs WITH (publish = 'insert');

# control conflict resolution
# (subscriber applies changes; conflicts cause replication to stop)
# handle with: ALTER SUBSCRIPTION ... SKIP or manual resolution

# cross-version replication (e.g., PG14 -> PG16)
# logical replication works across major versions!

# remove
DROP SUBSCRIPTION my_sub;
DROP PUBLICATION my_pub;

# basic EXPLAIN (query plan without running)
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

# EXPLAIN ANALYZE (actually runs the query, shows real stats)
EXPLAIN ANALYZE SELECT * FROM users JOIN posts ON users.id = posts.user_id;

# with buffer stats
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE id = 42;

# with format (JSON, TEXT, XML, YAML)
EXPLAIN (FORMAT JSON) SELECT * FROM users;

# common plan nodes:
#   Seq Scan:        full table scan (slow for large tables)
#   Index Scan:      index lookup + heap fetch
#   Index Only Scan: index-only (fastest, needs covering index)
#   Bitmap Scan:     bitmap of matching rows, then fetch
#   Hash Join:       hash one table, probe with other
#   Nested Loop:     for each left row, scan right (small datasets)
#   Sort:            explicit sort (look for "Sort Method: external merge" = bad)

# warning signs:
#   "Seq Scan on large_table" -> missing index
#   "Sort Method: external disk" -> increase work_mem
#   "Rows Removed by Filter: 999999" -> bad filter, missing index

# VACUUM (reclaim space from dead rows, don't lock)
VACUUM users;            -- regular vacuum
VACUUM FULL users;       -- rewrites table (locks, reclaims to OS)
VACUUM ANALYZE users;    -- vacuum + update statistics
VACUUM (VERBOSE, ANALYZE) users;  -- with output

# autovacuum (should be ON in production)
SHOW autovacuum;  -- should be on
SHOW autovacuum_vacuum_threshold;     -- default 50
SHOW autovacuum_vacuum_scale_factor;  -- default 0.2 (20% dead rows)

# tune autovacuum per table
ALTER TABLE users SET (
  autovacuum_vacuum_scale_factor = 0.1,  -- vacuum at 10% dead rows
  autovacuum_analyze_scale_factor = 0.05
);

# ANALYZE (update planner statistics — critical for query plans)
ANALYZE users;         -- sample the table
ANALYZE;               -- all tables

# check dead tuples (need vacuuming)
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

# check autovacuum activity
SELECT pid, datname, query
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%';

# key memory settings (in postgresql.conf)
SHOW shared_buffers;        -- default 128MB (set to 25% of RAM)
SHOW effective_cache_size;  -- default 4GB (set to 50-75% of RAM)
SHOW work_mem;              -- default 4MB (per-sort/hash memory)
SHOW maintenance_work_mem;  -- default 64MB (for VACUUM, CREATE INDEX)

# recommended production settings (example for 16GB RAM):
#   shared_buffers = 4GB
#   effective_cache_size = 12GB
#   work_mem = 64MB          (careful: per-operation, not global)
#   maintenance_work_mem = 512MB
#   max_connections = 100
#   random_page_cost = 1.1   (for SSDs; default 4.0 is for HDDs)

# check current connections vs max
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
SHOW max_connections;

# WAL settings for write performance
SHOW wal_buffers;        -- default -1 (auto-tuned to 1/32 shared_buffers)
SHOW checkpoint_timeout; -- default 5min
SHOW max_wal_size;       -- default 1GB

# parallel query
SHOW max_parallel_workers;        -- default 8
SHOW max_parallel_workers_per_gather; -- default 2
SET max_parallel_workers_per_gather = 4;  -- more parallelism

# reload config
SELECT pg_reload_conf();

# 1. use indexes (check with EXPLAIN)
# bad:  SELECT * FROM users WHERE lower(email) = '[email protected]'
# good: SELECT * FROM users WHERE email = '[email protected]'
#   (or use an expression index on lower(email))

# 2. avoid SELECT * (reduces I/O, enables index-only scans)
SELECT id, username FROM users WHERE active = true;

# 3. use EXISTS instead of IN for subqueries
-- BAD:  SELECT * FROM users WHERE id IN (SELECT user_id FROM posts)
-- GOOD: SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM posts WHERE user_id = u.id)

# 4. batch inserts (much faster than row-by-row)
INSERT INTO logs (msg) VALUES ('a'), ('b'), ('c');  -- one statement
-- or use COPY for bulk loads:
COPY logs FROM '/path/to/file.csv' WITH (FORMAT csv);

# 5. use CTEs or subqueries for readability, but check performance
# (PG12+ inlines CTEs; before that, CTEs were optimization fences)

# 6. avoid OFFSET for pagination (slow for large offsets)
-- BAD:  SELECT * FROM posts ORDER BY id OFFSET 100000 LIMIT 10
-- GOOD: SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 10

# 7. use partial indexes for common filtered queries
CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;

# 8. analyze after bulk loads
ANALYZE products;  -- update statistics for the planner

# find slow queries (needs pg_stat_statements extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
  query,
  calls,
  mean_exec_time AS avg_ms,
  total_exec_time AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

# currently running queries
SELECT
  pid,
  now() - query_start AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

# long-running transactions (block vacuum)
SELECT
  pid,
  now() - xact_start AS transaction_duration,
  state,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

# table sizes (find bloat candidates)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup, n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

# index usage (find unused indexes)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

# partition by date range (most common for time-series)
CREATE TABLE orders (
  id          bigserial,
  created_at  timestamptz NOT NULL,
  user_id     integer NOT NULL,
  amount      numeric
) PARTITION BY RANGE (created_at);

# create monthly partitions
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

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

# create a default partition (catches anything outside range)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

# insert (automatically routed to correct partition)
INSERT INTO orders (created_at, user_id, amount)
VALUES ('2025-01-15', 1, 100.00);  -- goes to orders_2025_01

# query (partition pruning — only scans relevant partitions)
SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

# sub-partition (e.g., range by date, then hash by user)
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
  PARTITION BY HASH (user_id);

# partition by discrete values (e.g., region, category)
CREATE TABLE users (
  id       serial,
  name     text,
  region   text NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE users_us PARTITION OF users
  FOR VALUES IN ('US', 'CA');

CREATE TABLE users_eu PARTITION OF users
  FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE users_apac PARTITION OF users
  FOR VALUES IN ('CN', 'JP', 'AU', 'SG');

CREATE TABLE users_other PARTITION OF users DEFAULT;

# insert routes automatically
INSERT INTO users (name, region) VALUES ('Alice', 'US');  -- users_us

# query prunes to relevant partition
SELECT * FROM users WHERE region = 'JP';  -- only scans users_apac

# multi-region apps benefit: partition by tenant
CREATE TABLE tenant_data (
  id          serial,
  tenant_id   integer NOT NULL,
  data        jsonb
) PARTITION BY LIST (tenant_id);

CREATE TABLE tenant_1 PARTITION OF tenant_data FOR VALUES IN (1);
CREATE TABLE tenant_2 PARTITION OF tenant_data FOR VALUES IN (2);

# partition by hash (distribute evenly)
CREATE TABLE events (
  id          bigserial,
  user_id     integer NOT NULL,
  event_type  text,
  data        jsonb
) PARTITION BY HASH (user_id);

# create 4 partitions (modulus 4, remainder 0-3)
CREATE TABLE events_0 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_1 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_3 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

# insert distributes by hash of user_id
INSERT INTO events (user_id, event_type) VALUES (42, 'login');  -- routes to a partition

# query by user_id prunes to one partition
SELECT * FROM events WHERE user_id = 42;

# note: range queries on user_id scan ALL partitions
SELECT * FROM events WHERE user_id > 100;  -- scans all 4

# adding partitions later requires recreating (modulus must be power of 2)
# plan partition count upfront!

# detach a partition (keep data, remove from parent)
ALTER TABLE orders DETACH PARTITION orders_2024_01;
-- now orders_2024_01 is a standalone table

# detach and archive
ALTER TABLE orders DETACH PARTITION orders_2024_01;
ALTER TABLE orders_2024_01 RENAME TO orders_archive_2024_01;
-- move to cheaper storage, export, etc.

# attach an existing table as a partition
CREATE TABLE orders_2025_03 (LIKE orders);
ALTER TABLE orders ATTACH PARTITION orders_2025_03
  FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

# move partition to different tablespace (tiered storage)
CREATE TABLESPACE cold_storage LOCATION '/mnt/cold';
ALTER TABLE orders_2024_01 SET TABLESPACE cold_storage;

# create future partitions automatically (pg_partman extension)
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.orders', 'created_at', 'native', 'monthly');
-- pg_partman auto-creates future partitions

# constraint exclusion (older partitioning, avoid for new work)
SET constraint_exclusion = on;  -- default 'partition' is usually fine

# check partition info
SELECT
  inhrelid::regclass AS partition,
  pg_get_expr(c.relpartbound, c.oid) AS partition_bounds
FROM pg_inherits
JOIN pg_class c ON inhrelid = c.oid
WHERE inhparent = 'orders'::regclass;

# when to partition:
#   - table > 100GB (hard to vacuum, slow queries)
#   - time-series data (drop old data fast)
#   - multi-tenant (isolate tenants)
#   - parallel maintenance (vacuum per partition)

# choosing partition key:
#   - most common filter in queries (enables pruning)
#   - time for time-series (created_at, ordered_at)
#   - tenant_id for multi-tenant

# choosing partition type:
#   - RANGE: time-series, ordered data
#   - LIST: discrete categories (region, tenant)
#   - HASH: even distribution, always query by key

# partition count:
#   - too few:  doesn't help (large partitions)
#   - too many: planning overhead (1000+ partitions slow planning)
#   - sweet spot: 50-500 partitions

# indexes on partitioned tables
CREATE INDEX idx_orders_user ON orders(user_id);  -- creates index on all partitions
CREATE INDEX idx_orders_created ON orders(created_at);

# unique constraint must include partition key
CREATE TABLE orders (...) PARTITION BY RANGE (created_at);
ALTER TABLE orders ADD CONSTRAINT unique_order
  UNIQUE (id, created_at);  -- must include created_at!

# foreign keys TO partitioned tables (PG12+)
ALTER TABLE order_items ADD FOREIGN KEY (order_id, order_date)
  REFERENCES orders(id, created_at);

# create a search column (tsvector)
ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
  to_tsvector('english', title || ' ' || body);

# index the search vector (GIN)
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

# search
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & index');

# match any word (OR)
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('postgres | mysql');

# phrase search
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('full text search');

# rank results
SELECT
  title,
  ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query
ORDER BY rank DESC;

# highlight matches
SELECT
  ts_headline('english', body, query) AS highlighted
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query;

# auto-update search_vector with a trigger
CREATE TRIGGER articles_search_update
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);

# JSONB path queries
SELECT data#>>'{address,city}' FROM users;  -- text at path

# JSON Path (PG12+) — powerful JSON querying
SELECT * FROM products
WHERE data @? '$.tags[*] ? (@ == "sale")';

# find products with price > 100 in JSON
SELECT * FROM products
WHERE data @? '$.price ? (@ > 100)';

# extract values with JSON Path
SELECT jsonb_path_query(data, '$.tags[*]') FROM products;

# JSONB aggregation (build JSON from rows)
SELECT
  jsonb_agg(jsonb_build_object('id', id, 'name', name)) AS products
FROM products
WHERE category = 'electronics';

# group by JSON field
SELECT
  data->>'category' AS category,
  count(*) AS count
FROM products
GROUP BY data->>'category'
ORDER BY count DESC;

# JSONB containment and existence
SELECT * FROM products WHERE data @> '{"color": "red"}';
SELECT * FROM products WHERE data ? 'discount';
SELECT * FROM products WHERE data ?| ARRAY['discount', 'sale'];
SELECT * FROM products WHERE data ?& ARRAY['discount', 'sale'];

# JSONB with GIN index for fast queries
CREATE INDEX idx_products_data ON products USING GIN (data jsonb_path_ops);
# jsonb_path_ops: smaller index, supports @> and path operators

-- enable the PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- create a table with geography (lat/lng)
CREATE TABLE places (
  id serial PRIMARY KEY,
  name text,
  location geography(POINT, 4326)
);

-- insert a point (lng, lat — note the order!)
INSERT INTO places (name, location)
VALUES ('Eiffel Tower', ST_MakePoint(2.2945, 48.8584)::geography);

-- find places within 5 km
SELECT name, ST_Distance(location, ST_MakePoint(2.3522, 48.8566)::geography) AS meters
FROM places
WHERE ST_DWithin(location, ST_MakePoint(2.3522, 48.8566)::geography, 5000);

-- bounding box query (uses GiST index)
SELECT name FROM places
WHERE location && ST_MakeEnvelope(2.2, 48.8, 2.4, 48.9, 4326);

-- find the nearest 10 places (KNN query)
SELECT name, location <-> ST_MakePoint(2.35, 48.85)::geography AS dist
FROM places
ORDER BY dist
LIMIT 10;

-- compute the area of a polygon (in square meters)
SELECT ST_Area(ST_GeomFromText(
  'POLYGON((2.3 48.8, 2.4 48.8, 2.4 48.9, 2.3 48.9, 2.3 48.8))', 4326)::geography
) AS sq_meters;

-- enable the postgres_fdw extension (foreign PostgreSQL)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- create a foreign server
CREATE SERVER foreign_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote.host', dbname 'remotedb', port '5432');

-- map a local user to the foreign server
CREATE USER MAPPING FOR local_user
  SERVER foreign_db
  OPTIONS (user 'remote_user', password 'secret');

-- import a schema from the foreign server
IMPORT FOREIGN SCHEMA public
  FROM SERVER foreign_db
  INTO remote_schema;

-- query a foreign table like a local one
SELECT * FROM remote_schema.users WHERE active = true;

-- file_fdw: read CSV files as tables
CREATE EXTENSION file_fdw;
CREATE SERVER csv_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE logs (
  id int, level text, message text, ts timestamp
) SERVER csv_server
  OPTIONS (filename '/var/log/app.csv', format 'csv', header 'true');

-- join local and remote data
SELECT l.id, u.name FROM local_orders l
  JOIN remote_schema.users u ON l.user_id = u.id;

-- acquire a session-level advisory lock (held until session ends)
SELECT pg_advisory_lock(12345);

-- release it
SELECT pg_advisory_unlock(12345);

-- transaction-level lock (auto-released on COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(67890);
-- critical section here
COMMIT;  -- lock auto-released

-- try-lock (non-blocking, returns true/false)
SELECT pg_try_advisory_lock(12345);  -- true if acquired, false if held

-- two-key variant (namespace + id)
SELECT pg_advisory_lock(100, 42);

-- use case: ensure only one worker runs a job
SELECT CASE
  WHEN pg_try_advisory_lock(999) THEN run_job()
  ELSE 'skipped: another worker is running'
END;

-- use case: per-row processing lock
SELECT id, pg_advisory_lock(hashtext(id::text))
FROM tasks WHERE status = 'pending'
LIMIT 10 FOR UPDATE SKIP LOCKED;

-- list all available extensions
SELECT * FROM pg_available_extensions ORDER BY name;

-- list installed extensions
SELECT * FROM pg_extension;

-- install an extension (requires superuser or privileges)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- install in a specific schema
CREATE EXTENSION postgis SCHEMA geo;

-- update an extension to a new version
ALTER EXTENSION pg_stat_statements UPDATE TO '1.10';

-- remove an extension
DROP EXTENSION IF EXISTS postgis CASCADE;

-- check extension versions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

-- enable in postgresql.conf first:
-- shared_preload_libraries = 'pg_stat_statements'
-- then restart PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- find the slowest queries by total time
SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- find queries with highest average time
SELECT query, calls, round(mean_exec_time, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- find queries that scan the most rows
SELECT query, rows, calls, rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 10;

-- reset stats (e.g., after a deploy)
SELECT pg_stat_statements_reset();

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- hash a password with SHA-256
SELECT encode(digest('mypassword', 'sha256'), 'hex');

-- generate a UUID v4
SELECT gen_random_uuid();

-- HMAC (keyed hashing)
SELECT encode(hmac('message', 'secretkey', 'sha256'), 'hex');

-- encrypt data with PGP symmetric encryption
SELECT armor(pgp_sym_encrypt('sensitive data', 'my_passphrase'))
  AS encrypted_blob;

-- decrypt data
SELECT pgp_sym_decrypt(
  dearmor('-----BEGIN PGP MESSAGE-----...-----END PGP MESSAGE-----'),
  'my_passphrase'
) AS decrypted;

-- store encrypted columns
CREATE TABLE secrets (
  id serial PRIMARY KEY,
  data bytea  -- store pgp_sym_encrypt() output here
);

INSERT INTO secrets (data)
VALUES (pgp_sym_encrypt('credit card number', 'passphrase'));

-- built-in UUID v4 (PostgreSQL 13+)
SELECT gen_random_uuid();

-- using uuid-ossp extension (for other UUID versions)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();  -- time-based
SELECT uuid_generate_v4();  -- random
SELECT uuid_generate_v5('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'name'); -- namespace+name

-- UUID primary key
CREATE TABLE events (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  data jsonb,
  created_at timestamptz DEFAULT now()
);

-- identity columns (PostgreSQL 10+, preferred over serial)
CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

-- compare: serial vs identity
-- serial: old, separate sequence, can be overridden
-- identity: SQL standard, tied to table, cleaner

-- get the last inserted ID
INSERT INTO users (name) VALUES ('Alice') RETURNING id;

-- PostGIS: geospatial data
CREATE EXTENSION postgis;

-- pg_trgm: trigram fuzzy text search
CREATE EXTENSION pg_trgm;
SELECT * FROM users WHERE name % 'Jon';  -- fuzzy match
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

-- btree_gin / btree_gist: B-tree support in GIN/GiST
CREATE EXTENSION btree_gin;

-- hstore: key-value store (legacy, use jsonb instead)
CREATE EXTENSION hstore;

-- pg_partman: partition management
CREATE EXTENSION pg_partman;

-- timescaledb: time-series optimization (third-party)
-- citus: distributed PostgreSQL (third-party)

-- intarray: integer array operations
CREATE EXTENSION intarray;
SELECT * FROM posts WHERE tags && ARRAY[1,2,3];

-- unaccent: remove accents for search
CREATE EXTENSION unaccent;
SELECT unaccent('café');  -- 'cafe'

-- see all active queries
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  wait_event_type,
  wait_event,
  query_start,
  now() - query_start AS duration,
  query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- find long-running queries (> 5 minutes)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

-- count connections by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- terminate a runaway query (use with caution!)
SELECT pg_cancel_backend(12345);     -- cancel the query
SELECT pg_terminate_backend(12345);  -- kill the session

-- find idle-in-transaction sessions (hold locks)
SELECT pid, usename, xact_start,
  now() - xact_start AS xact_duration,
  query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

-- see all locks
SELECT
  pid,
  relation::regclass AS table_name,
  mode,
  granted,
  query
FROM pg_locks
WHERE granted = false;

-- find blocking chains (who blocks whom)
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks ul ON ul.locktype = bl.locktype
  AND ul.relation IS NOT DISTINCT FROM bl.relation
  AND ul.granted
JOIN pg_stat_activity blocking ON ul.pid = blocking.pid;

-- lock modes: AccessShare, RowShare, RowExclusive,
--   ShareUpdateExclusive, Share, ShareRowExclusive,
--   Exclusive, AccessExclusive

-- check for table-level locks
SELECT relation::regclass, mode, pid, granted
FROM pg_locks
WHERE locktype = 'relation' AND granted = false;

-- set a lock timeout for a session
SET lock_timeout = '5s';
SET statement_timeout = '30s';

-- check table bloat (dead tuples)
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
  last_autovacuum,
  last_manual_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- manual vacuum (analyze updates planner stats)
VACUUM ANALYZE users;
VACUUM FULL users;  -- reclaims space, locks table!

-- check autovacuum settings
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;

-- tune autovacuum per table
ALTER TABLE big_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

-- check if any transactions prevent vacuum
SELECT pid, age(clock_timestamp(), xact_start) AS xact_age, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
ORDER BY xact_age DESC;

-- check wraparound risk (transaction ID age)
SELECT
  relname,
  age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind IN ('r', 't', 'm')
ORDER BY xid_age DESC LIMIT 10;

-- in postgresql.conf, enable slow query logging:
-- log_min_duration_statement = 100  -- log queries > 100ms
-- log_line_prefix = '%t [%p] %u@%d '
-- log_lock_waits = on
-- log_temp_files = 0
-- log_autovacuum_min_duration = 0

-- check current log settings
SHOW log_min_duration_statement;
SHOW log_destination;

-- view server logs (if using CSV logging)
CREATE FOREIGN TABLE pg_log (
  log_time timestamp,
  user_name text,
  database_name text,
  process_id int,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos int,
  context text,
  query text,
  query_pos int,
  location text,
  file_name text,
  file_line_num int,
  application_name text
) SERVER pglog
  OPTIONS (filename '/var/log/postgresql/postgres.csv', format 'csv');

-- query logs with SQL
SELECT log_time, error_severity, message
FROM pg_log
WHERE error_severity = 'ERROR'
ORDER BY log_time DESC LIMIT 20;

-- database size
SELECT pg_size_pretty(pg_database_size('mydb'));

-- table sizes (with indexes)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- cache hit ratio (should be > 99%)
SELECT
  sum(heap_blks_hit) AS hits,
  sum(heap_blks_read) AS reads,
  round(sum(heap_blks_hit)::numeric /
    NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) AS hit_ratio_pct
FROM pg_statio_user_tables;

-- index usage ratio
SELECT
  sum(idx_scan) AS index_scans,
  sum(seq_scan) AS seq_scans,
  round(sum(idx_scan)::numeric / NULLIF(sum(idx_scan) + sum(seq_scan), 0) * 100, 2) AS index_usage_pct
FROM pg_stat_user_tables;

-- connection stats
SELECT count(*) AS total,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity;

-- uptime and version
SELECT version();
SELECT pg_postmaster_start_time();